How often does my spreadsheet data refresh?

Spreadsheet widgets powered by a Google Sheets spreadsheet automatically refresh within 3 minutes from the connected spreadsheet's last update (if it updates at 2:02 pm, the widget will update by 2:05 pm).

For .xlsx files uploaded to Google Drive, Dropbox, and OneDrive the refresh rate is around every 15 minutes.

Connection source
File type
Refreshes every (mins)

Google Drive

Google Sheets

Within 3

Google Drive

.xlsx

15

Dropbox

.xlsx

15

OneDrive

.xlsx

15

Refresh rates when using import formulas

Widgets powered by import functions such as ImportRange and ImportData automatically refresh around every 15 minutes. Be aware though that when data from multiple import functions with their own refresh intervals are used in the same connected spreadsheet it can affect the accuracy of the data displayed.

If this isn't fast enough for your needs, you can write and run a script using Google Apps Script Editor and set your own refresh interval.

ImportData function script

To write a script for the ImportData function, follow these steps:

  1. Click on the Tools menu and select the option Script Editor.
  2. You'll see a script. Setup_script_blank_project.png
  3. Replace the default function with following script. Make sure the cell in your Sheet exactly match the one in the script, otherwise ImportData will not update automatically.
    function myFunction() {
      
      SpreadsheetApp.getActive().getRange('A1').setValue('=importdata("https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv")')
    }
    Replace_default_function_with_script
  4. Save your project and give it a name.
  5. Now skip to setting up a trigger for your new script.

ImportHTML function script

To write a script for the ImportHTML function, follow these steps:

  1. In a new Google Sheet, select the Tools menu and click Script Editor. A new Google Apps Script tab will open.
  2. Replace the default code with the following script:
    function getData() { 
      var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("<sheet-name>"); 
      var queryString = Math.random(); 
      var cellFunction = '=IMPORTHTML("<url>?","table",<index>)';
    
        sheetName.getRange('<import-cell>').setValue(cellFunction); 
    }
  3. Replace <sheet-name> with the name of your sheet (the tab name, not the name of the file).
  4. Replace <url> with the URL of your web page, e.g. https://www.bloomberg.com/markets/currencies/cross-rates.
  5. Replace <index> with the table position on the page, e.g. 1.
  6. Replace <import-cell> with the cell where you want to put your import statement, e.g. A1.google_apps_automatic_updates
  7. In the title field, add a name to your project.
  8. Next, select File, then choose Save.
  9. Now skip to setting up a trigger for your new script.

If you're curious about why the script is adding a random number to the end of our URL - without it, the website will be cached and the function stops importing the latest data.

ImportRange function script

To write a script for the ImportRange function, follow these steps:

  1. Click on the Tools menu and select the option Script Editor.
  2. You'll see a script. Setup_script_blank_project.png
  3. Replace the default function with following script. Then edit the script by first replacing the .setValue('IMPORTRANGE') function with your own, then making sure the .getRange('cell') in your Sheet exactly match the one in the script, otherwise ImportRange will not update automatically.
    function myFunction() {
      
      SpreadsheetApp.getActive().getRange('A1').setValue('IMPORTRANGE('https://docs.google.com/spreadsheets/d/abcd123abcd123', 'sheet1!A:B')')
    }
  4. Save your project and give it a name.
  5. Now skip to setting up a trigger for your new script.

ImportXML function script

To write a script for the ImportXML function, follow these steps:

  1. In a new Google Sheet, the first thing we'll do is split the ImportXML function into two logical pieces; the URL and the XPath query. Write the URL in cell A1 and the XPath query in cell A2.
  2. If you want to add another ImportXML function into this sheet so that you can do calculations with the data, add the second URL in cell A4 and the XPath query in cell A5.
  3. Give your Google Sheet a name.Prepare_google_sheet
  4. To write a script, click on the Tools menu.
  5. Select the option Script Editor.
  6. A Google Apps Script window will appear. Click the Blank Project option.

    Setup_script_blank_project

  7. Replace the default function with the script shown below:
    function getData() {
      var queryString = Math.random();
    
      var cellFunction1 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A2').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C1').setValue(cellFunction1);
    
      var cellFunction2 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A4').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A5').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C2').setValue(cellFunction2);
    }
  8. Double-check the following aspects of the first section of the script (var cellFunction1...):

    Cell reference A1 should reflect where your first URL is written in your Google Sheet.

    Cell reference A2 should reflect where your first XPath query is written in your Google Sheet.

    Cell reference C1 should reflect where you want to display the result of your ImportXML function.

  9. If you want to include a second ImportXML function, double-check the following in the second section of the script (var cellFunction2...):

    Cell reference A4 should reflect where your first URL is written in your Google Sheet.

    Cell reference A5 should reflect where your first XPath query is written in your Google Sheet.

    Cell reference C2 should reflect where you want to display the result of your second ImportXML function.

  10. If you don't want to include a second ImportXML function, delete everything from var cellFunction2... to ...setValue(cellFunction2); leaving just the following script:
    function getData() {
      var queryString = Math.random();
    
      var cellFunction1 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A2').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C1').setValue(cellFunction1);
    
      var cellFunction2 = '=IMPORTXML("' + SpreadsheetApp.getActiveSheet().getRange('A4').getValue() + '?' + queryString + '","'+ SpreadsheetApp.getActiveSheet().getRange('A5').getValue() + '")';
      SpreadsheetApp.getActiveSheet().getRange('C2').setValue(cellFunction2);
    }
    
  11. Save your script and give it a name.Save_script
  12. Now skip to setting up a trigger for your new script.

Note

In case you're wondering why we're adding a random number to the end of our URL, it's because otherwise the website will be cached and the function stops importing the latest data.

GoogleFinance function script

To write a script for the GoogleFinance function, follow these steps:

  1. Click on the Tools menu and select the option Script Editor.
  2. A Google Apps Script window will appear. Click the Blank Project option.Setup_script_blank_project
  3. Replace the default function with the following script:
    function getData() {
    SpreadsheetApp.getActiveSheet().getRange('A2').setValue('=GOOGLEFINANCE("GOOG", "price")');
    }
  4. Save your function and give it a name.
  5. Now skip to setting up a trigger for your new script.

Set up a trigger for your script

After writing your script set up a trigger so that it runs as often as you need.

  1. From inside the Script Editor, click on the Edit menu and select the option Current project's triggers.
  2. Click the + Add Trigger button in the bottom right corner of the page. add_trigger.png
  3. Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on a Minute timer.trigger settings in Google Sheets
  4. Once you set the trigger (and so that you don't have to wait 1 hour to get data), run the script for the first time.run_script.png

Tip

You can force the widget to update by reloading the spreadsheet. To do this, follow these steps:

  1. Hover your mouse pointer over the widget you want to edit.
  2. Click the more options icon in the top right corner.edit widget menu
  3. On the pop-up menu, Click Edit.
  4. On the edit widget screen's breadcrumbs menu, click the downward arrow beside the name of your widget.Edit widget breadcrumbs
  5. Click Reload Spreadsheet.
  6. Click Save once you're finished.
Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

Ready to create your own dashboard?

Get started for free

Still have questions? Get in touch.