SpreadsheetApp.openById displays error when run via the onOpen command

Question

I suspect this is a simple fix but I'm having problems in my Google App Script, I have read a number of posts that indicate these functions are deprecated in custom functions but I'm lost how to fix.

I have a script that is run automatically when the Google Sheet is opened using the onOpen() function. I tried to move the OpenByID() method inside this function but get the same error. Can someone tell me how to fix this specifically please.

function onOpen(e) {
//function test(e) {
  // Add a custom menu to the spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var masterSheet=ss.getSheetByName(masterTab);

  for(var xLOSName = 3; xLOSName <= 4; xLOSName++) 
  {
    Logger.log('Attempting to open file');
    var fileId = masterSheet.getRange(xLOSName,2).getValue();
    Logger.log('Opening File ID: ' + fileId);
    var xLOSSpreadsheet  = SpreadsheetApp.openById(masterSheet.getRange(xLOSName,2).getValue());

    //var xLOSSpreadsheet  = SpreadsheetApp.openById(masterSheet.getRange(xLOSName,2).getValue()).getSheets()[0];
    Logger.log(xLOSSpreadsheet.getName());
  }

NOTE: It never get to the point of logging the name of the file. Stupidly, this works when running from the debugger. Regards

Answer 1

You cannot use SpreadsheetApp.openById() in custom functions. It's not just deprecated it's not allowed.

Restrictions on Custom Functions

Also simple triggers like onOpen() cannot perform operations that require permission.

Simple Triggers Restrictions

If you require operations that require permission you must use installable triggers.

Answer 2

have you tried running that same code but from a custom function different from onOpen()? I have had similar problems that were in the end limitations on the onOpen() and onEdit() functions, and I was also looking for sintax errors and such...