How to make google spreadsheet refresh itself ever

Question

My google spreadsheet is using GOOGLEFINANCE('symbol','price) function to retrieve stock prices of my portfolio. Unfortunately, I have to refresh manually now. How can I make the spreadsheet refresh itself every 1 minute?

Thank you for your help.

Answer 1

If you're on the New Google Sheets, this is all you need to do, according to the docs:

change your recalculation setting to "On change and every minute" in your spreadsheet at File > Spreadsheet settings.

This will make the entire sheet update itself every minute, on the server side, regardless of whether you have the spreadsheet up in your browser or not.

If you're on the old Google Sheets, you'll want to add a cell with this formula to achieve the same functionality:

=GoogleClock()

EDIT to include old and new Google Sheets and change to =GoogleClock().

Answer 2

I had a similar problem with crypto updates. A kludgy hack that gets around this is to include a '+ now() - now()' stunt at the end of the cell formula, with the setting as above to recalculate every minute. This worked for my price updates, but, definitely an ugly hack.

Answer 3

use now() in any cell. then use that cell as a "dummy" parameter in a function. when now() changes every minute the formula recalculates. example: someFunction(a1,b1,c1) * (cell with now() / cell with now())

Answer 4

If you are only looking for a refresh rate for the GOOGLEFINANCE function, keep in mind that data delays can be up to 20 minutes (per Google Finance Disclaimer).

Single-symbol refresh rate (using GoogleClock)

Here is a modified version of the refresh action, taking the data delay into consideration, to save on unproductive refresh cycles.

=GoogleClock(GOOGLEFINANCE(symbol,"datadelay"))

For example, with:

• SYMBOL: GOOG
• DATA DELAY: 15 (minutes)

then

=GoogleClock(GOOGLEFINANCE("GOOG","datadelay"))

Results in a dynamic data-based refresh rate of:

=GoogleClock(15)

Multi-symbol refresh rate (using GoogleClock)

If your sheet contains a number of rows of symbols, you could add a datadelay column for each symbol and use the lowest value, for example:

=GoogleClock(MIN(dataDelayValuesNamedRange))

Where dataDelayValuesNamedRange is the absolute reference or named reference of the range of cells that contain the data delay values for each symbol (assuming these values are different).

Without GoogleClock()

The GoogleClock() function was removed in 2014 and replaced with settings setup for refreshing sheets. At present, I have confirmed that replacement settings is only on available in Sheets from when accessed from a desktop browser, not the mobile app (I'm using Google's mobile Sheets app updated 2016-03-14).

(This part of the answer is based on, and portions copied from, Google Docs Help)

To change how often "some" Google Sheets functions update:

1. Open a spreadsheet. Click File > Spreadsheet settings.
2. In the RECALCULATION section, choose a setting from the drop-down menu.
3. Setting options are:
   • On change
   • On change and every minute
   • On change and every hour
4. Click SAVE SETTINGS.

NOTE External data functions recalculate at the following intervals:

• ImportRange: 30 minutes
• ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
• GoogleFinance: 2 minutes

The references in earlier sections to the display and use of the datadelay attribute still apply, as well as the concepts for more efficient coding of sheets.

On a positive note, the new refresh option continues to be refreshed by Google servers regardless of whether you have the sheet loaded or not. That's a positive for shared sheets for sure; even more so for Google Apps Scripts (GAS), where GAS is used in workflow code or referenced data is used as a trigger for an event.

[*] in my understanding so far (I am currently testing this)