NPM Google Spreadsheets Error: Each row must be an object or an array

Question

I am attempting to add rows to a Google Spreadsheet after pulling from an API. Google Spreadsheet treats rows as objects, where the header is the key and that header's value is the property. Multiple rows are grouped in an array of objects (see more [here][1]). This is what I believe my code is doing, as it returns an array of dimensions and metrics objects. Perhaps Google Spreadsheets is having trouble because it appears to be nested (one layer for dimension, a second for profile id, reporting period ; one layer for metrics, a second for impressions and reactions.

One example of what's returned is here: {"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-29"},"metrics":{"impressions":1792,"reactions":0}}

Can you please help me how I can add these to a Google Spreadsheet?

const Sheet = require("./sheet.js");

fetch(url, {
  method: "POST",
  headers: {
    Authorization: "Bearer " + username,
    Accept: "application/json",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    filters: [
      "customer_profile_id.eq(3888890, 3888895, 3888919, 4205312, 4333025, 4401415, 4527629, 4540079, 4556340)",
      "reporting_period.in(2021-01-01...2021-05-01)",
    ],
    metrics: ["impressions", "reactions"],
    page: 1,
  }),
})
  .then(function (res) {
    return res.json();
  })
  .then(function (data) {
    var stringData = JSON.stringify(data);
    var arr = [];
    arr.push(stringData);
    console.log(arr);
    runIt(arr);
  });

async function runIt(arr) {
  const sheet = new Sheet();
  await sheet.load();
  await sheet.addRows(arr);
}```

// Which logs:

```[{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-29"},"metrics":{"impressions":1792,"reactions":0}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-30"},"metrics":{"impressions":2142,"reactions":2}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-31"},"metrics":{"impressions":1427,"reactions":1}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-02-01"},"metrics":{"impressions":1446,"reactions":0}}],"paging":{"current_page":1,"total_pages":2}}'```


  [1]: https://www.npmjs.com/package/google-spreadsheet

Answer 1

I believe your goal and your current situation as follows.

• You want to put the following values to Google Spreadsheet.

    [{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-29"},"metrics":{"impressions":1792,"reactions":0}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-30"},"metrics":{"impressions":2142,"reactions":2}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-31"},"metrics":{"impressions":1427,"reactions":1}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-02-01"},"metrics":{"impressions":1446,"reactions":0}}]
  

• Your header row is customer_profile_id, reporting_period.by(day), impressions and reactions.

  • In this case, these values are put in the cells "A1:D1".

• You want to achieve this using google-spreadsheet.

• You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

• In this case, I think that at first, it is required to create an array for putting to Spreadsheet from above values.
• Unfortunately, I cannot understand about the detail of const Sheet = require("./sheet.js");. So in this answer, "google-spreadsheet" is directly used. So please modify it for your actual situation.

Sample script:

Before you use this script, please put the values of customer_profile_id, reporting_period.by(day), impressions and reactions to the cells of "A1:D1". By this, the following script works. When the header row is not used, the values are not put. Please be careful this.

const { GoogleSpreadsheet } = require("google-spreadsheet");
const creds = require("###JSON file of your service account###");

async function sample() {
  const values = [{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-29"},"metrics":{"impressions":1792,"reactions":0}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-30"},"metrics":{"impressions":2142,"reactions":2}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-01-31"},"metrics":{"impressions":1427,"reactions":1}},{"dimensions":{"customer_profile_id":"4205312","reporting_period.by(day)":"2021-02-01"},"metrics":{"impressions":1446,"reactions":0}}];

  const doc = new GoogleSpreadsheet("###"); // Please set the Spreadsheet ID.
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  const sheet = doc.sheetsByIndex[0]; // In this sample, the values are put to the 1st sheet of Google Spreadsheet.

  // Create an array for putting to Spreadsheet.
  const rows = values.map((o) => Object.values(o).reduce((oo, e) => Object.assign(oo, e), {}));

  // Put the created array to Spreadsheet.
  await sheet.addRows(rows);
}

sample();

• In this sample script, your sample values are used. So, please modify it for your actual situation.

Result:

When above script is run, the following result is obtained.

Note:

• From your replying, unfortunately, I couldn't understand about the output situation you expect. So in this answer, I used the values of customer_profile_id, reporting_period.by(day), impressions and reactions as the header row. So, please modify this for your actual situation.

Reference:

• google-spreadsheet