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."
CodePudding user response:
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 useSheet2!A1
. Please be careful about this.By the way, from
appending to Google Sheets
ofInvalid 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',