0

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?

ChiZensei
  • 13
  • 3

1 Answers1

1

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))

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • This is actually exactly what I need! I'm just having trouble getting the second solution to work on my actual spreadsheet. On it I have the b1 and c1 data on D1 and E1, which I believe I just change the var to data[0][3] and data[0][4]? But the column that holds the registrations (ie. what I have on column D in the example) is on column I, and changing lines 8 and 9 to ...data[row][8]... isn't getting it to work. What am I missing? – ChiZensei Oct 02 '21 at 13:41
  • `data[row][3]` is column 'D'. If you need column 'I' it will be `data[row][8]`, I suppose. The `if (data[row][3].indexOf(c1) > -1)` means: if 4th cell in this row contains 'c1' then... – Yuri Khristich Oct 02 '21 at 16:39
  • I figured out it was case-sensitive and now it works like a charm. Thank you! – ChiZensei Oct 03 '21 at 06:57
  • Btw, what exactly do you want to do with these checkboxes? Basically they are qute useles if there is no any action after you click on them. The only things you can do in this case is just to stare on them on the screen and to save the sheet in PDF. – Yuri Khristich Oct 03 '21 at 08:51
  • 1
    I'm using the checkboxes to tick off attendees of the event as they arrive and pay. So it's mostly for being able to visually tell who's registered for which class and who's here as well as having a simple automated system for crossing people off. I also have another formula that counts all the TRUE values in the column so I know how many have made it and how many I'm still waiting on and at the end of the day what percentage of those that registered ended up attending. – ChiZensei Oct 03 '21 at 16:44