RSS feed to Google Sheets

In this post, we’ll explore how to use Feed Control and Google Sheets to monitor an RSS feed and automatically insert new items as rows in a spreadsheet.

We’ll first discuss Google Sheet’s IMPORTFEED function and then demonstrate how to use Feed Control with Google Apps Script to automatically add new items to your Google Sheet.

Viewing feeds with IMPORTFEED

Google Sheets offers an IMPORTFEED function to load a feed into your spreadsheet. This feature provides a dynamic snapshot of a feed, displaying the latest items (typically 10-20). While useful, it has limitations:

  1. It only shows the most recent items in the feed.
  2. You can’t delete item rows or edit cells without first copying the contents to break the link to the function.
FiveFilters.org feed loaded inside Google Sheets using the IMPORTFEED function

But what if you want to store each new item as a proper row in your sheet, allowing you to track updates over time?

Inserting items using Feed Control and Google Apps Script

Let’s walk through how to use Feed Control’s webhook functionality to automatically insert new rows into your Google Sheet when new RSS feed items are detected.

Note: Feed Control is a paid service, but we offer a 7-day free trial if you would like to test this process before committing.

Feed Control allows you to add feeds to your account, alerting you of new items via email, Slack, Discord, or webhooks. We’ll use webhooks in this guide to send new items to Google Sheets.

When setting up a webhook, you will need to provide Feed Control with a URL to the webhook handler. We’ll use Google Apps Script via Google Sheets to create a webhook handler tied to your sheet to achieve this.

Step-by-step guide

1. Create a new Google Sheet

Use this link or click Blank spreadsheet in Google Sheets.

2. Create an Apps Script for your sheet

  • Click Extensions > Apps Script from the menu.

Note: It’s important to create the script via your sheet’s Extensions menu because this creates a script that’s bound to your sheet, which is what the code in the next step expects.

3. Create the webhook handler for Feed Control

  • In the code editor, replace the empty function with the following code:
function doPost(e) {
  // Get spreadsheet connected to this script
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  
  // Add header row if this is an empty sheet
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(['Date', 'Title', 'Description', 'Author', 'URL', 'Content']);
  }
  
  // Parse the JSON payload from the webhook
  var jsonData = JSON.parse(e.postData.contents);
  
  // Extract the fields from the JSON data
  var date = jsonData.date || new Date().toISOString();
  var title = jsonData.title || '';
  var description = jsonData.description || '';
  var author = jsonData.author || '';
  var url = jsonData.url || '';
  var content = jsonData.content || '';
  
  // Append the data as a new row
  sheet.appendRow([date, title, description, author, url, content]);
  
  // Return a success response
  return ContentService.createTextOutput(JSON.stringify({ 'result': 'success' }))
    .setMimeType(ContentService.MimeType.JSON);
}

4. Deploy the webhook handler

  • Click on Deploy > New deployment
  • Choose Web app as the script type
  • Set the following options:
    • Description: Feed Control webhook handler
    • Execute as: Me
    • Who has access: Anyone

Note: Access to anyone does mean for your sheet, but for the webhook handler. It simply means that no additional authentication will be required to trigger the script. The URL to your handler will have a long unique ID. So unless you share it with someone else, it’s very unlikely anyone will be able to trigger the handler.

  • Click Deploy

Note: You may have to authorise access and navigate through a security warning. If you see such a message click Advanced and then Go to [project name] to continue.

5. Copy the Web App URL

  • Copy the long, unique URL generated for your webhook handler.

6. Add webhook integration in Feed Control

  • In Feed Control, go to Integrations > Webhook
  • Paste the URL you copied and click Save.

7. Enable integration on your feed

  • In Feed Control, select your desired feed (or add the feed first)
  • In the Alerts/Integrations tab, select your newly added webhook
  • Click Save to confirm

8. Test the setup

From now on the webhook will be triggered when new items are detected.

To test immediately, you can:

  • Delete the latest item in the feed from Feed Control (via the Feed Items tab)
  • Refresh the feed (via the Actions drop down)
  • Check your Google Sheet for a new row with the re-detected item

If everything is set up correctly, you should see a new row at the bottom of your sheet for the new feed item. It may take a minute for this to occur.

Updating the script

To modify the webhook handler script:

  1. Open your Google Sheet
  2. Click Extension > Apps Script
  3. Edit the script
  4. Click Deploy > Manage Deployments
  5. Click Edit (pencil icon)
  6. Under Version, choose New Version
  7. Click Deploy

With this setup, each new item from your RSS feed will be automatically added as a new row to your Google Sheet, allowing you to track and analyze feed updates over time.