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
– Barmar, Commented Aug 2, 2022 at 16:26
Thanks for the fast reply. When I make that change I get a different form of the error though: — Christopher Perkins
– Christopher Perkins, Commented Aug 2, 2022 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
– Christopher Perkins, Commented Aug 2, 2022 at 16:34

Tanaike · Accepted Answer · 2022-08-02 23:21:11Z

0

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:

answered Aug 2, 2022 at 23:21

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christopher Perkins Over a year ago

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: stackoverflow.com/questions/17657879/…

Tanaike Over a year ago

@Christopher Perkins Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points:

Modified script:

References:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related