Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Did you try the hack Chris? – Jacob Jan Tuinstra Jun 28 '14 at 21:12

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.

share|improve this answer
Really good one :) – Lipis Sep 17 '10 at 7:56
Interesting they say hourly. I seem to get instantaneous data with every page load. That is until it balks. Perhaps the 1 hour delay kicks in. But what is the rhyme or reason to the lock-down. I'll try your hack when that happens. Thanks. – Chris Noe Sep 17 '10 at 10:48

Another alternative that does not rely on link modification:


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

share|improve this answer

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:


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

share|improve this answer

Force recalculation of importrange by reinstating the cell formula


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                  |


function onOpen() {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();

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 + '")');


// 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.

share|improve this answer
Thanks for posting. You seem to be adding it to quite a few question here on SE. Does it actually help the OP? – Jacob Jan Tuinstra Jan 30 '14 at 20:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.