How to auto add/remove checkbox's whether different cells contain value?

Question

Im working with the new checkbox feature in google sheets.

in my worksheet, i would like to have a checklist column, but would want the number of checkbox's to be matched to the number of rows that have values in the worksheet.
For example, say column A contains name's of workers in the company (that changes from time to time), and column B contains Checkbox's, I would like there to be a checkbox to a row, only if the cell in column A contains a value.

Here is an example:

https://docs.google.com/spreadsheets/d/1YKxGzThlMXzJRSGLON8Do4dzJq6HEldcpNTbDzH3Wsw/edit?usp=sharing

How can i do this?

Answer 1

not sure if this will help or not. But one way I accomplish this is by using conditional formatting.

Example. If I have entries that go in A2:A and I want to show checkboxes in B2:B ONLY if there is data in the corresponding 'A' cell I do the following;

1. highlight the entire Column from B2:B
2. Insert -> Checkboxes (this will put checkboxes all the way down)
3. Highlight the entire Column of checkboxes
4. Format -> Conditional Formatting -> Custom Formula
5. Input the following custom formula; =ISBLANK($A2)
6. Change the background color and text color to be the same

This will then evaluate the 'A' Column and if it is blank, it will make the background and text color the same (white, for example) and all the checkboxes will 'disappear'. When you input information into column 'A', the checkbox will 'appear'

It's a simple workaround the scripting solutions that works in most cases.

Answer 2

NEWER (FASTER) BATCH PROCESSING ANSWER:
My previous answer (see below) was written hastily to illustrate how you could show or hide the new checkboxes based on the contents of another cell. To speed things up, the following new script processes everything into an array, and doesn't update the cells until all of the values are calculated. It still isn't as quick as a built in client side response, but it will handle hundreds or thousands of names within a second or two (so it's ideal for being able to paste in hundreds of names without getting bogged down) and I did try to employ the "best practices" from this page on developers.google.com.

Assumptions:

• You have made a copy of the following example sheet: https://docs.google.com/spreadsheets/d/1MSnQ1_6Sy018lAoehzQ55QVrfJldoKS5EnWjOxYWxIs/edit?usp=sharing (File > Make a copy...)
• Column A is your "Checkboxes" column (see example sheet)
• Column B is your "Names" column (see example sheet)

Step 1:
You can skip this step if you've copied the example sheet Highlight all the relevant cells in your checkboxes column, right click on them, and select "Data Validation" from the menu. From the popup dialog box, make sure to select "Checkbox" from the Criteria section and then hit "Save".

Step 2:
If you copied the example sheet, then this script should already be bound to the sheet, otherwise: Insert and save the following script into your script editor "Tools > Script Editor." Note: You may have to approve of the script running as per Google's security permissions.

  function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("New Checkboxes"); //change this to the name of your sheet
  ui = SpreadsheetApp.getUi();

  //PICK ONE & comment out the other one:
  //var names = ss.getRange("names");//Use this if you are naming the range
  var names = ss.getRange("B3:B");//Use this if you are naming the ranges

  var namesValues = names.getValues(); //Get array of all the names

  //PICK ONE & comment out the other one:
  //var checkboxes = ss.getRange("checkboxes"); //Use this if you are naming the range
  var checkboxes = ss.getRange("A3:A"); //Use this if you want to hard-code your range

  var cbRows = checkboxes.getHeight(); //Get # of rows in the ranges
  var cbValues = checkboxes.getValues(); //Get array of all the checkbox column cell values
  //Logger.log(cbValues);

  var newCBValues = new Array(cbRows); //Create an array to store all the new checkboxes values before we edit the actual spreadsheet

  for (var row = 0; row < cbRows; row++) {
    newCBValues[row] = new Array(0); // Make the array 2 dimensional (even though it only has 1 column, it must be 2D).
    if (namesValues[row] == "" || namesValues[row] == " ") { //If the name cell of this row is empty or blank then...
      newCBValues[row][0] = " "; //Set the value to one space (which will make the cell NOT true or false, and thus NOT display a checkbox).
      //Logger.log("newCBValues[" + row + "][0]: " + newCBValues[row][0]);
    }else{ //otherwise, if the name cell isn't blank...
      if (cbValues[row][0] === true) {
        newCBValues[row][0] = true; //Keep the checkbox checked if it's already checked
      }else{ //If the name cell isn't blank, and it's not true...
        newCBValues[row][0] = false; //Then Keep it or set it to False (an empty checkbox):
      }   
    }
  }
  checkboxes.setValues(newCBValues); // now that we have a completed array of our new checkbox values, let's edit the sheet with them!

}

Step 3:
Back in the spreadsheet, insert, edit, delete or type in a few names in your names column to see the changes.

That's it!

---

OLD (SLOW) ANSWER:
If you don't mind adding a script to your sheet, I figured out how to use the "new" checkboxes, but only show the boxes when you have a corresponding Name in the name column. For the purpose of my script, I did make named ranges of the checkbox column ("checkboxes"), and the names column ("names"). You must also create the same named ranges if you wish to use this script (or just hard code the ranges into the script -- which is not a great way to go if you are going to be changing the size of the range frequently by adding and removing names. Go to the menu Data > Named ranges...

Anyway, I created an updated example you can demo on this page: https://docs.google.com/spreadsheets/d/1MSnQ1_6Sy018lAoehzQ55QVrfJldoKS5EnWjOxYWxIs/edit#gid=1464332245

You will need to make a copy of this workbook, and then give permission to yourself to use the script below once you add it to your workbook from the menu Tools > Script Editor:

function onEdit() {
  var app = SpreadsheetApp;
  var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

  var names = activeSheet.getRange("names"); //Hard Code the Range here if you don't want to use named ranges (example: "B2:B")
  var checkboxes = activeSheet.getRange("checkboxes"); //Hard Code the Range here if you don't want to use named ranges (example: "A2:A")

  var namesFirstRow = names.getRow();
  var namesLength = names.getLastRow();
  var namesCol = names.getColumn();
  var checkboxesCol = checkboxes.getColumn();

  for (var i=0; i<namesLength; i++) {
    var checkCell = activeSheet.getRange(namesFirstRow+i,checkboxesCol);
    var nameCell = activeSheet.getRange(namesFirstRow+i,namesCol);
    Logger.log(checkCell.getA1Notation() + "  " + nameCell.getA1Notation());

    if (nameCell == "" || nameCell.isBlank()) { //If There is no name in the name column (it's empty or just blank)
      checkCell.setValue(" "); // Make the checkbox column blank so no checkbox appears 
    }else{
      if (checkCell.getValue() === true || checkCell.getValue() === false) {
        //Do nothing since a value is already set
        //app.getUi().alert(checkCell.getA1Notation() + " is " + checkCell.getValue());
      }else{      
        checkCell.setValue("FALSE"); //Place an empty checkbox (FALSE) if a name value has not been set in the name column
      }
    }
  } 

}

This will only work if you make sure your "checkboxes" range has already had the data validation selected to the "checkbox" criteria...

Instructions: For the column you need your checkboxes in: highlight the entire range of cells in the column (except for the header), right click on the selected column, choose "Data Validation" and then choose "Checkbox" for the criteria. Then all your "TRUE" and "FALSE" cells will be checked or unchecked checkboxes. Additionally, to integrate all the named ranges that are inside the script, make sure name this checkbox column range "checkboxes" (as per the instructions above).