Excel to Calendar Script
One of the motivations for writing a blog was to post scripts and write about them, much like Dr Drang.
Today, I have one such opportunity.
I’m recently starting a course and the admin gave the timetable in an excel spreadsheet. Each sheet represents a weekday. This is kind of uncomfortable to read and I assumed that I would have to add it to my calendar anyways at some point or other. Well, I’ve been learning AppleScript over the past few weeks, so what better way then to show off what I’ve learnt!
Here’s the code in its all glory:
set last_cell to ""
set start_range to ""
set end_range to ""
set d to ""
tell application "Numbers"
set hi to table 1 of active sheet of front document
set ho to cell range of hi
repeat with r in rows of ho
set row_num to address of r as number
if row_num > 2 and row_num mod 2 is 1 then
repeat with C in cells of r
set current_cell to formatted value of C as text
if name of column of C is "B" then
set d to word 1 of current_cell & space & word 2 of current_cell
if word 2 of current_cell is "Jan" then
set d to d & " 2022"
else
set d to d & " 2021"
end if
set d to AppleScript's date d
else if name of column of C > "B" and name of column of C ≤ "U" and current_cell is not "missing value" then
if last_cell is current_cell then
set end_range to name of column of C as text
else
if start_range is not "" then
set s_d to (value of cell 1 of column start_range of hi) as text
set e_d to (value of cell 1 of column end_range of hi) as text
if e_d is not "Date" and e_d is not "Week" then
set s_d to word 1 of s_d as number
if s_d is 12 or (s_d ≥ 1 and s_d ≤ 5) then
set s_d to date ((s_d as text) & ":00PM") of d
else if s_d ≥ 8 and s_d ≤ 11 then
set s_d to date ((s_d as text) & ":00AM") of d
end if
if (count of words of e_d) is 3 then
log e_d & " has 3 items"
set e_d to date (word 2 of e_d & ":00" & word 3 of e_d) of d
else if (count of words of e_d) is 4 then
log e_d & " has 3 items"
set e_d to date (word 3 of e_d & ":00PM") of d
else if (count of words of e_d) is 2 then
log e_d & " has 3 items"
set e_d to item 1 of word 2 of e_d as number
if e_d is 12 or (e_d ≥ 1 and e_d ≤ 5) then
set e_d to date ((e_d as text) & ":00PM") of d
else if e_d ≥ 8 and e_d ≤ 11 then
set e_d to date ((e_d as text) & ":00AM") of d
end if
end if
set summary to last_cell & space & s_d & "-" & e_d & " /School"
tell application "Calendar"
set dds to make new event at end of events of calendar "School" with properties {summary:last_cell, start date:s_d, end date:e_d}
end tell
end if
end if
set start_range to name of column of C as text
set end_range to start_range
end if
set last_cell to current_cell
end if
end repeat
end if
end repeat
end tell
I’m not going to lie, it’s an ugly piece of shit. I could have tidied it by writing functions, but I felt stubborn about writing everything in one module. This may have been my downfall.
One of the reasons why the code is so convoluted is because I had to account for a lot of random logic in the spreadsheet. The visual presentation may look nice, but it was very difficult to parse. Each calendar event was actually 2 cells merged together, so I had to ensure that the same event was added to my calendar twice. Additionally, I realised that the other sheets had a 8-6pm day compared to a 8-5pm day, so the code that checked the range of calendar events had to be modified (another weird thing: the calendar events were prefixed and suffixed with the date and calendar weeks? so checking those boundaries was something I had to check).
The hardest part was converting the data from 1 type to another. I ran into a syntax error because I tried to convert a string into a date object, but because I was working within a tell statement, I had to prepend the Applescript's keyword so that Applescript didn’t get confused with the date command supplied by Numbers.
One last hurdle was that I tried to take a shortcut by using Fantastical and its natural language parser so I didn’t have to do too much data wrangling. But the parser was very finicky so I had to feed everything into Calender anyways.
I might revisit this post and write more about my challenges, as well as create a more elegant solution in the future. I know I’ll have to look at the code again because the timetable will be updated again in June for the second half of the year. But I’m glad it’s working so far, and my calendar is looking very busy.