Function based trigger using Sheet instead of Spreadsheet failing-CodePudding

I have two form feeding into the same sheet and I need different triggers for each sheet. Tried to trigger using function based triggers but that is not working.

This function works:

function testTrigger() {
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
}

This one fails:

function testTriggerTwo() {
  var sheet = SpreadsheetApp.openById("...").getSheetByName("Responses");
  ScriptApp.newTrigger("myFunction")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
}

with

"Exception: The parameters (SpreadsheetApp.Sheet) don't match the method signature for ScriptApp.TriggerBuilder.forSpreadsheet."

Any ideas?

CodePudding user response：

function testTrigger() {
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction")
    .forSpreadsheet(sheet)//this sheet is a class Spreadsheet
    .create();
}

This one fails:

function testTriggerTwo() {
  var sheet = SpreadsheetApp.openById("...").getSheetByName("Responses");
  ScriptApp.newTrigger("myFunction")
    .forSpreadsheet(sheet)//this sheet is a class Sheet
    .onChange()
    .create();
}

One of the problems in dealing with Google Apps Script and Google in general is that the people that sell products decided to begin to use the term Sheet as being synonymous with Spreadsheet but in the Apps Documentation Sheet refers to Excel like worksheet and I guess Spreadsheet refers to the excel like workbook. One is the file and the other is a tab. In the second version of the function you are using a Class Sheet when you should be using a Class Spreadsheet.

I would recommend using something like this for trigger creation to avoid the possibility of creating more that one trigger for any given function which can produce issues that are hard to debug.

function testTrigger() {
  const ss = SpreadsheetApp.getActive();
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "myFunction").length == 0) {
    ScriptApp.newTrigger("myFunction")
      .forSpreadsheet(ss)
      .onChange()
      .create();
  }
}

By checking that none of the project project triggers have a handler with the same function name you can insure that you don't have two triggers for the same function.

CodePudding user response：

You can control it by getting the the sheet name (where the response is landing)

function onFormSubmit(e) {
  var sheet = e.range.getSheet().getName();
  //do whatever you want by comparing the sheet name
}

You can have only one onFormSubmit Trigger. But, you can do different things by an if statement