Count Bold Cells in Google Sheets Script

2019-07-22 12:47发布

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);
  }

}

3条回答
Melony?
2楼-- · 2019-07-22 13:36

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);

}
查看更多
Summer. ? 凉城
3楼-- · 2019-07-22 13:41

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

查看更多
戒情不戒烟
4楼-- · 2019-07-22 13:50

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.
}
查看更多
登录 后发表回答