Skip to content

Instantly share code, notes, and snippets.

@mathesong mathesong/reminderbot.R
Last active Oct 1, 2018

Embed
What would you like to do?
Making a Reminder Bot for Automating Meeting Organisation with R and Google Sheets
# 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)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.