0

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).

My new problem is that while a script will be copied along with the rest of the template, the installable triggers aren't. So, while the template spreadsheet has the correct installable trigger I need in order to modify the inventory spreadsheet, none of the auto-generated copies of the template have this.

I need to find either: -A way to automatically create the installable triggers when a new spreadsheet is generated -Some alternative way that lets the script modify other spreadsheets without encountering authentication issues like a simple trigger would

Suggestions welcomed and appreciated. Thank you!

To clarify: onEdit(e) is the name of a function triggered by an installable trigger, and all file/folder ID's have been removed. The code:

  function onEdit(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;
}

Execution transcript:

[19-06-07 16:32:41:011 PDT] Starting execution
[19-06-07 16:32:41:017 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-06-07 16:32:41:017 PDT] Spreadsheet.getActiveSheet() [0 seconds]
[19-06-07 16:32:41:018 PDT] SpreadsheetApp.getActiveRange() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getRow() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getLastRow() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getLastColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 16:32:41:149 PDT] Range.isBlank() [0.129 seconds]
[19-06-07 16:32:41:149 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:149 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.offset([0, 6]) [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:151 PDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 16:32:41:151 PDT] Range.isBlank() [0 seconds]
[19-06-07 16:32:41:153 PDT] Sheet.getRange([L1]) [0.001 seconds]
[19-06-07 16:32:41:269 PDT] Range.getValue() [0.116 seconds]
[19-06-07 16:32:41:367 PDT] SpreadsheetApp.openById([ID Removed]) [0.097 seconds]
[19-06-07 16:32:43:431 PDT] Spreadsheet.copy([Untitled Job]) [2.063 seconds]
[19-06-07 16:32:43:572 PDT] DriveApp.getFolderById([ID Removed]) [0.14 seconds]
[19-06-07 16:32:43:573 PDT] Spreadsheet.getId() [0 seconds]
[19-06-07 16:32:43:770 PDT] DriveApp.getFileById([ID Removed]) [0.197 seconds]
[19-06-07 16:32:43:771 PDT] File.getId() [0 seconds]
[19-06-07 16:32:44:529 PDT] Folder.addFile([Untitled Job]) [0.758 seconds]
[19-06-07 16:32:44:695 PDT] DriveApp.getRootFolder() [0.165 seconds]
[19-06-07 16:32:44:695 PDT] File.getId() [0 seconds]
[19-06-07 16:32:45:186 PDT] Folder.removeFile([Untitled Job]) [0.49 seconds]
[19-06-07 16:32:45:186 PDT] Spreadsheet.getId() [0 seconds]
[19-06-07 16:32:45:187 PDT] Logger.log([1, []]) [0 seconds]
[19-06-07 16:32:45:188 PDT] Logger.log([2, []]) [0 seconds]
[19-06-07 16:32:45:189 PDT] ScriptApp.newTrigger([IncrementDecrement]) [0 seconds]
[19-06-07 16:32:45:190 PDT] TriggerBuilder.forSpreadsheet([This is the ID of the new sheet- the correct ID]) [0 seconds]
[19-06-07 16:32:45:190 PDT] SpreadsheetTriggerBuilder.onEdit() [0 seconds]
[19-06-07 16:32:45:709 PDT] SpreadsheetTriggerBuilder.create() [0.518 seconds]
[19-06-07 16:32:45:709 PDT] Spreadsheet.getId() [0 seconds]
[19-06-07 16:32:45:710 PDT] Sheet.getRange([L1]) [0 seconds]
[19-06-07 16:32:45:710 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:45:794 PDT] SpreadsheetApp.openById([ID Removed]) [0.083 seconds]
[19-06-07 16:32:46:079 PDT] Spreadsheet.rename([Name Removed]) [0.284 seconds]
[19-06-07 16:32:46:079 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:079 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:080 PDT] SpreadsheetApp.openById([ID Removed]) [0 seconds]
[19-06-07 16:32:46:080 PDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 16:32:46:081 PDT] Sheet.getRange([B1]) [0 seconds]
[19-06-07 16:32:46:081 PDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 16:32:46:082 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:46:082 PDT] Range.setValue([Name Removed]) [0 seconds]
[19-06-07 16:32:46:083 PDT] SpreadsheetApp.openById([ID Removed]) [0 seconds]
[19-06-07 16:32:46:083 PDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 16:32:46:084 PDT] Sheet.getRange([B2]) [0 seconds]
[19-06-07 16:32:46:084 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:46:084 PDT] Range.setValue([2/24/19]) [0 seconds]
[19-06-07 16:32:46:084 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:085 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:085 PDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 16:32:46:086 PDT] SpreadsheetApp.openById([ID Removed]) [0 seconds]
[19-06-07 16:32:46:086 PDT] Spreadsheet.getUrl() [0 seconds]
[19-06-07 16:32:46:168 PDT] Range.setFormula([=HYPERLINK("Link removed","Click here for itemization")]) [0.081 seconds]
[19-06-07 16:32:46:169 PDT] Sheet.getRange([A3:D1000]) [0 seconds]
[19-06-07 16:32:46:170 PDT] Range.sort([{column=2.0, ascending=false}]) [0 seconds]
[19-06-07 16:32:46:488 PDT] Execution succeeded [5.155 seconds total runtime]
Alex S.
  • 61
  • 7

1 Answers1

0

You can do something like this:

function createInstallableTrigger(funcname) {
  if(!isTrigger(funcname)) {
    ScriptApp.newTrigger(funcname).forSpreadsheet('SpreadsheetId').onEdit().create();
  }
}

function isTrigger(funcName){
  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;
}

The isTrigger function just keeps you from installing more than one trigger for the same function.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Something weird is happening. It's creating an installable trigger, but on the original (master list) spreadsheet. The weird part is that the execution transcript shows the correct ID of the new spreadsheet (the one for which the trigger is intended for). [19-06-07 11:36:57:337 PDT] ScriptApp.newTrigger([IncrementDecrement]) [0 seconds] [19-06-07 11:36:57:338 PDT] TriggerBuilder.forSpreadsheet([The correct ID]) [0 seconds] [19-06-07 11:36:57:339 PDT] SpreadsheetTriggerBuilder.onEdit() [0 seconds] [19-06-07 11:36:57:935 PDT] SpreadsheetTriggerBuilder.create() [0.596 seconds] – Alex S. Jun 07 '19 at 18:47
  • I added an argument to createInstallableTrigger for the new spreadsheet ID, then invoked the function with createInstallableTrigger('IncrementDecrement',newSS.getId()); – Alex S. Jun 07 '19 at 18:49
  • What's the problem now? – Cooper Jun 07 '19 at 23:52
  • isTrigger needs the funcname. I forgot to put that in. – Cooper Jun 07 '19 at 23:55
  • You will need to rename your onEdit function to whatever your going to call the function now. – Cooper Jun 07 '19 at 23:57
  • So, let me clarify. This is the script on the "master list" sheet (the list of jobs). It's the script generating the new sheet. When a new sheet is generated, I'm calling your createInstallableTrigger function to create an installable trigger on the new sheet. The script on the new sheet (copied from template) has a function called IncrementDecrement, so my line createInstallableTrigger('IncrementDecrement',newSS.getId()); passes in the funcName and the ID of the new spreadsheet. – Alex S. Jun 08 '19 at 00:03
  • The problem is that while an installable trigger IS being created correctly, and a new spreadsheet IS being generated correctly, the installable trigger is being created on the "master list" spreadsheet, NOT the new spreadsheet. This is strange because the execution transcript shows the installable trigger being generated on the ID of the new spreadsheet, even though that's not what's happening. – Alex S. Jun 08 '19 at 00:04
  • The code that you posted has the name of the function as onEdit(). I hope you have changed that. – Cooper Jun 08 '19 at 00:07
  • The code I posted is the code from ONLY the original spreadsheet, not the new one. The name is irrelevant, because the function still works. An installable trigger I've already created on this spreadsheet is for a function called onEdit. – Alex S. Jun 08 '19 at 00:10
  • The function on the NEW spreadsheet, the one I'm using your functions to create a new installable trigger on, is called IncrementDecrement, which I've passed in as an argument to your function. – Alex S. Jun 08 '19 at 00:11
  • Are you saying I should place your functions on my template spreadsheet script (script for the new spreadsheet) instead? – Alex S. Jun 08 '19 at 00:12
  • That's what I'd do. But I have to admit that I hardly ever use triggers. – Cooper Jun 08 '19 at 00:17
  • The immediate issue I can think of with using that approach is triggering the function to create the installable trigger. Maybe a simple onOpen trigger for createInstallableTrigger on the new spreadsheet would be my best option? – Alex S. Jun 08 '19 at 00:21
  • I think I'd ask this portion of the question again to open it up to the rest of the volunteers just to get some more opinions on the matter. I can tell you I'm not the most knowledgeable volunteer on this site. – Cooper Jun 08 '19 at 00:23
  • Ok, thanks for all your help. You’ve gotten me a lot closer. – Alex S. Jun 08 '19 at 00:32