Google spreadsheets in array

Question

how do you store the collected values ​​in an array?

I can already collect values, but I don't know how to store them

function onGAPILoad() {
gapi.client.init({
  // Don't pass client nor scope as these will init auth2, which we don't want
  apiKey: API_KEY,
  discoveryDocs: DISCOVERY_DOCS,
}).then(function () {
  console.log('gapi initialized')
  chrome.identity.getAuthToken({interactive: true}, function(token) {
    gapi.auth.setToken({
      'access_token': token,
    });
    gapi.client.sheets.spreadsheets.values.batchGet({
      spreadsheetId: SPREADSHEET_ID,
      majorDimension: "COLUMNS",
        ranges: [
            "A2:A"
        ]

    }).then(function(values) {
         console.log(values)
    });
  })

}, function(error) {
  console.log('error', error)
});
}

Response in console:

{result: {…}, body: "{↵  "spreadsheetId": "1f99uX3zCeqF5Nlu4LJVQ_uEd9T4…↵          "BC639"↵        ]↵      ]↵    }↵  ]↵}↵", headers: {…}, status: 200, statusText: null}result: {spreadsheetId: "1f99uX3zCeqF5Nlu4LJVQ_uEd9T4Mao8r32eAHsijnjw", valueRanges: Array(1)}spreadsheetId: "1f99uX3zCeqF5Nlu4LJVQ_uEd9T4Mao8r32eAHsijnjw"valueRanges: [{…}]0: {range: "main!A2:A1000", majorDimension: "COLUMNS", values: Array(1)}length: 1__proto__: Array(0)__proto__: constructor: ƒ Object()__defineGetter__: ƒ __defineGetter__()__defineSetter__: ƒ __defineSetter__()hasOwnProperty: ƒ hasOwnProperty()arguments: (...)caller: (...)length: 1name: "hasOwnProperty"__proto__: ƒ ()[[Scopes]]: Scopes[0]__lookupGetter__: ƒ __lookupGetter__()__lookupSetter__: ƒ __lookupSetter__()isPrototypeOf: ƒ isPrototypeOf()propertyIsEnumerable: ƒ propertyIsEnumerable()toString: ƒ toString()valueOf: ƒ valueOf()toLocaleString: ƒ toLocaleString()get __proto__: ƒ __proto__()set __proto__: ƒ __proto__()body: "{↵  "spreadsheetId": "1f99uX3zCeqF5Nlu4LJVQ_uEd9T4Mao8r32eAHsijnjw",↵  "valueRanges": [↵    {↵      "range": "main!A2:A1000",↵      "majorDimension": "COLUMNS",↵      "values": [↵        [↵          "TC123",↵          "BC632",↵          "TC124",↵          "BC633",↵          "TC125",↵          "BC634",↵          "TC126",↵          "BC635",↵          "TC127",↵          "BC636",↵          "TC128",↵          "BC637",↵          "TC129",↵          "BC638",↵          "TC130",↵          "BC639"↵        ]↵      ]↵    }↵  ]↵}↵"headers: cache-control: "private"content-encoding: "gzip"content-length: "230"content-type: "application/json; charset=UTF-8"date: "Sat, 29 Feb 2020 18:16:00 GMT"server: "ESF"vary: "Origin, X-Origin, Referer"__proto__: Objectstatus: 200statusText: null__proto__: Object

Answer 1

• You want to retrieve the values from gapi.client.sheets.spreadsheets.values.batchGet().
• You want to retrieve the values as an array.
• You have already been able to retrieve the values using Sheets API with gapi.client.sheets.spreadsheets.values.batchGet().

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Pattern 1:

In this pattern, the retrieved values are retrieved as an array in gapi.client.init().

Modified script:

unction onGAPILoad() {
  gapi.client.init({
    // Don't pass client nor scope as these will init auth2, which we don't want
    apiKey: API_KEY,
    discoveryDocs: DISCOVERY_DOCS,
  }).then(function () {
    console.log('gapi initialized')
    chrome.identity.getAuthToken({interactive: true}, function(token) {
      gapi.auth.setToken({
        'access_token': token,
      });
      gapi.client.sheets.spreadsheets.values.batchGet({
        spreadsheetId: SPREADSHEET_ID,
        majorDimension: "COLUMNS",
          ranges: ["A2:A"]
      }).then(function(values) {
        const ar = values.result.valueRanges[0].values;  // <--- Modified
        console.log(ar);  // <--- Modified
      });
    })
  }, function(error) {
    console.log('error', error)
  });
}

• You can retrieve the values with const ar = values.result.valueRanges[0].values.

Pattern 2:

In this pattern, the retrieved values are sent to other function as the arguments.

Modified script:

function onGAPILoad() {
  gapi.client.init({
    // Don't pass client nor scope as these will init auth2, which we don't want
    apiKey: API_KEY,
    discoveryDocs: DISCOVERY_DOCS,
  }).then(function () {
    console.log('gapi initialized')
    chrome.identity.getAuthToken({interactive: true}, function(token) {
      gapi.auth.setToken({
        'access_token': token,
      });
      gapi.client.sheets.spreadsheets.values.batchGet({
        spreadsheetId: SPREADSHEET_ID,
        majorDimension: "COLUMNS",
          ranges: ["A2:A"]
      }).then(function(values) {
        getValues(values);
      });
    })
  }, function(error) {
    console.log('error', error)
  });
}

function getValues(e) {
  const values = e.result.valueRanges[0].values;
  console.log(values);
}

• You can retrieve the values at the function of getValues.

Pattern 3:

In this pattern, the retrieved values can be used in the function of onGAPILoad.

Modified script:

async function onGAPILoad() {
  const getValues = () => {
    return new Promise((resolve, reject) => {
      gapi.client.init({
        // Don't pass client nor scope as these will init auth2, which we don't want
        apiKey: API_KEY,
        discoveryDocs: DISCOVERY_DOCS,
      }).then(function () {
        console.log('gapi initialized')
        chrome.identity.getAuthToken({interactive: true}, function(token) {
          gapi.auth.setToken({
            'access_token': token,
          });
          gapi.client.sheets.spreadsheets.values.batchGet({
            spreadsheetId: SPREADSHEET_ID,
            majorDimension: "COLUMNS",
              ranges: ["A2:A"]
          }).then(function(values) {
            resolve(values);
          });
        })
      }, function(error) {
        reject(error);
      });
    });
  }

  const res = await getValues().catch(e => console.log(e));
  const values = res.result.valueRanges[0].values;
  console.log(values);
}

• You can retrieve the values at the last line.

Note:

• In your request, only one range is used. So I used res.result.valueRanges[0].values. When you want to use multiple ranges, please retrieve the values from res.result.valueRanges using the loop.

Reference:

• Method: spreadsheets.values.batchGet

If I misunderstood your question and these were not the direction you want, I apologize.