How do I automatically create checkbox in Google Sheets if X-CodePudding

I have a Google Sheet file linked to a Google Form, that I use to record registrations for classes by different teachers. Inside of it I create extra columns that mark "X" in a row, when someone registers for that particular class, using an =IF function. I'd like those "X"'s to be checkboxes so that I can check attendees in the sheet itself as they arrive, but there is no method I can find for a function to output a tickbox into the cell and I have next to no knowledge of JavaScript.

Example Sheet: https://docs.google.com/spreadsheets/d/19BUkEfo8dWcAfPDhmhBTuhC1-V-QROlfF0pWH75c9VA/edit?usp=sharing

Ideally, I'd have a custom function that basically does the same as my =IF but inserts a checkbox instead of writing "X" (ie. if cell A contains text from cell B insert checkbox, else leave empty).

Alternatively I also found this solution to a similar problem, that I think would work, but I don't know enough to tweak it to my needs. This way I'd keep my sheet as is and the script would just replace the X's with checkboxes?

CodePudding user response：

Probably it is not very efficient but it works:

// function creates menu 'SCRIPTS'

function onOpen() {
  SpreadsheetApp.getUi().createMenu('SCRIPTS')
  .addItem('Insert checkboxes', 'replace_x_to_checkboxes')
  .addToUi();
}

// function replaces all 'X' on the sheet with checkboxes

function replace_x_to_checkboxes() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();
  for (var row in data) 
    for (var col in data[row]) {
      if (data[row][col] == 'X') sheet.getRange( row 1, col 1).insertCheckboxes()
  }
}

To insert checkboxes without using formulas and 'X's you can run this function:

function insert_checkboxes() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var b1 = data[0][1]; // name from cell 'B1'
  var c1 = data[0][2]; // name from cell 'C1'

  for (var row=1; row<data.length; row  ) {
    if (data[row][3].indexOf(b1) > -1) sheet.getRange(row 1,2).insertCheckboxes();
    if (data[row][3].indexOf(c1) > -1) sheet.getRange(row 1,3).insertCheckboxes();
  }
}

Just in case. Instead of data[row][3].indexOf(b1) > -1 you can use a modern variant of the same condition data[row][3].includes(b1))