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

Question

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.

I'm stuck on the same exact thing... Any help would be appreciated! Seems bizarre that it would say it doesn't understand name "values" and complain about the lack of 'values' in the same (digital) breath... — thisissami

thisissami thisissami · Accepted Answer · 2016-06-07T21:14:05

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

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

1 Answers