Lunchbot: How we combined Google Sheets with Slack to simplify team lunches

We have a team lunch every Friday at Geckoboard. This usually means ordering in a takeaway from somewhere local, as well as some serious overeating.

As we grew in size, getting everyone to place their order in time was becoming a logistical nightmare.

There’d always be at least one person each week who would miss the link in Slack or just forget, and chasing people up was a resource intensive annoyance for Adele, our feeder (and Office Manager). We don’t want people to miss out!

Always on the lookout for a problem to solve, Jon, our QA Engineer, stepped in to save the day.

Here’s how he built Lunchbot, our Slack + Google Spreadsheets hassling integration.

Jon’s story

I originally started this project to teach myself Go but, after a lunchtime chat with Luis (our VP Customer Success), I realised it was going to be much easier to build using Google Scripts. By this point I really wanted to solve our ordering problem — even if that did mean getting my hands dirty with Javascript, and not learning any Go!

How it works

The Spreadsheet

We were already using Google Sheets to capture our lunch orders, but the original spreadsheet didn’t have much structure, with team members simply copying and pasting in their orders against their name. The first thing I did was put together a new template:

Deadline, Menu, Notes and Restaurant are the cells Adele updates each week. I also added 3 other columns beyond the usual name and order information:

• Completed
• Attending
• Slack - the person’s Slack username, to power the reminders

Attending

The attending column allows people to mark whether they’re in for lunch that day. Previously this was an entirely manual process for Adele, who would have to trawl through Google Calendar and work out who would be in that day.

Now the onus is placed on the individual. If you receive a reminder and you’re not planning on being in the office on Friday you just go in and set Attending to false. People who aren’t usually in the office on Friday are defaulted to false.

To make this as easy as possible I made it a ‘data validated’ column with boolean values and a handy description:

Completed

The completed column is what the Google Script checks every 2 hours to see whether a user needs to be reminded about ordering lunch. We use a function to keep it automatically up to date: lunch is seen as completed either when you update Attending to False or you write anything in one of the Starters, Mains, or Extras columns:

Total Heads

The spreadsheet also counts the total number of people attending with the following formula:

Opening lunch

For Adele to let everyone know orders are open for the week, I added a big green “Open Lunch” button. Pressing it would automatically post a message on our main company-wide Slack channel, notifying all the team members in the London office.

To avoid accidents, I ‘hid’ the button by throwing in some extra columns to push the button far off the screen to the right. You’d only find it by scrolling to it.

The button links to the following function which prepares and sends a Slack message to our #main channel, and sets up some triggers; one for the reminder and one to notify Adele when everyone has placed their order.

This is the shared triggerSlackRequest function which handles all the slack messaging:

Pestering users to fill out their orders!

The main part of the application is the pestering functionality! When the order is opened the script creates a trigger (basically just a scheduled job) which fires every 2 hours.

The script checks for anyone who’s not filled in their order, and sends them a message via slackbot with a direct link to that week’s spreadsheet. Best of all, this message can’t be silenced (evil smile).

To avoid spamming people overnight the script only runs during working hours — but changing this could be fun to experiment with!

Orders Complete

Given how much Adele can be juggling at any one time, I thought it would be helpful for the app to let her know as soon as everyone had completed their order. That way it would give her a bit more flexibility over when to make the order.

The second trigger we created when opening a lunch order does this. Every 5 minutes the script checks to see if everyone has completed their order.

If it detects all orders are complete, and it’s within office hours, it sends a Slack message to Adele to let her know that the lunch order is complete.

To avoid spamming Adele, the script updates a specific unused cell so it knows not to do it again.

Closing Lunch

Occasionally people would make last minute adjustments to their choices, not knowing if the order had already gone through. I know that sometimes I’d try to add a couple of extras at the last minute!

To make it clearer that ordering is over, and so that everyone’s tummies would know what to expect, we added a Close button to the spreadsheet. This also meant I could stop the script from running continuously in the the background for all time — not that Google would care.

Clicking ‘Close Lunch’ triggers the following function, which sends a message to Slack informing the team that lunch is closed, and deletes all the triggers for the current project:

This function greys out the rows of anyone not attending lunch, to make the order a bit easier to read:

Trying Lunchbot yourself

The script has completely solved our lunch ordering forgetfulness! If you’d like to use it yourself:

Copy this spreadsheet into your own personal Google Drive, then

1. Go to Tools | Script Editor, where you should see the script

2. Add your own Slack tokenized url so it can send messages

var slackWebhook = "YOUR PERSONAL SLACK TOKENIZED URL";

3. Add the Slack username of the person who should be notified when lunch orders are complete

var completedOrdersUser = "@username";

4. Add the Slack names for your team members on the spreadsheet itself in column B

5. Now you are ready to give it a spin, enjoy your lunch!