Google Spreadsheets and importing tables to CSV!

The below post helped me import train times to a CSV and then import it to a mysql database.

Plus it scrapes the data for you!!!

Google Spreadsheet ImportHtml auto update or refresh

Question:

I'm using the =ImportHtml function to get some data from an HTML table.

It works okay, but I need it to refresh. The HTML data will change periodically, so I need the spreadsheet to periodically check to see if the data have been updated.

Right now it seems to be pretty static, and I can't find a setting or anything to make it check for new data.

I've searched through some forums and can't find an answer or a workaround.

Please help!

(I'm a programmer, so feel free to get as techy as needed. And if anyone knows of anything in the Java API that can help me, please advise)

Solution:

Following is the work around to get the auto-refreshed values (results) from ImportHtml fomula:

You have to pass the parameters at the end of the URL (these parameters are meaning less, adding these parameters to URL will not change your results, and these parameters are used to only change the URL again and again so that we can get the refreshed values)

For example:

If you are having the following URL in ImportHtml formula:

=ImportHtml("http://www.nasdaq.com","table",4)

Then change it to:

=ImportHtml("http://www.nasdaq.com/?"& minute(now()),"table",4)

The above formula will update your imported content from html every minute.

But in this case also chances are that you will get the same table again after an hour, this is because suppose minute(now()) returns 6 as value, so it is going to return 6 after an hour hour, so chances are you will get 60 times updated value and then again you will get same results.

So to avoid it you can try following formula:

=ImportHtml("http://www.nasdaq.com/?"& year(now()) & month(now()) & day(now()) & hour(now()) & minute(now()),"table",4)

The above formula will give you updated results every minute.

And if you want updated result every second then change it to:

=ImportHtml("http://www.nasdaq.com/?"& year(now()) & month(now()) & day(now()) & hour(now()) & minute(now()) & second(now()),"table",4)

Note:

If you don't see auto update then just press "delete" key on any empty cell this will make your spreadsheet refresh this formula and it will work. (And if you want to auto update (refresh) then you can code a script for that)

I hope the above solution helps you.

Swiped from http://igoogledrive.blogspot.in/2012/09/google-spreadsheet-importhtml-auto-update-or-refresh.html