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

User · Accepted Answer

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 birthday 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                 gt  0  YEAR TODAY    - YEAR REGEXREPLACE K4 K                 1  YEAR TODAY    - YEAR REGEXREPLACE K4 K                 IF LEN K4 K   gt  0  IF TODAY   - DATE YEAR TODAY     MONTH K4 K   DAY K4 K    gt  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 get 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   Best regards  Christoph

User · Answer

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  quot   quot  key   Seems to work in Office 365 v16  hope it works for others in need

User · Answer

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 amp   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 amp   A1       TRUE  FALSE

User · Answer

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

User · Answer

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   Select cell  range Ctrl C 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

User · Answer

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  quot Copy to  gt  Existing Worksheet quot   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  quot put your sheet name here quot        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  lt   numRows  r  1        for  var c   1  c  lt   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     quot Finished  quot       https   developers google com apps-script reference spreadsheet spreadsheet toast String

User · Answer

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

User · Answer

Insert  quot checkbox quot   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

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

Examples related to google-sheets