0

I am trying to use a script in Google Sheets that allow me to sum all bold cells I read this post, but it doesn't works. I don't know if i am the problem, or if the function is not usefull for 2022 Google Sheets.

12 13 362

14 512 156

35 156 32

¿How can i sum the bold ones? I am sorry if my english is not the best, i hope that you could understand my problem

player0
  • 124,011
  • 12
  • 67
  • 124
elrul0
  • 9
  • 1

2 Answers2

0

Description

Use Range.getTextStyles() and then TextStyle.isBold() to determine which values should be added together.

Script

function runTest() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Sheet6");
    var range = sh.getDataRange();
    var values = range.getValues();
    var format = range.getTextStyles();
    var sum = 0;
    var i = 0;
    var j = 0;
    for( i=0; i<values.length; i++ ) {
      for( j=0; j<values[0].length; j++ ) {
        if( format[i][j].isBold() ) sum = sum+values[i][j];
      }
    }
    console.log(sum);
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Reference

TheWizEd
  • 7,517
  • 2
  • 11
  • 19
0

You can build a custom function as follows

function sumBold(rng) {
  var rngAddress = SpreadsheetApp.getActiveRange().getFormula().match(/\(([0-9A-Z:]+)/).pop().trim(); 
  var rng = SpreadsheetApp.getActiveSheet().getRange(rngAddress)
  var values = rng.getValues()
  var weights = rng.getTextStyles()
  var total = 0
  values.forEach((r,i) => r.forEach((c,j) => total += (!isNaN(c) && weights[i][j].isBold()) ? c : 0))
  return total
}

then =sumBold(A1:C3)

enter image description here

edit

to update when only the text style has been changed, add a dummy parameter as a checkbox

enter image description here

Custom functions are recalculated only when the spreadsheet is opened and when one of the arguments values are changed.

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Thaks Mike! the function works, but it only recognizes bold values when I first enter sumBold(), and then the sum of the cell I entered sumBold() in doesn't change when I change the cell's text type to bold. Is there an option for the function sumBold() to update automatically the sum when changing the text type of the values within the range I entered? Thanks you so much!! – elrul0 Mar 09 '22 at 15:46
  • Yes, the custom function is not updated when you change only the text style. To update, add a dummy paramater, for instance a checkbox, and check/uncheck to update. See my edit answer. There is no other simple solution. Custom functions are recalculated only when the spreadsheet is opened and when one of the arguments values are changed. – Mike Steelson Mar 09 '22 at 16:07
  • @elrul0 did you try the edited solution? – Mike Steelson Mar 12 '22 at 16:24