How to Force New Google Spreadsheets to refresh and recalculate?

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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)
}

Answer 5

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.

Answer 6

I believe that the accepted answer has some major issues (as I mentioned in my comments), so I put together a better approach. This approach works for me regardless of whether the formulas contain any of the NOW(), TODAY(), RAND(), or RANDBETWEEN() functions or not. In fact, it works with custom functions as well.

One thing to note is that this approach uses the setFormulas() function, which requires confirmation by the user. Therefore, instead of updating all the values only at the moment when the spreadsheet is open, the code below will create a new menu titled "Refresh" with a button "Refresh Data."

Anytime you press this button, the desired data range will be refreshed by re-pasting the original FORMULAS (not just values).**

Simply add this code to Tools -> Script Editor in your spreadsheet:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Refresh Data", functionName: "refreshData"});

  ss.addMenu("Refresh", menuEntries);
}

function refreshData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; 
  var myRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());  

  myRange.setFormulas(myRange.getFormulas());
}

Note that if you desire to refresh only a part of the spreasheet, change the values inside the getRange() function, as described in the relevant documentation.

Answer 7

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)

Answer 8

I have a workaround that worked perfectly for me, I have a large Sheet with many "IMPORTRANGE" and many links to other sheets, so it happened that everytime someone updated the sheet, it wasn't showing anywhere else so I had to go tab by tab, selecting the whole thing, deleting and returning in order to update it but I just went to put the formula "=IF(TODAY()=TODAY()," before all my formulas (it was a pain the neck) and it finally worked, everything is updated in real time.

This took me some time but in the end I paid off.

Answer 9

Place your cursor on blank cell, press F4