Google Sheets IMPORTHTML – Fix ‘Could Not Fetch URL’ Error

google sheetsimporthtml

I made a spreadsheet, a part of which uses the IMPORTHTML function to get data from this webpage. For the first couple of weeks that it ran, it worked pretty great, getting all the data from the table as needed. I use the function 3 times, to get the results from the 3 different tables on the page.

What I've written is: =IMPORTHTML("http://www.parkrun.org.uk/results/juniorclubs/?eventname=norwich-juniors","table", 3) in a cell.

It worked fine for a couple of weeks, but now just says that it can't fetch the URL. It returns the same error in a completely blank spreadsheet. What's up with this – have I made a really obvious mistake, or did Google change the way that the function should be used? Could something else affect this?

Best Answer

Oddly enough - in this exact situation - I played around with the page, a couple functions, variations, but the change that actually pulled in the data was simply changing the url from http to https..

see below image for live content successfully pulled

Related Solutions

Google-sheets – How to merge multiple spreadsheet content having the list of keys to them

One alternative is to use CONCATENATE or the ampersand operator to create the formula for you, but the result will be a text. Then copy, paste as value and edit the result to have a working formula.

You could automate this with Google Apps Script.

References
CONCATENATE - Docs editors Help
Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help

Google-sheets – Extract table from moneycontrol.com

When URL: http(s)://www.moneycontrol.com/stocks/fno/marketstats/futures/gainers/homebody.php?opttopic=&optinst=allfut&sel_mth=1&sort_order=0

opened in client (browser) following server side rendering of dynamic data are requested by browser:

 - send @   jquery-1.12.0.min.js:4 
 - ajax @   jquery-1.12.0.min.js:4
 - updaterss    @   jquery.webticker.js:39
 - (anonymous)  @   jquery.webticker.js:137
 - many more...

this is why you can not use IMPORTHTML or IMPORTXML to fetch Dynamic data as in this webpage.

How to check if Error: Imported Content is Empty is because of dynamic data?

use a la carte rendering of web page in question like:

=IMPORTXML("http://www.moneycontrol.com/stocks/fno/marketstats/futures/gainers/homebody.php?opttopic=&optinst=allfut&sel_mth=1&sort_order=0", "/")
- "/" will force to fetch everything from URL, and there is nothing useful in this IMPORT XML/HTML for your need.
Using the Chrome browser's INSPECT function displays all the client requests.

Best Answer

Related Solutions

Google-sheets – How to merge multiple spreadsheet content having the list of keys to them

Google-sheets – Extract table from moneycontrol.com

Related Topic