Google-sheets – onOpen Simple Trigger Failure

google sheetsgoogle-apps-script

I have a spreadsheet that serves as an often-copied template within my organization. The template contains a script that (onOpen) accesses data on a separate spreadsheet (a master database) and sets those values on a tab called "admin." The desired result is to have a copy of the master database living within the template sheet (and every subsequent copy of the template sheet). At this point, there are thousands of copies of the template sheet, each running that same script.

The onOpen trigger functioned for months, and every time anyone within my organization opened one of the template copies, an updated version of the master database was set in the admin tab. However, now onOpen has stopped working, and our thousands of sheets relying on it are failing to function properly. I can force the script to run from the script editor, and in that case, it works well. The onOpen simple trigger is the problem—and seemingly out of nowhere. Anyone have any ideas?

All users have editing access to the master database.

function onOpen() {
 var spreadsheet = SpreadsheetApp.getActive();//gets active spreadsheet

var source = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/redactedID/edit');//identifies master database by its URL
 var sourceSheet = source.getSheetByName('ItemMaster')//identifies main sheet within master database
 var admin = source.getSheetByName('admin')//identifies admin sheet within master database

 var item = admin.getRange("B5").getValue();//gets location (column #) of "Item" column based on match function
 var portion = admin.getRange("B10").getValue();//gets location (column #) of "Portion" column based on match function
 var CT = admin.getRange("B11").getValue();//gets location (column #) of "CT" column based on match function
 var LB = admin.getRange("B12").getValue();//gets location (column #) of "LB" column based on match function
 var OZ = admin.getRange("B13").getValue();//gets location (column #) of "OZ" column based on match function
 var GRAM = admin.getRange("B14").getValue();//gets location (column #) of "Gram" column based on match function

 var target = SpreadsheetApp.getActive().getSheetByName("admin");//identifies target, or where data should go after it's copied from master database
 var itemTargetRange = target.getRange(1,4,sourceSheet.getLastRow());//identifies specific range for specific data
 var itemSourceValues = sourceSheet.getRange(1,item,sourceSheet.getLastRow()).getValues();
 itemTargetRange.clearContent();//clears existing values to prepare range for updated values
 itemTargetRange.setValues(itemSourceValues);//sets new values

 var portionTargetRange = target.getRange(1,5,sourceSheet.getLastRow());//same as above
 var portionSourceValues = sourceSheet.getRange(1,portion,sourceSheet.getLastRow()).getValues();
 portionTargetRange.clearContent();
 portionTargetRange.setValues(portionSourceValues); 

 var CTTargetRange = target.getRange(1,6,sourceSheet.getLastRow());//same as above
 var CTSourceValues = sourceSheet.getRange(1,CT,sourceSheet.getLastRow()).getValues();
 CTTargetRange.clearContent();
 CTTargetRange.setValues(CTSourceValues); 

 var LBTargetRange = target.getRange(1,7,sourceSheet.getLastRow());//same as above
 var LBSourceValues = sourceSheet.getRange(1,LB,sourceSheet.getLastRow()).getValues();
 LBTargetRange.clearContent();
 LBTargetRange.setValues(LBSourceValues); 

 var OZTargetRange = target.getRange(1,8,sourceSheet.getLastRow());//same as above
 var OZSourceValues = sourceSheet.getRange(1,OZ,sourceSheet.getLastRow()).getValues();
 OZTargetRange.clearContent();
 OZTargetRange.setValues(OZSourceValues); 

 var gramTargetRange = target.getRange(1,9,sourceSheet.getLastRow());//same as above
 var gramSourceValues = sourceSheet.getRange(1,GRAM,sourceSheet.getLastRow()).getValues();
 gramTargetRange.clearContent();
 gramTargetRange.setValues(gramSourceValues); 

 var recipeCostSheet = SpreadsheetApp.getActive().getSheetByName("recipeCost");//identifies different sheet within current spreadsheet
 var unitValidationRange = recipeCostSheet.getRange('D11:D29');//identifies range of cells that need data validation rules
 var unitRange = admin.getRange("F1:Z1");//identifies range to use for data validation rules
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(unitRange,false).build();//defines rule
 unitValidationRange.setDataValidation(rule);//sets rule

 }

Best Answer

According to Simple triggers, they can't call services that require authorization to run, that explains why the script run from the Apps Script Editor but not when the spreadsheet is opened.

In order to make it able to run on open, considering that onOpen is a reserved word for simple triggers, change it's name then create a installable trigger.

If you have thousands of template copies that include the script on the question and should be updated too, bear in mind that bounded scripts can't be programmatically edited so you if you want to update them, it should be done manually.

Related Topic