create and bind onEdit script to spreadsheet from inside form-bound script

Question

I'm having a bit of trouble with this code, this is a script bound within a Google Form and triggered on Form Submit:

function onFormSubmit(e) {

  //these lines are to get the email address that was entered into the form
  var familyResponses = e.response.getItemResponses();
  var familyEmailAddress = familyResponses[0].getResponse();

  //this loads the template spreadsheet
  templateSpreadsheetFile = DriveApp.getFileById("xxxxxxxx");

  //this generates an 8-digit random number
  var eightDigitCode = Math.floor(Math.random() * (99999999 - 10000000 + 1)) + 10000000;

  //this makes a copy of the template spreadsheet, with the 8-digit code as the name of the copy, in the specified folder.
  var FamilyWorksheetsFolder = DriveApp.getFolderById("yyyyyyyy");
  var newSpreadsheetFile = templateSpreadsheetFile.makeCopy(eightDigitCode, FamilyWorksheetsFolder);
  newSpreadsheetFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT); //this allows anyone with the link to view+edit the new file

  //now we need to add a script to the new spreadsheet, that makes a copy every time it is edited
  ScriptApp.newTrigger('familyWorksheetScript')
      .forSpreadsheet(newSpreadsheetFile)
      .onEdit()
      .create();

  //these lines build the subject and body of the email
  var emailSubject = "link to your worksheet";
  var emailBody = "Here is the link to your worksheet: \n" + newSpreadsheetFile.getUrl() + "\n\n\
Your spreadsheet is named with your unique 8-digit code " + eightDigitCode

  //send the email
  GmailApp.sendEmail(familyEmailAddress, emailSubject, emailBody);

}

function familyWorksheetScript() {  
}

==============================================

The idea here is pretty simple:

family fills out form (one question: what's your email address?) and submits
onFormSubmit script runs (installed trigger), gets email address, generates random 8-digit code, makes a copy of a template spreadsheet, the copy is named with the 8-digit code. puts it in the right folder, and sets the permissions.
then the family is emailed with a link to the spreadsheet

All the above works. But I would now like to add the feature, from within this form-bound script, to create an on-edit triggered script bound to the new spreadsheet (copy of template, named with 8-digit code). And this is the part that I can't get to work. It's the ScriptApp.newTrigger code block, when I comment it out, the whole script runs fine I get the email at the end. But when I leave in the ScriptApp.newTrigger code uncommented, the script dies right at that spot. I can tell it's dying there because the new spreadsheet still gets created, but the email doesn't get sent. I don't know why it isn't working and I don't know how to troubleshoot it.

Any help would be much appreciated. Thanks!

This is the error message I get in the notification email: Start Function Error Message Trigger End 10/29/16 5:40 PM onFormSubmit Bad value (line 22, file "Code") formSubmit 10/29/16 5:40 PM — The Food Nerd

Serge insas Serge insas · Accepted Answer · 2016-10-29T21:02:20

You can't creat a trigger on a sheet that would act as the new sheet user, everything you create belongs to you ! the triggered function would run as "you", not the new user.

What I would suggest id to create an "onOpen" function with a UI that would ask for the new user to click on a "button" to run a function that would create the onEdit trigger, asking them for explicit authorization.

Edit

below is a sample code with an onEdit trigger working on the copy of the active spreadsheet, just for test purpose.

function createNewCopy(){
  var ss = SpreadsheetApp.getActive();
  var newSs = DriveApp.getFileById(ss.getId()).makeCopy('copyOf-'+ss.getName());
  var nSs = SpreadsheetApp.openById(newSs.getId());
  var trigger = ScriptApp.newTrigger('onEdit').forSpreadsheet(nSs.getId()).onEdit().create();
}

the onEdit in the original SS is as simple as that, just to check it works :

function onEdit(){
  Browser.msgBox('hello');
}

note that no trigger will be viewable in the spreadsheet's script editor ressource tab, it will only appear in your own triggered function list in your Google account.

create and bind onEdit script to spreadsheet from inside form-bound script

1 Answers

Edit