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 writing a script.

Note: Be aware that when data from multiple import functions – each with their own refresh intervals – are used in the same connected spreadsheet, it 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

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

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.

Note: 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

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.

Note: 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

Click on Tools > Script Editor.

Replace the default function with the following script:

function getData() { SpreadsheetApp.getActiveSheet().getRange('A2').setValue('=GOOGLEFINANCE("GOOG", "price")');}

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, click on the Edit menu and select the option Current project's triggers.
Click the + Add Trigger button in the bottom right corner of the page.
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, run the script for the first time. This means you don't have to wait 1 hour to get data.

Tip: You can force the widget to update by reloading the spreadsheet in Geckoboard.

Push to Custom Widgets from a Google Sheet

Refresh rate for Spreadsheets widgets

Use Google Sheets’ ImportHTML function to display data in Geckoboard

Use ImportRange to extract data needed for Geckoboard to a new spreadsheet