CPD Hacks – Saving Google Alerts to a spreadsheet

This is part of a new series of short articles documenting ways to manage and optimise your current awareness workflow, to help you keep up with the latest research and news on topics relevant to your work.

Did you know you can import RSS feed data directly into a Google spreadsheet? This can be a helpful way of storing news alerts such as mentions of your library or a particular promotional campaign. It can also be a useful way to store research on a particular topic for later.

For example, we can save a Google search results to our Google Sheets document. We can also do this for Google Scholar alerts and other types of RSS feeds. To do this we’ll use the IMPORTFEED function.

But first we need to find our RSS feed for a keyword search. We can create this feed using Google Alerts. Google Alerts are available for Google searches as well as for Google Scholar.

Setting up a Google Alert

You can access and manage your Google Alerts via: https://www.google.com/alerts and Google Scholar Alerts via https://scholar.google.com/scholar_alerts. But when you do a search, you also see a prompt to create an alert at the bottom of the search results page.

Let’s set up an alert for Research Data Management. You need to be logged in to a Google Account for this.

Once we create the Alert, we can then grab the RSS feed address by clicking on the RSS icon. This will open our RSS feed in the browser and will likely give us a warning like:
“This XML file does not appear to have any style information associated with it. The document tree is shown below”.

This is because browsers can’t read RSS files by default. That’s no problem, what we need is the URL for the page.

Saving your feed results in Google Sheets

We can import our feed data into the spreadsheet using the IMPORTFEED function. By default this will return all the available fields and all items currently published on the feed.

At it simplest, the formula would look like this:
=IMPORTFEED(“yourfeedurlgoeshere.rss”).

So, using our Google Alerts RSS feed, we would add into our cell:

=IMPORTFEED(“https://www.google.co.uk/alerts/feeds/14872310859135591997/3475723021076905659”)

This will give us all the content available, including the title, link, publication date and summary fields (if available). Note the double quotes around the URL, without these you’ll get an error.

Customising your feed results

You can specify each field to import into your spreadsheet by slightly altering the function in each column. The structure of our IMPORTFEED function (including the optional components) looks like this:

IMPORTFEED(url, [query], [headers], [num_items])

We can use the query field to specify different data fields available in the feed.

For example, if we wanted to include the item title and the URL but not the summary or other fields, when we will create a title field using the formula

=IMPORTFEED("https://www.google.co.uk/alerts/feeds/14872310859135591997/2932531268792792064", "items title" and 

then, in the next column, we can add a URL field with the formula

=IMPORTFEED("https://www.google.co.uk/alerts/feeds/14872310859135591997/2932531268792792064", "items url" . 

The Headers query can be used to fetch the column headings – it can be set to either TRUE or FALSE.

If we want to limit our feed to the most recent 10 items, for example, our formula would look like this:

=IMPORTFEED("https://www.google.co.uk/alerts/feeds/14872310859135591997/2932531268792792064", "items", FALSE, 10)

This can be useful if we want to publish our RSS feed data onto a website using the ‘Publish to the web’ option.

If you want to read more about the available options for customing your imported feed, check out the documentation available at: https://support.google.com/docs/answer/3093337?hl=en