Google Sheet Custom Function Not Recaculating

Question

Hi I have read about the caching issues with custom functions in Google Sheets, but I'm trying to understand why the following will successfully update if a cell is changed:

function doob(input){
 return input * 2;
}

but this will not update:

function doob(input){
 var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var range = sheet.getRange(input);
 var values = range.getValues();
 return values[0][0] * 2 ; 
}

I need to get the range in order to use the .getBackgroundColor() of each cell.

How are you sending as parameter? try with =doob("A1") ... or the range where you have your data. let me know if you still have error. — Gerardo
– Gerardo, Commented Jul 23, 2015 at 21:50

JPV · Accepted Answer · 2015-07-26 12:28:33Z

2

Probably because in the first function you would pass the cellreference directly,

=doob(A1)

and in the script the value of that cell is used. In the second, you would probably have to pass the range as string (since you want to get the backgroundcolors, so you are not after the values of that range, right ?)

=doob("A1:B8")

As you know, custom functions suffer from memoization. To work around that you could pass in the range a second time, without the quotation marks.

=doob("A1:B8", A1:B8)

That second paramater is a 'dummy' paramater as the script does nothing with it. BUT: any change in values in that range should make the custom function re-evaluate. However I don't know if that is gonna help you a lot if your final goal is to get the backgroundcolors.

edited Jul 26, 2015 at 12:28

answered Jul 24, 2015 at 9:42

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gneek Over a year ago

Yes! This totally works. Thank you, and the range is recalculated as well so I'm able to get the background colors of cells.

Collectives™ on Stack Overflow

Google Sheet Custom Function Not Recaculating

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related