Apps Script with Google Sheets : how to execute ScriptApp.newTrigger?

Question

I have a general Google Sheets document title "Roadmap". I want to use a copy of it to share will all my students. Eg. "John Doe Roadmap", "Jame Smith Roadmap"... And I want datas sent to a webhook (for Zapier) when someone work on one of the sheets, to update automatically a sheet with "Students Progress". For that, I need the code to be triggered each time the sheet is modified.

❌ I tried with triggers menu : but the trigger is not copied with the Spreadsheet when I create a copy.

❌ I tried with a simple trigger (onEdit()) : it's not authorised and I need to go in the code and execute it a First time manually to add authorisation. It's not good as I need to automate the process.

It seems the solution is to use an installed trigger. I added it in the code below... But... How to have the installation of the trigger... automatically triggered ?

The code is well copied with the copies of the main document, but if I don't go to the code to execute manually the function createEditTrigger(), the trigger is not installed and the code isn't triggered when someone modify the copied document. I don't know how to do. Here's my code:

function createEditTrigger() {
 ScriptApp.newTrigger("sendZap")
   .forSpreadsheet(SpreadsheetApp.getActive())
   .onEdit()
   .create();
}
function sendZap() {
  let ss = SpreadsheetApp.getActive();
  let activeSheet = SpreadsheetApp.getActiveSheet();
  var month = activeSheet.getName();
  var sh1=ss.getSheetByName('1er mois');
  var emailMember = sh1.getRange(5, 4).getValue();
  let data = {
    'currentMonth': month,
    'email': emailMember,
  };
  const params = {
    'method': 'POST',
    'contentType': 'application/json',
    'payload': JSON.stringify(data)
  }
  
  let res = UrlFetchApp.fetch('https://hooks.zapier.com/hooks/catch/XXXXXXXX/', params)
  SpreadsheetApp.getUi().alert("month: " + month + " email: " + emailMember);
}

Thank you.

Update Perhaps it doesn't work because, with programatically added trigger too it asks for permission when I run the function (in the code window). How to avoid this authorisation as it's only used with my own account for all ss? Said differently: when I save a copy of the ss, it saves the code attached too. But how can I copy the triggers too?

Currently you are running this code on every edit of ss. Check your executions. Also check your triggers to be sure you only have one trigger. You should check for other triggers of that name before creating another in createEditTrigger — Cooper
– Cooper, Commented Jul 12, 2022 at 15:32
Doing a a fetch in an onEdit and then launching an alert seems like an incredible nuisance to the user considering it's going to run on every edit of the spreadsheet — Cooper
– Cooper, Commented Jul 12, 2022 at 15:36
The alert is here only to test the script when I modify the spreadsheet. It will be removed for final use. I don't understand what you mean about trigger name: all I have to create the trigger is in this code. — PatriceK
– PatriceK, Commented Jul 12, 2022 at 15:40
The createEditTrigger function creates an onEdit trigger and it calls it's handFunction whose name in sendZap. It's best to check if there are other triggers whose handlerFunction is sendZap before creating another one because having multiple onEdit triggers for the same function can cause problems that are difficult to resolve. — Cooper
– Cooper, Commented Jul 12, 2022 at 15:58
Keep in mind if you have the trigger created then it is going to run that function on every edit of the spreadsheet unless you limit it to a specific sheet, range or whatever. — Cooper
– Cooper, Commented Jul 12, 2022 at 15:59

Mike Steelson · Accepted Answer · 2022-07-12 16:54:21Z

3

Maybe you can try this way. The goal is to make a menu and in this way, ask to activate the trigger if it is not already active.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('🌟 Menu 🌟')
    .addItem('👉 Activate', 'activate')
    .addToUi();
}
function activate(){
  myTriggerSetup()
  SpreadsheetApp.getActive().toast('your script is now active !')
}
function myTriggerSetup() {
  if(!isTrigger('sendZap')) {
    ScriptApp.newTrigger('sendZap')
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onEdit()
      .create();
  }
}
function isTrigger(funcName) {
     var r=false;
     if(funcName) {
       var allTriggers=ScriptApp.getProjectTriggers();
       var allHandlers=[];
       for(var i=0;i<allTriggers.length;i++) {
         allHandlers.push(allTriggers[i].getHandlerFunction());
       }
       if(allHandlers.indexOf(funcName)>-1) {
         r=true;
       }
     }
     return r;
}

answered Jul 12, 2022 at 16:54

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PatriceK Over a year ago

Thank you Mike ! This solution add a menu to activate the trigger, it's true... But with a big warning saying the app is using private data from user's account... It will be frightening for the user, and it's not true: the ss is on my account, not user's one, and only date and one cell already filled before the copy are sent to the webhook. But perhaps, it's just impossible to have a better solution?

Mike Steelson Over a year ago

Sorry, I wasn't aware of that. Not sure to find another solution.

Collectives™ on Stack Overflow

Apps Script with Google Sheets : how to execute ScriptApp.newTrigger?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related