Automating Checkboxes in Google Sheets with Apps Script

What is Google Apps Script?

Google Apps Script, a JavaScript-based language, automates and enhances tasks across Google Workspace apps like Sheets, Docs, and Gmail. Users create custom scripts, triggers, and workflows in a web-based IDE, empowering them to manipulate data within Google services. The tool’s accessibility to users with basic programming knowledge, combined with seamless integration with Google services, positions Google Apps Script as a potent tool for productivity enhancement.

Automating Checkboxes in the Sheets

1. Create a New Sheet:

   • Open your Google Sheet
   • Set up fake data with mackaroo
   • (Optional) set the named ranges, Data ⇒ Named ranges
   • Click on Extensions and then select Apps Script

2. Initialize the Script:

   • The initial code will look like this:

     function myFunction() {
     	//
     }
     

3. Rename the Function:

   • Change the function name to addCheckboxes:

     // Define a function named addCheckboxes
     function addCheckboxes() {
     	// Get the active sheet of the spreadsheet
     	const spreadsheet = SpreadsheetApp.getActiveSheet();
     
     	// Define the range where the data is located (e.g., "invitation" range)
     	// or A2:E
     	const range = spreadsheet.getRange("invitation");
     
     	// Get the values from the specified range
     	const list = range.getValues();
     
     	// Iterate through each row in the list
     	list.forEach((person, index) => {
     		// Extract data for each person
     		const firstName = person[1];
     		const lastName = person[2];
     		const email = person[3];
     		const inviteCol = person[4];
     
     		// Check if all required data is present and invite column is empty
     		if (firstName && lastName && email && iunviteCol === "") {
     			// Get the cell where the checkbox will be inserted
     			// index + 1, becasue it starts from 2nd row
     			// 5 is the E column that we want to add checkboxes
     			const cell = range.getCell(index + 1, 5).getA1Notation();
     
     			// Uncomment the following line if you want to log the cell location
     			// Logger.log(cell);
     
     			// Insert a checkbox in the specified cell
     			spreadsheet.getRange([cell]).insertCheckboxes();
     		}
     	});
     }
     

4. Run the Script:

   • Click the “Run” button.
   • A pop-up will appear, stating “Authorization required. This project requires your permission to access your data.”

5. Authorize the Script:

   • Click on “Review permissions”
   • Choose the Google account associated with the Google Sheets document
   • Click “Advanced” at the bottom of the permissions pop-up
   • Click on “Go to [Untitled Project]” (or the name of the Apps Script)
   • Review the permissions required by the script
   • Click the “Allow” button to grant the necessary permissions

Resources

mockaroo

Google Apps Script

Google Apps Script Guide with the Sheets

Apps Script Reference for the Sheets

Thank you!

Thank you for your time and for reading this!