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.