Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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 at 21:12

3 Answers 3

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
1  
Really good one :) –  Lipis Sep 17 '10 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 '10 at 10:48

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.

share|improve this answer

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.

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 at 20:26

Your Answer

 
discard

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.