Force Google Spreadsheet formula to recalculate

Question

I know this question has been asked before but the answers given are not valid for my case because it's slightly different.

I've created a formula that looks for sheets with a pattern in the name and then uses it's content to generate the output. For example

function sampleFormula(searchTerm) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets()
    .filter(function(sheet) {
      // If sheet name starts with DATA_SHEET_...
      return sheet.getSheetName().indexOf('DATA_SHEET_') === 0;
    });

  const result = [];

  sheets.forEach(function(sheet) {
    // We get all the rows in the sheet
    const rows = sheet.getDataRange().getValues();

    rows.forEach(function(row) => {
      // If the row it's what we are looking for we pick the columns A and C
      if (row[1] === searchTerm) {
        result.push([ row[0], row[2] ])
      }
    });
  });

  // If we found values we return them, otherwise we return emptry string
  return result.length ? result : '';
}

The thing is I need this formula to be re-calculated when a cell in a sheet with a name starting with DATA_SHEET_ changes.

I see most answers (and what I usually do) is to pass the range we want to watch as a parameter for the formula even if it's not used. But in this case it will not work because we don't know how many ranges are we watching and we don't even know the whole sheet name (it's injected by a web service using Google Spreadsheet API).

I was expecting Google Script to have something like range.watch(formula) or range.onChange(this) but I can't found anything like that.

I also tried to create a simple function that changes the value of cell B2 which every formula depends on but I need to restore it immediately so it's not considered a change (If I actually change it all formulas will break):

// This does NOT work
function forceUpdate() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const range = sheet.getRange(1, 1);
  const content = range.getValue();

  range.setValue('POTATO');
  range.setValue(content);
}

So I don't know what else can I do, I have like a hundred formulas on multiple sheets doing this and they are not updating when I modify the DATA_SHEET_... sheets.

Are the cells containing these formulas randomly placed, or is it always a square range(continuous set of columns and rows)? Also, do I understand it correctly that the data sheet values are updated automatically? — Anton Dementiev
The formulas are placed in different non-adjacent cells. The data sheet has some columns updated manually and some updated automatically. — A. Matías Quezada

Rubén Rubén · Accepted Answer · 2018-04-23T17:33:35

To force that a custom function be recalculated we could use a "triggering argument" that it's only taks will be to trigger the custom function recalculation. This triggering argument could be a cell reference that will be updated by a simple edit trigger or we could use an edit installable trigger to update all the formulas.

Example of using a cell reference as triggering argument

=sampleFormula("searchTerm",Triggers!A1)

Example of using an edit installable trigger to update all the formulas

Let say that formulas has the following form and the cell that holds the formula is Test!A1 and Test!F5

=sampleFormula("searchTerm",0)

where 0 just will be ignored by sampleFormula but will make it to be recalculated.

Set a edit installable trigger to fire the following function

function forceRecalculation(){
  updateFormula(['Test!A1','Test!F5']);
}

The function that will make the update could be something like the following:

function updateFormula(references){
  var rL = SpreadsheetApp.getActive().getRangeList(references);
  rL.getRanges().forEach(function(r){
    var formula = r.getFormula();
    var x = formula.match(/,(\d+)\)/)[1];
    var y = parseInt(x)+1;
    var newFormula = formula.replace(x,y.toString());
    r.setFormula(newFormula);
  });
}

As you can imagine the above example will be slower that using a cell reference as the triggering argument but in some scenarios could be convenient.

Force Google Spreadsheet formula to recalculate

1 Answers