Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error in creating request to Google Spreadsheet API: field 'values' could not be found

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.

like image 428
zonelsk Avatar asked Jun 06 '16 19:06

zonelsk


Video Answer


1 Answers

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).

like image 96
thisissami Avatar answered Sep 20 '22 07:09

thisissami