Unable to push array to Google Sheets-CodePudding

I am trying to push the contents of the array I send a function to a newly created google sheet. I am getting API errors when trying to push data to the spreadsheet I am not too sure what to do to resolve this not really understanding much of the documentation on this topic I believe I did everything right struggling to get this working though.

If I am able to merge the create event to push the content of the array I would like to understand how to do that instead as that would be better than creating then updating.

If anyone could help me solve my problem getting data onto the spreadsheet I would appreciate it very much

const {google} = require('googleapis');
const auth = new google.auth.GoogleAuth({
    keyFile: './my-project.json',
    scopes: [
        'https://www.googleapis.com/auth/drive',
        'https://www.googleapis.com/auth/drive.file',
        'https://www.googleapis.com/auth/spreadsheets'
    ],
});
google.options({auth: auth.getClient()});
 
/* Example of array received from function 
const transData = [
    {
        "issuer":"John Cena",
        "time": "7-27-2022",
        "id":"e0c69748-c47a-48ad-80f9-90b181627963",
        "receiver":"Tyrese Jenkins",
        "message":"This is Placeholder message text",
        "type":"remove",
        "amount":10,
        "title":"Test Title"
    },
    //... duplicated
]
*/
const createSpreadsheet = async (transData) => {
    let dateTime = new Date().toLocaleString()
    let label = `Placeholder - ${dateTime}`
    const sheets = google.sheets('v4');
    
    //Create a new spreadsheet to push array dataset to (Works)
    sheets.spreadsheets.create({
        auth: auth,
        resource: { properties: { "title": label } }
    }, function (err, response) {
        if (err) return console.log('The API returned an error: '   err);
 
        const sheetID = response.data.spreadsheetId
        const drive = google.drive({ version: "v3", auth: auth });
        
        //Update permissions of file so anyone with URL can view but NOT edit (Works)
        drive.permissions.create({
            fileId: sheetID,
            requestBody: {
                role: 'reader',
                type: 'anyone'
            }
        }, function (error, res) {
            if (error) return console.log(error);
            
            //Attempting to push array to fill the spreadsheet with data. (API Errors)
            sheets.spreadsheets.values.append({
                auth: auth,
                spreadsheetId: sheetID,
                range: 'Sheet1!A:H',
                resource: {//Invalid JSON payload received. Unknown name "type" at 'data.values[125]': Cannot find field. (for each field in array only posted 1 for example)
                    values: transData
                },
              /*resource: { Error: Invalid values[0][0]: struct_value 
                    values: [transData]
                },
                */
                valueInputOption: 'USER_ENTERED',
            }, (e, r) => {
                if (e) return console.log('The API returned an error: '   e);
                
                console.log(r)
            });
        });
        
    });
})

CodePudding user response：

Modification points:

From your showing script, if the value of transData is as follows, unfortunately, this value cannot be directly used in the request body of "Method: spreadsheets.values.append". In this case, it is required to convert it to a 2-dimensional array. I thought that this might be the reason for your issue of Invalid JSON payload received. Unknown name "type" at 'data.values[125]': Cannot find field. (for each field in the array only posted 1 for example).
```
  const transData = [
      {
          "issuer":"John Cena",
          "time": "7-27-2022",
          "id":"e0c69748-c47a-48ad-80f9-90b181627963",
          "receiver":"Tyrese Jenkins",
          "message":"This is Placeholder message text",
          "type":"remove",
          "amount":10,
          "title":"Test Title"
      },
      //... duplicated
  ]
```

When these points are reflected in your script, how about the following modification?

From:

sheets.spreadsheets.values.append({
    auth: auth,
    spreadsheetId: sheetID,
    range: 'Sheet1!A:H',
    resource: {
        values: transData
    },
  /*resource: { Error: Invalid values[0][0]: struct_value 
        values: [transData]
    },
    */
    valueInputOption: 'USER_ENTERED',
}, (e, r) => {
    if (e) return console.log('The API returned an error: '   e);
    
    console.log(r)
});

To:

const keys = Object.keys(transData[0]);
const values = [keys, ...transData.map((o) => keys.map((k) => o[k] || ""))];
sheets.spreadsheets.values.append(
  {
    auth: auth, // <--- Added
    spreadsheetId: sheetID,
    range: "A1",
    resource: { values },
    valueInputOption: "USER_ENTERED",
  },
  (e, r) => {
    if (e) return console.log("The API returned an error: "   e);
    console.log(r.data);
  }
);

In this modification, the value of transData is converted to a 2-dimensional array, and it is used as the request body.

In this modification, the header row is added. If you don't want to add the header row, please modify as follows.

From

  const values = [keys, ...transData.map((o) => keys.map((k) => o[k] || ""))];

  const values = transData.map((o) => keys.map((k) => o[k] || ""));

Reference:

Method: spreadsheets.values.append