Google Sheets – Add a CheckBox with a Script

google sheetsgoogle-apps-script

How can I use a script to add a CheckBox to a Sheet?

This is probaly trivial but I could not find a single example online and the CheckBox class isn't even mentioned in the docs.

From the GUI, I would go Insert -> CheckBox.

Setting the value of the cell to either TRUE or FALSE doesn't work.

Best Answer

Short answer

Add the checkbox from the Google Sheets UI, then use one of the copyTo methods of Class Range.

Explanation

NOTE: On April 19, 209 Google included on the Google Apps Script release notes the requireCheckbox method. See more details on my other answer.

The Google Apps Script Spreadsheet service doesn't include a methods for everything that could be done through the Google Sheets user interface. This is the case of the Insert > Checkbox which is a pretty new feature.

Even the Record macro feature can't do this. The following was recorded one momento ago

/** @OnlyCurrentDoc */

function InsertCheckbox() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  /*
   * Added to show the missing Insert > Checkbox step
   */
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('A1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

NOTE: If you don't want to pass all the cell properties (borders, formulas, background, etc. instead of SpreadsheetApp.CopyPasteType.PASTE_NORMAL use SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION.

Related Q on Stack Overflow

Placing checkboxes in Google Sheets using Apps Script

Related Solutions

Google-sheets – How to display the content of a (hidden) cell in a message box with a script

Besides some "style error" (missing var, ;) and the use of getValues() instead of getValue() the script looks to show the value of C2 from the Twee begrippen sheet. Perhaps you expect a value from another sheet, if so, you should replace Twee begrippen by the correct sheet name or use ss.getActiveSheet() to get the C2 value from the active sheet.

Google-sheets – How to you conditionally swap out a dropdown on a range of cells in Google Sheets

function dataValidation() {                                                                                     
var ss = SpreadsheetApp.getActiveSpreadsheet();                                                             
var Tr = ss.getRange("'Body Armor'!C1:C20").getValues(); //Testrange
// Variables and sheetnames
        var b = "'Body Armor'!";                                                                        
        var g = "GS256!" ;
        var gC = "'GS256 Classified'!";
        var h = "B10:B27";
        var l = "B28:B36";
// Get DV ranges
        var gh = ss.getRange(g+h) ;                                                                     
        var ghc = ss.getRange(gC+h);
        var gl = ss.getRange(g+l);                                                                      
        var glc = ss.getRange(gC+l);
// Build DV rules
        var ghr = SpreadsheetApp.newDataValidation().requireValueInRange(gh,true);
        var ghcr = SpreadsheetApp.newDataValidation().requireValueInRange(ghc,true);
        var glr = SpreadsheetApp.newDataValidation().requireValueInRange(gl,true);
        var glcr= SpreadsheetApp.newDataValidation().requireValueInRange(glc,true);
// Loop through each cell
        for(i=0;i<Tr.length;i++) {
                var j = i+1;
                var Hr = ss.getRange(b+"H"+j);
                var Jr = ss.getRange(b+"J"+j);
                var Lr =  ss.getRange(b+"L"+j);
// Test the test range and apply corresponding DV
        if (Tr[i][0] == true) {
                Hr.setDataValidation(ghcr);
                Jr.setDataValidation(ghcr);
                Lr.setDataValidation(glcr);
                }
                else {
                Hr.setDataValidation(ghr);
                Jr.setDataValidation(ghr);
                Lr.setDataValidation(glr);
                }
        }
}