Tuesday, September 18, 2012

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.

22 comments:

  1. That's brilliant and it helps me a lot.Thanks :)

    ReplyDelete
  2. This is awesome! If I add these & minute(now()) parameters to the end of the formula, will that make it auto-update when I run a function that references the importXML cell using script editor?

    or....

    Do you know how to auto-update a spreadsheet inside script editor? I've been looking around for a way to get the script to automatically update my importXML formulas for me every hour but can't find anything.

    ReplyDelete
  3. Thanks! I've been trying to get the GoogleFinance() function to automatically update. Maybe I'll just use ImportHTML() and another website.

    ReplyDelete
  4. When adding the parameter you provided, the spreadsheet is stuck on loading....
    Does this method still work?

    ReplyDelete
  5. I am also getting the "loading..." and not getting data anymore. Has anyone found a work around for this?

    ReplyDelete
  6. I get the "loading..." too and no more data. Also, what about a URL that doesn't need time parameters?

    ReplyDelete
  7. This works in the old Sheets, but no longer works in the new Sheets.
    Anybody know a way to refresh importHTML in new Sheets?

    ReplyDelete
  8. This works in the old Sheets, but no longer works in the new Sheets.
    Anybody know a way to refresh importHTML in new Sheets?

    ReplyDelete
  9. It seems as the the (now()) functions have stopped working all of the sudden. I had been using them fine and yesterday I noticed they are now giving me this error message, "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()" Any suggestions?

    ReplyDelete
    Replies
    1. Hi,
      I'm having the same problem.
      It used to work fine for me until recently and I receive the same error message.
      Any help would be very appreciated!
      Thank you

      Delete
    2. ye same error here, would be nice with a solution to this :(

      Delete
  10. The URL I'm using ends in "/index.aspx". How do I use your method with a URL like this?

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hi,
    For those of you that get the error "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()", I have a solution if you want to refresh the functions on a daily basis. I don't know of a solution for any frequency less than that.

    Basically, replace all the "now()" elements in the solution above with "today()".

    ReplyDelete
  13. #ERROR!
    "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()"

    ReplyDelete
  14. I have a solution that is working for me. Instead of the now, I append a googlefinance cell at the end of the url, and it works like a charm :D

    ReplyDelete
    Replies
    1. Excellent work. Looks like they don't like the regular updates. If they offered 5 10 or 15 minute updates whilst the sheet is open, that would be great.

      Delete
  15. google finance cell? Do you have example syntax on how you did it?

    ReplyDelete
  16. =IMPORTHTML("https://www.msn.com/en-us/sports/golf/leaderboard/"&GOOGLEFINANCE("GOOG","price"), "table", 2) - This seems to work

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. will the data remain updated or not?

      Delete