0

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.

2 Answers 2

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) {
  // ...
}
5
  • That's really great for not having to write out so many variables. How would that go performance wise? Is SpreadsheetApp.getActive() the only time data is 'read' from the spreadsheet? Or does every function like range.getRow() and range.getValues() count as being read from the spreadsheet?
    – DakotaE
    Jul 5, 2021 at 21:59
  • 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).
    – DakotaE
    Jul 5, 2021 at 22:06
  • I think the code above will spend most of its time in the getValues() calls. You can leave out the values: range.getValues() and only use namedRanges.myRangeName.range.getValues() when you need the values. Jul 5, 2021 at 22:21
  • 1
    I appreciate the help but you haven't really answered the most important point of my question so I can't accept it as the answer. Your suggestion was much slower than what I've tried already, so whilst convenient for my typing it's a lot slower for the end user (which is what I'm trying to fix).
    – DakotaE
    Jul 5, 2021 at 22:57
  • Edited the answer to include a discussion of script performance. The performance of your code is indeed likely to be dominated by the number of API calls, but it is usually getValue() type of calls rather than getRange(), getNamedRange() or getColumn() type of calls. Jul 6, 2021 at 7:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.