Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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 ...
Rubén - Volunteer Moderator -'s user avatar
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.. ...
Aurielle Perlmann's user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
user0's user avatar
  • 26.7k
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 ...
user0's user avatar
  • 26.7k
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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, ...
Rubén - Volunteer Moderator -'s user avatar
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","...
pnuts's user avatar
  • 17.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 ...
user0's user avatar
  • 26.7k
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 ...
Tedinoz's user avatar
  • 5,721
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.+...
Erik Tyler's user avatar
  • 5,048
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 ...
Catalyx's user avatar
  • 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(...
Tedinoz's user avatar
  • 5,721
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(&...
Rubén - Volunteer Moderator -'s user avatar
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","...
marikamitsos's user avatar
  • 12.9k
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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=...
user0's user avatar
  • 26.7k
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-...
user0's user avatar
  • 26.7k
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 ...
Emmabee's user avatar
  • 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 ...
Jas Mahay's user avatar
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")....
Murali Pujari's user avatar
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")
Aurielle Perlmann's user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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)
pnuts's user avatar
  • 17.6k
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(...
Aurielle Perlmann's user avatar
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
JVC's user avatar
  • 1,236

Only top scored, non community-wiki answers of a minimum length are eligible