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.

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(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)
  
}

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(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)
  }
  
}