Google-sheets – onOpen doesn’t execute

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I've got a google spreadsheet I created yesterday and used Tools -> Script Editor to create a function in a single file called Code.gs

function onOpen(e) {

  var email = (Session.getEffectiveUser().getEmail());
  var targetsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var mastersheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/.../edit#gid=...").getSheetByName("Master");
  var masterrange;
  var targetrange = targetsheet.getRange(2, 5, 1000);

  // when opened, we will grab the data from the master sheet and feed it into the target sheet.
  switch (email){
      case 'me@mydomain.com':
      masterrange = mastersheet.getRange(2, 21, 1000)
      break;  

  };

  targetrange.setValues(masterrange.getValues());
  Logger.log(masterrange.getValues());
}

So I can manually run this function and it does pull the data from mastersheet and populates the target sheet.

But if I change the mastersheet and close/re-open the target sheet, the update is not reflected.

If I delete the data from the target sheet and close/re-open, it's not populated on opening.

Am I missing something here? Just seems like the functions will not run at all.

My understanding of triggers is that I shouldn't have to do anything special to bind the onOpen(e) function as a trigger.

Best Answer

Simple Triggers, such as onOpen, are subject to certain restrictions:

  • They cannot access services that require authorization.
  • They can modify the file they are bound to, but cannot access other files because that would require authorization.
  • They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.

You were trying to get the email of EffectiveUser (which may or may not be possible) and also to access another spreadsheet (which is certainly impossible).

(If you wonder why this restriction is in place, imagine what would happen if someone shared with you a document with onOpen attached, which would access your other files.)

The solution is to use an installable trigger.

Related Topic