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
}