This is my code:
var sheets = google.sheets('v4');
sheets.spreadsheets.values.update(
{
auth: auth,
spreadsheetId: '1tsyo5XFh1CzlF6Xd_q1ciUQY9KDo_rWYGdrwlANBduc',
range: 'Sheet1!A1:D5',
values: [
["Item", "Cost", "Stocked", "Ship Date"],
["Wheel", "$20.50", "4", "3/1/2016"],
["Door", "$15", "2", "3/15/2016"],
["Engine", "$100", "1", "30/20/2016"],
["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
],
},
function (err, response) {
if (err) {
console.log(err);
return res.status(400).send({
message: errorHandler.getErrorMessage(err)
});
}
console.log(response);
});
I'm doing this by example from Google Doc
But I'm getting error:
{ [Error: Invalid JSON payload received. Unknown name "values": Cannot bind query parameter. Field 'values' could not be found in request message.] code: 400, errors:
[ { message: 'Invalid JSON payload received. Unknown name "values": Cannot bind query parameter. Field \'values\' could not be found in request message.',
domain: 'global',
reason: 'badRequest' } ] }
Rest of the code is from same docs and I'm using it for reading from spreadsheet and everything is working fine.
Ok I figured it out as per: http://google.github.io/google-api-nodejs-client/7.0.0/sheets.html
You need to edit your code to read as follows:
var sheets = google.sheets('v4');
sheets.spreadsheets.values.update(
{
auth: auth,
spreadsheetId: '1tsyo5XFh1CzlF6Xd_q1ciUQY9KDo_rWYGdrwlANBduc',
range: 'Sheet1!A1:D5',
valueInputOption: 'USER_ENTERED',
resource: {
values: [
["Item", "Cost", "Stocked", "Ship Date"],
["Wheel", "$20.50", "4", "3/1/2016"],
["Door", "$15", "2", "3/15/2016"],
["Engine", "$100", "1", "30/20/2016"],
["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
]},
},
function (err, response) {
if (err) {
console.log(err);
return res.status(400).send({
message: errorHandler.getErrorMessage(err)
});
}
console.log(response);
});
You can also put in 'RAW'
instead of 'USER_ENTERED'
for the valueInputOption
attribute if you want your values inputted into the spreadsheet literally as they are (instead of being formatted as if they would be if you were to enter them into the GUI).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With