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.

CodePudding user response：

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, the SpreadsheetApp.openById() will error out because of the lacking authorization.

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);
}

This has many of the benefits of using globals — the values are only set once, can be used by all the functions that run in the correct context, and possible modifications are seen by all functions within the same runtime instance. When a function does not use the settings, it will run faster, because those values do not get set.

CodePudding user response：

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