[google-sheets] How to Force New Google Spreadsheets to refresh and recalculate?

There were some codes written for this purpose but with the new add-ons they are no longer applicable.

What worked for me is inserting a column before the first column and deleting it immediately. Basically, do a change that will affect all the cells in the worksheet that will trigger recalculation.

I know that you are looking for an auto-refresh; perhaps some coming in here may be happy with a quick fix for a manual button (like the checkbox proposed above). I actually just stumbled upon a similar solution to the checkbox: select the cells you want to refresh, and then press CTRL and the "+" key. Seems to work in Office 365 v16; hope it works for others in need.

None of the existing answers worked for me, but this approach did.

The problem

I was seeing lots of cells say #REF!. These are cells in a sheet that I copied from another Google Sheet doc using "Copy to > Existing Worksheet". If I press Enter in any cell, it recalculates correctly, But I don't want to do that for millions of cells.

My answer

I ran this recalcSheet() script. It takes almost 0.5 seconds per cell, which is very slow but is faster than manually fixing each cell.

function recalcSheet(){  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = spreadsheet.getSheetByName("put_your_sheet_name_here");  // https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename
  // var range = sheet.getSelection().getActiveRange();
  // var range = sheet.getRange('A6:D6');
  var range = sheet.getDataRange();  
  recalcRange(range, spreadsheet);
}

function recalcRange(range, spreadsheet){
  // following structure of https://stackoverflow.com/a/52123839/470749
  Logger.log('Range: ' + range.getA1Notation());
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var startRow = range.getRow();
  var startCol = range.getColumn();
  Logger.log('row: ' + startRow);
  Logger.log('col: ' + startCol);
  Logger.log('numRows: ' + numRows);
  Logger.log('numCols: ' + numCols);

  for (var r = 1; r <= numRows; r+=1) {
    for (var c = 1; c <= numCols; c+=1) {
      var originalFormula = range.getCell(r, c).getFormula(); // https://developers.google.com/apps-script/reference/spreadsheet/range#getFormula()
      Logger.log(`r,c ${r}, ${c}; originalFormula: ${originalFormula}`);
      if(originalFormula){
        range.getCell(r, c).setFormula('');
        //SpreadsheetApp.flush(); // https://webapps.stackexchange.com/a/35970/27487
        range.getCell(r, c).setFormula(originalFormula);
      }
    }
  }
  spreadsheet.toast('Each cell in the range has been recalculated.', "Finished!"); // https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#toast(String)
}

Old question ... nonetheless, just add a checkbox somewhere in the sheet. Checking or unchecking it will refresh the cell formulae.

Quick, but manual

Updating NOW(), TODAY(), RAND(), or RANDBETWEEN() formulas

Press Backspace ? or Del on any empty cell to immediately trigger a recalculation of formulas depending on NOW(), TODAY(), RAND(), or RANDBETWEEN() (in all Sheets of the whole Spreadsheet, as usual).

(If no empty cell is at hand, you can delete a filled cell instead and then undo that with Ctrl+z.)

INDIRECT() formulas are unfortunately not updated like this by default.

Updating INDIRECT() formulas

You can update a (range of) cells of INDIRECT() formulas by pasting the range on itself:

1. Select cell/ range
2. Ctrl+C
3. Ctrl+V

You can use Ctrl+A to select the whole current Sheet in step 1.. But for large Sheets then the other 2 operations can take several seconds each.
A trick to know when the process of copying a large range has finished: Copy some single cell before copying your range: The single cell losing its dotted border will be your notification of the large copy finishing.

When the problem is in the recalculation of an IF condition, I add AND(ISDATE(NOW());condition) so that the cell is forced to recalculate according to what is set in the Calculation tab in Spreadsheet Settings as explained before.

This works because NOW is one of the functions that is affected by the Calculation setting and ISDATE(NOW()) always returns TRUE.

For example, in one of my sheets I had the following condition which I use to check whether a sheet with name stored in C1 is already created:

=IF(ISREF(INDIRECT(C$1&"!A1")); TRUE; FALSE)

In this case C1="February", so I expected the condition to become TRUE when a sheet with this name was created, which didn't happen. To force it to update, I changed the Calculation setting and used:

=IF(AND( ISDATE(NOW()) ; ISREF(INDIRECT(C$1&"!A1")) ); TRUE; FALSE)

Insert "checkbox". Every time you check or uncheck the box the sheet recalculates. If you put the text size for the checkbox at 2, the color at almost black and the cell shade to black, it becomes a button that recalculates.