17
votes
Accepted
How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?
Tl;Dr
The importhtml(), importxml(), importdata() and importfeed() functions only work with public pages that do not require signing in or other forms of authentication.
Further, if the content of a ...
- 44.6k
12
votes
Accepted
IMPORTHTML 'could not fetch URL' but used to be able to
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..
...
- 4,751
7
votes
What are the refresh characteristics of the Google Spreadsheet Import functions?
#Short answer
You are right, the import functions updates their result at certain intervals.
#Explanation
From Change a spreadsheet's locale, time zone, recalculation, and language, a help article ...
- 44.6k
6
votes
How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?
(maybe not the answer, but here is a quick humble scraping guide)
all starts with disabling JavaScript for the website to see what's left and what's possible to be scrapped
the first choice is always ...
- 26.2k
4
votes
Is it possible to use IMPORTHTML to return only certain cells?
pretty much possible:
=INDEX(IMPORTHTML("https://geizhals.de/?fs="&A1&"&in=";"table";0);2;3)
this one shall search for a phrase located in cell A1 and it ...
- 26.2k
3
votes
Accepted
Querying data through importHTML function in Google spreadsheet
I got it to work with this:
=QUERY(ImportHTML( "http://eciresults.nic.in/StatewiseU05.htm" , "table" , 9 ), "SELECT * where Col2>0",0)
- 1,844
2
votes
Import current price from website to spreadsheet
The error in your importxml was in the xpath syntax. here is the correct one:
=CONCATENATE(IMPORTXML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552","//*[@itemprop='price'][1]")...
- 4,751
2
votes
How do I resolve IMPORTHTML showing error #N/A?
On mouse over the cell's top right corner, Google Sheets shows extended error description "Import Internal Error":
That means that there is an error one the Google side. You could report it by ...
- 44.6k
2
votes
Manual importhtml()?
The error occurs because spreadsheet functions like importHTML can't be calculated by Google Apps Script.
The Class UrlFetchApp it's able to get content from a URL. Once the script got the content, ...
- 44.6k
2
votes
IMPORTHTML data is sporadically changing to incorrect format
Possibly try a different approach. Don't use query but instead, in A1 and copied across to B1:
=index(IMPORTHTML("http://www.thepowerof10.info/athletes/profile.aspx?athleteid=800922&viewby=date","...
- 17.4k
2
votes
Accepted
Do named ranges not work under certain conditions?
Regarding why INDEX does not work when there are named ranges as parameters, but does work in the other cases, it is not clear to me yet.
I was thinking that the problem is that INDEX requires that ...
- 44.6k
2
votes
IMPORTHTML 'could not fetch URL' but used to be able to
"Could not fetch URL" basically means that Google is not able to get the content of the URL. It's be very likely that this is caused by "something" on the side of the specified URL
Google use the ...
- 44.6k
2
votes
Accepted
Search prices for a list of items
not possible on https://geizhals.de/
but you can make a custom spreadsheet which will track down prices for you. something like: https://docs.google.com/spreadsheets/d/
unfortunately, not all items ...
- 26.2k
2
votes
=IMPORTHTML forces content to date format
There are likely to be many solutions to your question. Even in this answer, I would expect that the structure could be simplified - possiblky along the lines of the answer by Rubén in Trying to use ...
- 5,510
2
votes
=IMPORTHTML forces content to date format
@John Tokish, this should do the trick:
=ArrayFormula(REGEXREPLACE(TO_TEXT(IMPORTHTML("https://www.actionnetwork.com/nfl/nfl-against-the-spread-standings","table",1)),"200|20.+...
- 4,980
1
vote
Trying to use Google Sheets importHTML() to import a table. But no content appears
IMPORTHTML doesn't works because the page you are trying to scrape use JavaScript to add the data tables to the page. Google Sheets doesn't have built-in functions that are able to scrape data that is ...
- 44.6k
1
vote
How to import data from Investing.com into spreadsheet?
=IMPORTXML("https://in.investing.com/equities/jagson-airlines-bo",
"//span[@id='last_last']")
=IMPORTXML("https://in.investing.com/equities/jagson-airlines-bo",
"//span[@class=...
- 26.2k
1
vote
Extracting / scraping specific information off a website programatically
the simplest thing you can use is Google Sheet - online tool on steroids based on MS Excel.
and all you need is to paste this into some cell:
=IMPORTHTML("https://echa.europa.eu/fr/registration-...
- 26.2k
1
vote
Accepted
Truncate Text returned from indexhtml
=iferror(value(A1), if(right(A1)="k", left(A1, len(A1)-1)*1000, if(right(A1)="m", left(A1, len(A1)-1)*1000000, if(right(A1)="b", left(A1, len(A1)-1)*1000000000, "Unexpected Value"))))
This will check ...
- 231
1
vote
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 ...
- 11
1
vote
Importhtml data alert on new row updated
I got the solution.
var inc1=IoI.getRange(13,9).getValue();----------- Got row count cell
var inc2=IoI.getRange(14,9).getValue();
if(inc1!==inc2)
{
IOincincsendMail();
IoI.getRange("i14")....
1
vote
Trying to pull data from website into Google Sheet
It worked for me when i tried the formula you shared - but additionally this also works:
=IMPORTXML("https://www.pgatour.com/stats/stat.194.html","//tr")
- 4,751
1
vote
Google Sheets IMPORTHTML has long loading time - How can I improve efficiency?
How to improve efficiency
If it's possible, instead of having one large spreadsheet make several small spreadsheets in order to reduce the recalculation time.
Related Q&A
See my answer to How ...
- 44.6k
1
vote
Accepted
IMPORTHTML - Google Sheets - Can't find specific table
Would help to show what you tried, because this should work:
=importhtml("http://www.fangraphs.com/statsplits.aspx?playerid=13593&position=2B/SS&season=2017","table",8)
- 17.4k
1
vote
Trying to use Google Sheets importHTML() to import a table. It forces content to a date format
You can do this in a single formula using array_constrain, query, index and regexreplace:
={ARRAY_CONSTRAIN(IMPORTHTML("http://www.sportsnet.ca/hockey/nhl/standings/", "table"),100,10),arrayformula(...
- 4,751
1
vote
Accepted
Trying to use Google Sheets importHTML() to import a table. It forces content to a date format
Formulas
For columns 1-10
On cell A1 add the following formula:
=ARRAY_CONSTRAIN(IMPORTXML("http://www.sportsnet.ca/hockey/nhl/standings/",
"(//table)[1]//tr"),2000000,10)
For columns 11-13
On ...
- 44.6k
1
vote
Accepted
Various incorrect errors when using import functions
ARRRGH! It was user error. I did in fact have a now() embedded many levels deep in a nested formula that was a leftover from an experiment I did recently, and totally forgot about. Removed it and ...
- 1,192
1
vote
Google Sheets LEFT function trim text generated by IMPORTHTML function
There are a couple ways to do it:
If you specifically want to get rid of only that one element that shows up as "Sector" you can use the substitute function:
=substitute(iferror(INDEX(IMPORTHTML("...
- 4,751
Only top scored, non community-wiki answers of a minimum length are eligible