A Birthday Reminders App

Or how I discovered Google App Scripts

Before I started at Alpha Camp, one of the problems I had was collaborating with a relatively large, non-technical team that was in charge of planning birthday celebrations. There were great existing apps (with slightly different scopes) available, like Slack, Trello, GCal, etc, but none that had a zero learning curve.

My thought process at the time

The one app that everyone in the team had was Telegram. It’s basically Whatsapp, but not for dinosaurs (just kidding, it just seems like the younger crowd prefers Telegram. If you’re not familiar with it, go download it now, you’ll instantly appear 10 years younger.)

So the first course of action was to trawl the Telegram Bot store for a suitable bot that would allow me to 1) Schedule reminders easily, 2) Send reminders to a group. I didn’t find anything then, except for a couple of bots that worked for sending reminders to myself.

My solution

Ultimately, I used this guide to set up the messaging system. My bot would pull information from Google Calendar, and send reminders with specific text (and names).

How to manage your Telegram channel with your unique bot

The only remaining work to do was to add the birthdays to Google Calendar. Now, we had about 30 names to add, and we wanted to add reminders 7 days and 14 days before as well, so that would have been 90 manual entries. I chose to upload a CSV instead. At this point, I couldn’t code, but I was pretty good with Excel, so I did the heavy lifting with the concatenation and calculations here!

Import events to Google Calendar - Calendar Help

So this has been working for more than a year now. My main issue with this solution is that it is really difficult to replicate for someone who is less technical than I am: it requires creation of a bot, manipulation of a CSV file, uploading to GCal and carefully editing the IFTTT recipe. And I knew this was important because 5 different people had asked me how they could use my “Birthday Bot” for their own cell groups.

Fast Forward to Now

Recently, I spent some time reconsidering this problem in the face of new knowledge that I’d picked up at Alpha Camp. My first thought was to build a Rails app that accepts a CSV file, parses it, and sends POST requests to the Telegram API. But that seemed like complete overkill.

My second thought (which is the one I’m presently using) was to use a Google Script to directly send a JSON POST to the Telegram API. The cool part is that you can get the Google Script to run automatically on a daily basis.

Back story: A few weeks ago, I deployed an app on Heroku (the free tier), and realized first hand the annoyance of Heroku dozing, so I found a solution online that used a Google Sheet and an associated Google Script to GET the app periodically, preventing the app from dozing. That’s how I discovered Google App Script.

Basically, the user:

1. Adds my Telegram bot to their Telegram channel as an admin
2. Pastes in 3 columns (Name, Birthdate, Person in-charge) on the Birthday Tracker
3. Pastes in the Telegram channel handle
4. The script handles the rest!

The script itself

I won’t paste it here because the bot token is there! But basically GAS is built on Javascript, so I simply:

1. Collected array of dates from spreadsheet
2. Collected array of messages from spreadsheet
3. Compared each date to current date (gotcha here was that date objects are iffy in GAS. Just use String(date).substr(0,10) or something like that to compare the dates.
4. Sent JSON message if date === current date

Some screenshots:

Test data

The result

Possible future improvements

I might still try to do the Rails app some time in the future. I don’t like the fact that the user still has to manually add the bot to their channel, and that I can’t easily share the spreadsheet with the script attached to it (the daily scheduling isn’t automatically set when the sheet is copied).