CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Daniel Souza
Daniel Souza

Reputation: 430

How to request updateCells for multiple rows to have the same value

I'm using this request code to insert those checkboxes in a column, but I also need to have them all set to true by default. So far I've seen examples with multiple "values", one for every row, but I would like to know if there's a way to declare that only once and already set for all others in a range

var resource = {"requests": [
    {"repeatCell": {
      "cell": {"dataValidation": {"condition":{"type": "BOOLEAN"}}},
      "range": {"sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 300, "startColumnIndex": 18},
      "fields": "dataValidation"
      }
    },
    {"updateCells": {
      "rows": {"values": {"userEnteredValue": {"boolValue": true}}},
      "range": {"sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 300, "startColumnIndex": 18},
      "fields": "userEnteredValue"
      }
    }
  ]};
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

Upvotes: 2

Views: 2568

Answers (1)

tehhowch
tehhowch

Reputation: 9872

Rather than use both repeatCell and updateCells requests, a single repeatCell request can be used to alter both the "data validation" and the "user entered value" properties of the desired range. The key is that both the "fields" parameter, which indicates the properties to be modified, and the actual properties, must all be included (or intentionally omitted, for deletion).

All cells in the specified range (R2C19:R301C19, since "_____Index" means -> 0-base) will then be modified to use the specified properties as found in your request:

var resource = {"requests": [
  {"repeatCell": {
    "cell": {
      "dataValidation": {"condition":{"type": "BOOLEAN"}},
      "userEnteredValue": {"boolValue": true}
    },
    "range": {
      "sheetId": sheetId,
      "startRowIndex": 1,
      "endRowIndex": 300,
      "startColumnIndex": 18,
      "endColumnIndex": 19 // Specify the end to insert only one column of checkboxes
    },
    "fields": "dataValidation,userEnteredValue"
  }
}]};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

Note that if you omit the endColumnIndex, the GridRange is interpreted as unbounded:

All indexes are zero-based. Indexes are half open, e.g the start index is inclusive and the end index is exclusive -- [startIndex, endIndex). Missing indexes indicate the range is unbounded on that side.

References:

Upvotes: 4

Related Questions