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?
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('