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

Time:07-13

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 Asking for authorisation 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?

CodePudding user response:

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('           
  • Related