11

I have a column in Google Spreadsheet that calls an outside webservice using URLFetch in a Google Script. The cell uses two parameters in that cell's row. After a change occurs where those two parameters will return a different value if sent to the webservice again the cell retains a cached value. I know it is cached because if I copy and paste the row to another row, the webservice calculated values change to the correct ones.

Is there a way to force a row or a cell in a Google Spreadsheet to recalculate its value, especially if it is using an outside webservice in a custom function?

5
  • I do know that I can refresh all of the cells by either editing the script or sorting the data, but I am looking for something a little more fine tuned.
    – capn
    Jul 24 '12 at 17:13
  • Have you tried F5? Nov 25 '12 at 8:45
  • Yep, and ctrl-F5, just started closing and reopening when I needed it refreshed, didn't think about that when I was first adding all the data. After it became more of an add a line every few days file it didn't become as big of a deal.
    – capn
    Feb 25 '13 at 16:15
  • Ctrl-R will recalculate formulas...similar to Excel's F9.
    – Gary W.
    Dec 23 '16 at 17:57
  • Difficult to place that within a script, which is what the question asks for.
    – Chenmunka
    Dec 23 '16 at 18:22
6

Add the following line of code:

SpreadsheetApp.flush()

See reference: flush

3
  • Until there is a command that will flush a single cell, column, or row, I guess this is the heavy handed approach I will have to use. Its best to call it as needed for me in a single cell manually when I want all the data refreshed (F5 and ctrl-F5 does not do this, only opening and closing it will), since calling it each time would probably cause some unintended looping and duplication of work when the sheet is first opened.
    – capn
    Feb 25 '13 at 16:14
  • Thanks for this answer. Unfortunately, I tried running this line in its own macro but am getting Error An unknown error has occurred, please try again later.. The reason I'm trying to run it is because I see 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. So I'm hoping to find a full-workbook way (like I thought this answer would be).
    – Ryan
    Feb 14 at 14:07
  • I wrote a script that recalculates each formula in a Google Sheets sheet: stackoverflow.com/a/66197347/470749
    – Ryan
    Feb 14 at 16:16
9

I came here to look for an answer too for the same situation (calling data from external resource), but have since worked out an alternate method that works so I thought I'd share:

 1. Select the cell you want updated.
 2. Delete the cell.
 3. Undo with Edit > Undo or Ctrl + z

This is by no means the technically savvy way to do it, but if you delete the cell, then undo, it will reload.

Works a treat.

4
  • 1
    How do you work that into a script?
    – ale
    Dec 23 '16 at 18:25
  • wow, this is actually working! Note that you have to wait a few seconds before undoing, else it won't work
    – Hibbem
    Sep 5 '19 at 18:02
  • This did not work for me for webapps.stackexchange.com/questions/29283/…
    – Ryan
    Feb 14 at 14:24
  • This may not work if the formula has a check to avoid being executed more than once (useful to avoid re-requesting external resources). My solution in that case is 1.Select all the cells involved. 2.Cut (Ctrl+X) 3. Edit > Paste > Paste formula only.
    – Rub
    Apr 22 at 18:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.