Question

0

301

Views

Invalid JSON payload received when appending to Google Sheets API V4

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

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-09T08:43:58.382Z

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:

Method: spreadsheets.values.update

0

301