10

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

1

6 Answers 6

7

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.

3
  • 2
    Really good one :)
    – Lipis
    Sep 17, 2010 at 7:56
  • 1
    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, 2010 at 10:48
  • 1
    At this time, the proposed formula returns "Error: This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()"
    – Rubén
    Sep 3, 2016 at 14:25
7

#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

2
0

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

1
  • How do you know that data retrieved is the last version instead of a cached version?
    – Rubén
    Sep 3, 2016 at 14:23
-1

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.

-2

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.

1
  • 3
    Thanks for posting. You seem to be adding it to quite a few question here on SE. Does it actually help the OP? Jan 30, 2014 at 20:26
-3

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.

1
  • 2
    At this time, Google Sheets doesn't allow the use of non-deterministic functions on import functions.
    – Rubén
    Sep 3, 2016 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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