Google Sheets Force Recalculate

ForceRecalculate.gs

"use strict";

var FORMULA_DISABLED_PREFIX = '?';
var FORMULA_DISABLED_FULL_PREFIX = '=' + FORMULA_DISABLED_PREFIX;

//https://support.geckoboard.com/hc/en-us/articles/206260188
function ForceRecalculateActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  ForceRecalculateSheet(sheet);
}

function ForceRecalculateSheet(sheet) {
  var range = sheet.getRange('A1');
  ForceRecalculate(range);
}

function ForceRecalculateActiveRange() {
  var range = SpreadsheetApp.getActiveRange();
  //var range = SpreadsheetApp.getActiveSheet().getActiveCell();
  ForceRecalculate(range);
}

//https://stackoverflow.com/questions/31659186/how-to-refresh-a-sheets-cell-value-in-google-apps-script
function ForceRecalculate(range) {
  var formula = range.getFormula();

  if(formula != '') {
    if(formula.startsWith(FORMULA_DISABLED_FULL_PREFIX)) {
      //fix previous forced refresh potential failure
      formula = formula.replace(FORMULA_DISABLED_FULL_PREFIX, '=');
    } else {
      var tempFormula = formula.replace('=', FORMULA_DISABLED_PREFIX);
      range.setFormula(tempFormula);
      SpreadsheetApp.flush();
    }

    range.setFormula(formula);
    //SpreadsheetApp.flush();

    return true;
  }

  return false;
}

function ForceRecalculateAll(range) {
  //Logger.log('forceRecalculate'); // can see this in the logs
  //var value = SpreadsheetApp.getActiveSheet().getRange('A1').getValue() //presumably returns a value to the script
  //Logger.log(value);
  
  var affectedCount = 0;
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  
  for(var row = 1; row <= numRows; row++) {
    for(var col = 1; col <= numCols; col++) {
      var cell = range.getCell(row, col);
      
      if(ForceRecalculate(cell))
        affectedCount++;
    }
  }

  if(affectedCount > 0 )
  {
    SpreadsheetApp.flush();
  } else {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.toast("No formula found in range " + range.getA1Notation(), "Error");
  }
}

function ForceRecalculateActiveSpreadsheet() {
  //var sheet = SpreadsheetApp.getActiveSheet();
  //var spreadsheet = sheet.getParent();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  ForceRecalculateAllSheets(spreadsheet);
}

function ForceRecalculateAllSheets(spreadsheet) {
  var sheets = spreadsheet.getSheets();
  
  for(var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var range = sheet.getRange('A1');
    var formula = range.getFormula();
    
    if(formula) {
      if(!(/^=(?:IMPORTRANGE|QUERY)/i).test(formula)) {
        ForceRecalculate(range);
      }
    }
  }
}