Automatic insert Checkboxes in Google Sheets based on Cell Changes

In this blog, we’ll explore how to automatic insert checkboxes in Google Sheets based on Cell Changes. For example when task added in checklist then checkbox will insert automatically in Google Sheets.

Manual Methods to Insert Checkboxes in Google Sheets

Before diving into automation, let’s cover the basics of inserting checkboxes manually in Google Sheets. There are two main options for adding checkboxes in Google Sheets:

1. Using the Insert Menu

Open Google Sheets and select the cell or range where you want to insert checkboxes in Google Sheets.
Click on the “Insert” menu at the top of the screen.
Then you can see there is Checkbox option Click on “Checkbox” option.

This will add checkboxes to the selected cells or range in Google Sheets. It’s a straightforward method for adding checkboxes to your sheet, but it’s manual and doesn’t automatically adjust based on changes in other cells.

2. Insert Checkboxes from Data Validation

Another way to insert checkboxes is by using data validation:

Select the cell or range where you want to insert Checkboxes in Google Sheets.
Go to “Data” in the top menu and select “Data Validation. Image given above.
In the Data Validation dialog, click on dropdown menu and scroll down from “Criteria“. Then choose “Checkbox“. Image given below.

This method provides a bit more flexibility, allowing you to specify conditions under which a cell should be checked or unchecked and also Show warning or Reject input when data is invalid.

Automatic insert Checkboxes with Google Apps Script

Now, automate the checkbox insertion process using Google Apps Script. With a simple script, you can set up Google Sheets to automatically add checkboxes to a specific column whenever a change is detected in another column.

Below is a basic script to get you started:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var value = e.value;
  var requiredValue = “TRUE”;
  var timestamp = new Date();
 
  if (col == 2) { // Check if the edited cell is in column B
    var checkboxRange = sheet.getRange(row,3); // Target cell in column C
    checkboxRange.insertCheckboxes();
  }
}

How the Script Works

onEdit(e): This is a simple trigger function that automatically runs whenever you make an edit in the spreadsheet.
e.source.getActiveSheet(): Gets the active sheet where the edit occurred.
e.range: Retrieves the range of cells that were edited.
var row = range.getRow(); and var col = range.getColumn();: Get the row and column of the edited cell.
if (col == 2): Checks if the edit occurred in column B (you can change this column as needed).
var checkboxRange = sheet.getRange(row, 3);: Selects the corresponding cell in column C of the same row.
checkboxRange.insertCheckboxes();: Inserts a checkbox into the selected cell.

How to Add the Script to Your Sheet

Open your Google Sheet.
Go to “Extensions” menu and click on “Apps Script“.
Delete any existing code in the script editor and paste the script given above.
Click the disk icon to save the script and name it something like “AutoInsertCheckboxScript.”
Close the script editor.

From now on, whenever you edit a cell/add task in column B, a checkbox will automatically appear in column C of the same row.

Conclusion

Automatic insert Checkboxes in Google Sheets is a great way to enhance your spreadsheets and improve productivity. By using the Google Apps Script provided above, you can set up your sheet to automatically add checkboxes based on changes in specific cells. This automation can help streamline your workflows and make managing tasks and projects easier.

If you have any questions about using Google Sheets or Google Apps Script, or if you need further assistance, feel free to leave a comment below.