18
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 ...
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 ...
- 27.1k
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 ...
- 27.1k
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, ...
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.8k
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 ...
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 ...
- 27.1k
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,841
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.+...
- 5,123
1
vote
=IMPORTHTML forces content to date format
Here is a solution using a single formula
=ArrayFormula(
REGEXREPLACE(SPLIT(TRANSPOSE(SPLIT(JOIN("",TRANSPOSE(QUERY(TRANSPOSE(
{IMPORTXML(A1,"//tr/*"),TRANSPOSE(SPLIT(REPT(REPT(&...
1
vote
Accepted
Is it possible to trip only specific lines in google sheet importhtml function
Use regexextract(), like this:
=value(
regexextract(
importhtml("https://www.moneycontrol.com/commodity/gold-price.html", "table", 3),
":\d+\s+(\d+)"
)
)
- 15.7k
1
vote
How can I use IMPORTHTML to return a single value instead of an entire table
The following formula in A1 uses QUERY to return Column 3 only, while also limiting the result to a single value, with or without the column label.
A. Include Label
=query(
importhtml(
"...
- 2,422
1
vote
IMPORTHTML stuck on loading
This is currently a known bug with ImportHTML and ImportXML, and the fix is expected to come in the next few days. Custom functions are also broken and that breakage appears to be related to this ...
- 111
1
vote
Google Sheets IMPORTXML and IMPORTFEED and "Could not fetch url" Error
UNSUPPORTED GOOGLE SHEETS FEATURE
As of 2022, most API or other calls for data to sec.gov requires a declared customized header, per this other question on StackOverflow.
Google Sheets has a static ...
- 237
1
vote
Can you change the importhtml address for the website URL automatically? The website address ends in tomorrows date so changes each day
I think this is what @Rubén was getting at. This formula will automatically change to reflect the current date:
="https://www.poisonfoot.com/"&day(TODAY())&"-"&month(...
- 5,841
1
vote
Use importhtml to import a single cell vs a whole column
You can wrap your current formula with the INDEX function:
=INDEX(QUERY(IMPORTHTML("https://shop.tcgplayer.com/magic/darksteel/echoing-truth?xid=pid0b5fc15-f2ac-4f33-ae86-a3ed6d38bb44","...
- 13.1k
1
vote
ImportHTML Altering Data Format/URL Fetch Issue
Tl;Dr IMPORTHTML and IMPORTXML assigns the data type before adding the values to the spreadsheet. Numbers like 43863 are serial numbers used to represent dates, time and durations.
Google Sheets
...
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 ...
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=...
- 27.1k
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-...
- 27.1k
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 ...
- 241
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,879
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 ...
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.8k
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,879
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 ...
Only top scored, non community-wiki answers of a minimum length are eligible