What are the refresh characteristics of the Google Spreadsheet Import functions?

Question

The imported data is not always up to date with the source pages when I load the spreadsheet. When this happens, F5 refresh does not help. Nor does Ctl-F5. Apparently there is some kind of caching going on.

I'm thinking that they may be limiting the URL fetches when I try to refresh too often - like when I'm actively modifying my spreadsheet.

Doc reference: http://docs.google.com/support/bin/answer.py?answer=75507

Answer 1

As per this Google Docs Forum thread, the data should refresh about every hour

In that thread, a poster suggests appending a random querystring to the URL you wish to scrape from -

I used a simple workaround of appending a parameter which updates every minute.

Function call is: =ImportHtml("http://www.fifa.com/worldcup/standings/index.html?"&H81;"table";4)

Formula in H81 is: =Minute(Now())

This does the trick for me.

Answer 2

#Short answer

You are right, the import functions updates their result at certain intervals.

#Explanation From Change a spreadsheet's locale, time zone, recalculation, and language, a help article from the official documentation,

Note: External data functions recalculate at the following intervals:

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

It's worth to say that formulas are recalculated when they their arguments changes, so we could use this to force a refresh of the imported data.

Workaround

One way to force the recalculation of formulas with import functions is to concatenate to the URL a deterministic but innocuous parameter.

###Example

In the following example, "#rev="&A2 is added to the original URL

A1: https://en.wikipedia.org/wiki/List_of_gravitationally_rounded_objects_of_the_Solar_System
A2: 1
A3: 4
A4: =IMPORTHTML(A1&"#rev="&A2,"table",A3)

Every time that any of the values or A1, A2, or A3 changes, the formula will be recalculated. We could change the value of A2 to "force" a refresh of the table 4 from the URL in A1.

Custom time refresh interval for import functions

To have a custom refresh interval for import functions, use a time-driven trigger script to update the deterministic but innocuous URL parameter.

/**
 *
 * Updates at the interval set in the time driven trigger configuration
 * a cell value to be used as a deterministic bu unnicouus URL parameter 
 *
 */
function forceRefresh() {
  //Cell addrees of the deterministic but unnicouos URL parameter
  var reference = 'Sheet1!A2';   
  var rng = SpreadsheetApp.getActiveSpreadsheet().getRange(reference);
  rng.setValue(rng.getValue()+1);
}

#References

• Google Apps Script
• Installable triggers

Answer 3

Another alternative that does not rely on link modification:

=if(Minute(Now())=Minute(Now()),ImportHtml("http://www.fifa.com/worldcup/standings/index.html?";"table";4),"")

Since Minute(Now())=Minute(Now()) is always true, the if will always return the ImportHtml

Answer 4

Click File > Spreadsheet Setting... and check both sheets are in the same Locale and if responses are coming from Jotforms, set the same Locale there too.

Answer 5

Force recalculation of importrange by reinstating the cell formula

Required:

Imported Data - Your target where everything will be copied to.

Imported Data Config - Has the following fields:

+---------------------------------------------+--------------------------------+
|                      A                      |               B                |
+---------------------------------------------+--------------------------------+
| Import Data from Spreadsheet with the key:  | key                            |
| Import Data from Spreadsheet between range: | A:AA                           |
| Imported Data select columns:               | SELECT *                       |
| Imported Data criteria:                     | WHERE Col2 contains 'XYZ' |
| Imported Data should by ordered by column:  | ORDER BY Col2                  |
+---------------------------------------------+--------------------------------+

Script:

function onOpen() {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
}
catch(err)
{
Browser.msgBox(err);
}

var configsheet = ss.getSheetByName("Imported Data Config");

var configkey = configsheet.getRange("B1").getValue();
var configrange = configsheet.getRange("B2").getValue();
var configselect = configsheet.getRange("B3").getValue();
var configwhere = configsheet.getRange("B4").getValue();
var configorderby = configsheet.getRange("B5").getValue();

var importedsheet = ss.getSheetByName("Imported Data");
importedsheet.getRange("A1").setValue('=QUERY(IMPORTRANGE("' + configkey + '","' + configrange + '"),"' + configselect + ' ' + configwhere + ' ' + configorderby + '")');

SpreadsheetApp.flush();

// Solution of sourcecode is: http://stackoverflow.com/questions/13631584/how-can-i-force-a-recalculation-of-cell-using-importrange-function-in-a-google-s
// OnOpen Trigger: https://developers.google.com/apps-script/understanding_triggers
// Active Spreadsheet solution: https://productforums.google.com/forum/#!topic/docs/XIY0WNX0uL8

Browser.msgBox("Sync Complete!");
}

This allows you to change your formula without editing the script, and make it easier to transfer the script across various sheets.

NOTE: This is no longer required in the New Google Sheets released in May 2014.

Answer 6

The minute now does work very well (and you can just include that within the formula rather than referencing a cell).

However, with some URLs it does not work and appears to change the link itself and may reference another sheet.

I'm not going to be specific, but a link ending for example in:

.....ranking?ranking=xp_total&table=0"&Minute(Now()),"table",4)

will mess up the link itself. Not a full proof solution but still work for probably 90% of the links people use.