Skip to content

Syndicated Content: A Guide to Parsing RSS Feeds Using Google Sheets

In this quick tutorial we will parse a RSS feed (or several?) using Google Sheets and App Script

Let’s get started!

Step 1: Prepare Your Google Sheet

First, you need to create a Google Sheet where you will input your RSS feed URLs and where the script will write the parsed data.

  1. Open Google Sheets (https://sheets.google.com)
  2. Click on “Blank” to create a new spreadsheet.
  3. Name your sheet appropriately, like “TrendsFeed”.
  4. In cell A1, type “Feed URLs”, and in B1 and C1, type “Title” and “Description” respectively.

Step 2: Input your RSS feed URLs

In column A below the “Feed URLs” header, add the URLs of the RSS feeds that you want to parse. Each URL should be in its own cell, starting from A2.

Step 3: Add the Script

Now, let’s add the script that will parse the RSS feeds:

  1. Click on “Extensions” in the menu, then “Apps Script.”
  2. Delete any code in the script editor and replace it with the code provided.
  3. Name your project. This can be anything you like, such as “RSS Parser.”
  4. Click on the disk icon or select “File > Save” to save the script.
function parseFeeds() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var urls = sheet.getRange(2, 1, sheet.getLastRow()).getValues();
  
  // Clear existing content
  sheet.getRange('B2:C' + sheet.getLastRow()).clearContent();

  var maxItemsPerFeed = 5;  // Limit the number of items fetched from each feed

  var data = [];

  for (var i = 0; i < urls.length; i++) {
    var url = urls[i][0];

    if (url && url.startsWith('http')) {
      try {
        var xml = UrlFetchApp.fetch(url).getContentText();
        var document = XmlService.parse(xml);
        var root = document.getRootElement();

        if (root.getName() == 'rss') {
          // Parse as RSS
          data = data.concat(parseRSS(root, maxItemsPerFeed));
        } else if (root.getName() == 'feed') {
          // Parse as Atom
          data = data.concat(parseAtom(root, maxItemsPerFeed));
        }
      } catch (error) {
        console.error('Failed to parse feed at ' + url + ': ' + error.message);
      }
    }
  }
  // Write the data to the sheet, starting at row 2, column 2 (B2)
  sheet.getRange(2, 2, data.length, data[0].length).setValues(data);
}
function parseRSS(rss, maxItems) {
  var channel = rss.getChild('channel');
  var items = channel.getChildren('item');

  var data = [];

  // Loop through each item and extract data, up to maxItems
  for (var i = 0; i < Math.min(items.length, maxItems); i++) {
    var title = items[i].getChildText('title');
    var description = items[i].getChildText('description');

    data.push([title, description]);
  }
  return data;
}
function parseAtom(feed, maxItems) {
  var entries = feed.getChildren('entry');

  var data = [];

  // Loop through each entry and extract data, up to maxItems
  for (var i = 0; i < Math.min(entries.length, maxItems); i++) {
    var title = entries[i].getChildText('title');
    var content = entries[i].getChildText('content');

    data.push([title, content]);
  }
  return data;
}

Step 4: Run the Script

Finally, let’s run the script:

  1. To run the script, you will have to authorize it first. Click on the “Select function” drop-down and choose the parseFeeds function.
  2. Click on the play button (▶️) next to the dropdown to run the function.
  3. The first time you run the script, it will ask for permissions to access your Google Sheets and connect to an external service (to fetch the RSS feeds). Click on “Review Permissions,” choose your account, and then click on “Allow.”
  4. Once you’ve done this, run the script again by clicking the play button.

That’s it! The script should now fetch the latest items from each of your RSS feeds and insert them into your Google Sheet. You can refresh the data anytime by running the script again.

If you end up having trouble with step 3 and 4 I have provided a sheet that is already setup with the script. You should really try to do it yourself, it’s just copy paste and save! Either way, in case you get stuck you can make a copy of the Demo Sheet Here, add your RSS feeds and try step 4 again.

Notes:

  1. The script is set to fetch a maximum of 5 items from each feed. If you want more (careful!) or less, you can change the maxItemsPerFeed variable in the script to your desired number.
  2. If you add, remove, or change RSS feed URLs, just run the script again to update the data in the sheet.

There you have it! You’ve successfully parsed your sourced RSS feeds into Google Sheets. This structured data now gives you an accessible view of trending topics. As we’ve demonstrated, even without prior scripting knowledge,

Google Apps Script provides an approachable way to automate data extraction from RSS feeds.