IMPORTHTML, IMPORTXML Functions - Google Sheets Tutorial to Extract from Web Pages to Spreadsheets 2

Learn how to use IMPORTHTML, IMPORTXML Functions to extract information from websites to spreadsheets.
Find more advanced XPATH video here • XPath query, IMPORTXML...
Final Worksheets
docs.google.co...
Google Sheets
www.google.com...
Company Page
www.chicagocomp...

[@TheOneWhoMightBe]

I had been trying to figure out how to do a similar thing to getting the 'second column' data, and this gave me the exact answer I was looking for!

[@tasmedic]

Thanks ever so much for this. It encourages me to play with the functions, which, for me, is the best way of learning stuff like this!!

[@trueg261191]

Thank you for the information, you helped with a web scraping assessment I needed for a job role. Merci beaucoup!

[@William_sJazzLoft]

I will likely download my sheet from Drive to work on it in Excel. This was an excellent tutorial.

[@takaakiyamada7294]

Google Spreadsheet is so powerful! Thank you. :-)

[@prameshgyawali9732]

Hi, I have been able to create a stock portfolio watching your videos on Import html/xml functions. I am grateful to you. Can I make my portfolio auto refresh ? Could you please leave me a suggestion.

[@sadashivb5100]

hi

[@arklory4674]

Wow your the best

[@DavidElstob73]

Superb, my friend. Thank you.

[@WeMakeItEasy]

Thank you very much - For people in Europe - if it doesn't work then use ";" instead of "," as seperator :-)

[@argonaut119]

Interesting way to do web scrapes! Just grabbing the desired column solved your problem. Going to try that. Some questions on your methods. How did you get your "Inspect" web pages to come up? I am using Chrome and I see a totally different Inspection window. Yours looks much more helpful. Do you have a more basic video on how to inspect and look at the structure of a web page? My Inspect is just not too helpful. Also, I am trying to web scrape some financial info, and when I use IMPORTHTML or IMPORTXML, it is really slow and puts up a "Loading..." message for a while (some times Seconds or Minutes, sometimes it does not finish loading). This also happens when doing IMPORTRANGE calls from other spreadsheets (but I am not sure if these 2 problems are related or not). There is a definite performance problem with these IMPORT commands. Is this common? Could it be that the IMPORTHTML command is trying to download some tables that are very big? My performance issue even occurs even downloading a 3 row-2 column table from the web. Any ideas or thoughts that I could try? Thanks.

[@sunnyjones1064]

Thanks a lot brother..... May God bless you much

[@top_cat26]

thanks my friend, Greetings from Chile

[@friendscafe6870]

easy and great...

[@techie577]

That was very useful, thank you for the help!

[@RobertLongM]

Would Table[1] bring in only the first Table's text?

[@ExcelGoogleSheets]

Yes.

[@geeebeeez]

how can i import table which is visible only when certain tabs are pressed on site: for eg. on this url : in.tradingview.com/markets/stocks-india/sectorandindustry-sector/ i want to import table visible under the "performance" tab rather than "overview"

[@binodmaharjan9981]

@Gohit Bhat I am also having same problem. Did you find any solution? In my case, data table is splited into 2 pages. merolagani.com/StockQuote.aspx I am able to get data from page 1 but not able to get data from page 2 from that url.

[@geeebeeez]

@@binodmaharjan9981 couldn't find solution. it runs javascript to fetch data on web.

[@rebelresin5997]

Great video my friend. At the end of the video you had mentioned you can do this with a product grid. Is there a video on this? Or any help on that would be amazing

[@pankajthakur5531]

sir plz make video on how i extract live data continue from sites in google sheet plz sir make video on it i m waiting for this concept,

[@moneymaker8227]

Good tutorial.!! I have a question.. I would like to know how import data to google sheet in cells that are already filled by other data. ?? I'm trying to automate my stocks imported financial data (income statement, balance sheet, cash flow statement) but I'm only able to import one by one because google sheet cells are already filled by the first stock data I imported.! How Can I RESOLVE This Issue.?? Thanks

[@joguland]

how about divs? is it possible?

[@alish3096]

will this function make an autmation for the sheet?

[@SELF213_]

Are we able to edit the table once its imported to google sheet? For example I would only want one column from the table. Also will it keep updating live everytime the table is updated throughout the day?

[@marcustan6184]

I’m very curious, what keyboard and mouse are you using? It’s always so satisfying to hear them click 😂

[@moazzumgillani4852]

Wowwww!!!!!, Love ir

[@reyanngonzales5891]

I've tried this on one of our tools that is not publicly available (meaning you have to log in). And Its giving me an error. I dont know if there is another step so it would automatically extract the data. | Idont know if this would help but table class in "inspect" is "widefat". Hope you can accommodate my inquiry

[@ExcelGoogleSheets]

You can't use it for sites not publicly available.

[@reyanngonzales5891]

@@ExcelGoogleSheets Thanks for the fast response. :)

[@RobertLongM]

What if the Inspect - just has as class?

[@LanceJarvis1959]

Hi, you've probably got an answer for this by now but just in case you haven't - I experimented with using '//div' instead of '//table' and it worked :) Drilling down further into the div I used '//div/p[1]' for the first line of data in the div and then found I could use '//div/p[1]/a[1]' to get the first piece of data in the first link in the first line. I hope you find this useful.

[@RobertLongM]

@@LanceJarvis1959 I actually didn't! I believe I did okay around with that ... I'll try again. If I fall I sent you the URL Thank You!

[@LanceJarvis1959]

@@RobertLongM Not wishing you a failure or anything, but please do. :)

[@optimallc5233]

How to import data from sites with login and pass??? need help on this

[@Drone-pk]

Do tell me also

[@Edward-ko9pn]

What browser are you using? Thanks

[@francisabellana780]

Thank you!

[@mikemccartneyable]

Superb, thanks

[@brunocesar2283]

Is it possible to use the importdata function to bring an account's contact data?

[@TheOgi22]

05:57 - WTF????? Why this is work with word "list"??? Where in the page code is this word????? I totaly not understeand why is this work. In page code is "ul" not "list". 09:08 - Hey this "//table/tr" is impossible to work! Correct is "//table//tr"

[@rhinozz43]

5:57 google sheets is programmed to accept "list" so any random person can use it. 9:08 /tr does work because the root node and the current selected node are the same.

[@shrikantnikam2426]

I want get data from local market price list and make graph

[@OmDetox]

Thanks for the help. How can you now get the url from the items in the list? lets say I want to have a list of all the communities and the link to the pages about those communities?

[@ExcelGoogleSheets]

This should do it =IMPORTXML("en.wikipedia.org/wiki/Community_areas_in_Chicago","//table/tr/td[3]/ul/li/a/@href")

[@jameswotring9432]

With this kind of import, does it update automatically if the table was updated on the website? if not is there a way to do that?

[@3kleft]

I´m wondering the same thing.

[@3kleft]

@mikell Hey! Thanks for the info; I'm not really good at making scripts. Could I reach out to you or find some way to get help? Thanks again.

[@craigviner4530]

Do all websites allow this? On inspecting the website I want to get info from I can see the table I want, but when inputting the formal into Google sheets it can't be found? Wondering if some websites block this functionality?

[@ExcelGoogleSheets]

No, the page must be loaded by the server, not by javascript for this to work. As javascript loaded websites are slowly taking over the web, this will progressively work on less & less sites.

[@tgiardina08]

What do you do if the table has a unique name instead of a number?

[@andersballe16]

Im trying to import a list from a website with checkmarks When I copy it copes into 3 columns: Column 1 - number Column 2 - blank (because of checkmark) Column 3 - name of the item. How can I import only number into column 1 and name of item into column 2?

[@ExcelGoogleSheets]

=QUERY(IMPORTFORMULA(link,args),"SELECT Col1, Col3",0)

[@senjuzumaki]

how would i do this for lets say getting the dividend yield for a stock from yahoo finance page

[@1egend204]

What is best method to import data from JavaScript rendered pages

[@ExcelGoogleSheets]

Check out on my other channel kzbin.info/aero/PLRmEk9smitaXljDN2CjwZ96o4Aj87iuC5

[@1egend204]

@@ExcelGoogleSheets Thankyou

[@9ebooks]

How to change the url inside the fonction from a list of keywords

[@RobertLongM]

I used importHTML with "Table",8 and it works, but I want to learn how to use the IMPORTXML in the same way - so do you do IMPORTXML and use the path as "//table[8]" ? I doesn't work

[@ОлжасТольмухамет]

if data on website changins its changing in real-time in sheet?

[@billjones3720]

Apparently so, but only when you open the sheet.

[@स्तोत्ररत्नम]

Apply sheet refresh rate

[@felixmorris8379]

Hi - I'm trying to import a table from a website however after inspecting the code, it looks like the table is populated with angular content (ng_content), and therefore the importXML function is not working as the table I'm trying to import from (I'm assuming) looks empty to the computer. Is there a way I can import the angular content data?

[@nimmueh]

Is it dynamic? Will values change automatically along with the page?

[@ExcelGoogleSheets]

yes.

[@newborn7850]

Thanku , Pls can u tell me how to reverse . I want to import from Google spread sheet one row at a time and display in HTML page

[@RobertLongM]

Wow - Great work Odd that HTML Tables / Divs can get parsed with XML import? I always assumed XPath is for XML code

[@ExcelGoogleSheets]

It is, but they both have similar structure so you can parse HTML with XML parsing tools.

[@Gilmour101Li95]

Thanks a lot for this video, unfortunaly, I search the way to recover data from my gmail... I receive email on html format with table, and I dont manage to use this fonction with this because I have not an URL to give at IMPORTHTML... How can I do it ??

[@ghayas76]

Very nice learning video. Sir, How to import .xls file from weblink directly to google sheets?

[@ExcelGoogleSheets]

I don't think you can do it directly. You'll need to use some sort of service in the middle.

[@ssp281]

how to extract data which is depend on selection on site

[@andriisev]

How to import price from Newegg?

[@vivisaens3487]

Excuse me, I have a question, When I apply =importxml(".....", "//table") All the data compare in the only cell that I selected, how is it possible? How can I solve this problem? Thank you

[@christianauner1890]

Hi there. I watched the video and tried it myself but unfortunately it doesn't work. I think I'm making a mistake or the link I have is wrong. I received a link from my dealer where I can get the XML file. These are then only displayed as text in the browser. I would like to import them into Excel or Sheets for analysis. But unfortunately it doesn't work. Maybe you could help me? Best regards

[@cutiepets664]

How to import a graph data??

[@bdonato11]

When I use importHTML it frequently won't update. If I go in and change the table from 1 to 3 and back again, it finally loads. What is the best way to function to load?

[@praveenchandrasekaran7064]

I have few questions. 1. How to count the words in the page 2. I use more than 8 import function it's take more time to load. Kindly give few tips to load faster. 3. I use this method for more than 10 url's In my single page I have more than one h1. So use this method it's come to next row. How I get everything in one cell.

[@muhammadnauman3048]

one more thing , my web contain agent data .i checked the source the name of agents are in //a/href . in tried this in function as //a/@href but its showing wrong data. any help woould be appreciated.

[@Basudeb_Chandra]

"resource at url contents exceeded maximum size" how to cover up this problem?

[@webserviceargentina7025]

Hello from Argentina, i need UPDATE CELL OR PAGE every 5 minutes, who is apps script i would be use for this?? thanks for your attention

[@muhammadnauman3048]

Hi tried this & succeed but importXML only pulling 1st page my web contains approx 100 .please advise how to pull all pages with on functions

[@AlexSmith1]

How does one import data behind a credentialed barrier?

[@ExcelGoogleSheets]

No easy way.

[@jibinpeter]

I tried to grab data from NSE India website and finds not working with the function =importhtml(“www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm”,”table”,1). Can you suggest possible corrections..

[@HagridThoughts]

Can you import tables from Google Docs? I want to update a table in a Google Doc and the data automatically be updated in a Google Sheet

[@ExcelGoogleSheets]

You'll need to write a script for that.

[@HagridThoughts]

Learn Google Spreadsheets okay thanks! Random Question: If I create a script project file in one spreadsheet, how can I import that project file into another spreadsheets?

[@salvatorezaccaria3903]

HI great video... but I have a problem with this formula =IMPORTXML("en.wikipedia.org/wiki/Community_areas_in_Chicago";"//table/tr")... my google sheet works with semicolon instead of comma... So what is the problem? Can you help me? The error that I notice is "the imported subject is empty"... Thanks

[@duplishop]

"ERROR" when I copy your formula in my own google sheets. Can you help me please ?

[@JasonNosajasoNosaj]

An updated formula in the sample spreadsheet would be helpful

[@rsolsjo]

Just tried this. Keep in mind you have to use brackets now, so it's like this: =IMPORTHTML("en.wikipedia.org/wiki/Beat_It"; "table"; 1)

[@gosthwriterful]

Hi, Thank for your explanation! It works! GREAT ☺😀 But i need more help. What do I have to do when in a web page there is table with a 'information details' button, which actually is an hyperlink to another page? Does it exist any formula or solution that can help me? May I write you in private, please? Thanks for your precious support! Micol

[@sanjibpramanik9268]

So, you can click on the 'information details' button through your web browser to open the page, then copy the url, and use it on formula, but you also need inspect that the content of the page is html or XML, if it XML then use 'importxml' formula. I don't know exactly but this may help you.

[@dwightaguinaldo3421]

I'd like to extract just the Forward Dividend from this site =IMPORTHTML("finance.yahoo.com/quote/XOM?p=XOM&.tsrc=fin-srch","table",2) In this example, the answer is "3.48" How can I find the path to extract just the dollar amount instead of the whole table?

[@rafsan_jane]

Hi, I think its only work on Wiki, I try other sites but it does not work. Any way to use other sites or directory sites? Thanks

[@ExcelGoogleSheets]

It will work on sites where HTML is fully loaded on initial page load. Any page that is built by adding content with JavaScript won't work.

[@generallesson9305]

I am tried but unsuccessful. The web page is not comming like yours.

[@jitendrachampati508]

Hi sir,,, but I can't import data from Yahoo finance to Google sheet

[@ExcelGoogleSheets]

To get dynamically generated data use this kzbin.info/aero/PLRmEk9smitaXljDN2CjwZ96o4Aj87iuC5

[@dracutden2]

what if the website is a ".php"

[@poptanivinod]

i tried lot of times every time i got errors help me

[@Mikarevival]

=importxml("xxx";"//table//tr//td[4]") --> use double /

[@elartificial_es]

do =IMPORTHTML("en.wikipedia.org/wiki/Community_areas_in_Chicago";"table";1) instead of =IMPORTHTML("en.wikipedia.org/wiki/Community_areas_in_Chicago","table",1)

[@giamar3026]

@@Mikarevival One thing to take in count, in some countries as mine,, the "," should be replace with ";", other case doesn't work

[@travisdejong2354]

I managed to import my data from Wikipedia but I can't delete or change text.

[@ExcelGoogleSheets]

Copy/paste values only.

[@karthikdarkangle]

How do i import specific price from a website to google sheets (Ex: "www.amazon.in/gp/product/B06W55K9N6/ref=ox_sc_act_title_4?ie=UTF8&psc=1&smid=A14CZOWI0VEHLG" price tag is 5899 and i want it to display only the price in spread sheet)

[@dgiri2333]

How to importXML from Truecaller on Google shit

[@cuneiformscript2665]

🙏🏻

[@RobertLongM]

How do you work with DIV ID / CLASS on a page with so many sub elements? For example - if you go to this website (BLOCK LOCATION) it will show a full USA Results on left as well as on a map. www.heatstoprefractorymortar.com/dealers/ I want to see if I can pull that list which is in div[@id='wpsl-stores'] If I use that - =importxml(A1,"//div[@id='wpsl-stores']") the result is NULL (Blank)

[@Gamesofmach]

Man you would try more difficult site.. like ecommerce

[@ExcelGoogleSheets]

You may want to watch this one instead kzbin.info/www/bejne/pqi9ZWehdsqchdE

[@viveksawant8401]

how can i reduce refresh time from 1 hr to 15 min?

[@dannfish]

This info is great. I need to get some baskeball team stats imported for some reason the 7th table on this website "www.basketball-reference.com/leagues/NBA_2020.html#opponent-stats-per_game::none" does not come up. Can anyone help me correct this formula "=IMPORTHTML("www.basketball-reference.com/leagues/NBA_2020.html#opponent-stats-per_game::none","table",7)"? Thanks for the content

[@probuildjohnsonify]

Yeah Still Dont Work....

[@ronyjoseph529]

Great video, thanks.:) CAn i import the contents from KZbin: "kzbin.info/door/8p19gUXJYTsUPEpusHgteQvideos?view=0&sort=da&flow=grid" to sheets? I tried importhtml with 'list' element, but entire list is not coming up. Please help, Thanks.

[@ExcelGoogleSheets]

KZbin now loads a lot of its HTML using JavaScript, so these functions are not likely to work very well.

[@ronyjoseph529]

@@ExcelGoogleSheets Yeah, I understood. Thanks very much for replying. I've copied the contents of your KZbin Channel (Cmd+A) and arranged to get the list. This is the sheet with list of your channel videos: docs.google.com/spreadsheets/d/16uw9viUNSVLX4MWP_JOW9Dum3rsUollwDh16cZAFReQ/edit?usp=sharing

[@jelenaadamlje9836]

Doesn't work:-)