When i put on global variables the trigger onOpen() doesn't work

Question

When i put on global variables the trigger onOpen() doesn't work. do you know if there is a solution for that? Thank you!

 /**variables globales */
 var elLibro = SpreadsheetApp.openById("1pfbd7oziXmBTsOh5RhfvhsjW5wU6QF9suN7SNrE8rew");
 var laHoja = elLibro.getSheetByName("Cuotas");//obtiene la hoja
 var laHojaTablaFinal = elLibro.getSheetByName("Tabla final");

function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}

when i delete my global variables the trigger onOpen works good. i'm trying to Add a custom menu to the spreadsheet.

Answer 1

The onOpen(e) function is a simple trigger that will run automatically each time the spreadsheet is opened. In that context, API methods that require authorization are not available.

When a script function is called by the spreadsheet, globals get evaluated before the function runs. In the onOpen(e) context, SpreadsheetApp.openById() will error out because of the lacking authorization, and onOpen(e) never gets the chance to run.

One way to solve this is to put your globals inside a function, say getSettings_(), that returns an object that contains the often used variables, and call it from the functions that use those values. It could be something like this:

let settings;

function getSettings_() {
  if (settings) {
    return settings;
  }
  settings = {
    elLibro: SpreadsheetApp.openById("1pfbd7oziXmBTsOh5RhfvhsjW5wU6QF9suN7SNrE8rew"),
    laHoja: elLibro.getSheetByName("Cuotas"),
    laHojaTablaFinal: elLibro.getSheetByName("Tabla final"),
  };
  return settings;
}

function someFunction(someParameter) {
  const settings = getSettings_();
  const someValue = settings.laHoja.getRange('A1');
  console.log(someValue);
}

function onOpen(e) {
  // ... does not call getSettings_()
  // ...
}

This pattern has the benefit that functions that require those globals can get them easily, while functions that do not require those globals do not need to get them and will thus run faster.

The values in settings are only set once. When multiple functions call getSettings_() in turn, the first call fills the settings object, and subsequent calls just get a reference to that same copy of the object. Possible runtime modifications to settings are seen by all functions within the same runtime instance.

Answer 2

Using global variables only when you need them:

const gobj={get init(){delete this.init;this.globals=getGlobals();this.init=PropertiesService.getScriptProperties().getProperties();}};
 readInit();

function readInit() {
  gobj.init;
}

Using Lazy Load