Last active
October 2, 2023 18:40
-
-
Save alexminza/66430a169c69bad9cd18cb8636bdb29e to your computer and use it in GitHub Desktop.
Google Sheets Force Recalculate
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
"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); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment