Spreadsheet widgets powered by import functions, such as ImportRange and ImportData, automatically refresh around every 15 minutes. If this isn't often enough for your needs, you can set your own refresh interval by using Google Apps Script to speed up the frequency of the refresh.
When data from multiple import functions, each with their own refresh interval, are used in the same connected spreadsheet, this can affect the accuracy of the data displayed.
Writing the Apps Script
We've included steps for ImportData, ImportHTML, ImportRange, ImportXML, and GoogleFinance.
ImportData script
In your Google Sheet, click on Tools > Script Editor.
Replace the default function with the following script.
function myFunction() { SpreadsheetApp.getActive().getRange('A1').setValue('=importdata("https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv")') }Make sure the cell in your sheet exactly matches the one in the script; otherwise, ImportData will not update automatically.
Select File > Save, give it a name, and then skip to setting up a trigger for your new script.
ImportHTML script
In your Google Sheet, click on Tools > Script Editor.
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); }Replace
<sheet-name>with the name of your sheet (the tab name, not the name of the file).Replace
<url>with the URL of your web page, e.g.https://www.bloomberg.com/markets/currencies/cross-rates.Replace
<index>with the table position on the page, e.g.1.Replace
<import-cell>with the cell where you want to put your import statement, e.g.A1.In the title field, add a name to your project.
Select File > Save. Then skip to setting up a trigger for your new script.
The random number at the end of our URL is required. Without it, the website will be cached, and the function stops importing the latest data.
ImportRange script
In your Google Sheet, click on Tools > Script Editor.
Replace the default function with the following script.
function myFunction() {
SpreadsheetApp.getActive().getRange('A1').setValue('=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A:B")');
}Edit the script by first replacing the
.setValue('IMPORTRANGE')function with your own.Then making sure the
.getRange('cell')in your sheet exactly matches the one in the script; otherwise, ImportRange will not update automatically.Select File > Save, give it a name, and then skip to setting up a trigger for your new script.
ImportXML script
In a new Google Sheet, we'll first need to 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.
If you want to add another ImportXML function to 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.
Give your Google Sheet a name.
To write the script, click on Tools > Script Editor.
Replace the default function with 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); }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.
A2 should reflect where your first XPath query is written in your Google Sheet.
C1 should reflect where you want to display the result of your ImportXML function.
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.
A5 should reflect where your first XPath query is written in your Google Sheet.
C2 should reflect where you want to display the result of your second ImportXML function.
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); }Select File > Save, give it a name, and then skip to setting up a trigger for your new script.
The random number at the end of our URL is required. Without it, the website will be cached, and the function stops importing the latest data.
GoogleFinance script
In your Google Sheet, click on Tools > Script Editor.
Replace the default function with the following script:
function getData() { SpreadsheetApp.getActiveSheet().getRange('A2').setValue('=GOOGLEFINANCE("GOOG", "price")');}Update the ticker symbol as preferred within the script.
Select File > Save, give it a name, and then 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 to run as often as you need.
From inside the Script Editor, hover over the left side menu, then click Triggers.
Click the + Add Trigger button in the bottom right corner.
Set a trigger for the myFunction function that runs from Head on a Time-driven source that's triggered on a Minute timer.
Once you've set the trigger, click Editor in the left menu to return to the code editor.
Then click Run to run the script for the first time.
