CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
gneek
gneek

Reputation: 87

Google Sheet Custom Function Not Recaculating

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.

Upvotes: 0

Views: 111

Answers (1)

JPV
JPV

Reputation: 27242

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.

Upvotes: 2

Related Questions