javascript - Count Bold Cells in Google Sheets Script - Stack Overflow

admin管理员组

文章数量:1335130

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

}

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

}

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Dec 12, 2017 at 23:21 Wicket 38.5k99 gold badges7878 silver badges193193 bronze badges asked Dec 12, 2017 at 21:23 Christo GallagherChristo Gallagher 2111 silver badge22 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 3

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

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

}

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

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.
}

本文标签： javascriptCount Bold Cells in Google Sheets ScriptStack Overflow