CODEWITHSUNDEEP
node.jsgoogle-sheetsgoogle-sheets-api
Dollarslice
Dollarslice

Reputation: 10284

Invalid Values error when attempting to use 'append' in google sheets API

I am attempting to append information onto a spreadsheet with the following code. 

    let values = [
        [
            [tweet.user.name, tweet.user.client_id]
        ]
    ];

    let resource = {
        values,
    };

    const sheets = google.sheets({version: 'v4', oAuth2Client});

    sheets.spreadsheets.values.append({
    auth: oAuth2Client,
    spreadsheetId: '***redacted***',
    range: 'A:C',
    valueInputOption: 'RAW',
    resource,
}, (err, result) => {
    if (err) {
    // Handle error.
    console.log(err);
    } else {
    console.log(`${result.updates.updatedCells} cells appended.`);
    }
});

However I am receiving this error. I think it's some sort of formatting issue, but I have no idea what it could be.

'Invalid values[2][0]: list_value {\n values {\n string_value: "Structure of Reign"\n }\n values {\n null_value: NULL_VALUE\n }\n}\n',

Upvotes: 1

Views: 3932

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this modification?

Modification points:

  • values is required to be 2 dimensional array.
  • Result can be retrieved by result.data.
  • {\n values {\n string_value: "Structure of Reign"\n }\n values {\n null_value: NULL_VALUE\n }\n}\n indicates that tweet.user.client_id is undefined, while tweet.user.name has a value of Structure of Reign.
    • About this, please confirm tweet.user.client_id, because I'm not sure about your situation.

Modified script:

Please modify as follows.

From: 
let values = [[[tweet.user.name, tweet.user.client_id]]];
To: 
let values = [[tweet.user.name, tweet.user.client_id]];

And

From: 
console.log(`${result.updates.updatedCells} cells appended.`);
To: 
console.log(`${result.data.updates.updatedCells} cells appended.`);

Note:

  • This modification supposes that you can use Sheets API.
  • When other error occurs even if you modified those, please confirm the following points.
    1. Update "googleapis" to the latest version.
    2. Whether Sheets API is enabled at API console.
    3. Whether the access token has the scopes for using the method of values.append.

Reference:

If this modification didn't work, I apologize.

Upvotes: 4

Related Questions