8

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?

  • 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? – Jacob Jan Tuinstra 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
4

Add the following line of code:

SpreadsheetApp.flush()

See reference: flush

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

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.

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.

  • 1
    How do you work that into a script? – ale Dec 23 '16 at 18:25

protected by Community Nov 21 '17 at 21:31

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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