Google Sheets: Batch getRangeByName via Apps Scrip

Question

Is it possible to get multiple ranges by name with one call? I have a complex function that needs to grab many ranges by name and Spreadsheet.getRangeByName(name) is slowing down my script significantly. Sometimes these calls take ~2 seconds, and sometimes a single call can take ~45 seconds.

Restrictions:

• I need grab the range (not just value(s)) because the script formats, grabs A1Notation for later use, offets, etc.
• The addresses of these named ranges change as a result of other functions

What I have tried:

• Store the Range object in the Document Properties. This doesn't seem to work because Ranges only have functions. JSON.stringify(Range) returns nothing.
• Use Google Sheets API v4's batchGet function but it appears to be getting values only

Answer 1

For named ranges, the only method of specific acquisition is via the workbook's getRangeByName method. There are also workbook-level and sheet-level collections available:

var namedRangeArray = SpreadsheetApp.getActive().getNamedRanges();
var a1array = [];
namedRangeArray.forEach(function (namedRange) {
  var range = namedRange.getRange();
  a1array.push(range.getA1Notation()); // Doesn't include a sheet reference...
  ...
});

---

I originally misread your question as referring to acquiring multiple ranges by A1 notation. For this, the RangeList class offers a very minor speed improvement, but it currently only accepts an A1/R1C1-style input array, e.g. [ "A1", "B2", "C3" ], and requires the ranges to be on the same sheet.

function compare_GetRange_And_GetRangeList() {
  var rangeStrings = [
    "A1:A100", "B2:B101", "C3:C102"
  ];
  var sheet = SpreadsheetApp.getActive().getActiveSheet()

  // Acquire multiple.
  console.time("Get rangelist");
  var ranges = sheet.getRangeList(rangeStrings);
  console.timeEnd("Get rangelist");

  // Acquire single.
  var separate = [];
  console.time("Get individual");
  ["D4:D103", "E5:E104", "F6:F105"].forEach(function (a1) {
    separate.push(sheet.getRange(a1));
  });
  console.timeEnd("Get individual");

  // Do something with the acquired ranges.
  [].concat(ranges.getRanges(), separate).forEach(function (rl) {
    console.log(rl.getA1Notation());
  });
}

If you run this and then check Stackdriver, you can see the execution times, e.g.:

I used different ranges for each call to avoid the Apps Script server caching the reference between method comparisons.