Making a Reminder Bot for Automating Meeting Organisation using R and Google Sheets

a.k.a. How to get your boss to buy you a Raspberry Pi to play with

Posted by Granville Matheson on Saturday, September 29, 2018

TOC

TL;DR

Organising our journal club meetings took a lot of time. Here’s how I used R and my Raspberry Pi to automate away the boring parts of arranging these meetings. The code is provided in a GitHub gist here, but I’ll walk through it all in the post below, especially since it can’t just be run out of the box: it will need some modifications for each specific use case depending on requirements.

How it felt having the reminderbot handling all the boring stuff

How it felt having the reminderbot handling all the boring stuff

Background

I work in academia, and in academia there are journal clubs. For anyone unfamilar with the concept, these usually take the form of meetings focused around a particular article, which most attendees have hopefully read, and where one person, the presenter, who has read the article more deeply than everyone else will go through it with everyone. This meeting usually involves taking the article apart, finding its flaws, admiring the good stuff, explaining the parts that people struggled with, and everyone getting a little bit smarter and savvier in the process.

Almost every single journal club I have been a part of has had the same issues (and I presume these generalise to other meeting types too):

  1. Presenters needing to re-schedule (usually at the last minute) based on other commitments
  2. A (very) broken mailing list
  3. Presenters forgetting that it was their week until too late
  4. A lack of presenters

All of these problems quickly become time-consuming for whoever is organising the journal club, and they’re no fun to solve. Somehow, I’d managed to find myself involved in organising two journal clubs at the same time, and the date that I would be going on my paternity leave (yay Sweden!) was nearing. I was going to be gone for 6 months, and wanted to make sure that I wouldn’t need to arrange things while I was away, and that the journal clubs wouldn’t shrivel up and die while I was away. This is what led to the development of the reminderbot, to automate as much of the task as possible.

As with virtually all problems that can be solved with technology, some of this problem can’t be solved by technology - though at least some of it can, and that’s a start! Here I’ll go through how it was built so that you can adapt it and build a similar system for your own use cases. And I’ll go through some of the human aspects too that I believe contributed to our journal clubs working out so well.

Please let me know if you get something similar up and running yourself - I would be absolutely thrilled to hear that this was helpful for someone else out there in the void!

Initial setup

For starters, I strongly recommend starting a new Google account for your reminderbot. Everything here uses the Google ecosystem, and keeping it all in one place makes everything much easier.

Once you’ve started your new google account, you will then need to set it up so that it can send emails. We’ll be using the brilliant gmailr package. You’ll need to create a new project on the Google API: following the instructions on the gmailr GitHub page, and you’ll have it up in no time. Create a secret file as per the instructions and save it.

Now you should be ready to go on the Google side. Now you’ll need a few R packages. Install ’em if you don’t already have ’em.

library(tidyverse)
library(googlesheets)
library(lubridate)
library(gmailr)
library(stringdist)
library(glue)

Now just load the secret file, and specify the email address for the reminderbot.

reminderbot_email <- # INSERT 

gmailr::use_secret_file(SECRET_JSON_FILE) # INSERT
gmail_auth(scope = 'full', secret_file = SECRET_JSON_FILE) # INSERT

Finally, you will need to figure out some kind of daily automated running of the script. I use my Raspberry Pi, and cron, but there is also a Windows-based version of cron. Just make sure that it’s on a computer that doesn’t get turned off very often (hence a Rasperry Pi is perfect as it uses very little power, makes no noise, is fun to play with, and this is a great excuse to ask your boss to buy you one)

Solving the Issues

Issue 1: Rescheduling

When we first started the journal club, people often needed to change their week due to other commitments, or things coming up etc. This would mean that they would have to email me, and then I would have to ask around until I found someone to replace them, and then we’d all have to juggle around the schedule to fit them in somewhere else. It sucked.

Solution

We simply put the calendar on Google Sheets, gave everyone the ability to edit it, and told them that we were all responsible for the calendar. Anyone could reach an agreement with anyone else, and fix their position on the calendar. This functioned to de-centralise this task, and take most of the burden off the main organiser.

You can find an example of one such calendar here. The “Type” column represents the type of meeting: in our case, we wanted to distinguish between journal clubs (JCs) and Work in Progress (WiP) meetings, and to divide the mailing lists for these meetings (so that members of other research groups were only invited for JC meetings and not for WiP meetings).

The column on the right of “Names” is there for all the people who are on the roster who can be called up to present at the meetings. We have had the situation where guest students and PIs (bosses in academia lingo) didn’t want to present, but wanted to be able to attend. So the easiest solution was to keep a list of the people who would be presenting in the right corner. This should be maintained by the person with access to the mailing list responses, and should be copied from that form. Otherwise bad things can happen with typo’s (more on that later).

Issue 2: Broken Mailing Lists

I get shivers just thinking about this one. I would get an email telling me to add someone to the mailing list. I would do so and send out a mail, only for someone else to send a mail around responding an old email without the new person. So the new person would fall off the list repeatedly until, by the grace of the gods, they would finally stick. And then removing people from the list was just as hard for the same reasons.

Solution

I set up a Google Form for people to add or remove themselves from the mailing list. You can find an example here (for some reason, I don’t appear to be able to share the form with view and copy access without also providing edit access, so you’ll have to make your own based on this example). The motivation for having a “Group” question was that it allows us to have some meetings with only members of one or the other group (in the above example, JC vs WiP meetings), but otherwise it’s superfluous.

Once you’ve made your own form, if you click on responses, and then on “View responses in Sheets” (the green sheet button), it will automatically create a new sheet into which all the answers are deposited. You can find an example of that here. The trick is then to automate maintenance of the list.

Automating mailing list maintenance using the googlesheets package

This we can do using the amazing googlesheets package. This package can access all of the sheets within the signed-in Google account’s Drive. This is another reason why I recommend you start a special Google account for your reminderbot. Check that you can access the files using the gs_ls() function, which provides a list for the signed-in Google account.

The googlesheets package understands each sheet by its filename (not URL). So we can read in the mailing list form output as follows:

mlsheetname <- MAILING_LIST_SHEETNAME  # INSERT

mlsheet <- gs_title(mlsheetname)
ml <- gs_read(mlsheet)

and now ml should contain the mailing list sheet outputted from the form.

Now, we want that every time the script is run, that it reads through the names on the list, keeps only the last instance of each email address, removes the duplicates, and then removes all the people who do not want to be included on the list, and then updates the form. This we do as follows:

ml <- ml[!duplicated(ml$`Email Address`, fromLast = T),] %>%
  rename(YesNo = `Mailing List Membership`) %>%
  filter(YesNo == 'Yes')

gs_edit_cells(mlsheet, input=ml, anchor='A2', trim = T, col_names = F)

Right! So now we have an updated mailing list that anyone can add or remove themselves from at any time without contacting anyone else.

Issue 3: Forgetfulness

Now, all that we need to do is to remind people when it’s their week, and that they should choose their article. Then we need to announce to everyone else when they have chosen their article, and what the article will be.

Solution

Automated reminder and announcement emails!

Knowing when to stop

The most important thing here is to make sure that we don’t spam people too much: our bot needs to know when to stop, otherwise it will annoy everyone and we’ll be asked to turn it off. In the case of reminders for the presenter, this will be when the presenter has added their title to the calendar. When this takes place, the bot should announce the meeting to everyone on the mailing list. But in order that it doesn’t announce the same meeting twice, we need to keep track of which meetings have been announced, which we can do with another sheet. You can find an example of that here. Note that when you create it, you should fill in at least one date to help R understand that it’s a column header and then data. I recommend just making a copy of this form.

Then we can read that in as follows.

ansheetname <- ANNOUNCED_DATES_SHEETNAME  # INSERT

ansheet <- gs_title(ansheetname)
an <- gs_read(ansheet) %>%
  mutate(Date = as.Date(Date))  # Just to be sure

Next, our bot should read in the calendar. We filter the list, choosing only dates in the future, for which there is an assigned presenter (i.e. it won’t spam everyone about a meeting on Christmas day), and for dates which have not already been announced.

calsheetname <- CALENDAR_SHEETNAME  # INSERT

calsheet <- gs_title(calsheetname)
cal <- gs_read(calsheet) %>%
  select(Date, Type, Presenter, Title) %>%
  filter(Date > Sys.Date()) %>%
  filter(!is.na(Presenter)) %>%
  filter(!(Date %in% an$Date)) %>% 
  arrange(Date)

Summarising what the bot knows

We’re nearly ready to start thinking about sending some emails. But before that, it’s a good idea to collect everything our bot knows so that we can use these in our various action scenarios.

nextDate <- cal$Date[1]
days2next <- as.numeric( nextDate - Sys.Date() )
nextPresenter <- cal$Presenter[1]

type <- cal$Type[1]


# If you want a meeting type criterion too:
isjc <- ifelse(type=='JC', T, F)

Prepping information for the emails

In our emails, we’ll want to provide some information about links to the form, the calendar and the mailing list. In our group, we also So let’s prepare that too. I highly recommend using shortened URLs, since they are pretty ugly otherwise. The google URL shortener can be found here. Then we can prepare a sign off message, since this will be common to all of our emails.

mailinglisturl <- URL  # INSERT
calendarurl <- URL  # INSERT
articlefolder <- URL  # INSERT

signoff <- glue('\n\nKind Regards,\nreminderbot\n\n',
                  'Calendar: {calendarurl}\n',
                  'Articles or slides upload: {articlefolder}\n\n',
                  'Add or remove yourself from the mailing list: {mailinglisturl}')

By the way, we also created a folder on Drive where people could store their slides or articles for everyone else to be able to easily download them (i.e. articlefolder above). We put a few example folders in there (named by date, e.g. 2018-02-20) so that people would be able to see how they should save their own assigned dates. Though I guess another alternative would be to have reminderbot automatically create the folders).

Next, we need to know who the presenter will be. This will involve comparing the name on the calendar with the names on the mailing list. As I mentioned before, the organiser should be maintaining the list of people who want to present at journal club meetings on the right side of the calendar, and they should be copied across from the mailing list. But every time human beings are able to access and change the data manually, mistakes will inevitably happen. And this is where fuzzy matching comes in: we can define a certain number of typo’s that we are happy for our reminderbot to ignore when matching. Adjust this based on experience and how similar people’s names are in your group.

name_fuzziness <- 5   # this many characters may differ in the name matching

which_presenterEmail <- stringdist::amatch(nextPresenter, 
                                           ml$Name, 
                                           method = 'dl', 
                                           maxDist=name_fuzziness, 
                                           nomatch = NA)
nextPresenter_email <- ifelse(!is.na(which_presenterEmail), ml$`Email Address`[which_presenterEmail], NA)

Next, we have to choose which emails to send to for everyone. As we discussed before, we can make a selection based on group membership, as below.

everyone_emails <- ifelse(isjc, 
                          paste(ml$`Email Address`, collapse = '; '),
                          paste(ml$`Email Address`[ml$`Research Group`=='Case Lab'], collapse = '; '))   # Not to other groups if not JC

Then I also added the possibility to have some admins, who will receive all mails including the reminders. This is good at the start so that they can monitor that the reminderbot is working as intended. I have actually never turned them off, as it’s just nice to know that it’s working.

admin_email <- EMAILS # INSERT - can be more than 1 separated by semicolons
mail_admin_too <- T  # Should the admin also be CC'd?

Then we just need the title of the meeting, or for a journal club, the article name.

nextArticle <- cal$Title[1]

Sending the emails

At this point, we have all the information ready, and all the details summarised. We just have to make some special circumstances for sending out the various emails.

  • Our meetings were weekly, thus emails should only be sent if the meeting was 7 or fewer days away.
  • The reminderbot should be able to find a matching name in the mailing list. If not, it will need to email everyone and say that it doesn’t recognise the next name. Unless there is a guest speaker, in which case there should also be a title.
  • If the presenter has not filled in a title within 2 days of the final meeting, then the reminderbot would email everyone and tell them that it’s not having any luck contacting the presenter. This could be because the presenter is sick, or the matching is incorrect, or their email address is incorrect etc. There should be ample time for the presenter to fill in a title, but enough time before the final meeting to re-arrange if need be. Our meetings were on Thursdays and Fridays, so 2 days worked fine, but for meetings earlier in the week, you should probably tweak this (or make it two weekdays).
  • The reminderbot script is run on each weekday. This means that the presenter will have received 3 reminders before the reminderbot will give up and mail everyone.

The general structure then, is to start with an if statement, then to define the mail(s), attach the signoff, send, and then add a row to the announced dates sheet if it was sent to everyone.

If the name does not exist

This is a special case of an unrecognised presenter and no title, and means that everyone should be mailed immediately, and the reminderbot skips this week.

if(days2next <= 7 & is.na(nextPresenter_email) & is.na(nextArticle) ) {
  
  # i.e. Presenter's name doesn't match an email address and no title
  
  announcement_email <- mime(
    To = everyone_emails,
    From = reminderbot_email,
    Subject = "Research Meeting this week",
    body = glue('Dear All\n\n',
                  'The research meeting this week will be presented by {nextPresenter} on {nextDate}. ',
                  'The meeting type will be {type}. I do not however find a matching name on ',
                  'the email list. This can either mean the the schedule has been filled in incorrectly, ',
                  'or that {nextPresenter} has filled in a slightly different name in the Mailing List.\n\n',
                  'If there is nothing wrong with the schedule, then can {nextPresenter} please ',
                  'announce their presentation manually for this week and fill out the mailing list ',
                  'form again.{signoff}'))
  send_message(announcement_email)
  gs_add_row(ansheet,input = nextDate)
  
}

Otherwise, for guest presenters, there should already be a title.

if(days2next <= 7 & is.na(nextPresenter_email) & !is.na(nextArticle) ) {
  
  # i.e. Presenter's name doesn't match an email address but there is a title
  
  announcement_email <- mime(
    To = everyone_emails,
    From = reminderbot_email,
    Subject = "Research Meeting this week",
      body = paste0('Dear All\n\n',
                    'The research meeting this week will be presented by {nextPresenter} on {nextDate}. ',
                    'The meeting type will be {type}.\n\n',
                    'The topic will be {nextArticle}. {signoff}'))
  send_message(announcement_email)
  gs_add_row(ansheet,input = nextDate)
  
}
If the name does exist

This is the usual scenario, and we define an initial if statement to check for it, and then break it down for the other cases

if(days2next <= 7 & !is.na(nextPresenter_email)) {
  
   # i.e. Main if: Normal situation - coming in less than a week, and email exists
  
  # Add the admin addresses if they should see everything
  if(mail_admin_too) { nextPresenter_email <- paste(nextPresenter_email, admin_email, sep = '; ') }
  
  
  # Case 1
  if(is.na(nextArticle) & !is.na(nextPresenter)) {    
    
     # i.e. Presenter hasn't filled in article name
    
    reminder_email <- mime(
      To = nextPresenter_email,
      From = reminderbot_email,
      Subject = "Research Meeting Reminder: Your Presentation Title is Missing",
      body =  glue('Dear {nextPresenter}\n\n',
                    'This is a reminder that you will be presenting at the research meeting this week on {nextDate}',
                    '. The meeting type will be {type}.\n\n Please fill in which topic/article you will be presenting ',
                    'in the calendar.{signoff}'))
    send_message(reminder_email)
    
  }
  
  
  # Case 2
  if(is.na(nextArticle) & !is.na(nextPresenter) & days2next <= 2) {     
    
    # i.e. Presenter hasn't filled in article name and 2 days left
    
    reminder_email <- mime(
      To = nextPresenter_email,
      From = reminderbot_email,
      Subject = "Research Meeting Reminder: Your Presentation Title is Missing",
      body = glue('Dear {nextPresenter} \n\n',
                    'This is a reminder that you will be presenting at the research meeting this week on {nextDate}',
                    '. The meeting type will be {type}.\n\n', 'You have still not filled in which article you will be ',
                    'presenting in the calendar. Since it is coming up so soon, kindly mail the topic/article to everyone ',
                    'yourself.{signoff}'))
    send_message(reminder_email)
    
    announcement_email <- mime(
      To = everyone_emails,
      From = reminderbot_email,
      Subject = "Research Meeting This Week",
      body = glue('Dear All\n\n',
                    'The research meeting this week will be presented by {nextPresenter} on {nextDate}. ',
                    'The meeting type will be {type}. You can expect a mail soon with ',
                    'the title, or perhaps the presenter has not received their ',
                    'reminders, in which case someone should speak to them.{signoff}'))
    send_message(announcement_email)
    gs_add_row(ansheet,input = nextDate)
    
  }
  
  
  # Case 3
  if(!is.na(nextArticle) & !is.na(nextPresenter)) {     
    
    # i.e. Presenter has filled in article name and <7 days left
    
    announcement_email <- mime(
      To = everyone_emails,
      From = reminderbot_email,
      Subject = "Research Meeting this Week",
      body = paste0('Dear All\n\n',
                    'The research meeting this week will be presented by {nextPresenter} on {nextDate}. ',
                    'The meeting type will be {type}.\n\n',
                    'The topic will be {nextArticle}. {signoff}'))
    send_message(announcement_email)
    gs_add_row(ansheet,input = nextDate)
  }
  
}

I’ll go through these one by one.

Case 1: the email is sent only to the presenter (and to the admins if that’s selected). This informs them that they will be presenting and should fill in their title in the calendar.

Case 2: this is for when the title is still not filled in and it’s 2 days before the meeting. The presenter is mailed to be told that they need to actually manually email everyone to tell them the title for the meeting. And everyone else is emailed too to check that the presenter has actually been receiving the mails. Both emails are sent.

Case 3: the presenter has now filled in their title, but the meeting has not been publicly announced. This announces the meeting to everyone.

Issue 4: Participation

As with almost everything involving people, there is also a part of this problem that can’t be solved by any tech. People need to made to feel comfortable contributing, and to feel comfortable not knowing all the answers. While this post is mostly to demonstrate how I automated the organisational aspect, I figured I also may as well share all the tips on the people side too from our experience. These guidelines are specifically for journal clubs though, so if you were just here for making a tool for general meetings, then I don’t think this section would be helpful for you.

Solution

  • For starters, we had to be a little bit hard on participation: it was always difficult to get people to volunteer to present at the start. Everyone is always busier than they would like to be, and people seldom came forward. This was solved by having a list of all the people who were willing to present at some point, and just periodically pasting all the names in and rearranging them according to when people expected to be able to be able to present. This meant having to set aside 10 minutes at the start of a meeting for admin once every few months whenever we were nearing the end of the list, where we would just copy the names list over again, and rearrange as necessary. This meant that everyone gets an equal number of times to present, and if a few people are particularly keen, then they can jump in in case someone is ill, or doesn’t manage to swap dates with anyone. This was hugely helpful in getting over any initial unwillingness to volunteer.

  • Foster an atmosphere of mutual learning, and encourage people to choose things that they would like to learn more about about, with the explicit goal of all coming together to understand it better. When we were all helping each other to really understand the complexities, and to get into the strengths and weaknesses, then we were in it together. Additionally, by having a group of people with different backgrounds, we could all help each other through the parts we struggled most with.

  • Meet before lunch. This was purely accidental based on when people were available, but it meant that discussions, arguments, questions etc. could continue over lunch, and would in turn grow to be more social and less meeting-y.

  • This one is a bit obvious, but the one journal club which was “opt-in for a journal club for articles about this topic” was much more successful than the journal club which was “you should be part of this journal club because you are a PhD within this department”.

Automating the process

I set the reminderbot script to run on my raspberry pi, using cron, running at a time one hour after the meeting finished (i.e. so that the 7 day alert is sent after the previous meeting ends). I simply set the script up with the gmailr JSON secret file, and then made a bash execution script. The bash script looked as follows:

#!/bin/bash
cd home/pi/R/reminderBot/
Rscript reminderbot.R

Then I could edit the cron tasks using the following in the terminal

sudo crontab -e

And there are loads of instructions for how to do that out on the interwebs.

If you’re looking to get a Raspberry Pi for this purpose, I got the “essentials kit” from Pimoroni, which was pretty straightforward to set up, even though I am certainly no Linux expert. I did have a little bit of a hard time with downloading the R packages and memory, but I solved this by downloading the packages from outside of R directly from the Debian repositories. I can’t remember which guides I used, but I could find this, as well as this guide about using R on an RPi on a quick Google of the topic.

Wrap up

I hope this code-through was helpful! The reminderbot has become a beloved feature at work, and when it’s been down it’s been quite nice seeing everything descend into chaos (not the chaos itself that is, but to see that reminderbot was actually performing an important function!), and I hope that this is useful for someone (anyone) out there. Please get in touch with success stories, or if you have any issues, or advice or suggestions.

Please let me know if you get your own reminderbot working - I’d be thrilled to hear of any success stories!

Please let me know if you get your own reminderbot working - I’d be thrilled to hear of any success stories!

Note

Because of the fact that it all needs to be customised to your particular application, I can’t guarantee that my example will all work flawlessly, and I could easily have missed things along the way, since this was a generic version I made based on two different working versions for two slightly different journal clubs to which I added bits and pieces along the way without testing, so there could very well be errors. Please let me know if you spot anything missing or incorrect!

Also, note that I moved things around for the code-through to make it more didactic. The code in the script is ordered differently so that it’s easier to modify to get it to work correctly.


comments powered by Disqus