Update custom function automatically

Question

I have a custom function as a Google Spreadsheet script, which depends on some other cells' values. It runs ok the first time I put it in a cell, but then when I change the data in the other cells it stays the same. How can I make the first cell update its value automatically when I change the other cells?

Answer 1

I found an answer which doesn't fully make me happy but it's good enough I guess. If you add a function called onEdit to the script of a spreadsheet, it will be called every time stuff is edited. So, this is the code that worked for me: (it has some details that could be useful so I left them unedited)

function onEdit(event) {
  if (SpreadsheetApp.getActiveSheet().getName().substr(0,5) == "thing")
    SpreadsheetApp.getActiveSheet().getRange("I1").setValue(myCustomFunction());
}

This makes sheets whose name begins with thing, get the result of myCustomFunction into cell I1.

Answer 2

I have a similar problem.

This is how I am doing it atm, but its not the best solution. I am looking for a better one.

If any value at sheet Prices and column D changes. Meaning if any cell value changes in the whole column it updates the custom function value.

//Search Price sheet with the given name. Return price. dummy param updates google ss once the "Prices" sheet values changed.
function searchPrice(price,dummy)
{
  var SPREADSHEET_NAME = "Prices";
  var SEARCH_COL_IDX = 2;
  var RETURN_COL_IDX = 3;
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
  for (var i = 0; i < values.length; i++)
  {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == price)
    {
      return row[RETURN_COL_IDX];
    }
  }
}

This is how you call it =searchPrice(B8,Prices!D:D)

Just give your custom function a dummy param. It doesn't do anything in the custom function.