Force formula refresh through Apps Script

Question

Sometime, when changing the value of a cell, a formula is not refreshed automatically in a spreadsheet and a comment appears on the cell to select the cell and press CTRL+SHIFT+E to force the reload of the formula

I want to call this reload function from Apps Script. Is there a way to do it with Apps Script ? I tried Spreadsheet.flush(), but it's not working.

Thanks for help, Best regards

Answer 1

Perhaps this "not possible" answer has changed with new Spreadsheets or at some other time since 2012.

If you store the cell's formula, clear the cell, and then set the cell's formula, it seems to reevaluate just fine for me.

Here's a quick and dirty overview of what I'm doing:

// highlight a single cell and run this function
function myFunction() {
  var curr_cell = SpreadsheetApp.getActiveRange();
  var formula = curr_cell.getFormula();
  // clear only the contents, not notes or comments or formatting.
  curr_cell.clearContents();
  curr_cell.setFormula(formula);
}

Answer 2

This should work, set some other formula and revert back it again

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getDataRange();

var values = range.getValues();
var formulas = range.getFormulas()

for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
        if (formulas[i][j]) {
            var f = sheet.getRange(i + 1, j + 1).clearContent().setFormula('=true');
            SpreadsheetApp.flush();
            f.setFormula(formulas[i][j])
        }
    }
}

Answer 3

Unfortunately it's not possible to trigger a re-evaluation of the formulas using Apps Script.