How to bulk update non-consecutive rows in Google Sheets using the batchUpdate API?

[samliew]

@Wemakefuture

I am afraid that is not possible to do in bulk you need to search for the v​alue and than update the row.

Yes, that is possible

You’ve already found the rows you want to update, so it’s possible to create a range (ValueRange) for each row.

According to the documentation for spreadsheets.values/batchUpdate, your request would look like this

You can have one or more ranges (ValueRange) because the data is an array (square brackets above).

Click on each of those links to see how to craft each section of the query.

Here’s what your query could look like:

{
  "valueInputOption": "RAW",
  "data": [{
    "range": "'A{{rownum}}:B{{rownum}}",
    "majorDimension": "ROWS",
    "values": [[
      "cell1",
      "cell2",
      ... more columns
    ]]
  }, {
    "range": "'A{{rownum}}:B{{rownum}}",
    "majorDimension": "ROWS",
    "values": [[
      "cell1",
      "cell2",
      ... more columns
    ]]
  }, {
    ... more ranges
  }],
  "includeValuesInResponse": {{false}}
}

Screenshot of module: