Custom function won't refresh as inputs are changed

Question

I have a custom function that finds the value of another cell and displays it. When the source cell is changed, the function does not reflect.

https://docs.google.com/spreadsheets/d/1wfFe__g0VdXGAAaPthuhmWQo3A2nQtSVUhfGBt6aIQ0/edit?usp=sharing

Refreshing google sheets

function findRate() {
  var accountName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1).getValue(); //determine the account name to use in the horizontal search
  var rateTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rates'); //hold the name of the rate tab for further dissection
  var rateNumColumns =rateTab.getLastColumn(); //count the number of columns on the rate tab so we can later create an array
  var rateNumRows = rateTab.getLastRow(); //count the number of rows on the rate tab so we can create an array
  var rateSheet = rateTab.getRange(1,1,rateNumRows,rateNumColumns).getValues(); //create an array based on the number of rows & columns on the rate tab
  var currentRow = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getRow(); //gets the current row so we can get the name of the rate to search
  var rateToSearch = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(currentRow,1).getValue(); //gets the name of the rate to search on the rates tab
  for(rr=0;rr<rateSheet.length;++rr){
    if (rateSheet[rr][0]==rateToSearch){break} ;// if we find the name of the 
      }
  for(cc=0;cc<rateNumColumns;++cc){
    if (rateSheet[0][cc]==accountName){break};
      }
  var rate = rateSheet[rr][cc] ; //the value of the rate as specified by rate name and account name
  return rate;
}

If I change a rate in the rate tab, I need the custom function to recognize the new rate and update its value

Answer 1

• You want to recalculate the custom function of =findRate(), when the cells of the sheet name of Rates are edited.

If my understanding is correct, how about adding the following sample script? Please think of this as just one of several answers.

Solution:

In order to recalculate the custom function, in this answer, the formula of =findRate() is overwritten by the script running with the OnEdit event trigger (in this case, it's the simple trigger.). By this, the recalculate is executed. But, when the formula is directly replaced by the same formula, the recalculate is not executed. So I used the following flow.

1. Retrieve all ranges of cells which have the formula of =findRate() from the sheet of "Projected Revenue".
2. Clear the formulas of the ranges.
3. Put the formulas to the ranges.

By this flow, when the cell of the sheet of "Rates" is edited, the custom function of =findRate() is recalculated by automatically running onEdit().

Sample script:

Please copy and paste the following script to the script editor. Then, please edit the cells of sheet name of Rates. By this, onEdit() is automatically run by the OnEdit event trigger.

function onEdit(e) {
  var range = e.range;
  if (range.getSheet().getSheetName() == "Rates" && range.rowStart > 1 && range.columnStart > 1) {
    var sheetName = "Projected Revenue"; // If you want to change the sheet name, please modify this.
    var formula = "=findRate()";// If you want to change the function name, please modify this.

    var sheet = e.source.getSheetByName(sheetName);
    var ranges = sheet.createTextFinder(formula).matchFormulaText(true).findAll().map(function(e) {return e.getA1Notation()});
    sheet.getRangeList(ranges).clearContent();
    SpreadsheetApp.flush();
    sheet.getRangeList(ranges).setFormula(formula);
  }
}

Note:

• onEdit(e) is run by the OnEdit event trigger. So when you directly run onEdit(e), an error occurs. Please be careful this.
• In this sample script, as a sample, even when the row 1 and column "A" of the sheet of "Rates" are edited, the custom function is not recalculated. If you want to modify this and give the limitation of range you want to edit, please modify the above script.

References:

• Simple Triggers
• Class TextFinder
• Class RangeList
• flush()

If I misunderstood your question and this was not the result you want, I apologize.

Added:

The proposal from TheMaster's comment was reflected to the script. When sheet.createTextFinder(formula).matchFormulaText(true).replaceAllWith(formula) can be used, also I think that the process cost will be much reduced. But in my environment, it seemed that the formulas are required to be cleared once to refresh the custom function, even if flush() is used. So I have proposed above flow.

But, now I could notice a workaround using replaceAllWith() of TextFinder. So I would like to add it. The flow of this workaround is as follows.

1. Replace all values of =findRate() to a value in the sheet of Projected Revenue using replaceAllWith()..
   • In this case, as a test case, the formulas are replaced to sample.
2. Replace sample to =findRate() using replaceAllWith().

By this flow, I could confirm that =findRate() is recalculated. And also, it seems that flush() is not required for this situation.

Sample script:

Please copy and paste the following script to the script editor. Then, please edit the cells of sheet name of Rates. By this, onEdit() is automatically run by the OnEdit event trigger.

function onEdit(e) {
  var range = e.range;
  if (range.getSheet().getSheetName() == "Rates" && range.rowStart > 1 && range.columnStart > 1) {
    var sheetName = "Projected Revenue";
    var formula = "=findRate()";
    var tempValue = "sample";

    var sheet = e.source.getSheetByName(sheetName);
    sheet.createTextFinder(formula).matchFormulaText(true).replaceAllWith(tempValue);
    sheet.createTextFinder(tempValue).matchFormulaText(true).replaceAllWith(formula);
  }
}