Is ss.getNamedRanges() faster than ss.getRangeByName("Range1") for using multiple ranges as 'global' variables?

Question

I am trying to declare some 'global variables' for spreadsheet automation in Google Scripts. I know that Google Scripts runs everything every time something is changed (including the so called 'global variables'), so I'm trying to limit my calls to the spreadsheet. I have upwards of 100 or so named ranges (though most probably won't be needed).

I'm mostly using these variables to make my life easier when changing the spreadsheet (we're currently using it as a production schedule with a lot of extra features). There's a lot modification for newly requested automation features and I'd like to try and avoid having to change things in multiple places at once.

I would like to know if making an array with ss.getNamedRanges() and then subsequently finding the range I need using its name (as below) is considered multiple calls to the spreadsheet or just one?

const ss = SpreadsheetApp.getActive();
const namedRanges = ss.getNamedRanges();

const sch_itemName = find(r => r.getName() == "sch_itemName").getRange().getColumn();
const sch_itemNum = find(r => r.getName() == "sch_itemNum").getRange().getColumn();
const task_taskDesc = find(r => r.getName() == "task_taskDesc").getRange().getColumn();
//... etc.

Is that method any faster or slower than finding the ranges using the getRangeByName function (as below)

const ss = SpreadsheetApp.getActive();

const sch_itemName = ss.getRangeByName("sch_itemName").getColumn();
const sch_itemNum = ss.getRangeByName("sch_itemNum").getColumn();
const task_taskDesc = ss.getRangeByName("task_taskDesc").getColumn();
//... etc.

If neither of these are efficient, any suggestions for what might be better? Should I bother using global variables at all? Thank you in advance!

Edit: I was looking at best practices (developers.google.com/apps-script/guides/support/best-practices) and am trying to understand when something is considered a call to the spreadsheet (which is bad practice when done excessively). Edit: Changed terminology to be more consistent.

Answer 1

If you need to get more than one named range, ss.getNamedRanges() will be faster than using multiple ss.getRangeByName() calls.

The performance of any Apps Script function will typically be dominated by the number of API calls to SpreadsheetApp and other such classes or endpoints. Often, the grand majority of execution time is spent in getValue() type of calls. See the best practices and custom function optimization tips.

You are asking this in the context of using global variables to easily access named ranges. Using global variables only makes sense if all or most of the functions you call from custom functions, custom menu items or triggers actually use named ranges. If they do, you may still want to minimize the number of global variables to make the code easier to maintain.

Instead of using multiple global variables, you can get an object that encapsulates references to all named ranges in the spreadsheet like this:

let NAMED_RANGES = {};
initializeGlobals_();

function initializeGlobals_() {
  NAMED_RANGES = getNamedRangesAsObject_();
}

/**
* Gets an object that contains references to
* all named ranges in the spreadsheet.
*/
function getNamedRangesAsObject_() {
  const ss = SpreadsheetApp.getActive();
  const namedRanges = ss.getNamedRanges();
  const ranges = {};
  namedRanges.forEach(namedRange => {
    const range = namedRange.getRange();
    ranges[namedRange.getName()] = {
      range: range,
      rangeA1: range.getA1Notation(),
      rowStart: range.getRow(),
      rowEnd: range.getLastRow(),
      columnStart: range.getColumn(),
      columnEnd: range.getLastColumn(),
    };
  });
  return ranges;
}

You can then use the named ranges object like this:

NAMED_RANGES.task_taskDesc.range.setBackground('blue');

and

if (row >= NAMED_RANGES.sch_itemName.rowStart) {
  // ...
}

Answer 2

After some speed testing, I've found that both options below seem to be making calls to the spreadsheet anytime a .getX() is used such as .getValue(), .getColumn(), .getRange(), etc.

const ss = SpreadsheetApp.getActive();
const namedRanges = ss.getNamedRanges();

const sch_itemName = find(r => r.getName() == "sch_itemName").getRange().getColumn();
const sch_itemNum = find(r => r.getName() == "sch_itemNum").getRange().getColumn();
const task_taskDesc = find(r => r.getName() == "task_taskDesc").getRange().getColumn();
//... etc.

const ss = SpreadsheetApp.getActive();

const sch_itemName = ss.getRangeByName("sch_itemName").getColumn();
const sch_itemNum = ss.getRangeByName("sch_itemNum").getColumn();
const task_taskDesc = ss.getRangeByName("task_taskDesc").getColumn();
//... etc.

With that in mind, I've opted to instead store the name of the ranges as a string and use ss.getRangeByName("rangeName") within the functions themselves.