google spreadsheet editor: update sparse cells

Question

I need to update roughly 300 sparse cells, like A1, B200, A1000, B5000, A6000, A6001... in a spreadsheet with about 10000 rows, and 200 columns.

Right now I am updating cells one by one, but I was looking for batch method to make just 1 call and pass an array to update only these cells. I searched other SO threads and google spreadsheet documentation, but for batch updates they are mostly for consecutive cells in a range. I couldn't find anything for updating sparse cells with one call.

Google recommendation is to make batch updates instead of individual calls. So is there any method to make a call and pass only the cells that need to be updated?

How do you determine which cells to update? Meaning, what is your definition of a sparse cell? — Karl_S
@Karl_S the spreadsheet is updated daily, and the cells needing update are determined from the other columns in their rows. A simplified example would be like this: Col C, D determines whether A, B needs to be updated. So they are not predetermined. — apadana
OK, so sparse just means various unconnected cells in the file. What factors would determine which cells would be changed? If Col D is _____ then B becomes _____, or something similar to this would be what I am looking to know. Can you provide a sample file? Also, is the file updated daily via a formula, script, or manually? — Karl_S

random-parts random-parts · Accepted Answer · 2017-08-09T13:40:31

You could use the Advanced Sheets Service with Google Sheets API v4 & Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId)

Be sure to Enable the service in your script

function updateMultipleRanges () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range_one = sheet.getRange("A1:D3").getA1Notation();
  var range_two = sheet.getRange("G1:G4").getA1Notation();
  
  // Create the JSON Request Body
  var update_req = {
    "valueInputOption": "RAW",
    "data": [{
        "range": range_one,
        "majorDimension": "ROWS",
        "values": [[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]],
      },
      {
        "range": range_two,
        "majorDimension": "ROWS",
        "values": [[1], [2], [3], [4]],
      }],
    "includeValuesInResponse": false
  }

  Sheets.Spreadsheets.Values.batchUpdate(JSON.stringify(update_req), ss.getId())
}

Create a ValueRange object for each range you want to update and add it to the data[]

google spreadsheet editor: update sparse cells

1 Answers