Google Scripts - trigger and function in different sheets

Question

I need to:

create a new sheet
call the main function
set a trigger that will call the main function

Basically this:

function new_campaign(){
    var sheet_name = new_sheet();
    main_function(sheet_name);
    trigger(sheet_name);
}

With a single sheet there would be no problem because I could set before the sheet name as a global variable. The problem is that I have to create multiple sheets and all have to keep on working.

I created the function of the new sheet so that it's returning the name of the sheet, so I can call the main_function passing the sheet. Unfortunately for the trigger is not that easy because I don't understand how to pass the sheet.

function trigger(sheetName) {
  ScriptApp.newTrigger("main_function")
  .timeBased()
  .everyMinutes(1)
  .create();
}

UPDATE

As suggested, I tried tu use PropertiesService mapping the ID of the trigger with the parameter I need to use in the function (which in my case is the sheetName)

function trigger(sheetName) {
  var triggerID = ScriptApp.newTrigger("main_function")
  .timeBased()
  .everyMinutes(1)
  .create()
  .getUniqueId();
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty(triggerID, sheetName);
}

UPDATE 2

I tried to extract the trigger ID from the event object to get the sheetName from the scriptProperties. Now my main_function look like this

function main_function() {
    var e = arguments[0];
    var scriptProperties = PropertiesService.getScriptProperties();
    var sheetName = scriptProperties.getProperty(e.triggerUid);
}

You can't pass parameters to trigger functions - Google does this (as part of passing the event object). You could, however, use the trigger ID and PropertiesService to store a map between the ID and the sheet it should work with. — tehhowch
Thank you for the answer! :) I found this, but I cannot figure out how to use it in my case. stackoverflow.com/questions/33360803/… How do I obtain trigger ID and how to I map it with the sheet? Sorry, but I'm new to this. Code (also simplified) would be really appreciated! :) — Davide
You get the trigger ID from the created trigger: developers.google.com/apps-script/reference/script/trigger You use PropertiesService by setting a key and a value - the value would be the parameters your function needs, and the key is the trigger ID. In your triggered function, you use the event object and look up the stored value for the trigger ID in the event object. Apps Script documentation on triggers, event objects, and Properties Service should have what you need to write a script - you can get better help once you have that effort to share. — tehhowch
Thank you very much! really appreciate the help! I updated the original question with the code changed. One problem still remain though. You said to use event objects, so I should do something like: event.triggerUid The problem is that I don't know how to find the event object and the docs makes an example receiving the event object. developers.google.com/apps-script/guides/triggers/events How can I call the event object? Thank you!! :) — Davide
Your triggered function (e.g. main_function, onEdit, etc.) receives the event object as an input argument automatically. You can refer to it in the triggered function as either arguments[0], or, if you specified a parameter name, that parameter name (e.g. evobj in function foo(evobj) { /** code */}). Google will handle creating the event object each time the trigger activates. — tehhowch

Davide Davide · Accepted Answer · 2018-08-21T14:07:38

SOLUTION

There is a function which handles the other functions and the trigger:

function new_campaign(){
    var sheet_name = new_sheet(); //function to create a new sheet
    main_function(); //to execute main_function without delay the first time
    trigger(sheet_name); //set the trigger
}

trigger function:

function trigger(sheetName) {
  var triggerID = ScriptApp.newTrigger("main_function")
  .timeBased()
  .everyMinutes(1)
  .create()
  .getUniqueId();
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty(triggerID, sheetName); //association between sheetName and triggerID to call it back
}

and the main_function function:

function main_function() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName;

    var e = arguments[0]; //argument[0] contains the event object

    //if else to handle the first case
    //if the event object is defined, it extract the sheetName associated to the triggerID in the ScriptProperties
    //otherwise take the active sheet
    if (!(e == undefined)){
        var scriptProperties = PropertiesService.getScriptProperties();
        sheetName = scriptProperties.getProperty(e.triggerUid);
    }else{
        var sheet = ss.getActiveSheet();
        sheetName = sheet.getSheetName();
    }

    var sheet = ss.getSheetByName(sheetName);
    //...
}

Google Scripts - trigger and function in different sheets

1 Answers