mercredi 13 avril 2022

Get checkbox values in given range in Google Sheets

I'd like to create a function that returns 2D array containing checkbox values in given range:

  • true if checked
  • false if unchecked
  • null if no checkbox

enter image description here

function getCheckboxInfo(range): {
  const sheet =  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  range = sheet.getRange(rangeA1);
  return /* (boolean|null)[][] */
}

Although I found similar methods for values (range.getValues()) or notes (range.getNotes()), I haven't found anything like that for checkboxes. The only solution I got so far doesn't look very optimal

return range.getValues().map((row, i) => {
  return row.map((_, j) => sheet.getRange(i + 1, j + 1).isChecked())
});

Is there a better way to do it?

EDIT: Solution using method suggested by Ricardo

  const values = range.getValues();
  const validators = range.getDataValidations()

  return validators.map((row, i) => {
    return row.map((v, j) => v && v.getCriteriaType() === SpreadsheetApp.DataValidationCriteria.CHECKBOX ? !!values[i][j] : null)
  })



Aucun commentaire:

Enregistrer un commentaire