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

[Qasim_Abbas]

Hi guys ,
I have question and situation I want to update the google sheet based on row found , so letsuppose I want to update the rows 2,4,6,9,12,45,34,54,65

How can I do that using google sheet API in a single module using batch I did not found anything specific on that ,

I found a batch update but it ask for range to start and end , but only want to update specific rows

any help will be much appericated

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

[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:

[samliew]

1. Build each row (range) JSON data

So first, you’ll need to build the JSON data for each row.

While you can use the “Aggregate to JSON” module, a simpler way would be to use the “Text Aggregator”:

{
  "range": "'A{{rownum}}:B{{rownum}}",
  "majorDimension": "ROWS",
  "values": [[
    "{{col1_value}}",
    "{{col2_value}}",
    ... more columns
  ]]
}

2. Insert into Sheets data array

Then, you can insert this aggregated JSON data into the Sheets module like this: