In a few days, we will have our annual NSERC-CRSNG meeting for grant reviews. In a nutshell (the process will be the same as last year), we get an excel file that looks like a calendar, with about 45 slots of 20 minutes, from Monday 8 am till Friday 5 pm. This year, I wanted to create automatically notifications that could get directly into my agenda. And actually, that’s easy with calendar.

First, we can extract information for an excel file, or from a pdf document (which is a printed version of an excel file). First let us read the excel document

library("readxl")
loc = "/Users/ac/Downloads/NSERC.xlsx"
data_xls = read_excel(loc)

Then, I use the structure of the document: each column is a day, so I start on Monday, and then I go down, row by row. Each time I have something which looks like “RGPIN-2024-12345”, I create an ics file, with the reference name, and the appropriate time

library(stringr)
library(calendar)
library(lubridate)
ext_RGPIN = function(chr) str_extract_all(chr, "RGPIN-2024-[0-9]{4}|R[0-9]{1}")[[1]]
ext_time = function(chr)strsplit(as.character(chr)," - ")[[1]][1]
for(j in 2:6){
for(i in 1:nrow(data_xls)){
read_RGPIN = ext_RGPIN(data_xls[i,j])
if(!is.na(read_RGPIN[1])) {
dayhour = paste("2025-02-0",j," ",ext_time(data_xls[i,1]),sep="")
s <- lubridate::ymd_hm(dayhour,tz = "EST")
ic = ic_event(
start = s,
end = s+20*60 ,
summary = paste(read_RGPIN[1]," (",read_RGPIN[2],")",sep=""),
format = "%Y-%m-%d %H:%M")
ic_write(ic, paste("ic_NSERC",read_RGPIN[1],".ics",sep=""))
cat(read_RGPIN[1],"...",dayhour,"\n")
}}}

(to illustrate, I imported those in 2025). Finally, I can import all those notifications in my agenda.