0

I have many rows that I want to count how many cells are bolded in a Google Sheets. I've looked through plugins and searched for pre-made code snippets (I have no programming skills).

I found a script that does exactly what I want it to do, but the cells it checks and where it outputs the answer is hard coded in located here (answer from user random-parts): Count Bold Cells in Google Sheets Script

I have several hundred rows that I want to know how many, if any, bolded cells, so I would have to make hundreds of separate scripts to use that. I've tried to turn it into a command I can drag/copy on the sheet but that skill is way beyond me.

This is the code that counts all the cells with bold, but the range input and output is hard coded in

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

}

Can someone please provide a script and formula that would allow me to define a range in the sheet and then drag it and have it update?

Martin
  • 1
  • In order to correctly understand your situation, can you provide a sample Spreadsheet and the result you want? Of course, please remove your personal information. – Tanaike May 26 '19 at 22:23

1 Answers1

0

Here is the link to a working example, and below is the code behind. The important thing to note is that you need to refresh your browser window for formulas to re-calculate. The formula result cannot be refreshed by just changing the cells style.

function onOpen(){
  refresh()
}

function refresh(){
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet2")
  var formulaCol = sheet.getRange("E:E").getColumn()
  var formulaRange = sheet.getRange(2, formulaCol, sheet.getDataRange().getLastRow()-1, 1) 
  var rowCount = formulaRange.getLastRow()-formulaRange.getRow()+1
  var dummyFormulas = []
  var formulas = formulaRange.getFormulas()
  for(var i=0; i<rowCount; i++){
    dummyFormulas.push(['=""'])
  }
  Logger.log(dummyFormulas)
  Logger.log(formulas)
  formulaRange.setFormulas(dummyFormulas)
  SpreadsheetApp.flush()
  formulaRange.setFormulas(formulas)
}

function countBoldCells(startRow, startColumn, endRow, endColumn, random){
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = SpreadsheetApp.getActiveSheet().getRange(startRow, startColumn, endRow-startRow+1, endColumn-startColumn+1)

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

  return filter_bold.length;
}
GoranK
  • 1,628
  • 2
  • 12
  • 22
  • Thanks for the suggestion. I don't think that gets me what I'm looking for. It still only outputs to 1 cell. I'm hoping to update hundreds of cells at the end of each row. I found this example that lets you drag a simple formula just like the standard ones in Sheets, but this one is the sum of the contents, I just need to count the cells: [link](https://stackoverflow.com/questions/14248726/google-spreadsheet-sum-of-all-bold-cells) – Martin May 26 '19 at 19:07
  • Thank you so much for updating this suggestion. – Martin Jun 01 '19 at 23:10
  • The function works, but it isn't really doing what I need it to, not sure if I'm doing something wrong. There are 2 challenges I'm having: 1) I want to have the total number of bolded cells read from a different sheet in the same document. I tried using this formula =countBoldCells(row('Main Data'!C2),COLUMN('Main Data'!C2),row('Main Data'!Z2),COLUMN('Main Data'!Z2)) but that didn't work. 2) Once the script has run it won't update the count, even if I refresh AND have the script run again. and lastly its changing the cells the formula is in to green (but I'm okay with this) – Martin Jun 01 '19 at 23:17