OnOpen Troubleshooting

[Chris Howard]

I'm working on a script in a template.  When the template file is opened, it should make a copy of itself and rename that file to the name provided by the user from a popup box.  When I've been debugging/running this in the editor, it works fine.  When I started testing from the user's perspective, it doesn't finish the script.  It prompts the user for the name, then stops.  Since the editor isn't open, there's no log i can review.  I was wondering if anyone knew certain things that wouldn't work via the OnOpen() function or why this code would work when executing it from the editor, but not when it's triggered by opening the file.

From what i can tell, it stops shortly after 

  var response = ui.prompt("Enter the Partner's name:");

Below is a tweaked version of my script

function onOpen(){

 var ss = SpreadsheetApp.getActiveSpreadsheet();

  if(ss.getName() == "Assessment Tool")

  {

    newCopyAndEmail()

  }

}

function newCopyAndEmail(){

  

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var t1 = ss.getSheetByName('Tier 1 Questionnaire');

  var lists = ss.getSheetByName('Lists');

  var ui = SpreadsheetApp.getUi(); 

  var response = ui.prompt("Enter the Partner's name:");

   

  //new wait message

  var output = HtmlService

    .createHtmlOutput('<b style="text-align:center">' + "Creating " +  response.getResponseText() + "'s Assessment Tool.</b>")

    .setWidth(700)

    .setHeight(200); 

  SpreadsheetApp.getUi().showModalDialog(output, 'Please wait...');

  

  var destFolder = DriveApp.getFolderById("folder");

  var newFile = DriveApp.getFileById(ss.getId()).makeCopy(response.getResponseText() + " - Assessment Tool", destFolder); 

  var output = HtmlService.createHtmlOutput('<script>google.script.host.close();</script>');

  const email = Session.getActiveUser().getEmail();

                                                             

  var spreadsheetId = newFile.getId();

  var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId;

  var html = "<script>window.open('" + url + "');google.script.host.close();</script>";

  //opens new file in browser

  var userInterface = HtmlService

    .createHtmlOutput(html)

    .setWidth(700)

    .setHeight(200);   

  SpreadsheetApp.getUi().showModalDialog(userInterface, "Opening " + response.getResponseText() + " - Assessment Tool...");                                                              

                                                                

                                                               

  

  //increase ID of PAT

  var currentID = lists.getRange("B2").getValue()

  currentID = currentID + 1

  lists.getRange("B2").setValue(currentID)

  

  //set Client based on response  

  var newFile2 = SpreadsheetApp.openByUrl(url + "/edit");

  var newFileT1 = newFile2.getSheetByName('Tier 1 Questionnaire');

  var newFileT2 = newFile2.getSheetByName('Tier 2 Questionnaire');

  var newFileInsr = newFile2.getSheetByName('Instructions');

  var newFileWait = newFile2.getSheetByName('Please Wait');

  newFileT1.getRange("C3").setValue(response.getResponseText())

  newFileT2.getRange("C3").setValue(response.getResponseText())

  

  //show Instructions

  newFileInsr.showSheet();

  newFileWait.hideSheet();

  

  

  

  const subject = `Assessment Tool for ${newFile.getName()}`;                                                           

  var link = newFile.getUrl();

  var body = 'Click <a href="'+ link + '">this link</a> to continue the Assessment Tool!'

 

  if (MailApp.getRemainingDailyQuota() > 0)

    GmailApp.sendEmail(email, subject, body, {

      htmlBody: body

    });

  

  var htmlOutput = HtmlService

    .createHtmlOutput('A Parternship Assessment Tool has been created for ' + response.getResponseText() + '.  <br /><br />You have also been sent an email with the link to the new file.<br /><br /> Please close this tab.')

    .setWidth(300)

    .setHeight(300);

  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Assessment Tool File Created')

  

  

}

Many thanks for any insights this group can offer.

Thanks,

Chris

[Alan Wells]

The function name onOpen() is a reserved function name. The name is reserved for the document open event.  It doesn't need to be installed.  It's a simple trigger.  Because simple triggers run without authorization, they can not execute lines of code that require permission from the user.  onOpen() can not run:

var ss = SpreadsheetApp.getActiveSpreadsheet();

If you use an installed "Open" trigger with a function name of something other than onOpen, then the code can run lines of code that have been authorized by the user.

So, change the name of onOpen to something else, and install an On Open trigger.  That can be done with code.  The user needs to first authorize the code to run.  It would be a security hole if there was a way to run code that accessed the users account without them approving it.