1

Hi I've a small tool to edit a Google Sheet using the API v4. I can read the data from said sheet but I can't append anything.

               $.ajax({
                    url: 'https://sheets.googleapis.com/v4/spreadsheets/' +
                        'SPREADSHEETID' +
                        '/values/' + 'TAB' +
                        '?alt=json&key=KEY',
                    type: 'POST',
                    data: {
                        majorDimension: 'ROWS',
                        range: 'A1:A2',
                        values: '32'
                    },
                    dataType: 'jsonp',
                    success: function (data) {
                        console.log(data);
                    }
                });

This is the code I'm using, but I keep getting the following error: Invalid JSON payload received. Unknown name \"values\": Cannot bind query parameter. Field 'values' could not be found in request message."

Guillermo Oramas R.
  • 1,303
  • 2
  • 14
  • 31
  • check your format: https://github.com/googleapis/google-api-nodejs-client/issues/588#issuecomment-243323554 – Kos Nov 28 '21 at 18:50
  • is it the same for ajax, I'm not using nodejs. @Kos – Guillermo Oramas R. Nov 28 '21 at 19:56
  • What is `TAB`?? – TheMaster Nov 28 '21 at 21:13
  • 1
    @TheMaster When I saw the endpoint of the script, I thought that OP might want to use the method of spreadsheets.values.update of Sheets API. [Ref](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update) In that case, I think that `TAB` will be the range. And, that might be the sheet name. – Tanaike Nov 29 '21 at 02:59

1 Answers1

2

I believe your goal is as follows.

  • You want to put the values to the Spreadsheet using the method of spreadsheets.values.update of Sheets API with ajax.

Modification points:

  • The method of spreadsheets.values.update uses the PUT method. In your script, it seems that you are trying to use the POST method.
  • Unfortunately, in the current stage, when the PUT and POST methods are used, these methods cannot be requested with the API key. In this case, please use the access token.
  • In the method of spreadsheets.values.update, please send the request body as the string type. And, the content type is application/json.
  • And, the value is a 2-dimensional array.

When these points are reflected in your script, it becomes as follows.

Modified script:

$.ajax({
  url: 'https://sheets.googleapis.com/v4/spreadsheets/###spreadsheetId###/values/A1?valueInputOption=USER_ENTERED',
  method: 'PUT',
  data: JSON.stringify({majorDimension: 'ROWS', values: [['32']]}),
  headers: {
    "Authorization": "Bearer ###your access token###",
    "Content-Type": "application/json"
  },
  success: function (data) {
    console.log(data);
  },
  error: function (res) {
    console.log(res.responseText);
  }
});

  • When you use A1 as the range, the value is put to the 1st tab. When you want to put the value to the specific sheet, for example, please use Sheet2!A1. Please be careful about this.

  • By the way, from appending to Google Sheets of Invalid JSON payload received when appending to Google Sheets API V4, if you want to append the value of "32" to the Spreadsheet, please modify the above script as follows. The reference of the method of spreadsheets.values.append is here.

    • From

        url: 'https://sheets.googleapis.com/v4/spreadsheets/###spreadsheetId###/values/A1?valueInputOption=USER_ENTERED',
  method: 'PUT',

    • To

        url: 'https://sheets.googleapis.com/v4/spreadsheets/###spreadsheetId###/values/###SheetName###:append?valueInputOption=USER_ENTERED',
  method: 'POST',

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165