How to Use Google Sheets for Web Scraping & Campaign Building

We’ve all been in a scenario the place we needed to extract knowledge from a web site sooner or later.

When engaged on a brand new account or marketing campaign, you may not have the info or the data accessible for the creation of the adverts, for instance.

In a perfect world, we might have been supplied with the entire content material, touchdown pages, and related data we’d like, in an easy-to-import format reminiscent of a CSV, Excel spreadsheet, or Google Sheet. (Or on the very least, supplied what we’d like as tabbed knowledge that may be imported into one of many aforementioned codecs.)

However that’s not at all times the way in which it goes.

These missing the instruments for net scraping — or the coding data to make use of one thing like Python to assist with the duty — could have needed to resort to the tedious job of manually copying and pasting presumably lots of or hundreds of entries.

In a latest job, my workforce was requested to:


Proceed Studying Under

  • Go to the consumer’s web site.
  • Obtain greater than 150 new merchandise unfold throughout 15 completely different pages.
  • Copy and paste the product identify and touchdown web page URL for every product right into a spreadsheet.

Now, you may think about how prolonged the duty would have been if we’d carried out simply that and manually executed the duty.

Not solely is it time-consuming, however with somebody manually going by way of that many objects and pages and bodily having to repeat and paste the info product by product, the probabilities of making a mistake or two are fairly excessive.

It will then require much more time to overview the doc and ensure it was error-free.

There must be a greater manner.

Excellent news: There’s! Let me present you the way we did it.


Enter Google Sheets. I’d such as you to fulfill the IMPORTXML perform.

In accordance with Google’s support page, IMPORTXML “imports knowledge from any of assorted structured knowledge sorts together with XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.”


Proceed Studying Under

Basically, IMPORTXML is a perform lets you scrape structured knowledge from webpages — no coding data required.

For instance, it’s fast and straightforward to extract knowledge reminiscent of web page titles, descriptions, or hyperlinks, but additionally extra complicated data.

How Can IMPORTXML Assist Scrape Parts of a Webpage?

The perform itself is fairly easy and solely requires two values:

  • The URL of the webpage we intend to extract or scrape the data from.
  • And the XPath of the component during which the info is contained.

XPath stands for XML Path Language and can be utilized to navigate by way of parts and attributes in an XML doc.

For instance, to extract the web page title from, we might use:

=IMPORTXML(“”, “//title”)

This can return the worth: Moon touchdown – Wikipedia.

Or, if we’re in search of the web page description, do that:

=IMPORTXML(“”,”//meta[@name=’description’]/@content material”)

SEJ Home Page Meta Description Pulled with IMPORTXML.

Here’s a shortlist of among the commonest and helpful XPath queries:

  • Web page title: //title
  • Web page meta description: //meta[@name=’description’]/@content material
  • Web page H1: //h1
  • Web page hyperlinks: //@href

See IMPORTXML in Motion

Since discovering IMPORTXML in Google Sheets, it has actually turn into certainly one of our secret weapons within the automation of lots of our each day duties, from marketing campaign and adverts creation to content material analysis, and extra.

Furthermore, the perform mixed with different formulation and add-ons can be utilized for extra superior duties that in any other case would require subtle options and growth, reminiscent of instruments in-built Python.

However on this occasion, we are going to have a look at IMPORTXML in its most simple kind: scraping knowledge from an internet web page.

Let’s take a look at a sensible instance.

Think about that we’ve been requested to create a marketing campaign for Search Engine Journal.

They want us to promote the final 30 articles which have been printed beneath the PPC part of the web site.


Proceed Studying Under

A reasonably easy job, you may say.

Sadly, the editors aren’t capable of ship us the info and have kindly requested us to confer with the web site to supply the data required to arrange the marketing campaign.

As talked about initially of our article, a technique to do that can be to open two browser home windows — one with the web site, and the opposite with Google Sheets or Excel. We might then begin copying and pasting the data over, article by article, and hyperlink by hyperlink.

However utilizing IMPORTXML in Google Sheets, we will obtain the identical output with little to no threat of creating errors, in a fraction of the time.

Right here’s how.

Step 1: Begin with a Contemporary Google Sheet

First, we open a brand new, clean Google Sheets doc:

Start with a Blank Google Sheets Document.

Step 2: Add the Content material You Have to Scrape

Add the URL of the web page (or pages) we need to scrape the data from.


Proceed Studying Under

In our case, we begin with

Add the URL of the Page You Want to Scrape.

Step 3: Discover the XPath

We discover the XPath of the component we need to import the content material of into our knowledge spreadsheet.

In our instance, let’s begin with the titles of the newest 30 articles.

Head to Chrome. As soon as hovering over the title of one of many articles, right-click and choose Examine.

Open the Chrome WebDev Tool.

This can open the Chrome Dev Instruments window:

Find and Copy the XPath Element You Want to Extract.

Guarantee that the article title continues to be chosen and highlighted, then right-click once more and select Copy > Copy XPath.


Proceed Studying Under

Step 4: Extract the Knowledge Into Google Sheets

Again in your Google Sheets doc, introduce the IMPORTXML perform as follows:

=IMPORTXML(B1,”//*[starts-with(@id, ‘title’)]”)

A few issues to notice:

First, in our formulation, we’ve changed the URL of the web page with the reference to the cell the place the URL is saved (B1).

Second, when copying the XPath from Chrome, it will at all times be enclosed in double-quotes.


Nonetheless, in an effort to be sure that it doesn’t break the formulation, the double quotes signal will have to be modified to the only quote signal.


Notice that on this occasion, as a result of the web page ID title modifications for every article (title_1, title_2, and so on), we should barely modify the question and use “starts-with” in an effort to seize all parts on the web page with an ID that accommodates ‘title.’

Here’s what that appears on the Google Sheets doc:

An example of IMPORTXML.

And in only a few moments, that is what the outcomes appear like after the question has been loaded the info onto the spreadsheet:

Titles Imported in Google Sheets.

As you may see, the listing returns all articles which might be featured on the web page that we’ve simply scraped (together with my earlier piece about automation and the right way to use Ad Customizers to Improve Google Ads campaign performance).


Proceed Studying Under

You possibly can apply this to scraping another piece of data must arrange your advert marketing campaign, as nicely.

Let’s add the touchdown web page URLs, the featured snippet of every article, and the identify of the writer into our Sheets doc.

For the touchdown web page URLs, we have to tweak the question to specify that we’re after the HREF component connected to the article title.

Subsequently, our question will appear like this:

=IMPORTXML(B1,”//*[starts-with(@id, ‘title’)]/@href”)

Now, append ‘/@href’ to the top of the Xpath.

Import the Article Links.

Voila! Immediately, we’ve the URLs of the touchdown pages:

Articles and URLs Imported in Google Sheets.

You are able to do the identical for the featured snippets and writer names:

All the Data is Scraped and Imported in Google Sheets.


One factor to watch out for is that so as to have the ability to totally develop and fill within the spreadsheet with all knowledge returned by the question, the column during which the info is populated should have sufficient cells free and no different knowledge in the way in which.


Proceed Studying Under

This works in an analogous method to after we use an ARRAYFORMULA, for the formulation to develop there should be no different knowledge in the identical column.


And there you’ve a totally automated, error-free, method to scrape knowledge from (doubtlessly) any webpage, whether or not you want the content material and product descriptions, or ecommerce knowledge reminiscent of product value or transport prices.

In a time when data and knowledge will be the benefit required to ship higher than common outcomes, the flexibility to scrape net pages and structured content material in a straightforward and fast manner will be priceless. In addition to, as we’ve seen above, IMPORTXML can assist to chop execution instances and scale back the probabilities of making errors.

Moreover, the perform is not only an ideal device that may be solely used for PPC tasks, however as a substitute will be actually helpful throughout many alternative initiatives that require net scraping, together with website positioning and content material duties.

Extra Assets:


Proceed Studying Under

Picture Credit

All screenshots taken by writer, August 2021

Source link