update multiple cells in google spreadsheet using ajax

Question

how can i update multiple cells value at one time? suppose i want to update cells A5,C5 and F5 all together. i have done something like this.but it doesn't work.******* `

$.ajax({
  type: 'put',
  headers: { Authorization: auth, 'content-type': 'application/json' },
  data: JSON.stringify(

    {           
                "range": "users!A5",
                "values": [["abc"]]
            },
    {           
                "range": "users!C5",
                "values": [["abc2"]]
            },{           
                "range": "users!F5",
                "values": [["ab"]]
            }

),
  url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId + '?valueInputOption=RAW',
  success: function (r) {
    console.log(r)
  }, error: function (r) {
    console.log(r)
  }
})

`

And if you use a [batchUpdate](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate) instead? If I'm not mistaken the `Method: spreadsheets.values.update` only works with 1 range. You can see an example [here](https://stackoverflow.com/questions/63602605/) for batch update, and some documentation from Google [here](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest) — Giselle Valladares, Feb 09 '23 at 18:52
@[user3369120](https://stackoverflow.com/users/3369120/user3369120), I posted an answer that might help you. — Giselle Valladares, Feb 09 '23 at 21:29
I got your new question in [your previous question](https://stackoverflow.com/q/71681219). When I posted an answer for your new question, I found [your posted new question](https://stackoverflow.com/q/75401633). So, I would like to post it here. Please confirm it. If I misunderstood your new question, I apologize. — Tanaike, Feb 10 '23 at 00:59

Giselle Valladares · Answer 1 · 2023-02-10T16:47:23.717

I made some test, I use the following to make the updates in multiple cells.

I change the type from:

 type: 'put',

to:

 type: 'post',

I also change the request from:

data: JSON.stringify(

    {           
                "range": "users!A5",
                "values": [["abc"]]
            },
    {           
                "range": "users!C5",
                "values": [["abc2"]]
            },{           
                "range": "users!F5",
                "values": [["ab"]]
            }

),

to:

 data: JSON.stringify(
{
  "requests": [
    {
      "updateCells": {
        "range": {
          "sheetId": worksheet-id,
          "startRowIndex": 4,
          "startColumnIndex": 0,
          "endRowIndex": 5,
          "endColumnIndex": 1
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredValue": {
                  "stringValue": "abc"
                }
              }
            ]
          }
        ],
        "fields": "userEnteredValue"
      }
    },
    {
      "updateCells": {
        "range": {
          "sheetId": worksheet-id,
          "startRowIndex": 4,
          "startColumnIndex": 2,
          "endColumnIndex": 3,
          "endRowIndex": 5
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredValue": {
                  "stringValue": "abc2"
                }
              }
            ]
          }
        ],
        "fields": "userEnteredValue"
      }
    },
    {
      "updateCells": {
        "range": {
          "sheetId": worksheet-id,
          "startRowIndex": 4,
          "startColumnIndex": 5,
          "endColumnIndex": 6,
          "endRowIndex": 5
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredValue": {
                  "stringValue": "ab"
                }
              }
            ]
          }
        ],
        "fields": "userEnteredValue"
      }
    }
  ]
}

),

Note: the worksheet-id mentioned in the range can be found in the URL of the Google Sheet, like this:

Or getting the sheet properties. Keep in mind that the worksheet-id is 0 if you are working with “Sheet1,” and is not required to add it in that scenario.

Lastly, I change the URL from:

 url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId + '?valueInputOption=RAW'

to:

 url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId + '/values:batchUpdate'

score 0 · Accepted Answer · answered Feb 10 '23 at 00:59

In this case, "Method: spreadsheets.values.batchUpdate" can be used.

In your showing script, it is required to modify the endpoint and the method, and the request body. When these are reflected in your script, it becomes as follows.

In your script, the spreadsheet ID is used as the variable name of sheetId. Although I'm not sure about your actual situation, I'm worried about it. In this case, please use the spreadsheet ID instead of the sheet ID. Please be careful about this.

Modified script:

const auth = "Bearer ###"; // Please set your access token.
const spreadsheetId = "###"; // Please set your spreadsheet ID.
$.ajax({
  type: 'POST',
  headers: { Authorization: auth, 'content-type': 'application/json' },
  data: JSON.stringify({
    "data": [
      {
        "range": "users!A5",
        "values": [["abc"]]
      },
      {
        "range": "users!C5",
        "values": [["abc2"]]
      }, {
        "range": "users!F5",
        "values": [["ab"]]
      }
    ],
    "valueInputOption": "USER_ENTERED"
  }),
  url: 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + '/values:batchUpdate',
  success: function (r) {
    console.log(r)
  }, error: function (r) {
    console.log(r)
  }
})

When this script is run, the values of "abc", "abc2" and "ab" are put into the cells "users!A5", "users!C5" and "users!F5", respectively.

Reference:

Method: spreadsheets.values.batchUpdate

update multiple cells in google spreadsheet using ajax

2 Answers2

Modified script:

Reference:

Linked