Web feeds help us to stay up to speed with recent posts, news, articles, or podcasts from sources we love and want to follow.
For a long time, we have been using feed readers of all kinds to access these web feeds, some of them not so great. But what if you could make your own feed reader in less than 5 mins? What if you could go beyond that and even customize your feed reader to meet your preferences?
Yes, it is possible. In this article, we will look at how to import data from RSS and Atom feeds to Google Sheets. We will start by looking at what makes this operation possible then we will head over to advanced techniques that can help us tailor our feeds to match our preferences.
- IMPORTFEED function
- RSS and Atom feeds
- Why import RSS and atom feeds
- Syntax of the IMPORTFEED function
- How to import data from RSS and atom feeds
- Retrieve specific articles from a website
- Retrieving item summary
- Retrieving the author of the latest article
What is the IMPORTFEED function in Google Sheets?
It is a Google Sheets function that retrieves data from RSS and Atom feeds when given a feed URL.
What exactly are RSS and Atom feeds?
RSS which stands for Really Simple Syndication and Atom are both what we refer to as web feeds. Essentially, they make it possible to subscribe to updates from a website e.g. a news website might want to provide breaking news to its audience, to do this, the site offers a link through which anyone can subscribe to updates.
To receive and view the updates you need a platform that will interpret the XML format that the web feeds come as. Hence, the need for feed readers. Using feed readers we can retrieve the XML data and interpret them into information we want to see.
Why import data from RSS and Atom feeds to Google Sheets?
Google Sheets can be used as a feed reader, an aggregator in which you can access your feeds. This is made possible by a simple function — the IMPORTFEED function. Benefits include:
- Easy to set up
- Same spreadsheet environment we are familiar with
- Offers us numerous ways to customize our feeds
We will look at what this function offers by considering some IMPORTFEED examples.
- Using cell reference
IMPORTFEED(A3, B3, C3, D3)
Where the cells A3, B3, C3, and D3 all hold values for the parameters of the IMPORTFEED function in Google Sheets.
Syntax of the IMPORTFEED function in Google Sheets
The syntax of the IMORTFEED function in Google Sheets is as given under:
= IMPORTFEED(url, [query], [headers], [num_items])
All the parameters in square brackets are optional arguments.
url – The RSS or ATOM feed’s URL. It is not usually provided on websites these days. But we will look at how to get it for any given site.
Note: When passing the URL directly into the function it must be in quotation marks (” “); however, this should be removed if we reference it from a cell.
query – Specifies what data to fetch from url. By default this is Items. Here are some forms this could take:
- “feed” refers to a single row in the feed data
- “feed <type>” – refers to a particular attribute of the feed, e.g title, description, author, etc.
- “items” refers to all content in the feed
- “items <type>” – item type can be title, date, author, etc.
headers – include column headers as an extra row on top of the returned data. Set to FALSE by default.
To copy the spreadsheet used for this tutorial simply click on this link – Here
How to import data from RSS and Atom feeds to Google Sheets
Using the IMPORTFEED function in Google Sheets involves two major steps. We need to first find the feed URL for our website. These days, a lot of websites do not provide a link. Some of them aren’t even aware that they have an RSS feed.
Step 1: Find the RSS feed of the website
In a case where a website doesn’t openly make its feed’s URL available, we need to find it. There are several ways to do this. But let us look at a very simple one, follow the steps below
- On the web page simply press Ctrl + U (for Mac, Cmd + U). This command will take you to the page source, ie, the code behind the page.
- Type Ctrl + F (Mac: Cmd + U), this will trigger the search function. Enter “feed” into this search bar.
- The feed for the website will be highlighted. Simply copy the URL specified and head over to Google Sheets where you will be providing this to the IMPORTFEED function as a parameter.
Step 2: Find the URL
Now that we have discussed the prerequisite let’s take a look at a few IMPORTFEED examples. We will be using the feed URL of our website https://blog.tryamigo.com/. You can find the feed URL for this website using the simple trick shared.
Step 3: Enter the URL
Unlike most Google Sheets formulas that involve us starting with a table of data. The IMPORTFEED function works in the reversal. It will return a data table using the feed URL we provided. So let’s enter the URL.
In the cell of choice type in the following:
Select the function from the auto-suggest menu.
Paste in the URL you got into the formula.
As previously mentioned, other parameters of this function are optional. So the URL we entered is enough for the formula to work.
Hit the Enter key after supplying the URL.
The IMPORTFEED function in Google Sheets returns a table that contains the newest articles on the site and other related fields such as the author, the date, and time for each of the articles published.
Note: Changes in the source, eg, a new article published, will be immediately reflected in the spreadsheet without any user’s imput.
This is an IMPORTFEED example showing its most basic usage. But there are several ways we can utilize the formula, let’s take a look at them.
Retrieving a specific amount of articles from a website
We can decide that we are only interested in seeing 5 articles instead of the whole feed. And if you observed, the data returned in the previous example has no header.
So, let us return only 3 articles in this IMPORTFEED example. To do this we are going to set the item_count parameter to zero.
Then we will set the header parameter to true.
Enter the function:
=IMPORTFEED("https://blog.tryamigo.com/feed/", "items", TRUE, 3)
The IMPORTFEED function in Google Sheets returned three total articles just as we specified. Also, if you observe, the various columns now have a title at the top. This can be quite handy in situations where we are dealing with large amounts of data.
Returning item summary using the IMPORTFEED function in Google Sheets
Assuming that we are interested in seeing the summary of the articles so that we can just quickly read and stay up to date, we can use the item summary parameter.
Enter the formula below in a cell or the functions bar.
=IMPORTFEED("https://www.blog.tryamigo.com/feed", item summary, TRUE, 5)
Alternatively, you can use the cell referencing technique.
We have specified that we want the IMPORTFEED function to return only the summaries for the various articles. The summary refers to the content of these articles.
Only the Summary column is filled in the table as we can see above.
Tip: Instead of inputting the values directly into the formula, use cell reference (keep the input in the cell and then reference them in the formula). This is a neater way to do it.
Using the IMPORTFEED function in Google Sheets to fetch the author of the latest article
What if we wanted to see only the author of the most recent article? It is easy. What we need is the items author Query. This query lets us retrieve or manipulate data related to the author of the article, you can do things like retrieve all the names of the authors, check the last published author e.t.c
Set the query parameter as “items author,” And set the number of items to 1.
Our header should remain as 1.
Our single-author query was successful. Now, we can immediately tell if our favourite author was the one who just published an article.
You can import data from RSS and Atom feeds in Google Sheets using the very simple technique we discussed in this article. The IMPORTFEED function in Google sheets is very handy and gives us the needed flexibility over our feed data. Hope this RSS google sheets integration guide helped you.
Check out similar articles on other handy Google Sheets functions on our blog: