You have checkboxes in your spreadsheet, you’ll like to count them easily. Well, this script I’ll provide will help you with exactly that. Non-coders can easily follow instructions and add this script to their projects. Namaste! This is Nibesh Khadka. I am a freelancer that develops scripts to automate Google Workspace Apps like Gmail, Google Sheets, Google Docs, etc. For Non-Coders If you’re not a coder and don’t wanna be bothered with a tedious explanation then you can just follow these steps: Open Script editor. On your spreadsheet click the extensions tab and open the apps script as shown in the image above. Remove all the code in . Then, copy and paste the code from the section in this blog. code.gs Full Code Save and then reload the . Now, reopen the Apps script as instructed in step 1 ( spreadsheet Sometimes the script doesn't work without saving and reloading). After this, you just have to make a minor edit as instructed below. Full Code Here’s the full script for this blog: // make minor change here.
// insert the number equivalent of your column with checkboxes here inside square boxes, like [1,2]
// for instance 1 for A, 5 for E, and so on.
const checkBoxColNumber = [5, 8];

/**
 * The function creates a custom menu in spreadsheet
 */
function menu() {

  SpreadsheetApp.getUi()
    .createMenu("Count Checkboxes")
    .addItem("Count Checkboxes", "countCheckbox")
    .addToUi();

}

/**
 * OnOpen is resposbile to insert menu to spreadsheet.
 */
function onOpen(e) {
  menu();
}



/**
 * CountCheckbox goes through the active sheet and counts the number of checked and unchecked box among non-empty rows
 */
function countCheckbox() {

  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();
  // remove first header column
  data.shift();

  // initate counts as zero
  let trueCount = 0;
  let falseCount = 0;

  // value count will be a nested list but as same length as Counting Checkboxes
  let checkBoxColValueCounts = [...checkBoxColNumber];

  // loop through checkBoxColNumber list and data list 
  for (let j = 0; j < checkBoxColNumber.length; j++) {
    for (let i = 0; i < data.length; i++) {
      // [...new Set(data[i])].filter(String) check to get unique value if its either just true or false
      if ([...new Set(data[i])].filter(String)[0] === true || [...new Set(data[i])].filter(String)[0] === false || data[i].join("") === "") { continue; }
      // if values is true push increase true count else false count
      if (data[i][checkBoxColNumber[j] - 1] === true) {
        trueCount += 1;

      } else {
        falseCount += 1;

      }
    }

    // add true and false count as nested list in respective place
    checkBoxColValueCounts[j] = [trueCount, falseCount];
    // reset 
    //console.log(checkBoxColValueCounts)
    trueCount = 0;
    falseCount = 0;
  }

  // create alert string with all info embeded
  let countStatsString = "";
  for (let j = 0; j < checkBoxColValueCounts.length; j++) {
    countStatsString += `\n In the ${checkBoxColNumber[j]} column, There are ${checkBoxColValueCounts[j][0]} boxes checkd and ${checkBoxColValueCounts[j][1]} unchecked boxes among non-empty rows.`
  }
  // alert the result
  SpreadsheetApp.getUi().alert(countStatsString);
} Using Script in Your Spread Sheet For this script to work in your spreadsheet you’re gonna have to make a tiny change in code. // make minor change here.
// insert the number equivalent of your column with checkboxes here inside square boxes, like [1,2]
// for instance 1 for A, 5 for E, and so on.
const checkBoxColNumber = [5, 8]; See this code in the beginning, you’ll have to replace these values with the number equivalent of the letters representing the columns with the values in your spreadsheet. For instance, 5 for E and 8 for H. If it’s just one column then just insert one value inside the square box like [5]. [5, 8] checkbox without the comma Navigating the Menu After this, you should save the script and reload your spreadsheet you should be able to see the menu in your spreadsheet, with the title “Count Checkboxes“ similar to the image below. When you run this function you’ll see an alert box with the sum of all checked and unchecked boxes for non-empty rows in your spreadsheet. Thank You for Your Time If you would like me to write short and quick blogs like these then leave requests in the comments. I make Add-Ons for and can also write Google Apps Scripts for you. If you need my services let me . Google know

Removing Duplicates in Google Sheets: A Guide for Non-coders

How to Highlight Duplicate Rows in Google Sheets?

Subscribe To Our News Letter For Free Contents

Hire Me

Newsletter

Buy Me a Coffee

Nominated for 2022 - Tech Tutorial Teacher of the Year

Too Long; Didn't Read

Make resilience your competitive advantage

Using Apps Script to Count Checkboxes in Google Sheets

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Access User Location with Permission Handler in Flutter

104 Stories To Learn About Go

105 Stories To Learn About Functional Programming

100+ Free Pluralsight Courses to learn Python, Java, and Spring Boot

10 Websites to Learn JavaScript for Beginners

Access User Location with Permission Handler in Flutter

104 Stories To Learn About Go

105 Stories To Learn About Functional Programming

100+ Free Pluralsight Courses to learn Python, Java, and Spring Boot

10 Websites to Learn JavaScript for Beginners

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps