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

File -> Spreadsheet Settings -> (Tab) Calculation -> Recalculation (3 Options)

• On change
• On change and every minute
• On change and every hour
  This affects how often NOW, TODAY, RAND, and RANDBETWEEN are updated.

but.. .. it updates only if the functions' arguments (their ranges, cells) are affected by that.
  

from my example
I use google spreadsheet to find out the age of a person. I have his birth date in the format (dd.mm.yyyy) -> it's the used format here in Switzerland.

=ARRAYFORMULA(IF(ISTEXT(K4:K), IF(TODAY() - DATE(YEAR(TODAY()), MONTH(REGEXREPLACE(K4:K, "[.]", "/")), DAY(REGEXREPLACE(K4:K, "[.]", "/"))) > 0, YEAR(TODAY()) - YEAR(REGEXREPLACE(K4:K, "[.]", "/")) + 1, YEAR(TODAY()) - YEAR(REGEXREPLACE(K4:K, "[.]", "/"))), IF(LEN(K4:K) > 0, IF(TODAY() - DATE(YEAR(TODAY()), MONTH(K4:K), DAY(K4:K)) > 0, YEAR(TODAY()) - YEAR(K4:K) + 1, YEAR(TODAY()) - YEAR(K4:K)), "")))

I'm using TODAY() and I did the recalculation settings described above. -> but no automatically refresh. :-(
It updates only if I change some value inside the ranges where the function is looking for.

So I wrote a Google Script (Tools -> Script Editor..) for that purpose.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetMaster = ss.getSheetByName("Master"); 
  var sortRange = sheetMaster.getRange(firstRow, firstColumn, lastRow, lastColumn);  

  sortRange.getCell(1, 2).setValue(sortRange.getCell(1, 2).getValue());
}

You need to set numbers for firstRow, firstColumn, lastRow, lastColumn

The Script gets active when the spreadsheets open, writes the content of one cell into the same cell again. That's enough to trigger the TODAY() function.

Look for more information on that link from Edward Moffett: Force google sheet formula to recalculate.

Answer 2

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

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

Answer 5

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

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 9

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 10

If you want to refresh a single row - press on its number to select it, then press Delete to delete the whole row and Ctrl+Z to immediately restore and recalculate it.

If you want to refresh the whole worksheet, press in the top left corner (where row numbers and column letters are crossed) to select everything and then Delete with Ctrl+Z again to restore and recalculate.

Answer 11

Place your cursor on blank cell, press F4

Answer 12

nothing worked, but I found a solution for myself - add an empty line in the working range of the formula (via the right mouse button), and then immediately cancel the action (Ctrl + Z). Profit! all formulas will be updated.

Answer 13

This works:

function refreshCell() {
//refresh formula in cell A1

var sheet = SpreadsheetApp.getActive().getActiveSheet()
var r_Cell_1 = sheet.getRange(1,1,1,1).getFormula()

sheet.getRange(1,1,1,1).setValue("xx")
sheet.getRange(2,1,1,1).setValue(r_Cell_1)

var r_Cell_2 = sheet.getRange(2,1,1,1).getFormula()
sheet.getRange(1,1,1,1).setValue(r_Cell_2)
sheet.getRange(2,1,1,1).clearContent()
}

Answer 14

I've modified Brunox13's code for my needs. range.setFormulas(...) removes the values that are not formulas. This code doesn't.

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

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

function isSheetEmpty(sheet) {
  return sheet.getDataRange().getValues().join("") === "";
}

function refreshData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Buffer sheet to save the data
  var bufferSheet = ss.insertSheet();

  ss.getSheets().forEach(sheet => {
    // Skip the buffer sheet and the empty ones, they will raise an exception
    if (!isSheetEmpty(sheet)) {
      // It seems to update properly only when the sheet is active
      sheet.activate();

      // Getting the ranges to swap the data 
      var myRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());  
      
      var bufferSheetRange = bufferSheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());

      // Swapping the data forth and back
      myRange.copyTo(bufferSheetRange);
      sheet.clear();

      SpreadsheetApp.flush();

      bufferSheetRange.copyTo(myRange);
      bufferSheet.clear();

      SpreadsheetApp.flush();
    }
  });

  // Buffer sheet is not needed anymore 
  ss.deleteSheet(bufferSheet);
}

// I use this function to check the last update
function CustomCurrentTime() {
  // To check the update 
  timezone = "GMT+3";
  return Utilities.formatDate(new Date(), timezone, "dd-MM-yyyy HH:mm:ss"); // "yyyy-MM-dd'T'HH:mm:ss'Z'"
}

Answer 15

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.