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

reminderbot.R

# Please note that this script is not fully tested. This is a genericised 
# version of two working versions, where I've added and removed bits and 
# pieces along the way without testing them. Please let me know if there 
# are errors or things missing, because this could easily have happened.


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


# Setup and Settings

reminderbot_email <- # INSERT 

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

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


# URLs for signoff: I suggest using shortened URLs for aesthetic reasons in the emails

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

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


# Sheet names which reminderBot must access: must be the same names as on Google Drive: 
# can check accessible sheet names with gs_ls()

ansheetname <- ANNOUNCED_DATES_SHEETNAME  # INSERT
calsheetname <- CALENDAR_SHEETNAME  # INSERT
mlsheetname <- MAILING_LIST_SHEETNAME  # INSERT


# Already Announced List - this is a list of the dates which have already been announced 
# so that the same date is not announced more than once.  Note that the sheet needs at least 
# the header and one entry before adding to it will work.

ansheet <- gs_title(ansheetname)
an <- gs_read(ansheet) %>%
  mutate(Date = as.Date(Date))


# Calendar - reads from the calendar and examines the upcoming dates which have not been announced

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)


# Mailing List - reads the mailing list, and updates it according to the last time everyone
# completed the form.  In this way, it allows people to remove themselves from the list.

mlsheet <- gs_title(mlsheetname)
ml <- gs_read(mlsheet)
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)


# Summary

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

type <- cal$Type[1]


# For our situation, JC meetings were open to our and other groups, while other meetings were only for 
# members of our group.  Hence this, the group question in the form, and the ifelse below for everyone_emails.

isjc <- ifelse(type=='JC', 1, 0)


# This finds the presenter's email from the mailing list form responses.  Fuzzy matching in case there are issues,
# but the best way to get around this is to keep all the presenters' names in the names column of the sheet anc 
# to copy-paste them into the presenter dates.

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)


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

nextArticle <- cal$Title[1]
signoff <- glue('\n\nKind Regards,\nReminder Bot\n\n',
                  'Calendar: {calendarurl}\n',
                  'Articles or slides upload: {articlefolder}\n\n',
                  'Add or remove yourself from the mailing list: {mailinglisturl}')



# Sending the emails

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
  
  if(mail_admin_too) { nextPresenter_email <- paste(nextPresenter_email, admin_email, sep = '; ') }
  
  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)
    
  }
  
  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 article, 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)
    
  }
  
  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)
  }
  
}

Colaborar

Colaborar

Prosgu