Tiny Elf is a Slack Bot driven by a Google Spreadsheet. It rosters team members as hosts to a recurring meeting on a round-robin basis after checking their availability on their Google calendars. When rostered, Tiny Elf sends out reminders to a common Slack channel tagging the rostered team member. It rotates team members based on the last time they were rostered and keeps track of it on the Google spreadsheet.

Setup in five steps

Your Tiny Elf bot can be set up in five easy steps.

1. Make your copy of the Tiny Elf spreadsheet
2. Change the team data
3. Change the settings
4. Authorise the script
5. Set the trigger

You are done 🎉

Now, wait for the next team member to be rostered 🔔

Features

Tiny Elf comes with a few nifty features.

• The sheet owner can change settings directly from the settings sheet. No need to touch the Google Apps Script files.
• The bot can check if the event exists before sending out notifications. This helps with skipping notifications for meetings that are not held on a daily/weekly basis.
• When rostering team members, the bot checks if they are available; have accepted the event invitation; they are available and have accepted the invite. It can also skip the check.
• You can set and reset your automated trigger specifying when you want it to run.
• Allows you to send a notification up to 7 days ahead or on the date of the meeting.
• Allows you to specify custom messages to be sent via Slack with dynamic replacements for certain values.

Open-source

The Tiny Elf source code is available on GitHub with an MIT license. It can be extended and customised to suit your unique requirements. Bug fixes or enhancements that benefit others are welcome. Please submit your pull requests. 😊

The nitty gritty in the spreadsheet

Tiny Elf runs off a Spreadsheet which has team data and all the settings. The Tiny Elf spreadsheet is shared publicly and a copy can be made for your use.

The Team sheet

The Team sheet contains data that is spread across the following six columns.

• Name
• Email address
• Slack member ID
• Enabled
• Roster for days
• Last host date

The Settings sheet

The Settings sheet contains the following settings and each setting is explained in detail.

Calendar event details

• Event title
• Event start hour
• Event start minute
• Event end hour
• Event end minute
• Check event exists
• Roster team members when

Automated daily reminder trigger settings

• Trigger hour
• Trigger minute
• Trigger days before
• Trigger on days

Slack message settings

• Slack Webhook URL
• Team member notification summary
• Team member notification body
• Team member notification footer
• Busy notification summary
• Busy notification

The 'Instructions' and 'License and distribution' sheets

These two sheets contain enough details for an end-user to use Tiny Elf without looking at the source code.

Some real-life scenarios

Tiny Elf is already in use across three teams and here are some ways they use it. Note that each team runs their unique copy of the Tiny Elf Spreadsheet but uses the same code. The data and settings are unique to each scenario.

The settings for each scenario are mentioned here as examples/inspiration. Note that only significant changes are mentioned here for brevity.

Weekday meetings with a Friday special

Here is how the first team who uses Tiny Elf has configured it. The majority of their team attends meetings daily and some of them only on Fridays.

The setup

Roster for days - Mon, Tue, Wed, Thu, Fri is set for those who attend daily and Fri for those who attend only on Fridays.

Trigger days before is set as 0 since this notification goes out each weekday.

Trigger on days is set as Mon, Tue, Wed, Thu, Fri so that it runs every weekday.

Team member notification body is set as

Hi $[slackHandle] :wave:
You are rostered to host *Hello World* today. Here 
are <link to our wiki|instructions> for you to host it!
Give us a shout here if you can't make it.

Monday to Thursday meetings

The second team who uses Tiny Elf has configured it to run only from Monday to Thursday.

The setup

Roster for days is set as Mon, Tue, Wed, Thu for all team members.

Trigger days before is set as 0 since this notification goes out on the day the script is triggered.

Trigger on days is set as Mon, Tue, Wed, Thu so that it runs every weekday excluding Friday.

Team member notification body is set as

Hi $[slackHandle] :wave:
You are rostered to host *FED standup* today. You can lead with:
• What we did yesterday
• What we’re doing today
• Does anyone want to bring anything up
Here are some <link to our wiki|tips> for you to host it! 
Give us a shout here if you can't make it.

Note that you can use the Unicode character • (hex 0x2022) instead of markdown as the Slack documentation suggests.

Meet every other Friday - Notify on Wednesday

The third team has a meeting every other Friday and wanted the notification for the host to go out two days in advance.

The setup

Roster for days is set as Fri for all Team members.

Enabled is set as No for one team member as they were on extended leave. This prevented Tiny Elf from picking them as the host.

Check event exists is set as Yes. This checks to see if the event exists on the calendar before the script is run.

Trigger days before is set as 2 since this notification goes out on Wednesday.

Trigger on days is set as Wed so that it runs every Wednesday only. But as it checks for the calendar event, notifications are sent out only every other week.

Slack message with instructions on your intranet

The Slack notification can contain links to your intranet or company wiki. Use the Slack format of <https://intranet-address.com/page|text description of link> to include links in your notification.

Notify on a private channel

The notifications can be sent to a private Slack channel. The only requirement is that the person setting up the incoming webhook has access to that private channel. Once the webhook URL is configured, Tiny Elf works the same as it would on a public channel.

History and how Tiny Elf came to be

We have got a team of 15+ developers who meet every Monday to Thursday and this increases to 20+ developers every Friday. We wanted to give each team member a chance to host the meeting to take away a central authority and give each individual an opportunity to grow by chairing and running a meeting.

But this was easier said than done since our virtual meeting software (Zoom and Google Meet) didn't natively support rostering. So it became a manual task to keep track of who was rostered and who was next. Going alphabetically by name didn't help since some team members could not to attend all meetings due to other commitments or meetings at the same time.

Hmmm... What if I could automate this process and give the assigned host a heads-up?

Automation - version 1

As I was already maintaining the list of hosts in a Google spreadsheet, I wrote some JavaScript on the Google Script Platform to perform some automation. The code essentially checked each team member's Google calendar for any overlapping meetings and if there were none, it sent out a notification on a shared Slack channel asking them to be the host. It also recorded the last date they hosted the meeting so that it could rotate them after all the others have had a go at hosting the meeting. It also incorporated some smarts to assign certain team members only on a Friday.

Automation - version 1.1

This automation was working so well that a second team requested this bot to help them assign hosts from Monday to Thursday. That was easy enough, but I had to make changes to the source code as almost everything was hard coded. This included the meeting time, title and message templates.

Automation - version 1.2?

Tiny Elf got enough notice that a third team now requested the bot for their meeting. But that meeting was held every other Friday. Also, they wanted notifications to go out on a Wednesday.

Another copy? Another go at changing the source code?

TypeScript to the rescue

Luckily, I had started learning TypeScript and this was a project I was converting from vanilla JavaScript to TypeScript.

Next steps

I have already opened an issue for myself to automate integration testing as it does take time when I have to do it manually.

When reviewing the draft of this post, my wife asked if messages could be sent as an SMS or a WhatsApp message. Tiny Elf wouldn't be a Slack bot then - but it's something worth thinking about.

Resources

As I was learning TypeScript and grappling with Google Scripts, these resources were of immense help and I have borrowed some code/direction from them.

• How to send Slack alerts from Google Sheets / Google Apps Script by Rowan Barnes
• Google Apps & TypeScript Project Template by Ian Sanders
• TypeScript tips from Borislav Hadzhiev