4

I have some trouble with a spreadsheet: as said in the title, I put in a column a formula which is calling a custom script based on the value of another cell, but when I modify this other cell, the formula does not update... This seems to work with standard formulas, but, in my cell calling my script:

  • If I try to add a blank in the fomula cell, the result is still not updated.
  • If I clear the formula cell, and re-type the formula, it's still showing the old value.
  • If I copy paste the formula cell in another one, the new cell is up-to-date.

Here is my script. If few words: for given 'company' parameter, it search for all rows matching this criterion and store the 3rd column cell in a variable, finally returned ( so return the last value ):

function getLastStatut(company) {
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

  var out = "not found";
  var row;
  for(i in values){
    row = values[i];
    if(row[1]==company){
      out = row[2];
    }
  }
  return out;
}

And for example:

  • A1 : Date
  • A2 : Test
  • A3 : Running
  • A4 : =getLastStatut(B1)

So A4 display "Running", but if I change A3, it still shows "Running", whereas it should display the value of A3.

Is this a bug or is there something I'm doing wrong? Any help is welcome.

Alexis

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
Alexis
  • 707
  • 8
  • 33

1 Answers1

7

The problem is with the caching "feature" for custom functions. I explain it in this other thread, please read.

But the bottom line here is, a custom function should not access data that is not static, all variable information should be passed as parameter. In your case, the whole data set should be a parameter of your script. Custom functions should never get any range at all.

//getting values like this is wrong (for a custom function)
var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
Community
  • 1
  • 1
Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Thanks Henrique, it's a shame i didn't find the link you gave. I finally changed my function definition to have: 'function getLastStatus(company,values)' and call it this way '=getLastStatut(B19,A:C)' as I only need to parse the first three columns. – Alexis May 11 '12 at 06:26
  • 1
    Sometimes it's difficult to come up with a good search criteria. Specially when we're not sure what the problem is :) – Henrique G. Abreu May 11 '12 at 14:36