Google script to change the contents of a cell to a checked checkbox if the cell is "TRUE"

1
votes

Beginner here. I have a google sheet in which columns L, M, and N can contain the following things: "TRUE", "FALSE", blank, something else. I would like to write a script that looks at columns L, M, and N, if the value in a cell is "TRUE", it inserts a checked checkbox, and if the value in a cell is "FALSE", it inserts an unchecked checkbox. If the cell contains anything else, I would like the script to leave it as is. Thanks in advance for any tips!

3
if-statementgoogle-apps-scriptcheckboxgoogle-sheets
L, M and N are columns not rows. Do you agree? – Cooper
You mention blank, something else do you want it to be an unchecked box or do you want to leave it blank or something else? – CodeCamper
@Cooper Yes, they are columns, not rows. I apologize! – Jen
If the cell is blank or something else (usually n/a), I want to leave it as is - so leave it blank if it is blank, leave it n/a if it is n/a, etc. Essentially, I do not want to script to consider these cells. – Jen

3 Answers

1
votes
function addCheckbox() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:A11').activate();
  var value = spreadsheet.getRange('A1:A11').getValue();
  if(value == "TRUE" || value == "FALSE" ){
  spreadsheet.getRange('A1:A11').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireCheckbox()
  .build());
}
};

Output before function:

enter image description here

Output after function:

enter image description here

Adjust it to your columns / rows.

1
votes

Try

function addCheckboxAll() {
  var rule = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false)
    .requireCheckbox()
    .build()
  SpreadsheetApp.getActive().getRange('A1:A').setDataValidation(rule);
};

if you only want to define checkboxes on true/false cells

function addCheckbox() {
  var rng = SpreadsheetApp.getActive().getRange('A1:A')
  var values = rng.getValues()
  var rule = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false)
    .requireCheckbox()
    .build()
  var rules=[]
  values.forEach(function(val){
    if (val==true || val==false){rules.push([null])}else{rules.push([rule])}
  })
  rng.setDataValidations(rules)
};
0
votes

It is not necessary to use a google app script to accomplish this, when you insert a checkbox all you have to do is type in the formula bar the formula you want. As long as it returns a true or false it will retain the checkbox.

Say column A contains a series of TRUE and other

In cell B1 you could put =arrayformula(if(A:A=true,TRUE,FALSE))

Then highlight column B right click last option is Data Validation and select Checkbox.

There is no reason to do this with a google app script... unless there is another part to this question.