Google spreadsheet editor: update sparse cells

Question

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

Now I am updating the cells one by one, but I was looking for a batch method to make just one call and pass an array to update only these cells. I searched for other SO streams and google spreadsheet documentation, but for batch updates, they are mainly intended for consecutive cells in a range. I could not find anything to update sparse cells in a single call.

Google recommends doing batch updates instead of single calls. So, is there a way to make a call and pass only those cells that need to be updated?

Answer 1

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

Mandatory Enable service in 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 that you want to update and add it todata[]