Question 11257 in Jquery Question posted in
The official Jquery Api Documentation can be found here.

update multiple cells in google spreadsheet using ajax – Jquery

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)
  }
})

`

Tags:

2

Answers


  1. 0

    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:

    worksheet ID

    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'
    Login or Signup to reply.
  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search