You write inputs, then read a formula cell that depends on them, and get the old answer. Apps Script batches writes, so the read can run before Sheets has recalculated with your new values. flushThenGetValues(rangeOrA1, opts) calls SpreadsheetApp.flush() first, then reads the range: raw values by default, or display values (what the cell shows) with { display: true }.
When to use
- Write inputs → read a total / lookup / status formula in the same run
- Emailing or exporting numbers that come from formulas you just fed
- Tests and dry runs where you check a calculated cell after writing
How to use this snippet
/**
* Flush pending writes, then read a range.
*
* @param {string|GoogleAppsScript.Spreadsheet.Range} rangeOrA1
* A1 notation ('Summary!D10' or 'D10' with opts.sheet) or a Range
* @param {{display?: boolean,
* sheet?: GoogleAppsScript.Spreadsheet.Sheet}=} opts
* display true = getDisplayValues() (formatted text), default getValues()
* sheet resolve plain A1 like 'D10' against this sheet
* @return {Array<Array<*>>}
*/
function flushThenGetValues(rangeOrA1, opts) {
opts = opts || {};
// 1. Apply every pending write so dependent formulas recalculate.
SpreadsheetApp.flush();
// 2. Resolve the range (A1 string or Range object).
var range = typeof rangeOrA1 === 'string'
? (opts.sheet || SpreadsheetApp.getActive()).getRange(rangeOrA1)
: rangeOrA1;
// 3. Raw values (numbers, Dates) or display text ("$1,200.00").
return opts.display ? range.getDisplayValues() : range.getValues();
}
Example
// Quote tab: B2 = quantity, B3 = unit price, B6 = =B2*B3*(1+TaxRate)
function quoteTotal(qty, unitPrice) {
var sheet = SpreadsheetApp.getActive().getSheetByName('Quote');
sheet.getRange('B2:B3').setValues([[qty], [unitPrice]]);
var raw = flushThenGetValues('B6', { sheet: sheet })[0][0]; // 1234.5
var shown = flushThenGetValues('B6', { sheet: sheet, display: true })[0][0]; // "$1,234.50"
return { raw: raw, shown: shown };
}
Tips:
flush()isn’t free. Call it once after a batch of writes, not after everysetValuein a loop.- Use display values for emails and PDFs (the formatting is already applied), and raw values for math and comparisons.
- Formulas that pull in outside data (IMPORTRANGE, IMPORTXML, GOOGLEFINANCE) can still be loading after a flush. Don’t rely on them in the same run.
- Volatile functions (
NOW(),RAND()) recalculate on flush too, so expect new values.
Tip: NitroGAS Co-Pilot can spot “write then read a formula” spots in an existing script and add a single flushThenGetValues where the stale read happens.
Happy Coding!
