Count Bold Cells in Google Sheets Script

Question

So, I'm not much of a coder to be honest, but I've managed to fumble my way through counting cell background colour, but struggling to get it to work for counting cells where the font is bold. I've detailed my function below, which counts only 6 cells with a bold font style, but there is 13 cells with a bold font style.

function countboldcells() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = sheet.getRange("E2:S7");
  var range_output = sheet.getRange("G14");
  var cell_styles = range_input.getFontStyle();
  var count = 0;

 for(var r = 0; r < cell_styles.length; r++) {
    for(var c = 0; c < cell_styles[0].length; c++) {
      if(cell_styles.isBold = true) {
        count = count + 1;
      }
    }
     range_output.setValue(count);
  }

}

score 3 · Answer 1 · answered Dec 12 '17 at 21:27

3

Your if statement needs to have 3 "=" inside the parentheses
if(cell_styles.isBold === true)

answered Dec 12 '17 at 21:27

Steve Bohmbach

327
1
11

score 3 · Answer 2 · answered Dec 12 '17 at 22:35

getFontWeights() is the method that will return bold or not. Then the easy way to count them would be to flatten the array, filter all of the "bold" elements and get the length of the filtered list

function countboldcells() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = sheet.getRange("E2:S7");
  var range_output = sheet.getRange("G14");

  // Get the fontWeights of the range and flatten the array
  var cell_styles = range_input.getFontWeights().join().split(",");

  // Filter out any value that is not "bold"
  var filter_bold = cell_styles.filter(function (e) { return e == "bold" });

  // Set the count
  range_output.setValue(filter_bold.length);

}

score 1 · Answer 3 · answered Dec 13 '17 at 02:23

Here is your code with corrections. Explanations are in the comments.

function countboldcells() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = sheet.getRange("E2:S7"); 
  var range_output = sheet.getRange("G14");
  var cell_styles = range_input.getFontWeights(); // getFontStyle can only return 'italic' or 'normal'
  var count = 0;

  for(var r = 0; r < cell_styles.length; r++) {
    for(var c = 0; c < cell_styles[0].length; c++) { // isBold is a method for Google Documents only (not sheets) 
      if(cell_styles[r][c] === "bold") { // you need at least two '=' signs // also include the index of cell_styles
        count = count + 1; // count += 1 would also work
      }
    }
  }
  range_output.setValue(count); // make sure you setValue only when booth loops are done.
}

Count Bold Cells in Google Sheets Script

3 Answers3

Linked