Installing time based trigger upon creating a new spreadsheet

Question

I have a spreadsheet that I'm using as a template for a purchase order log. This spreadsheet has a scripted onOpen(e) function that automatically creates a timed trigger for another function that downloads the spreadsheet as an XLS file to a specific shared drive.

The purpose of the trigger is to automate the download process so any copied spreadsheet data is not lost due to human error.

My problem is that when a user makes a copy of the template, the onOpen(e) function will not run due to the fact that you have to manually authorize said user to run scripts for the new spreadsheet.

Is there any way to automate script authorization?

function onOpen(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssn = ss.getId();
  var ssf = DriveApp.getFileById(ssn);
  var test = ss.getOwner().getEmail();
  var tName = ScriptApp.getProjectTriggers();
 
  if (test !=  "itadmin@company.com") {
  
    if (tName.length > 0) {
      ScriptApp.deleteTrigger(tName.pop());
    }

    ScriptApp.newTrigger('downloadXLS')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(1)
      .create();

    ss.addEditor("itadmin@company.com");
    ssf.setOwner("itadmin@company.com");
  }
}

I guess if he tries to execute this function he will be prompted a window to authorize the application. But if you want to fully automate it, then I am afraid the answer is no. — Marios, Nov 12 '20 at 16:38

Rubén · Answer 1 · 2022-03-31T20:16:17.483

The onOpen simple trigger is executed every time a spreadsheet is opened. I think that it's a bad choice for creating a trigger "upon creating a new spreadsheet" by the other hand there is no way to automate the authorization of a bounded script.

Another approach is to create an add-on. One of the advantages of this approach is that the authorization will be required the first time the script is installed and when the script scopes be changed.

Another advantage is that a single script will be used by all the spreadsheets instead of having a copy of the original script on each spreadsheet.

Resource

https://developers.google.com/gsuite/add-ons/overview

Assign permissions for bound script to access google services for a user while copying spreadsheet to new folder

Installing time based trigger upon creating a new spreadsheet

1 Answers1

Related