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

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.

CodePudding user response：

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

Modification points:

Modified script:

References: