calendar selection

I found this solution posted by johneday
http://www.johneday.com/1086/reference-selected-calendar-events-applescript
where the author

  1. detected the event identification contained in com.apple.ical SelectedEvents
  2. SQL’ed calendar and event id’s
  3. to reference Application “Calendar”'s calendar id’s event id
  4. and then to sqlite3 the ~/Library/Calendars/Calendar Cache database
  5. After the Calendar Cache database’s calendar and event id are identified, Calendar’s event’s properties and contents could be processed

------------------------------
-- ABOUT
------------------------------
--http://www.johneday.com/1086/reference-selected-calendar-events-applescript
-- Written and tested in Yosemite, Calendar Version 8.0
-- The plist may take several seconds to update after a new event has been selected.
-- Only the first instance of a recurring event will be referenced. 
 
-------------------------------
-- MAIN CODE
------------------------------
set defaultsReply to (do shell script "defaults read com.apple.ical SelectedEvents")
set selectedEvents to parseDefaults(defaultsReply)
 
if selectedEvents = {} then
    display notification "Please try again" with title "No Calendar Event Selected"
    return
end if
 
set eventReferenceList to {}
repeat with sEvent in selectedEvents
    set {eventID, calendarID} to sqlQuery(sEvent)
    tell application "Calendar"
        set eventReference to event id eventID of calendar id calendarID
        -- INSERT YOUR CODE TO PROCESS EACH EVENT
         
        -- Example of "Alert 15 minutes before start"
        my addDisplayAlarm(eventReference, -15)
         
        -- OR BUILD A LIST OF EVENTS
        set end of eventReferenceList to eventReference
    end tell
end repeat
return eventReferenceList
 
------------------------------
-- HANDLERS
------------------------------
on parseDefaults(resultText)
    set localUIDs to {}
    set {TID, text item delimiters} to {text item delimiters, quote}
    set resultItems to text items of resultText
    set text item delimiters to TID
    repeat with i from 1 to (count resultItems)
        if i mod 2 = 0 then set end of localUIDs to resultItems's item i
    end repeat
    return localUIDs
end parseDefaults
 
 
on sqlQuery(localUID)
    local dateString, localUID
    if localUID contains "/" then
        set {TID, text item delimiters} to {text item delimiters, "/"}
        set {dateString, localUID} to text items of localUID
        set text item delimiters to TID
    end if
     
    set sqlText to "
        SELECT DISTINCT zcalendaritem.zshareduid AS eventID
             , znode.zuid as calID
          FROM zcalendaritem
          JOIN znode
            ON znode.z_pk = zcalendaritem.zcalendar
           AND zcalendaritem.zlocaluid = '" & localUID & "'
        ;"
     
    set sqlPath to POSIX path of (path to library folder from user domain) & "Calendars/Calendar Cache"
    set {TID, text item delimiters} to {text item delimiters, "|"}
    set {eID, cID} to text items of (do shell script "echo " & quoted form of sqlText & " | sqlite3 " & quoted form of sqlPath)
    set text item delimiters to TID
    return {eID, cID}
end sqlQuery
 
 
on addDisplayAlarm(myEvent, triggerInterval)
    tell application "Calendar"
        tell myEvent
            if not (exists (display alarms whose trigger interval = triggerInterval)) then
                set myAlarm to make new display alarm at end of display alarms with properties {trigger interval:triggerInterval}
            end if
        end tell
    end tell
end addDisplayAlarm

Might an alternative ASObjC method be substituted for the SQL command in johneday’s script?

No – it’s accessing the backing store using SQLite because there’s no legitimate way. It’s a hack.

Shane,
It appears that in my attempt to find an ASobjC method, I have run afoul of some rule, of which I was not familiar.
If an applescript runs a shell script calling sql on my calendar’s database , from your perspective it would be illegitimate or hacking my own computer. I, however, do not clearly comprehend your analysis.

Are you saying that

  1. the applescript sql method is illegitimate as it places my calendar library cache at risk for destruction or corruption?
  2. although I am accessing my own calendar’s library information, it is illegal?
  3. something else altogether different?

If this data is on my computer, I do not understand where illegitimacy arises, in an attempt to extract it.
If you might explain your thoughts further, I would greatly appreciate your insights.

Calendar data is handled by the Core Data framework, which is also used by lots of other parts of the OS, as well as many apps. In turn, Core Data generally uses SQLite to serialize the actual data to disk.

You can’t do any harm reading that data directly – only if you write to it. However, there’s no guarantee that the SQLite database won’t change. It can change because Apple decides to modify the behavior of the relevant framework, but it can also change if they update the Core Data framework.

So it’s not legitimate in the sense of being a back-door approach, and therefore potentially fragile. But that doesn’t mean you can’t use it.

That said, now that I look closely at the code, it’s in two parts: reading the real ID (which is different from the iCal scripting ID) of the selected event via defaults, and then getting the iCal event ID and calendar ID via sqlite3. It seems to me that you can use defaults to get the real ID, and then use one of my calendar libs and a bit of ASObjC to get the iCal event ID and calendar ID – no need for sqlite3.

So something like this:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "EventKit"
use script "CalendarLib EC" version "1.1.1"
use scripting additions

set selectedID to (((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s objectForKey:"SelectedEvents")'s objectForKey:"iCal")'s firstObject()
if selectedID = missing value then error "No selection found"
set theEKEventStore to fetch store
set theEvent to theEKEventStore's eventWithIdentifier:selectedID
set theEventID to theEvent's calendarItemExternalIdentifier() as text
set theCalID to theEvent's calendar()'s calendarIdentifier() as text

tell application id "com.apple.iCal" -- Calendar
	tell event id theEventID of calendar id theCalID
		--
	end tell
end tell

Shane,
Thanks for the Core Data framework explanation and for the ASObjC example.
For reasons, that I cannot explain, the following two scripts do not yield the same identifier.

  1. NSUserDefaults’s object for key
set selectedID to (((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s objectForKey:"SelectedEvents")'s objectForKey:"iCal")'s firstObject()

  1. shell script defaults read
set defaultsReply to (do shell script "defaults read com.apple.ical SelectedEvents")

What might some of the possible reasons for these differing results?

One’s returning a string and the other an NSString. As you’re passing the result to a method that ultimately requires an NSString, coercing the NSString to a string is just a waste of time. But you can do so if you want to compare them.

Shane,
I appreciate your explanations and understand that the ASObC script using NSUserDefaults returns an NS String.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "EventKit"
use script "CalendarLib E
set selectedID to (((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s objectForKey:"SelectedEvents")'s objectForKey:"iCal")'s firstObject()

This ASObC script, however, returns missing value
The AS shell script using defaults read

set defaultsReply to (do shell script "defaults read com.apple.ical SelectedEvents")

as you said returns a more standard string…
“{
iCal = (
"3207A4D8-FA77-4DAF-8E50-75810465D860"
);
}”
It does not return a missing value.

I understand your discussion of illegitimate access and that the underlying or Core Data framework might change without notice from Apple. In your opinion, should the ASObjC return a a missing value when the AS shell script returns a string?

What do you get from this:

use framework "Foundation"
set selectedID to ((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s objectForKey:"SelectedEvents") as record

Before I open the Calendar application or before I select an event in one of the calendars, the first Applescript:

set defaultsReply to (do shell script "defaults read com.apple.ical SelectedEvents"

yields
“{
iCal = (
);
}”

and the second Applescript:

use framework "Foundation"
set selectedID to ((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s objectForKey:"SelectedEvents") as record

yields
{iCal:{“0A269956-5214-4A75-9D89-35483077DDDA”}}

After I select an event in one of the calendars, the first Applescript:

set defaultsReply to (do shell script "defaults read com.apple.ical SelectedEvents"

now yields
“{
iCal = (
"20170802T000000Z/B2F1BC7F-BB1B-406F-99A9-9EB8739EBF3A"
);
}”

but the second Applescript:

use framework "Foundation"
set selectedID to ((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s objectForKey:"SelectedEvents") as record

still yields
{iCal:{“0A269956-5214-4A75-9D89-35483077DDDA”}}

For some reason, the two commands appear to be deriving data from different sources.
I appreciate any of your insights on this conundrum.

I have no idea why they’re different. You could try running this:

use framework "Foundation"
set selectedID to ((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.ical")'s dictionaryRepresentation()) as record

and this:

set defaultsReply to (do shell script "defaults read com.apple.ical"

And comparing them.

FWIW, I suspect this is being used to pass information about the selection between Apple’s apps, and it wouldn’t surprise me if there’s a little more involved.

So it looks like NSUserDefaults is taking a snapshot of the defaults, and they don’t change until you quit the host app. I’m really not sure why – it seems very odd.

So it looks like you need to use defaults to read the id – but you should still be able to use the rest of the script I posted.

OK, I think I found the problem – we’re using the wrong name. It’s not com.apple.ical, it’s com.apple.iCal.

Try this:

use framework "Foundation"
set selectedID to ((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.iCal")'s objectForKey:"SelectedEvents") as record

Shane, you are correct!
What a difference an upper case C iCal makes!
Now, both scripts …

use framework "Foundation"
use scripting additions
set defaultsReply to do shell script "defaults read com.apple.ical SelectedEvents"
set selectedID to (current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.iCal")'s objectForKey:"SelectedEvents"
 

…access the same object identification.

I wish I knew the reason that might explain the lower case c ical NS script yielding an NS string,rather than returning an error.
If you could explain, I would appreciate the education.
In either regard, thanks for your great help.

It seems that ical works to read the values, but that they’re cached somewhere by the app, and presumably whatever process synchronizes the cache insists on iCal. So whatever you get the first time, you continue to get (until you reboot the script editor).

Calendar data is handled by the Core Data framework, which is also used by lots of other parts of the OS, as well as many apps. In turn, Core Data generally uses SQLite to serialize the actual data to disk.

This is my modified version to add 3 mail reminders in advance on different date offsets to the event. See the hints to make it work on Mojave (not tested on Catalina).



-- "Add 3 Types of Mail Alerts to selected Calendar events.applescript"

------------------------------
-- ABOUT
------------------------------

-- Written and tested in Yosemite, Calendar Version 8.0
-- acsr: Working up to Mojave and Calendar 11.0 when saved as app and granted full disk access
-- The plist may take several seconds to update after a new event has been selected.
-- Only the first instance of a recurring event will be referenced. 

-- based on the script by http://www.johneday.com/ http://www.johneday.com/1086/reference-selected-calendar-events-applescript
-- Tested and modified in El-Capitan on 20180307_190555-acsr

-- Update Docs for Mojave
-- You need to save the script as script program app and allow 
-- full disk access in System Preferences -> Security for the app
-- otherwise the pure applescript will fail silently when trying to access the 
-- database file at "~/Library/Calendars/Calendar Cache"

-- to debug or change the script open the app by dragging it on the ScriptEditor 
-- and allow full disk access for the scripteditor as well.

-- V1.1 20200605_135601-acsr Update as script app to allow full disc access in Mojave
-- V1.0 20180307_190555-acsr

set triggerInterval1 to -1 * 60 * 24
set triggerInterval2 to -5 * 60 * 24
set triggerInterval3 to -14 * 60 * 24

-------------------------------
-- MAIN CODE
------------------------------
set defaultsReply to (do shell script "defaults read com.apple.ical SelectedEvents")
set selectedEvents to parseDefaults(defaultsReply)

if selectedEvents = {} then
	display notification "Please try again" with title "No Calendar Event Selected"
	return
end if

set eventReferenceList to {}
repeat with sEvent in selectedEvents
	set {eventID, calendarID} to sqlQuery(sEvent)
	tell application "Calendar"
		set eventReference to event id eventID of calendar id calendarID
		-- INSERT YOUR CODE TO PROCESS EACH EVENT
		
		-- Example of "Alert 15 minutes before start"
		-- my addDisplayAlarm(eventReference, -15)
		
		-- MailAlert 1 day before start"
		my addMailAlarm(eventReference, triggerInterval1)
		
		-- MailAlert 5 day before start"
		my addMailAlarm(eventReference, triggerInterval2)
		
		-- MailAlert 14 day before start"
		my addMailAlarm(eventReference, triggerInterval3)
		
		-- OR BUILD A LIST OF EVENTS
		set end of eventReferenceList to eventReference
	end tell
end repeat
return eventReferenceList

------------------------------
-- HANDLERS
------------------------------
on parseDefaults(resultText)
	set localUIDs to {}
	set {TID, text item delimiters} to {text item delimiters, quote}
	set resultItems to text items of resultText
	set text item delimiters to TID
	repeat with i from 1 to (count resultItems)
		if i mod 2 = 0 then set end of localUIDs to resultItems's item i
	end repeat
	return localUIDs
end parseDefaults


on sqlQuery(localUID)
	local dateString, localUID
	if localUID contains "/" then
		set {TID, text item delimiters} to {text item delimiters, "/"}
		set {dateString, localUID} to text items of localUID
		set text item delimiters to TID
	end if
	
	set sqlText to "\n        SELECT DISTINCT zcalendaritem.zshareduid AS eventID\n             , znode.zuid as calID\n          FROM zcalendaritem\n          JOIN znode\n            ON znode.z_pk = zcalendaritem.zcalendar\n           AND zcalendaritem.zlocaluid = '" & localUID & "'\n        ;"
	
	set sqlPath to POSIX path of (path to library folder from user domain) & "Calendars/Calendar Cache"
	set {TID, text item delimiters} to {text item delimiters, "|"}
	set {eID, cID} to text items of (do shell script "echo " & quoted form of sqlText & " | sqlite3 " & quoted form of sqlPath)
	set text item delimiters to TID
	return {eID, cID}
end sqlQuery


on addDisplayAlarm(myEvent, triggerInterval)
	tell application "Calendar"
		tell myEvent
			if not (exists (display alarms whose trigger interval = triggerInterval)) then
				set myAlarm to make new display alarm at end of display alarms with properties {trigger interval:triggerInterval}
			end if
		end tell
	end tell
end addDisplayAlarm

on addMailAlarm(myEvent, triggerInterval)
	tell application "Calendar"
		tell myEvent
			if not (exists (mail alarms whose trigger interval = triggerInterval)) then
				set myAlarm to make new mail alarm at end of mail alarms with properties {trigger interval:triggerInterval}
			end if
		end tell
	end tell
end addMailAlarm


Update for Mojave!

You need to save the script as script program app and allow full disk access in System Preferences - Security for the app otherwise the pure applescript will fail silently when trying to access the database file at “~/Library/Calendars/Calendar Cache”

to debug or change the script open the app by dragging it on the ScriptEditor and allow full disk access for the scripteditor as well.

Update: to my “Add 3 Types of Mail Alerts to selected Calendar events.applescript”

it worked up to early Monterey (after fixing permissions again).

Now with later versions of Monterey it stopped working and finally in Ventura I get this error message:

AppleScript Error Dialog
“Parse error near line 2: no such table: zcalendaritem” in bold
and: “Parse error near line 2: no such table: zcalendaritem (1)” in plain below

The error can be drilled down to be part of the SQL Statement function “sqlText” in the Shell command.

**set** {eID, cID} **to** *text items* **of** (**do shell script** "echo " & quoted form **of** sqlText & " | sqlite3 " & quoted form **of** sqlPath)

There is also an Accessibility issue mentioned in this post related to another project:

No idea what they fixed inside their app. Just reestablishing the accessibility access in system preferences did not help to fix the issue. Maybe the SQL Database naming has changed.

I’m using a similar solution that only uses the pre-installed OS X software. The script is not mine, and I don’t remember where I found it:
 

use framework "Foundation"
use scripting additions

-- get Calendar.app's selection
set localUID to (iCal of ((current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.apple.iCal")'s objectForKey:"SelectedEvents")) as text
set {selectedEventID, selectedCalendarID} to my sqlQuery(localUID)

-- find ICS file, read its content as text
set calendarsFolder to ("" & (path to library folder from user domain) & "Calendars") as alias
set calendarsFolderPath to quoted form of (POSIX path of calendarsFolder)
try
	set thePath to do shell script "find " & calendarsFolderPath & " -name " & selectedEventID & ".ics"
end try
set icsFileText to read thePath as «class utf8»

-- get Date Created and Date Modified
repeat with aParagraph in (paragraphs of icsFileText)
	if aParagraph begins with "CREATED:" then set DateCreated to text 9 thru -1 of aParagraph
	if aParagraph begins with "LAST-MODIFIED:" then set DateModified to text 15 thru -1 of aParagraph
end repeat
return {Date_Created:DateCreated, Date_Modified:DateModified}


-- parsing Calendar.app Cache to determine calendar ID by selected event localUID
on sqlQuery(localUID)
	local dateString, localUID
	if localUID contains "/" then
		set {TID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "/"}
		set {dateString, localUID} to text items of localUID
		set AppleScript's text item delimiters to TID
	end if
	set sqlText to "
 SELECT DISTINCT zcalendaritem.zshareduid AS eventID
 , znode.zuid as calID
 FROM zcalendaritem
 JOIN znode
 ON znode.z_pk = zcalendaritem.zcalendar
 AND zcalendaritem.zlocaluid = '" & localUID & "'
 ;"
	set sqlPath to POSIX path of (path to library folder from user domain) & "Calendars/Calendar Cache"
	set {TID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "|"}
	set {eID, cID} to text items of (do shell script "echo " & quoted form of sqlText & " | sqlite3 " & quoted form of sqlPath)
	set AppleScript's text item delimiters to TID
	return {eID, cID}
end sqlQuery

 

@KniazidisR For my Environment on Ventura I receive the same error for your code:

Parse error near line 2: no such table: zcalendaritem

I was not requested for any permissions missing for your code running directly from Scripteditor (Scripteditor was granted enough rights usually)

What I found out so far is a fundamental change of the sqlite3 database filename, location and fieldnames used in the query (not to mention maybe actual data formats).

Old Database Location

The old sqlite3 Database until Catalina and early Monterey was stored in a file path with no file extension at: /Users/[userid]/Library/Calendars/Calendar Cache (the path used in the old non working code).

Remark: The old name Calendar Cache is a bit misleading. It is actually the working database and copy of all calendars for the UI and Reminders etc. and fed additionally from the externally subscribed calendars. In this intention the database caches these calendars when you are offline.

On Ventura

  • The old Database exists on full path /Users/[userid]/Library/Calendars/Calendar Cache , as an empty file (shown in Catalina to Ventua as a folder!)
  • The new Database lives in /Users/[userid]/Library/Calendars/Calendar.sqlitedb.

Getting UUIDs from Selection seems OK

The shell command to figure out the UUIDs of selected calendar items still works:
defaults read com.apple.ical SelectedEvents

I suppose the parsing into a list of UUID is working as well.

How to update the SQL Query?
The challenge is now (if AppleScript can still access the database via sqlite shell command!):

  • understand the old query (almost, but not yet)
  • Try to get a copy of an old database from Catalina Backups handy (for Understanding)
  • use a tool like DB Browser for SQLite from https://sqlitebrowser.org/ to inspect the databases
    • I could open both files successfuly as a copy residing on the desktop. The in place database on Ventura cannot be opened due to file lock)
  • find the corresponding fields in the new database
  • update the SQL query to figure out the eventID and calendarID needed to call the functions to create new alerts.

Due to poor documentation of abbreviated variable names, I ca understand the coarse concept but still lacking final confidence about the SQL statement parts (I am not an SQL expert).

Maybe that helps other to go further…