Google Apps Script - Scripts in copied spreadsheet not retaining trigger

Question

I am using a script in a Google Spreadsheet to, upon form submission, copy an existing spreadsheet and google form to create a new spreadsheet and new form and then connect the new form to the new spreadsheet so the new spreadsheet is receiving the responses from the new form.

The script in the copied spreadsheet is copied to the new spreadsheet, but the installed triggers don't exist. Is there a way to create those triggers from the original spreadsheet's script (the spreadsheet that received the form submission that created the new SS and form) or do I need to rely upon non-installed triggers in the new spreadsheet to create the installed trigger?

Answer 1

Triggers run scripts which require authorization by the user. Because your script is bound to a spreadsheet, it will need to be authorized on each copy.

I have a similar system (copies of a master sheet + code) and we addressed this by adding a custom menu to run a script when someone makes a copy. I added a custom menu and a setup script which would authorize the trigger.

function onOpen(e) {
  var ui = SpreadsheetApp.getUi().createMenu("PGP Setup").addItem("Run", "setup").addToUi();
}

function setup() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('makeDocs')
    .timeBased()
    .everyHours(1)
    .create();
}

It's easy on the user who created the sheet and has been reliable for us so far.

Answer 2

I was able to solve my issue with the code below. The function 'newSSTrigger' is in the original script (not a copied one) and is called after the new SS and Form are created and sync'd - this is where the the variable idOfNewSS comes from. The trigger will not create a script in the new objects or even be seen in the new object's scripts as an installed trigger. To find the trigger go to Edit>All Your Triggers from any script. Triggers not greyed out are attached to the document in some way.

This seems to have two benefits: 1) I never have to deal with any permissions - the triggers work without me touching the new docs at all. 2) If I update the 'myFunction' (below) it changes how the existing triggers work because the trigger retrieves its instructions from the original script - in its current state. This means I can update all existing triggers created by this script just by editing this function.

function newSSTrigger(idOfNewSS) {
  var newSS = SpreadsheetApp.openById(idOfNewSS);
  ScriptApp.newTrigger("myFunction")
    .forSpreadsheet(newSS)
    .onFormSubmit()
    .create();
}

function myFunction() {
  do stuff...
}

Answer 3

A different option that would work for people who are not just copying a sheet programmatically (or who are anticipating users making copies that the developer doesn't have permission to edit) is to take care of it for the user inside the bound script. You could use code similar to this:

function onOpen(){

var triggers = ScriptApp.getProjectTriggers();
if(triggers.length == 0){
ScriptApp.newTrigger('yourFunction')
      .timeBased()
      .everyHours(1)
      .create();
      }

This says that if there are no triggers, add a time-based trigger. You can see this documentation for variations.