Invalid JSON Payload Received writing to Google Sheet from JavaScript/Ajax

Question

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.

You're not sending JSON. `$.ajax` encodes `data:` as a URL-encoded string, not JSON. — Barmar, Aug 02 '22 at 16:26
Thanks for the fast reply. When I make that change I get a different form of the error though: — Christopher Perkins, Aug 02 '22 at 16:34
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. — Christopher Perkins, Aug 02 '22 at 16:34

score 0 · Accepted Answer · answered Aug 02 '22 at 23:21

Modification points:

From My goal is to write to an existing Google Sheet via a Ajax/JavaScript call on the client-side. and your showing script, in this case, the API key cannot be used for updating the Spreadsheet. Unfortunately, it seems that this is the current specification. In this case, it is required to use the access token. Please be careful about this.
- In order to retrieve the access token, I thought that this official document is useful. Ref And, I think that you can also see about it at Stackoverflow.
When you want to use spreadsheets.values.batchUpdate method of Sheets API, in your script, the POST method is required to be used.
And, the request body is required to be modified.

I thought that the reason of your current issue is due to the above points. When these points are reflected in your script, it becomes as follows.

Modified script:

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 put to row 8 in "Sheet1".

References:

Yes, this resolved the issue and now I also get [['Field1', 'Field2', 'Field3']] appearing successfully in my Sheet. Thanks! For anyone else who comes across this, I had a bit of trouble actually getting my Access Token from OAuth 2.0; I ended up following this answer from this other stack overflow to do it: https://stackoverflow.com/questions/17657879/does-google-provide-test-users-for-integration-testing/68844709#68844709 — Christopher Perkins, Aug 03 '22 at 13:05
@Christopher Perkins Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, Aug 04 '22 at 00:41

Invalid JSON Payload Received writing to Google Sheet from JavaScript/Ajax

1 Answers1

Modification points:

Modified script:

References: