RSS Notifications in Google Chat Rooms via AppScript & Google Sheets

Update September 27th, 2020: Added support for ATOM feeds as well as RSS (the structure is different). The github repo is updated, and all it requires is a new column in the google sheet.

Not too much to this process…i just wanted to see if i could get RSS notifications from various sources into Google Chat without too much overhead. I assume at some point there will be a built-in bot for this provided by Google, but for now, all it takes is a bit of AppScript and about 5 minutes to set up.

Before we get started, note that by default, the first time this is run, it will send notifications for ALL the items in an RSS feed … because every item will be seen as happening “after” the script’s last run-time. Just be aware of that before you blow up a room ….or if you want to configure a default “last run” date in the appscript, you could do that too i guess :-)

What this requires…

— Google Chat Room(s)

— URL(s) for some RSS Feeds

— URL(s) for icons for the feeds

— AppScript code (link to repo)

— Google Sheet (template to copy)

What it does…

— Checks RSS Feeds on the schedule you define

— Posts a “card” notification to the Chat Room for the feed

— Card links to the full notification on the source website

The Process…

First, create a google sheet from this template, or set one up for yourself. You’ll need it to contain columns for the RSS feed’s Name, URL, Type (RSS or ATOM), and an image URL to use as an icon inside the card notifications.

You’ll also have a “status” column so you can enable/disable individual feeds if needed.

Next, go to one of your Google Chat rooms to create a webhook URL. This is needed in the google sheet so you can tie each RSS feed to a room. You can of course send all notifications into a single room, but the script allows to send to many.

To create a webhook URL for your room, click on your room name at the top of the chat window…and click “Manage webhooks”

Then give your webhook a name, and to make it look even better, paste an image URL.

Then on the next screen after saving it, you can click on the copy icon beside the webhook — then paste it into the Google Sheet in the webhook_url column.

Now you have the Google Sheet, and the Webhook setup, the last thing is to add the appscript code and connect it all together. Make sure you have at least one feed set as “active” in the sheet first.

Creating the appscript project & code

Copy the code from the repo here:

https://github.com/usaussie/appscript-chat-rss/blob/master/code.gs

Next, go to script.google.com and create a new project.

Paste the code from the repo over the top of everything in the default “Code.gs” file. You can rename the file to something else if you want — i renamed my copy to “RssToChatCards.gs” but it’s really up to you.

Now you can update the variables at the top of the script to point to your version of the google sheet, and the tab name (should be “feed_data” unless you changed it from the default).

Time to test!

The first time you run the script, you’ll be asked to accept permissions to access your google sheet (needed for reading the feed info in), and then also fetch/call external URLs (sending the notification card to the webhook).

Run the “fetch_all_feeds” function from the menu bar, which looks like this in the new UI …

or this in the old/current UI …

The sequence of prompts for permission will look something like this…although when running with my organizational G Suite account i didn’t get the warning….whereas with a personal gmail.com account, i was warned about the app being untrusted/unverified.

Then it will push any notifications to the configured chat room as cards that look something like this:

Automation…

Once you’ve run it once, you can configure it to run automatically on a schedule. In the new UI you click on “Triggers” on the left. In the old UI, you click on the clock icon to go to the triggers page. Either way, you just need to set the “fetch_all_feeds” function on whatever schedule you want. It’s been tested with an “every minute” schedule without issue, but for a lot of use cases, every 5 minutes is probably good enough.

Concluding…

That’s pretty much it. I’ll probably make some small enhancements on the card detail, like adding in a truncated copy of the “description” field from the feed as another section, but that requires a little more work to format line-breaks and html correctly (and could easily break things based on how easy it is for RSS feeds to have weird stuff in them).

Update September 27th, 2020: Added support for ATOM feeds as well as RSS (the structure is different). The github repo is updated, and all it requires is a new column in the google sheet.