Sum cells if they are not bold

Question

I'm confused with my Google Apps script which is purposed to calculate the sum of the cells only if these cells are bold.

Here is the source:

function SumIfNotBold(range, startcol, startrow){
  // convert from int to ALPHANUMERIC 
  // - thanks to Daniel at http://stackoverflow.com/a/3145054/2828136
  var start_col_id = String.fromCharCode(64 + startcol);
  var end_col_id = String.fromCharCode(64 + startcol + range[0].length -1);
  var endrow = startrow + range.length - 1

  // build the range string, then get the font weights
  var range_string = start_col_id + startrow + ":" + end_col_id + endrow
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var getWeights = ss.getRange(range_string).getFontWeights();

  var x = 0;
  var value;
  for(var i = 0; i < range.length; i++) {
    for(var j = 0; j < range[0].length; j++) {
      if(getWeights[i][j].toString() != "bold") {
        value = range[i][j];
        if (!isNaN(value)){
          x += value;
        }
      }
    }
  }
  return x;

Here is the formula:

=(SumIfNotBold(K2:K100,COLUMN(K2), ROW(K2)))*1

I have three major concerns:

1. When I set up a trigger to launch this script on any edits I accidentally receive an email from Google Apps stating that

TypeError: Cannot read property "length" from undefined. (line 7, file "SumIfNotBold")

Thus, how can I fix it? Are there any ways to ignore these automatically delivered notifications?

2. The formula doesn't calculate the sum of cells if they are on the other list. For example, if I put the formula on B list but the cells are located on A list then this script doesn't work properly in terms of deriving wrong calculations.

3. When the cell values are updated the formula derivation is not. In this case I'm refreshing the formula itself (i.e., changing "K2:K50" to "K3:K50" and once back) to get an updated derivation.

Please, help me with fixing the issues with this script. Or, if it would be better to use a new one to calculate the sum in non-bold cells then I'll be happy to accept your new solution.

Answer 1

Here is a version of this script that addresses some of the issues you raised. It is invoked simply as =sumifnotbold(A3:C8) or =sumifnotbold(Sheet2!A3:C8) if using another sheet.

As any custom function, it is automatically recalculated if an entry in the range to which it refers is edited. It is not automatically recalculated if you change the font from bold to normal or back. In this case you can quickly refresh the function by delete-undo on any nonempty cell in the range which it sums. (That is, delete some number, and then undo the deletion.)

Most of the function gets a reference to the passed range by parsing the formula in the active cell. Caveat: this is based on the assumption that the function is used on its own, =sumifnotbold(B2:C4). It will not work within another function like =max(A1, sumifnotbold(B2:C4).

function sumifnotbold(reference) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  }

  // everything above is range extraction from the formula
  // actual computation begins now

  var weights = range.getFontWeights();
  var numbers = range.getValues();  
  var x = 0;
  for (var i = 0; i < numbers.length; i++) {
    for (var j = 0; j < numbers[0].length; j++) {
      if (weights[i][j] != "bold" && typeof numbers[i][j] == 'number') {
        x += numbers[i][j];
      }
    }
  }
  return x;
}