Google Sheets API - Import all named Ranges-CodePudding

I have a google sheet with several named ranges. I would like to import every single named range in the sheet and save them as individual objects named after the named range. Furthermore, there are 4 similar blocks of data. Therefore the names of the ranges are structured like "block1_name1" "block1_name2" "block2_name1" "block2_name2" etc.

With the following code I can enter every range manually, but there are too many to enter them all manually:


const API_KEY = "###"; // Please set your API key.

const ID = "###"; // Please set your Spreadsheet ID.

const RANGE = ["Range1", "Range2"]; // Named ranges

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);

How can I import every named range automatically?

How can I save them as a different objects for each datablock like block1.name1 etc?

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the named range list from Google Spreadsheet, and want to retrieve the values from the named ranges.
And, you want to export the values as {namedRange1: values1, namedRange2: values2,,,}.
You want to achieve this by directly requesting the endpoint of Sheets API with fetch API of Javascript.

In this case, how about the following modification?

Modified script:

const API_KEY = "###"; // Please set your API key.
const ID = "###"; // Please set your Spreadsheet ID.

// 1. Retrieve the named range list.
const base = `https:\/\/sheets.googleapis.com/v4/spreadsheets/${ID}`;
const res1 = await fetch(`${base}?key=${API_KEY}&fields=namedRanges(name)`);
const { namedRanges } = await res1.json();

// 2. Retrieve values from named ranges.
const ranges = namedRanges.map(({ name }) => `ranges=${encodeURIComponent(name)}`).join("&");
const res2 = await fetch(`${base}/values:batchGet?key=${API_KEY}&${ranges}`);
const { valueRanges } = await res2.json();

// 3. Create an output object.
const res = valueRanges.reduce((o, { values }, i) => (o[namedRanges[i].name] = values, o), {});
console.log(res);

// For your 2nd question.
const res3 = valueRanges.reduce((o, { values }, i) => {
  const [k1, k2] = namedRanges[i].name.split("_");
  if (o[k1]) {
    o[k1][k2] = values;
  } else {
    o[k1] = { [k2]: values };
  }
  return o;
}, {});
console.log(res3);

Testing:

When this script is run, the following result can be seen at the console.

{
  "block1_name2":[###values###],
  "block2_name2":[###values###],
  ,
  ,
  ,
}

Note:

When you have a lot of named ranges, it is required to separate the request for retrieving the values, because of the limitation of the length of the URL. Please be careful about this.
About your 2nd question, when you want to convert from {"block1_name2":[###values###],,,} to {"block1": {"name2":[###values###]},,,}, as a premise, I think that it is required to decide the format of the name of the named ranges. In this case, from your showing sample named ranges, it supposes that your format of the name of all named ranges is like block#_name#. Please be careful about this.

References:

CodePudding user response：

You can use the Method: spreadsheets.get to list the ranges of a Google Spreadsheet. This is returned as an object.

{
      "namedRangeId": "xxxxxxxxxxx",
      "name": "test",
      "range": {
        "startRowIndex": 4,
        "endRowIndex": 10,
        "startColumnIndex": 0,
        "endColumnIndex": 3
      }
    },

A simple sample using the method, and limiting the field for name ranges only:

function execute() {
    return gapi.client.sheets.spreadsheets.get({
      "spreadsheetId": "Google_Sheet_ID",
      "fields": "namedRanges"
    })

You can read more information about this method in the Google Documentation here

And there is a sample complete code on the same documentation. You can then use the name in "name": "test", to rename the objects.