My goal is to write to an existing Google Sheet via a Ajax/JavaScript call on the client-side.
However, I'm currently getting the following error:
Invalid JSON payload received. Unknown name "{"range":"Sheet1!A8:C8","majorDimension":"ROWS","values":[["Field1","Field2","Field3"]]}": Cannot bind query parameter. Field '{"range":"Sheet1!A8:C8","majorDimension":"ROWS","values":[["Field1","Field2","Field3"]]}' could not be found in request message.
This is my JavaScript code:
const sheetID = "--privateField--";
const key = "--privateField--";
var url = "https://sheets.googleapis.com/v4/spreadsheets/"+sheetID+"/values:batchUpdate/?key="+key;
$.ajax({
url: url,
type: 'PUT',
dataType: 'jsonp',
valueInputOption: 'USER_ENTERED',
data: JSON.stringify({
range: 'Sheet1!A8:C8',
majorDimension: 'ROWS',
values: [
['Field1', 'Field2', 'Field3']
]
}),
headers: {
"Content-Type": "application/json"
},
success: function(data) {
console.log(data);
},
error: function(data) {
console.log(data);
}
Where sheetID & key are replaced with actual production values.
I've tried many different variations of this API call based on different Stack Overflow results I've found, but haven't been able to get anything working. It seems to me like I've specified 'Values' correctly (an array of arrays), but the error message seems to indicate otherwise.
I've been able to read from Google Sheets using Ajax/JavaScript on the client-side before, and am now trying to get an example working for writing to the sheet.
Any advice or tips would be greatly appreciated.
Since My goal is to write to an existing Google Sheet via a Ajax/JavaScript call on the client-side. and your script showing, in this case the API key cannot be used to update the spreadsheet. Unfortunately, it appears that this is the current specification. In this case, it is required to use the access token. Be careful with this.
To retrieve the access token, I thought this official document is helpful. Ref And, I think you can also see it on Stackoverflow.
When you want to use the Sheets API spreadsheets.values.batchUpdate method, in your script, use of the POST method is required.
And it is required to modify the body of the request.
I thought that the reason for your current problem is due to the above points. When these points are reflected in your script, it becomes the following.
const sheetID = "--privateField--"; const accessToken = "### your access token ###"; var url = "https://sheets.googleapis.com/v4/spreadsheets/" + sheetID + "/values:batchUpdate"; var requestBody = { valueInputOption: 'USER_ENTERED', data: [{ range: 'Sheet1!A8:C8', majorDimension: 'ROWS', values: [['Field1', 'Field2', 'Field3']] }] }; $.ajax({ url: url, type: 'POST', data: JSON.stringify(requestBody), headers: { "Content-Type": "application/json", "Authorization": "Bearer " + accessToken }, success: function (data) { console.log(data); }, error: function (data) { console.log(data); } }); When I tested this script, I confirmed that the values of [['Field1', 'Field2', 'Field3']] are placed in row 8 in "Sheet1".