5 Functions for Web Scraping in Google Sheets

[@EamonnCottrell]

⭐⭐Grab the FREE demo sheet here - www.gotsheet.xyz/c/web-scraping-google-sheets-functions

[@jmathew6988]

Very useful tutorial. Nicely explained. Very thoughtful of you to include the XPATH cheat sheet. Thanks for the tutorial!

[@EamonnCottrell]

Thanks! And you’re welcome too! Yeah, that cheat sheet is very useful.

[@zoidoko7001]

Very helpful, thanks

[@EamonnCottrell]

You’re welcome!

[@matiasmartinez6983]

muy bueno eamonn, justo lo que estaba buscando. Ahí va su like buen hombre

[@jonathandorozowsky4005]

This is amazing! Subbed.

[@EamonnCottrell]

Awesome! Thanks!

[@charlesmurray3255]

some superior content here, tyvm

[@EamonnCottrell]

Thanks! Hope it’s helpful!

[@asa_howard]

Let's go! 🚀🚀🚀

[@EamonnCottrell]

Go go go 🎉😊

[@mikiallen7733]

can you provide example pls on the case of Google local guide reviews ? best

[@EamonnCottrell]

What do you mean exactly? Importing reviews from a Google Maps listing? I am not sure you could do something like that as those pages are loaded quite dynamically.

[@BenVost]

How do I go deeper than the first page? I love the way you explained everything and I feel I now know enough to be dangerous 😆 Thank you so much

[@BenVost]

I managed to manually go deeper by using the link as my source, but it seems like almost as much work as writing the pages out by hand 😆

[@EamonnCottrell]

I've only ever referenced the page using the one url. I'm with you - got enough knowledge to be dangerous too 😂😂

[@EamonnCottrell]

@@BenVost lol yeah it can get a bit too much depending on the use case

[@BenVost]

My first import works, but writes the data in columns, not rows. This means that when I do the slug, the initial query no longer works because it would overwrite the slug query. This is my query =importxml(B1,"//*[@class='childpages-macro conf-macro output-block']"). Is there a way to force it vertically?

[@BenVost]

I've found that I can add a =TRANSPOSE() to surround the above line

[@EamonnCottrell]

I’d use the TOCOL or TRANSPOSE functions. Play around with these but I think they should get it in the format you’re looking for. You can probably wrap the import with one of these. Or import it and then transform once it’s in your sheet

[@BenVost]

@@EamonnCottrell Yes, I used =TRANSPOSE(importxml(C4,"/html/body/div[3]/article/div/div/div/ul[1]")) and it worked fine. I was right-clicking on the item in the inspector and choosing Copy XPath, but it doesn't seem as generic as the class I used before?

[@EamonnCottrell]

@@BenVost Yeah the last time I was working with xpath I hit some snags when copying the xpath like that. I expect it was user error but that's why I went more towards just looking for class names on the elements since it seemed more straightforward for what I was doing.

[@BenVost]

@@EamonnCottrell I've gone back to the class. I think it will be more flexible

[@murataksu6496]

Hi everyone. I’m beginner on Google sheet. Is there any platform for to find answers for questions???

[@EamonnCottrell]

Feel free to ask away on any of my videos; I'm very responsive and open to new topic ideas. The data and spreadsheet community on LinkedIn is also quite good. You can find me over there too - www.linkedin.com/in/eamonncottrell/

[@murataksu6496]

@@EamonnCottrell Thank you so much. I have some plans to learn a few programs/software after 45 years old. Spread sheets is one of them. Wish me luck!

[@EamonnCottrell]

@@murataksu6496 Good luck! I also have a newsletter with quick tips if you want to check it out here: got-sheet.beehiiv.com/

[@senatiseguimiento]

in the case that the web hv a login and password?

[@EamonnCottrell]

not a way to do this on a protected website using just these functions (that I'm aware of)...would need a more robust coding tool

[@abigailmapuladikobo9941]

Importxml could not fetch data from a google scholar url. Why is this?

[@EamonnCottrell]

Curious. What's the url? These functions are definitely not foolproof, and some websites may not be able to be this easily scraped. There are a lot of variables.

[@abigailmapuladikobo9941]

@@EamonnCottrell "scholar.google.com/scholar?start=0&q=%22research+methods%22+AND+Africa+AND+training&hl=en&as_sdt=0,5" I tried to scrape the titles.

[@josephquijano9206]

You get me a subs. BTW bruh, can Google Sheet know if a specific word I want is inside a website? For example the word "Greatest" and i want the google sheet determine if it is in a website. thank you!

[@EamonnCottrell]

Cool idea! Yes...you could combine IMPORTXML with some logic to find this out. For instance, I could search for the word "Professional" in every on a web site using this formula (assuming the url is in A1): =IF(COUNTIF(ARRAYFORMULA(IMPORTXML(A1,"//div")),"*Professional*")>0,TRUE,FALSE) So you still need to feed it an element to look through like all the divs or h2s, but this'll do the trick👍

[@josephquijano9206]

@@EamonnCottrell thanks for the tip! BTW, your videos in this channel are dopes. The editing looks like been making by a GOLD channel. Keep it up and you're in the right track! Also, if you can make a video where google sheet can check if an email is valid or invalid using a script. EVA email verifier seems can do the trick. God bless!

[@MuhammadFAH33M]

Clear Explanation 👍 Questions Will the important HTML Is up-to-date data from the source website? If no then please tell us a way to keep a live data 2- I want to scrap ecommerce website product data, how to auto scrap Next page ? 3- How about import data via json file url most e-commerce website uses it eg Shopify I'll be thankful if you please create an ecommerce website data scraping vidoe or share your tips so ill give it a try 🙂

[@EamonnCottrell]

You may want to explore more robust tooling like a plugin or python scripting if you need to handle refreshing/changing data often. But, yes, you can pull data from ecommerce sites using importhtml if the data in question is in a list or a table. Here's a note from Google on how data freshness is handled: support.google.com/docs/answer/12188454?hl=en#:~:text=To%20ensure%20users%20get%20fresh,and%20sheet%20don't%20change.

[@MuhammadFAH33M]

Dear@@EamonnCottrell Thanks for your prompt response. Yesterday i've tried to scrap e-commerce website but Google Sheet using ImportXML returns N/A Error. Even i've used Xpath Tools to validate my selections. They were working perfectly fine but for Google Sheet formula wasn't Looks like this solution is for simple sites which allow fetching info I'll look into the freshness of data via your suggested link 🖇️ Thanks 😊

[@EamonnCottrell]

@@MuhammadFAH33M Yes, I expect that you'll need a more robust solution for bigger, more complicated sites. I have run into issues trying to get data for example from Amazon or similar ecomm sites in the past. ScrapingBee and Octoparse are two platforms I've heard of with a larger set of tools; some googling will get you the rest of the way there. Good luck!

[@MuhammadFAH33M]

@@EamonnCottrell Thank you so much for advanced software suggestions. i'll explorer them. ScrapingBee seems to be new to me :)

[@AbuZikraBilal]

nice one but the memes/GIFs in between the video killed the joy.

[@DataFrenchy]

🤘🤘🤘

[@loistverberg900]

Very helpful. But please nix the funny pictures. When I'm trying to follow something useful they are distracting. Don't forget that what seems simple to you is not to the people trying to learn from you. Cut out the stupid gifs and people will thank you.

[@BL-zq7vd]

dude why you want to look like a goofball on youtube ? u and a million others think looking like a goofball helps. it doesnt!