How to configure an "on edit" trigger on a file created automatically (with a google script)?

Question

I have followed the following tutorial : https://developers.google.com/apps-script/storing_data_spreadsheets#writing-1.

First, I have a source spreadsheet with the following columns ( First Name, Last Name and Department) and associated data (see a screenshot of the source file here : https://developers.google.com/apps-script/images/reading_spreadsheet_data_image1.jpg).

Then, I dynamically create one Sheet per department name (see tutorial code) : every sheet contains the information about employees in that department. (see tutorial screenshot).

I have customized this tutorial to create, dynamically, one Spreadsheet per departement instead of one Sheet per department.

When each child Spreadsheet is modified, i want to launch an "On Edit" trigger. This "On Edit" trigger will re-generated the source spreadsheet with all child spreasheets data.

I have tested this functionnality but it seems that i can't program the creation of an "OnEdit" trigger on a distant spreadsheet (execution validation issue). It seems that only a manual creation (of the "On Edit" trigger) is possible.

Is it possible to create, dynamically with Google Script, a trigger on a distant spreadsheet ?

Best Regards

Mogsdad Mogsdad · Accepted Answer · 2013-06-04T21:01:53

You can create a trigger function that handles events from remote spreadsheets or forms. See Class Triggerbuilder.

Your trigger function must be accessible to the script creating the trigger - in other words, it must be part of the same script.

var ssKey = 'XXXXXXXXXXXXXXXXXXXXX';

function myFunction() {
  ScriptApp.newTrigger('myOnEdit')
           .forSpreadsheet(ssKey)
           .onEdit()
           .create();
}

function myOnEdit(e) {
  Logger.log(JSON.stringify(e));
}

Of course, you'll want to do something useful in the trigger function, but this is just an example.

There is an issue to be aware of. This remote function will not receive the documented source property documented under "Spreadsheet Edit Events" in Understanding Events. See Issue 2856, and star it for updates.

Why does that matter? If that worked, you MIGHT be able to have the same trigger function registered to handle events from ALL your spreadsheets, and use the source property to work on one sheet at a time.

How to configure an "on edit" trigger on a file created automatically (with a google script)?

1 Answers