Design & Development

3 Google Sheets Functions to Track Competitors, More

Google Sheets can monitor competitors’ prices, capture news headlines, and devour data from websites, CSV files, and more. The tool helps small business owners and marketers gain a competitive advantage.

Google Sheets, the search giant’s cloud-based spreadsheet service, does more than just make your financial statements legible. The service has many powerful functions and features. Three of these may even help your business collect competitor and industry data that could lead to better decisions.

IMPORTXML

The first, and most powerful, of the Google Sheets functions I will discuss is IMPORTXML.

Although its name implies that it is for importing extensible markup language (XML) documents, this Sheets feature can be used to ingest several forms of structured data, including XML, hypertext markup language (HTML), comma-separated value (CSV) files, tab-separated value (TSV) files, and RSS feeds.

As an example, imagine that you compete with a popular retailer like Zumiez. Although you don’t offer everything that Zumiez carries, you do have the same particular brand of women’s twill jogger pants.

Google Sheets IMPORTXML function can be used to capture price data from a competitor's product detail pages.

Google Sheets IMPORTXML function can be used to capture price data from a competitor’s product detail pages.

You want to make certain that your price is competitive, so you create a new Google Sheet to track Zumiez. After you have created a title section, perhaps “Zumiez Women’s Pants to Track,” and a few column headers like “Product Name,” “Product Price,” and “URL,” you are ready to start tracking Zumiez’s prices.

You can place the data that IMPORTXML retrieves almost anywhere in a sheet, including below some meaningful headers.

You can place the data that IMPORTXML retrieves almost anywhere in a sheet, including below some meaningful headers.

The IMPORTXML function takes two parameters. First it requires the URL for the resource you want to parse for your data. Next it wants an xpath query.

The IMPORTXML function requires two parameters.

The IMPORTXML function requires two parameters.

To apply this function, click into a Google Sheets field. First type an equals sign and then the function name, IMPORTXML.

=IMPORTEXML

Functions in Google Sheets use parentheses to surround parameters.

=IMPORTEXML()

Next, add the target URL inside of the parentheses. This URL is treated like a quote. In this example, the URL goes directly to the first of the target product detail pages.

=IMPORTEXML( “http://www.zumiez.com/almost-famous-khaki-twill-jogger-pants.html”)

Finally, you will need to use xpath to identify just the element that you want Google Sheets to capture.

=IMPORTEXML( “http://...”, “//h1[contains(@itemprop, 'name')]” )

When you hit enter on your keyboard, Google Sheets will pull in the product name from the Zumiez product detail page.

Do the same thing for the price, and you are starting to automatically track Zumiez. In terms of structure, that is all there is to it.

For some, however, the xpath query might not be self-explanatory. Xpath (the XML path language) is a querying language used to select particular nodes within structured data.

The first part of the xpath — with the two slashes, “//” — tells Sheets to search through the document from the top down, if you will, until it finds a node similar to what we describe next.

In the example, the next description or node is “h1.” It tells Sheets to look for a heading one tag.

Finally, we don’t want just any h1 tag. We want one that contains an attribute called “itemprop,” where that attribute is equal to “name.”

//h1[contains(@itemprop, 'name')]

Finding the proper xpath can take some practice and experience with HTML. One of the easiest ways to find the xpath is to use the developer tools that are built into your web browser. In Google Chrome, as an example, right or alternate click on the product name and select “Inspect Element.”

The developer tools in Google Chrome can be helpful for defining an xpath.

The developer tools in Google Chrome can be helpful for defining an xpath.

Chrome’s developer tools will open, showing you the element (node) you are focused on.

Repeat this process for each product that you want to monitor.

IMPORTFEED

The IMPORTFEED function in Google Sheets is designed to parse an RSS or ATOM feed. The function has one required parameter — the URL for the feed you want to ingest — and three optional parameters.

The IMPORTFEED function only has one required parameter, but option ones help you zero in on the data you want.

The IMPORTFEED function only has one required parameter, but option ones help you zero in on the data you want.

Imagine, for example, that you wanted to bring in the entire Practical Ecommerce article feed. You would click into a Google Sheets field and type the following command.

=IMPORTFEED("https://www.practicalecommerce.com/feed")

When you clicked enter, Google Sheets would bring in all of the articles in the feed.

Google Sheets can act like an RSS reader, gathering all of the articles from a feed.

Google Sheets can act like an RSS reader, gathering all of the articles from a feed.

The second parameter, which is optional, can be used to get a feed description, a feed attribute, or even a particular node from the feed. In this example, Google Sheets will return only the article titles.

=IMPORTFEED("https://www.practicalecommerce.com/feed", "items title")

The third parameter can be used to include column headers. Simply, add “true.” Note here that the second parameter is set to an empty string, which is the default.

=IMPORTFEED("https://www.practicalecommerce.com/feed", "", "true")

The fourth parameter will limit the number of items to return. So if you only want the last five articles, your function would look like this.

=IMPORTFEED("https://www.practicalecommerce.com/feed", "", "true", 5)

IMPORTDATA

The Google Sheets IMPORTDATA function will pull in information from a CSV or TSV file. This can be helpful for absorbing data from web tools or government services.

The Sheets IMPORTDATA function has just one required parameter.

The Sheets IMPORTDATA function has just one required parameter.

To use an example directly from the Google Sheets documentation, if you wanted to look at the U.S. 2010 census, you could pull it into a Sheet just like this.

=IMPORTDATA("http://www.census.gov/2010census/csv/pop_change.csv")
Pulling in data from a CSV or TSV file is easy with IMPORTDATA.

Pulling in data from a CSV or TSV file is easy with IMPORTDATA.

Summing Up

Google Sheets has some powerful tricks that will help small business owners collect actionable data. The best on this list, for me, is IMPORTXML and its ability to track competitor prices.

Armando Roggio
Armando Roggio
Bio   •   RSS Feed


x