RLearnsR - 6 months ago 21

Javascript 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`

- 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?

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

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