Google Drive - Google Apps Script

UPDATE no. 2: Implementation Guide: SUM values if font is bold – Google Drive Spreadsheet

NEW: Screen recording of the solution at the end of this post.

This is second part of the guide of how to implement custom function into Google Drive Spreadsheets.

UPDATE: If you would like to easily update calculated values from this custom function, than you have to go following way: http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet – I just followed mentioned guide there and I have extra button in menu now, which allows me easily refresh all values which were calculated by my custom function.

I have been receiving many questions how exactly use the script mentioned in previous part of this guide so I decided, that it would be better to write it down incl. screenshots.

1. Open some of Your already created spreadsheet or create blank one

2. Using main menu at the top choose: Tools -> Script manager

Scripts manager screenshot

3. In this dialog click New button and it will guide You to the Scripts editor

4. Paste following code into editor, give some name to the script (top left corner input field) and Save it (Ctrl+S)

function sumWhereIsFontWeight(rangeSpecification) {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);

var x = 0;

for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {

var cell = range.getCell(i, j);

if(cell.getFontWeight() == 'bold')
x += parseFloat(cell.getValue());
}
}

return x;
}

Screenshot of Script editor

4. Get back to spreadsheet window and reload Your Scrip Manager dialog (circle arrow icon in top right corner) and You should see Your newly created script

5. Now You should be able to use this function in Your spreadsheet. To make it more clear, I added to the A column demo of what I added into columns in the B column (of course without those slashes at the beggining “//”). Here is screenshot of demo:

Screenshot of script applied

 UPDATE 2 (April 6th 2018):
I have been asked multiple times in the comments, if this solution still works etc. So I did test on my Google Drive today and here is output:
Share this:

17 thoughts on “UPDATE no. 2: Implementation Guide: SUM values if font is bold – Google Drive Spreadsheet

  1. How do you refresh it? For me refreshing works only when I change the range, it does not refresh on the fly, meaning after bolding/unbolding a cell.

  2. I still am not able to refresh on the fly – did you ever find a solution for bolding versus unbolding and the script updating accordingly without having to change the range?

  3. When I try this it says “Result is not a number” – cells in range ONLY have numbers in them! Any thoughts?

  4. Hi, Thanks for the script! It works perfectly fine. If i would like to add a criteria in the formula. what can be done?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.