Google Sheets API - get multiple named ranges at once

Question

I have a quick question regarding google sheets api using java script. Can I enter multiple ranges (e.g. named ranges) at once to keep the number of requests low?

The code I have so far:

const RANGE = "'GM Cheat Sheet'!";

const response = await fetch(
        `https://sheets.googleapis.com/v4/spreadsheets/${ID}/values/${RANGE}?key=${API_KEY}`
    );
    const { values } = await response.json();
    console.log(values);

Any help is appreciated :)

I tried defining the range as follows:

const RANGE = ["'GM Cheat Sheet'!Range1", "'GM Cheat Sheet'!Range2"];

This did not work.

Answer 1

In your situation, how about using "Method: spreadsheets.values.batchGet"? When this method is used, the multiple ranges can be used by one API call. When this is reflected in your script, it becomes as follows.

Modified script:

const API_KEY = "###"; // Please set your API key.
const ID = "###"; // Please set your Spreadsheet ID.
const RANGE = ["Range1", "Range2"]; // This is from your question.

const ranges = RANGE.map(e => `ranges=${encodeURIComponent(e)}`).join("&");
const response = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${ID}/values:batchGet?key=${API_KEY}&${ranges}`);
const { valueRanges } = await response.json();

const obj = valueRanges.reduce((o, { values }, i) => (o[RANGE[i]] = values, o), {});
console.log(obj);

• When this script is run, the values are retrieved from the ranges of RANGE.

• In the case of a named range, the name of the named range is a unique value in the Google Spreadsheet. So, in this case, when "Range1" and "Range2" are the named range, you can use const RANGE = ["Range1", "Range2"];.

• By adding const res = valueRanges.reduce((o, { values }, i) => (o[RANGE[i]] = values, o), {});, the returned value is as follows. By this, you can retrieve the values of the named range as obj.Range1 and obj["Range1"].

    {
      "Range1":[### values ###],
      "Range2":[### values ###]
    }
  

Reference:

• Method: spreadsheets.values.batchGet