Formula with custom script doesn't update in Google Spreadsheet

4
votes

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

1
google-sheetsgoogle-apps-script

1 Answers

7
votes

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();