How To Scrape ANYTHING Directly From Google Sheets

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
in today's tutorial I'm going to show you how to extract information from the web using Google Sheets so if you have a list of LinkedIn profile links and if you want to collect the most up-to-date information you can do this with one click you can collect prices on Amazon products and pretty much anything else that you would otherwise do manually to make all of this possible we're going to be using a browser extension called protein it's a workflow automation tool and for this specific use case there are just three actions the first one is going to get all of your URLs from a given spreadsheet the second action is going to visit each one of the pages that you want to scrape and then it's going to extract information from them and finally we need to find that Google spreadsheet row and update it with the new information hence the third action let's dive in for the first use case we're going to scrape LinkedIn profile links here I have a spreadsheet with a website link over here so I will try to scrape information from that page I already have the browser extension installed so let's open it up and here I'm going to create a new automation like we've broken down the automation before we need to access a list of links in that spreadsheet so I'm going to click on Google Sheets if you do not have it installed you can just click on this plus icon and find Google Sheets over here all right let's scroll down and let's select the section get table from Google sheet we're going to pick our Google sheet it's called scraping websites let's select that and let's select the tab called sheet1 click on done and the second action is going to do the magic it's going to visit every page in the background and collect information for that we have this scraper action as you can see there are a bunch of different scraper actions one to scrape data in the background meaning that each website URL is going to be opened in the background in your browser and it's going to collect the information and the scraper action here asks us for which URLs do we want to scrape so we're going to pick URLs from our previous action and select website links this is this column over here in the spreadsheet and I'm bringing one more time and then let's go ahead and pick a scraper template I'm going to explain what scraper templates are in a more detailed video that you can see by clicking on this little card over here or by going to the description of this video a scraper template just informs bardeen which elements you want to extract from a given website but you already comes with hundreds of pre-built scraper templates so for the LinkedIn profile use case I can just type in LinkedIn profile and here we have it show up as you can see it's built by berdin you can also scrape information from Twitter from crunchbase and obviously you could build your very own scraper for pretty much any website on the web which makes it super exciting okay I'm going to click on done and so far we've gotten all the links we've scraped the links in the background and gotten additional information let's add that additional information into our original spreadsheet you can also create a new spreadsheet with information if you want to but I want to keep things a little bit more clean for that I'm going to scroll down and here we have update Google Sheets tabs Rose so I'm going to pick the same spreadsheet scripting websites pick our tab this action knows which sheet to add the information to but it doesn't know the role to add the information to so we have a list of all of the data coming from LinkedIn that was great now we need to match the data that comes from the scraper to a given spreadsheet Row for that we need to pick a column that we're going to search for a specific data field so we're going to pick website link because that's the field that we originally used so we're looking up website links column and then we want to match each row by the profile link URL let's scroll down and pick this URL variable this is the LinkedIn profile page this is how sheets get updated and finally let's add the data to a given spreadsheet row there are two ways to do it the more complicated way is this you can add a new column over here that will be later created and we and you can call it for example name or full name of the person full name you can do it this way and then from here you can go ahead and click on this field and then pick scraper action and map out data coming from the scraper such as name to this new column that was created personally I don't want to do it for 20 or 30 Fields so there is a little workaround you can click on use commands then let's delete everything we have from there instead I'm going to get all of the data coming from the scrape reaction just like this this way columns that the scraper has but the Google sheet doesn't have will be created in our Google sheet click on done click on done again let's call it something like enrich sheets with LinkedIn profiles all right let's click on Save and now is the magical moment I can minimize the window so you can see what's going to happen which is pretty exciting let's click on this Automation and it's going to run here as you can see our new web page was opened up boarding doesn't require you to pass on any login credentials any cookies because all of the web scraping happens locally in your browser all right and just like this here we have all of the information there's so many fields San Francisco headline about current company education wow there's even in the email field which is amazing now that our automation is built all you need to do is add more profiles which you can also scrape using perdim and then click on the boarding icon over there tap on the Playbook card the data is being scraped in the background so you can always click on this hide button you can close Google Sheets you can do whatever you need to do then watch the magic happen watch the sheet populate itself [Applause] [Music] if you're not new to this channel you already know that I like taking out missions to the next level so now let's add a trigger to this automation when I add a new LinkedIn profile link I want for Dean to recognize that and automatically scrape the data in the background let's go ahead and modify this automation a little bit what I want to do is I want to duplicate this automation so that I have the original and call it automated okay this is our automation over here I'm going to open it in the Builder and then all I need to do is I need to add a trigger when the new spreadsheet row is added so I'm going to go to Sheets over here scroll down and find the trigger when row is added to Google sheet now let's pick the sheet okay there we have it and we no longer need this get table from Google Sheets action because it's going to get all of the data fields where I only want to get the new row that was added I'm going to delete this action over here and what we'll need to do is we'll need to remap the data coming to the scraper so the URL link over here needs to come from this Google sheet and let's look up our website link the original variable let's pick that same scraper template LinkedIn profile let's click on done and let's enable this Autobook that's it this is our newly updated automation let me just pick somebody else's profile for example johanneso would you copy the link paste it here into our spreadsheet and then I'm going to show you what's going to happen when you open up our theme you can click on show activity you'll see all of the instances of this automation being triggered according checks spreadsheets for changes about every minute so you need to know that it's not quite instant okay and just like this as you can see we have a new spreadsheet row that was added over there and again in show activity you see this instance of the automation being run it is your turn to build your very own automation you can go to the description of this video to download per team and if you have some really unique website to scrape the information from make sure to watch this video next about how to use bernian data scraper and how to build your very own scraper templates I'll see you there
Info
Channel: Bardeen
Views: 14,598
Rating: undefined out of 5
Keywords: how to automate web scraping using google sheets, web scraping with google sheets, web scraping google sheets, google sheets get data from website, web scraper tutorial, web scrape in google sheets, data scraping, how to automate web scraping, bardeen scraper, web scraper video tutorial, data enrichment, enrich google sheet, how to scrape websites, scraping from google sheets, google sheets scraping, data mining, data extraction, web scraping guide
Id: c9IDz8E_nek
Channel Id: undefined
Length: 8min 31sec (511 seconds)
Published: Wed May 31 2023
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.