How to make a free BirthdayBot using Slack and Google Sheets

This tutorial walks through how to set up a Google Sheet with birthday data so that a Slack app automatically posts a message on someone’s birthday. To do this, you’ll need:

1. Some experience with JavaScript
2. Admin access to your Slack space (note: just Admin is sufficient; Owner isn’t necessary)
3. Admin/Edit access to the Google Sheet
4. Time to read through documentation when required, and the ability to debug if necessary

This isn’t a copypasta-friendly tutorial. You’ll need to read through, understand the logic, write your own code, and make your own adjustments when necessary.

This information is correct as of September 2022. Let me know if you hit any bumps!

Thank you to William S on the Slack forum for giving me pointers on this topic :)

Background: I am a fan of automation and an equally avid fan of any excuse to eat birthday cake. I wanted a reminder for when it’s people’s birthdays but the only option in the Slack app marketplace for a BirthdayBot is sadly $$$, so I looked at DIY options. I wanted to keep the process as transparent and easily-managed as possible — there’s nothing worse than something only being understood or maintainable by one person, who might then leave (aka a low bus factor… bad idea).

I reached out to the Slack community forum with my initial ideas, and they were responsive and helpful (thanks, y’all!). Basically, there are three options:

1. Low/no code. Use a service like Zapier or Autocode and simply click the pieces together.
2. Slack app plus Google Sheets script. Make use of a straightforward custom Slack app, incoming webhooks, and Sheets’ automation capabilities.
3. Running a lambda on AWS using Bolt. The most technical option, which gives you the most customisation. Bolt is a framework for JS, Java, and Python for building Slack apps.

Out of curiosity, I did try Zapier, but because the automation is “multi-step” this goes beyond the limits of their free plan. I ended up building out #2 for maintenance ease. Though I could find some tutorials for creating a Slack app, and others for Google App Scripts (see “Resources” at the end), I couldn’t find a single one that linked it all together specifically with the birthday logic I needed. If you’re trying to make a BirthdayBot, I hope this helps!

What the process will do, end-to-end:

1. A trigger will run every day to run a script on your spreadsheet
2. The script will check today’s date (month & day) against all listed birthdays
3. If it finds any matches, it will ping your custom Slack app with a payload using an incoming webhook
4. Inside the payload is a message. Your Slack app will post that message
5. Someone will be wished a very happy birthday!

Process you need to follow to build it out:

1. Create the spreadsheet
2. Create the Slack app and bot
3. Write the spreadsheet script
4. Set the script trigger

Let’s go through each step in detail.

1. Create the spreadsheet

Create a new Google Sheets spreadsheet with two columns — “Name” and “Birthday (mm/dd)”. Format them as plain text. Fill them in with your data, and include a “Test Test” and today’s date for testing purposes.

2. Create the Slack app and bot

Head to https://api.slack.com/apps and click Create New App → From scratch . Complete the fields.

Now, we’ll set up the incoming webhook URL. This URL means external services (in this case, Google App Scripts) can send information to your Slack app and post to a channel you specifiy.

1. Go to Incoming Webhooks on the left nav
2. Toggle the webhook to On
3. Scroll down and click Add new webhook to channel
4. Select your channel (it might be worth setting up a #tech-sandbox channel or similar!)

Keep this tab open — you’ll want the webhook URL in the next step.

3. Write the spreadsheet script

In your Sheet, go to Extensions → App Scripts. I hadn’t even heard of Google App Scripts before now and it blew my tiny mind. Very powerful, and (at least by me) criminally underused.

Take a look at the left nav:

Left nav for Google App Scripts

You’ll only be using Editor (the code editor to write the scripts, in this step) and Triggers (in step 4).

I’m posting a screenshot instead of a code snippet here for two reasons: one, Medium mutilates the formatting, but two I’d prefer you to read through the logic than copypasta and have to spend a while debugging in the dark. Once you’ve read through and it makes sense, you’ll be able to write yours much more quickly and easily.

Big thank you to SpreadsheetDev for the logic help at this point — I’ve not used spreadsheets for much before and your post was super helpful conceptually. Thank you.

Helpful tip: the params in .getRange() are 4 integers in this order.

Once you’ve read through the above, and written out your version (adding or omitting anything you need from the example): test it! Use the Run button in the menu bar to try it out:

With a bit of fiddling and learning, you should be able to get your Slack app auto-posting every time you hit Run, and then you can fix any formatting. If you get stuck, use Scripts’ equivalent to console.log: Logger.log(param to log out) (see the docs on that here).

Make sure it all looks good before you move onto setting up the trigger — that’s just the final admin. This step, where you tweak the payload, make sure your interpolation is working, get to grips with how everything works — this is where all the learning happens. Enjoy it! Congratulate yourself every time something works out.

Note: messages have to be in JSON format for Slack to be able to parse them. In my example, I’ve used JSON.stringify(payload)but you can also write out the JSON — the Slack docs on this topic are really great.

Once you’re successfully receiving the message you’re expecting, remove the test data from the spreadsheet and move onto step 4.

4. Set the script trigger

On the Triggers left nav, add a new trigger. Pick Day timer and a time slot that suits you:

Click Save, and that’s it!

In our company spreadsheet, I’ve also added a column for “Work anniversaries” and created a separate .gs file for that logic. Let me know if you use this setup for anything else!

Please note, I haven’t used Slack’s concepts of Blocks in this tutorial as the message is so simple — just one line. It’s certainly something to look into if you want to build a more extravagant bot message and the Slack docs are great.

Useful resources:

• Article: How to send Slack alerts from Google Sheets/Google App Scripts
• Article: How to make a Slackbot using Google Sheets
• Article: Birthday reminders using Google Sheets