Google Docs Help: Import Functions Speed Up

Learn how to make Google Docs and Spreadsheets functions work faster. This article is written by ahab.

Google Docs Spreadsheets Import Functions Speed Up


All Google Docs spreadsheet Import functions have two big limitations:
  • They create a number of CONTINUE functions to accommodate for the first evaluation of the function (e.g. when ImportHTML imports the first time a block of data of 3 columns by 2 rows and an hour later this should be 5 columns by 20 rows there are still only the CONTINUE functions for the first evaluation and you see only part of the updated import). This can easily be remedied by manually adding more CONTINUE functions, but you must be aware of this!
  • They update only every hour or so.
For the latter there are is a (set of complementary) workaround(s) one should try:
Add to the URL used in the import function a URL parameter that changes about every 2 minutes (faster isn\47t possible).
 
E.g. let\47s try to get as often as possible data from www.timeanddate.com, i.e the URL http://www.timeanddate.com/worldclock/
Let\47s only import the UTC time using this import:
=ImportHTML("http://www.timeanddate.com/worldclock/"; "table"; 4)
The above Import will only update every hour or so.
 
- You can skip the small text below if you are only looking for the drop-in workaround -
Now let\47s trigger the update more often by appending a GoogleFinance function call. I.e we add &REPT(GoogleFinance("GOOG");0) to the above import; this expression will trigger as oft as GoogleFinance updates but will result in a null string as we use REPT( ...; 0)
So the new import becomes:
=ImportHTML("http://www.timeanddate.com/worldclock/"; "table";  4)&REPT(GoogleFinance("GOOG");0)
This will trigger the Import every 2 minutes or so, but there is no update of the retrieved data; apparently the Google server uses a buffer based on the URL that is only updated every hour.
 
Let\47s fool the Google server into thinking we\47re using a new URL every 2 minutes by modifying the URL. We can do so by adding a URL parameter that will be ignored by the www.timeanddate.com server. E.g. we add to the URL &"?workaround="&INT(NOW()*1E3) ; this expression will change about every 2 minutes. Note the workaround URL parameter is and should be a bogus URL parameter, the intention is that the web server ignores it, but that Google Docs thinks it\46#39;s part of the URL. So you may change the URL parameter name  \46#39;workaround\46#39; into \46#39;anysillyurlparameternmayouwantaslongasthewebserverignoresit\46#39;.
So the final import becomes:
=ImportHTML("http://www.timeanddate.com/worldclock/"  &"?workaround="&INT(NOW()*1E3); "table"; 4) &REPT(GoogleFinance("GOOG");0)
This will trigger *and* update the Import every 2 minutes.
 
Notice the expression &"?workaround="&INT(NOW()*1E3) is valid when the URL parameter is the first URL parameter.
If there already are URL parameters in use you need to use &"&workaround="&INT(NOW()*1E3) instead.
E.g. suppose the URL plus first URL paramater was:
"http://www.timeanddate.com/worldclock/?GID\0750" then the new URL would become:
"http://www.timeanddate.com/worldclock/?GID\0750"&"&workaround="&INT(NOW()*1E3)
 
 
Drop-in workaround
As a kind of a final \47drop-in\47 workaround I\47ve put both the trigger and the URL modifier into one expression, so you get e.g. this Import formula:
=ImportHTML("http://www.timeanddate.com/worldclock/" &"?workaround="&INT(NOW()*1E3)&REPT(GoogleFinance("GOOG");0); "table"; 4)
This means that you can make any import more frequent by adding to the URL either:
&"?workaround="&INT(NOW()*1E3)&REPT(GoogleFinance("GOOG");0)
when the above is the first/only URL parameter
or
&"&workaround="&INT(NOW()*1E3)&REPT(GoogleFinance("GOOG");0)
when already other URL parameters are part of the URL.
 
Note: there always will be URLs for which this workaround does not work. So please remember it\47s a workaround, not an official way to solve the delayed update problem.
 
Addendum:

If you want to have \47full\47 control over the update interval (which is however always limited because of the fixed trigger of
GoogleFinance every 2 minutes or so) you could wrtite the sub-expression INT(NOW()*1E3) as:
INT(NOW()/TIME(0;10;0))
The time used as the string parameter to VALUE (here 10 minutes, i.e TIME(0;10;0)) will be the interval the modified URL will change (but is because of the interval GoogleFinance triggers always rounded up to a multiple of the GooogleFinance update period, thus a multiple of 2 minutes or so. So the granularity is around 2 minutes and the interval for which the update time can be manipulated is from about 2 minutes to 1 hour).
E.g.:
=ImportHTML("http://www.timeanddate.com/worldclock/" &"?workaround="&INT(NOW()/TIME(0;10;0))&REPT(GoogleFinance("GOOG");0) ; "table";4)

 

And as alternative drop-in workarounds:
&"?workaround="&INT(NOW()/TIME(0;2;0))&REPT(GoogleFinance("GOOG");0)
when the above is the first/only URL parameter
or
&"&workaround="&INT(NOW()/TIME(0;2;0))&REPT(GoogleFinance("GOOG");0)
when already other URL parameters are part of the URL.

Comments

Article rating:
Your rating:
Moderated collaboration
All signed in users can suggest edits to the knol, but these need approval from an author before being published
Version: 2
Versions
Last edited: Oct 8, 2008 5:10 PM.

Activity for this knol

This week:

15pageviews

Totals:

329pageviews
©2009 Google