Installable Triggers Being Generated in Wrong Spreadsheet

1
votes

My script currently auto-generates spreadsheets (itemization lists) from a source template sheet, and a hyperlink to the new sheet is placed on a master list spreadsheet (list of jobs). The end goal is for the itemization lists to edit quantity values on a separate inventory spreadsheet.

I had originally encountered a problem with opening other spreadsheets in the script using a simple trigger (onEdit(e)) due to a Google authentication limitation, but this was fixed by changing it to an installable trigger (thanks to ross for the solution).

I figured out how to automatically create new installable triggers thanks to Cooper. I added a function to the master spreadsheet script that automatically generates an installable trigger on a sheet whose ID is passed into the function.

The new spreadsheet is generated correctly, and the new installable trigger is generated correctly. However, the new trigger is being created on the original, MASTER LIST spreadsheet, not the NEW spreadsheet. The execution transcript in particular is confusing me, because it says it's creating the trigger on the correct spreadsheet. However, the trigger indeed does not appear on the new spreadsheet and on the Google 'All my Triggers' page it has a new trigger assigned to the MASTER LIST.

[19-06-07 16:32:45:190 PDT] [19-06-07 22:43:51:832 EDT] TriggerBuilder.forSpreadsheet([This is the ID of the new sheet- the correct ID, but it's actually doing this for the current Spreadsheet instead]) [0 seconds]

The Code (please understand all file/folder ID's have been removed):

function main(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = e.range;
  var newId;
  var newName;
  var hyperlinkString;

  //Check if edit occurred in relevant range
  if((range.getColumn() !== 1) && (range.getColumn() !== 2)) return;

  //Check if both columns were filled after edit
  if((range.getColumn() == 1) && (range.offset(0,1).isBlank() == true)) return;
  if((range.getColumn() == 2) && (range.offset(0,-1).isBlank() == true)) return;

  //Get new document name from concatenate formula in column H
  if(range.getColumn() == 1) newName = range.offset(0,7).getValue();
  if(range.getColumn() == 2) newName = range.offset(0,6).getValue();

  //Check whether the edits occurred on the jobs list or receptions list (indicated by '2' or '3' in L1)
  //Calls function to create new job sheet or reception sheet from template, gets ID of new spreadsheet
  if(((range.getColumn() == 1) && (range.offset(0,3).isBlank() == true)) || ((range.getColumn() == 2) && (range.offset(0,2).isBlank() == true))) { 
    if(sheet.getRange('L1').getValue() == 2) newId = newJob();
    if(sheet.getRange('L1').getValue() == 3) newId = newReception();
  }
  else {
    if(range.getColumn() == 1) {
      hyperlinkString = range.offset(0,3).getFormula();
      newId = hyperlinkString.substring(77,121);
    }
    if(range.getColumn() == 2) {
      hyperlinkString = range.offset(0,2).getFormula();
      newId = hyperlinkString.substring(77,121);
    }
  }

  //Set the name of the new spreadsheet
  SpreadsheetApp.openById(newId).rename(newName);

  //Enter name and date information onto new spreadsheet
  if(range.getColumn() == 1) {
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B1').setValue(range.getValue());
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B2').setValue(range.offset(0,1).getValue());
  }
  if(range.getColumn() == 2) {
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B1').setValue(range.offset(0,-1).getValue());
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B2').setValue(range.getValue());
  }


  //Creates hyperlink to new spreadsheet
  if (range.getColumn() == 1) range.offset(0,3).setFormula("=HYPERLINK(\"" + SpreadsheetApp.openById(newId).getUrl() +"\",\"Click here for itemization\")");
  if (range.getColumn() == 2) range.offset(0,2).setFormula("=HYPERLINK(\"" + SpreadsheetApp.openById(newId).getUrl() +"\",\"Click here for itemization\")");

  //Sort list descending from most recent date
  sheet.getRange("A3:D1000").sort({column: 2, ascending: false});
}

function newJob() {
  //Open template
  var jobTemplateSS = SpreadsheetApp.openById("ID");

  //Create new spreadsheet from copy of template spreadsheet
  var newSS = jobTemplateSS.copy("Untitled Job");

  //Get folder
  var jobFolder = DriveApp.getFolderById("ID");

  //Get ID of new file
  var newSSFile = DriveApp.getFileById(newSS.getId());

  //Copy file to the correct directory and delete the instance created in root
  jobFolder.addFile(newSSFile);
  DriveApp.getRootFolder().removeFile(newSSFile);

  createInstallableTrigger('IncrementDecrement',newSS.getId());

  //Pass ID of new spreadsheet back to calling function
  return(newSS.getId());
} 

function newReception() {
  //Open template
  var receptionTemplateSS = SpreadsheetApp.openById("ID");

  //Create new spreadsheet from copy of template spreadsheet
  var newSS = receptionTemplateSS.copy("Untitled Reception");

  //Get folder
  var receptionFolder = DriveApp.getFolderById("ID");

  //Get ID of new file
  var newSSFile = DriveApp.getFileById(newSS.getId());

  //Copy file to the correct directory and delete the instance created in root
  receptionFolder.addFile(newSSFile);
  DriveApp.getRootFolder().removeFile(newSSFile);

  createInstallableTrigger('IncrementDecrement',newSS.getId());

  //Pass ID of new spreadsheet back to calling function
  return(newSS.getId());
}

function createInstallableTrigger(funcName,ssId) {
  Logger.log("1");
  if(!isTrigger()) {
    ScriptApp.newTrigger(funcName).forSpreadsheet(ssId).onEdit().create();
  }
}

function isTrigger(funcName){
  Logger.log("2");
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Full Execution Transcript:

[19-06-07 22:43:46:036 EDT] Starting execution
[19-06-07 22:43:46:041 EDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-06-07 22:43:46:042 EDT] Spreadsheet.getActiveSheet() [0 seconds]
[19-06-07 22:43:46:043 EDT] SpreadsheetApp.getActiveRange() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getRow() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getLastRow() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getLastColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:044 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:044 EDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 22:43:46:194 EDT] Range.isBlank() [0.149 seconds]
[19-06-07 22:43:46:194 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:194 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.offset([0, 6]) [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:196 EDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 22:43:46:196 EDT] Range.isBlank() [0 seconds]
[19-06-07 22:43:46:196 EDT] Sheet.getRange([L1]) [0 seconds]
[19-06-07 22:43:46:335 EDT] Range.getValue() [0.138 seconds]
[19-06-07 22:43:46:452 EDT] SpreadsheetApp.openById([ID]) [0.117 seconds]
[19-06-07 22:43:49:422 EDT] Spreadsheet.copy([Untitled Job]) [2.969 seconds]
[19-06-07 22:43:49:581 EDT] DriveApp.getFolderById([ID]) [0.157 seconds]
[19-06-07 22:43:49:581 EDT] Spreadsheet.getId() [0 seconds]
[19-06-07 22:43:49:769 EDT] DriveApp.getFileById([ID]) [0.188 seconds]
[19-06-07 22:43:49:770 EDT] File.getId() [0 seconds]
[19-06-07 22:43:51:126 EDT] Folder.addFile([Untitled Job]) [1.356 seconds]
[19-06-07 22:43:51:275 EDT] DriveApp.getRootFolder() [0.148 seconds]
[19-06-07 22:43:51:275 EDT] File.getId() [0 seconds]
[19-06-07 22:43:51:827 EDT] Folder.removeFile([Untitled Job]) [0.551 seconds]
[19-06-07 22:43:51:827 EDT] Spreadsheet.getId() [0 seconds]
[19-06-07 22:43:51:829 EDT] Logger.log([1, []]) [0 seconds]
[19-06-07 22:43:51:829 EDT] Logger.log([2, []]) [0 seconds]
[19-06-07 22:43:51:831 EDT] ScriptApp.newTrigger([IncrementDecrement]) [0 seconds]
[19-06-07 22:43:51:832 EDT] TriggerBuilder.forSpreadsheet([This is the ID of the new sheet- the correct ID]) [0 seconds]
[19-06-07 22:43:51:832 EDT] SpreadsheetTriggerBuilder.onEdit() [0 seconds]
[19-06-07 22:43:52:356 EDT] SpreadsheetTriggerBuilder.create() [0.523 seconds]
[19-06-07 22:43:52:356 EDT] Spreadsheet.getId() [0 seconds]
[19-06-07 22:43:52:357 EDT] Sheet.getRange([L1]) [0 seconds]
[19-06-07 22:43:52:357 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:52:470 EDT] SpreadsheetApp.openById([ID]) [0.112 seconds]
[19-06-07 22:43:52:780 EDT] Spreadsheet.rename([Name]) [0.31 seconds]
[19-06-07 22:43:52:781 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:781 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:782 EDT] SpreadsheetApp.openById([ID]) [0 seconds]
[19-06-07 22:43:52:783 EDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 22:43:52:783 EDT] Sheet.getRange([B1]) [0 seconds]
[19-06-07 22:43:52:784 EDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 22:43:52:784 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:52:785 EDT] Range.setValue([Name]) [0 seconds]
[19-06-07 22:43:52:785 EDT] SpreadsheetApp.openById([ID]) [0 seconds]
[19-06-07 22:43:52:786 EDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 22:43:52:786 EDT] Sheet.getRange([B2]) [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.setValue([2/30/19]) [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:788 EDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 22:43:52:788 EDT] SpreadsheetApp.openById([ID]) [0 seconds]
[19-06-07 22:43:52:788 EDT] Spreadsheet.getUrl() [0 seconds]
[19-06-07 22:43:52:905 EDT] Range.setFormula([=HYPERLINK("Link","Click here for itemization")]) [0.116 seconds]
[19-06-07 22:43:52:906 EDT] Sheet.getRange([A3:D1000]) [0 seconds]
[19-06-07 22:43:52:908 EDT] Range.sort([{column=2.0, ascending=false}]) [0 seconds]
[19-06-07 22:43:53:266 EDT] Execution succeeded [6.868 seconds total runtime]
1
google-apps-scriptgoogle-sheets
I suspect that you'll get two trigger events if you name you function onEdit() one from the installable and one for the simple trigger. – Cooper
To avoid further confusion, I went ahead and renamed the function from onEdit() to main(). The problem still persists however. – Alex S.
I think this makes perfect sense. You create the trigger within the execution context of the script tied to your master sheet (the one that contains all the scopes and the one you authorized to modify Drive files on your behalf). I suspect this context is also shared by your 'IncrementDecrement' handler function. If triggers were attached to the external file, the other spreadsheet wouldn't know how to call the handler function because it doesn't share runtime environment with the master sheet. – Anton Dementiev
In my case, the trigger was correctly executed on the external file but triggers themselves are attached to the cloud project that you execute your code from. – Anton Dementiev

1 Answers

1
votes

Update: It appears that this is either:

-A bug, in which case I've posted a bug report

-An issue with unclear documentation: the Google documentation for ScriptApp.newTrigger.forSpreadsheet(ID) states that this "Creates and returns a SpreadsheetTriggerBuilder tied to the spreadsheet with the given ID." If Google's intent was for the trigger to activate by edits on the spreadsheet with the given ID, this should be reflected in the documentation. Instead, it's ambiguous and my understanding when I read it was that the new trigger would be added to the triggers list of the spreadsheet with the given ID.

If there are any further suggestions or takes on this, feel free to post. Thanks.