For those interested, here’s an AppleScript script (based upon an example at https://iworkautomation.com/numbers/table-edit.html) for parsing the selected rows of the Numbers table and adding events to Fantastical:
tell application "Numbers"
activate
try
if not (exists document 1) then error number 1000
tell document 1
try
tell active sheet
set the selectedTable to ¬
(the first table whose class of selection range is range)
end tell
on error
error number 1001
end try
tell selectedTable
set theseRows to the rows of selection range
repeat with i from 1 to the count of theseRows
set cellValues to the formatted value of every cell of item i of theseRows
set calendarString to last item of cellValues
tell application "Fantastical 2"
parse sentence calendarString with add immediately
end tell
end repeat
end tell
end tell
on error errorMessage number errorNumber
if errorNumber is 1000 then
set alertString to "MISSING RESOURCE"
set errorMessage to "Please create or open a document before running this script."
else if errorNumber is 1001 then
set alertString to "SELECTION ERROR"
set errorMessage to "Please select a table before running this script."
else
set alertString to "EXECUTION ERROR"
end if
display alert alertString message errorMessage buttons {"Cancel"}
error number -128
end try
end tell
Enjoyed the first episode. I’ve never got into workflow before but I’m now inspired to give it a try and have built one that automates dialling into conference calls. Pretty happy with it! I’ll share it in the iOS section.
I read the article, but have also read the privacy statement.
In the monitization they promise “extensive analytics” and in the statement thay indicate they can share all your data with 3rd parties, without your explicit consent. So unfortunatey for me IFTTT is a non-starter.
I don`t use Fantastical on my Mac, so I adapted the AppleScript @Sal made to create the multiple events from the Numbers sheet directly in stock Calendar app. The underlying Numbers sheet from @RosemaryOrchard had to be adapted slightly, you can find it here (Dropbox link).
To use it, just fill in or copy your events in the Numbers sheet. The event end date is calculated, just fill in duration (in minutes). Also the event alarm needs to be entered in minutes. Once you’re done, select the rows you want to make into events and run the script.
I learned a lot while writing and debugging the script, thanks a lot, Sal, for the guidance your script gave me.
tell application "Numbers"
activate
try
if not (exists document 1) then error number 1000
tell document 1
try
tell active sheet
set the selectedTable to ¬
(the first table whose class of selection range is range)
end tell
on error
error number 1001
end try
tell selectedTable
set theseRows to the rows of selection range
repeat with i from 1 to the count of theseRows
set cellValues to the value of every cell of item i of theseRows
set theEventName to first item of cellValues
set theStartDate to third item of cellValues
set theEndDate to fifth item of cellValues
set theAlarm to seventh item of cellValues
set theNotes to sixth item of cellValues
set theLocation to second item of cellValues
set theCalName to eighth item of cellValues
tell application "Calendar"
tell calendar theCalName
set theEvent to make new event with properties {summary:theEventName, start date:theStartDate, end date:theEndDate, location:theLocation, description:theNotes}
tell theEvent
make new display alarm at end with properties {trigger interval:-theAlarm}
end tell
end tell
end tell
end repeat
end tell
end tell
on error errorMessage number errorNumber
if errorNumber is 1000 then
set alertString to "MISSING RESOURCE"
set errorMessage to "Please create or open a document before running this script."
else if errorNumber is 1001 then
set alertString to "SELECTION ERROR"
set errorMessage to "Please select a table before running this script."
else
set alertString to "EXECUTION ERROR"
end if
display alert alertString message errorMessage buttons {"Cancel"}
error number -128
end try
end tell
I was just about to plug my Drafts action when I saw that @jmreekes had already done so above! Thanks for posting that.
Since I’m here, may as well give it another plug, and explain in more detail. It basically replicates the functionality of Fantastical natural language parsing for adding events, but directly in Drafts using the javascript calendar scripting. Especially for adding a large number of calendar events on iOS, it’s much faster than Fantastical because it doesn’t have to do the URL scheme hopping back and forth. All the events go straight into the calendar almost instantly.
Syntax is identical to Fantastical, except for recurring events, which are not currently supported (this is a limitation of the Drafts scripting capabilities).
Using a segment of your script, here’s an AppleScript “clean and wax” trick for getting and setting an array of values in a single line of code:
tell selectedTable
set theseRows to the rows of selection range
repeat with i from 1 to the count of theseRows
set thisRow to item i of theseRows
copy the formatted value of every cell of thisRow to ¬
{eventName, startDate, endDate, eventAlarm, eventNotes, eventLocation, calendarName, eventString}
tell application "Calendar"
tell calendar theCalName
set newEvent to make new event with properties ¬
{summary:eventName, start date:startDate, end date:endDate, location:eventLocation, description:eventNotes}
tell newEvent
make new display alarm at end with properties {trigger interval:-eventAlarm}
end tell
end tell
end tell
end repeat
end tell
@RosemaryOrchard: Thank you for mentioning the scenario of entering information based on external (already digital!) data into a calendar. At the start of each year I used to manually put the “release dates” of magazines that I subscribe to in a separate calendar “Magazines” to know when I can expect them. I manually entered one and then duplicated/moved that one in Fantastical (getting the dates from a web page from the publisher). I now realise that a simple Drafts JavaScript action makes this much easier:
let calendar = Calendar.find("Magazines");
let text = draft.content.split('\n');
let title = text.shift();
for (line of text) {
let date = line;
let event = calendar.createEvent();
event.title = title;
event.startDate = date;
event.endDate = date;
event.isAllDay = true;
if (!event.update()) {
alert(event.lastError);
}
}
Enter the magazine name on the first line of a new draft and copy/paste the “release dates” below. Run the action. Done!
Thanks for the tip ! I suppose the variables in the array have to be in the corresponding order to the columns in the spreadsheet? In my original script they were not listed in order, since they were referenced directly. Also there is one column (duration) that needs to be skipped.
One question regarding the Workflow. Is there a way to add multiple reminders to a created event?
For doctor’s appointments that are scheduled way in advance I usually add a reminder 2 months, 1 month, 1 week and 1 day in advance.
Just to be able to reschedule if my plans change
Sadly not, Fantastical for iOS supports adding multiple reminders to one event but I haven’t yet found a way to script that. The built in Calendar app only supports the one alert as far as I know. As a workaround you could create a reminders list called “upcoming events” and have Workflow add reminders to those - but of course these won’t change if your event changes.
Thanks for the great information - because of this new podcast I was able to automate the scheduling of courses I teach each quarter. I look forward to learning more in the future.
Fantastic first episode, Rose and David. I’m sure that this will be one of my favorite podcasts. Thanks so much for your time and effort sharing these resources with the Apple community. You two are superstars!
Since Workflow/Shortcuts will no doubt be regularly featured, I thought it would be a good time to share Backup & Restore My Workflows.
Using all @Sal 's advice (thanks ) here is the (hopefully) final script to bulk import events into stock MacOS Calendar app. Since all popular calendar apps use the same database, it works also with Fantastical etc.with the additional flexibility for those who use just the stock app.
You can also specify multiple alarms, as requested above by @leo. I included support for only two in my script, but if you need more, just add column(s) in the Numbers sheet and adapt the script by adding additional line(s) for third etc. alarm(s).
Bulk import of events into Calendar
tell application "Numbers"
activate
try
if not (exists document 1) then error number 1000
tell document 1
try
tell active sheet
set the selectedTable to ¬
(the first table whose class of selection range is range)
end tell
on error
error number 1001
end try
tell selectedTable
set theseRows to the rows of selection range
repeat with i from 1 to the count of theseRows
copy the value of every cell of item i of theseRows to ¬
{eventName, eventLocation, startDate, eventDuration, endDate, eventNotes, eventAlarm1, eventAlarm2, calendarName}
tell application "Calendar"
tell calendar calendarName
set newEvent to make new event with properties ¬
{summary:eventName, start date:startDate, end date:endDate, location:eventLocation, description:eventNotes}
tell newEvent
make new display alarm at end with properties {trigger interval:-eventAlarm1}
make new display alarm at end with properties {trigger interval:-eventAlarm2}
end tell
end tell
end tell
end repeat
end tell
end tell
on error errorMessage number errorNumber
if errorNumber is 1000 then
set alertString to "MISSING RESOURCE"
set errorMessage to "Please create or open a document before running this script."
else if errorNumber is 1001 then
set alertString to "SELECTION ERROR"
set errorMessage to "Please select a table before running this script."
else
set alertString to "EXECUTION ERROR"
end if
display alert alertString message errorMessage buttons {"Cancel"}
error number -128
end try
end tell
To Use:
Copy the Numbers sheet to your Mac.
Enter/copy events (duration and alarms have to be entered in minutes).
Specify the name of the calendar you want to import into.
Select the rows you want to import.
Run the script.
We’ve been looking at those, and of course IFTTT will work on Android (and is actually more powerful on the non iCloud calendar options), we do have a section of this forum for Android though, and I hope we can find out more about the tools that you can use on Android for things like this!
! I suppose the variables in the array have to be in the corresponding order to the columns in the spreadsheet? In my original script they were not listed in order, since they were referenced directly. Also there is one column (duration) that needs to be skipped.
) here is the (hopefully) final script to bulk import events into stock MacOS Calendar app. Since all popular calendar apps use the same database, it works also with Fantastical etc.with the additional flexibility for those who use just the stock app.