Have only one checkbox checked per row in google sheets

0
votes

I am new to Google sheets, and I am having trouble writing a script to have only one checked box per row with two columns checked. I have two columns, E and F, both columns are all checkboxes. Each row can only have one box checked. If one is TRUE the other turns FALSE and vice versa. Also how can a cell be locked if checkbox is TRUE, and the only way to unlock the cell is with a password.

Thank you

1
google-apps-scriptgoogle-sheetscheckboxgoogle-sheets-formula

1 Answers

0
votes

Check this out for info on how to work with checkboxes in Google Sheets and Google Apps Script: https://spreadsheet.dev/working-with-checkboxes-in-google-sheets-using-google-apps-script

You can use this to run code when a cell is edited: https://developers.google.com/apps-script/guides/triggers/#onedite

You could essentially create something like this.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = e.range;

  // if a checkbox was edited
  if (range.getDataValidation().getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {

    // if the checkbox in column E was edited
    if (range.getColumn() == 5) {
      // set the checkbox in column F on the same row to the opposite value
      sheet.getRange(range.getRow(), 6).setValue(!range.getValue());
    }

    // same with the other column
    if (range.getColumn() == 6) {
      sheet.getRange(range.getRow(), 5).setValue(!range.getValue());
    }

  }
  
}