Wednesday, September 18, 2019
  • Index
  •  » Code Exchange
  •  » The script to count specific calendar events ––ready to use

#1 2019-09-09 06:19:37 am

scrutinizer82
Member
Registered: 2015-08-04
Posts: 5

The script to count specific calendar events ––ready to use

Applescript:

(*
Date created: 09/01/2019
Date last modified: 17/09/2019
*)


(*
IF YOU ARE NOT AN EXPERIENCED SCRIPTER OR A DEVELOPER DO NOT MODIFY THIS SCRIPT!
*)



(*
The script was created in macOS ver. 10.13.6 "High Sierra";
The script has been confirmed to work properly and produce the intended result with the versions of OS X and macOS 10.9.0 and higher right up to the ver. 10.13.6 "High Sierra" included;
The script HAS NOT BEEN TESTED to work properly and produce the intended result with the versions of macOS 10.14.0 - 10.14.6 "Mojave" and all the beta versions of macOS 10.15 "Catalina" that have been made publicly accessible as of the present date;
The script assumes the versions of the application "Numbers" 3.0 and higher are installed on your OS X or macOS operating system of the version 10.9.0 and higher. It will not work with all the versions of the application "Numbers" prior to the version 3.0 (the application "Numbers" constituting "iWork '09" applications package).
*)



(* Warning: Font names must take the form of
PostScript font names only (refer to FontBook utility) else the script fails*)



(*INSTRUCTIONS FOR USE:

To use the script place it in the folder "~/Library/Scripts" of your home folder. Then, run the utility "Script Editor" located in the folder "Utilities" of the folder "Applications". Open its preferences, go to the pane "General" and select the two options "Show the scripts menu in the menu bar" and "Show this computer scripts". The scripts menu icon ("a manuscript scroll") appears in the menu bar. Run this script by clicking the icon and choosing "Sort events by month.scpt" from the dropdown.

*)



(*DESCRIPTION:

The script takes selected calendar events data and puts it in a workbook created with the application "Numbers" in the form of a table. It allows you to decide whether the data will be written to the existing workbook or a new workbook will be created. If the former, then new sheets containing tables will be created if deemed necessary by the script.

All the created "Numbers" documents are saved to the "Documents" folder.
*)


(*
ISSUES:

The script's handling of recurring events is unsatisfactory it returning only an incomplete events cycle originating with the parent event. This is because of the Calendar's AppleScript implementation and to be addressed in the future versions.
*)




property theYear : "2019"
property theMonth : missing value
set TestDate to (current date)
property Months4Table : {}
property EventMonthsInfo : {}
property DocName : ""
property DocNum : ""
property DocExtension : ""
property ChosenDocName : ""
property TheFullPath : ""
property CalendarName : ""
property EventName : "Meeting"
property FileCount : integer

repeat
   set Prompt to display dialog "Enter the year as a YYYY digits format" buttons {"Cancel", "OK"} default button 2 cancel button 1 default answer theYear
   
   if button returned of Prompt is not "Cancel" then set theYear to text returned of Prompt
   if (count (characters of theYear)) = 4 then
       try
           repeat with anItem in (characters of theYear)
               anItem as number
           end repeat
           exit repeat
       on error
           display alert "Invalid value. Try again." message "Only numbers are acceptable." buttons {"Cancel", "OK"} default button 2
           if button returned of result is "Cancel" then return
       end try
   else
       display alert "Invalid value." message "A year must contain 4 digits."
   end if
   
end repeat


set year of TestDate to theYear

class of TestDate is date --the date entered correctly


tell application "Calendar"
   run
   set CalendarIcon to (path to resource "App.icns" in bundle (((path to applications folder) as text) & "Calendar.app") as alias)
   set AllCalendars to get name of every calendar
   delay 0.5
   
   set CalendarName to (choose from list AllCalendars with title "Calendars of all accounts." with prompt "Select a calendar." default items {(item 1 of AllCalendars)} cancel button name "Cancel" without multiple selections allowed) as text
   if result is not "false" then
       set TheTargetCalendar to (get calendar named CalendarName)
       repeat
           set EventName to (text returned of (display dialog "Enter a name of the event." default answer EventName buttons {"Cancel", "OK"} default button 2 cancel button 1 with icon CalendarIcon)) --the events in question
           set SelectedEventsbyName to get every event of TheTargetCalendar whose summary contains EventName
           if SelectedEventsbyName is {} then
               set DialogMessage to display dialog "No event with this name." & return & "Enter the name of another event" buttons {"Cancel", "OK"} default button 2 cancel button 1 with icon CalendarIcon
               if button returned of DialogMessage is "Cancel" then
                   error number -128
               end if
           else
               
               exit repeat
           end if
       end repeat
       set SelectedEventsbyYear to {}
       repeat with anEvent in SelectedEventsbyName
           set theStartDate to (start date of contents of anEvent)
           set Year2Get to year of theStartDate
           if Year2Get is (theYear as number) then set end of SelectedEventsbyYear to contents of anEvent
       end repeat
       
       set EventMonths to {}
       repeat with anEvent in SelectedEventsbyYear
           set theStartDate to (start date of contents of anEvent)
           set Month2Get to month of theStartDate
           set end of EventMonths to (Month2Get as text)
       end repeat
       
       set EventMonthsInfo to {} --list of records
       set MonthRecord to missing value --this is a initial value because the very 1st processed object will be unique.
       
       repeat with aMonth in EventMonths
           set CurrentMonth to contents of aMonth
           set MonthRecord to missing value --else it will persistently retain the last value
           repeat with aRec in EventMonthsInfo
               if CurrentMonth = theMonth of contents of aRec then
                   set MonthRecord to contents of aRec
                   exit repeat
               end if
           end repeat
           if MonthRecord is missing value then
               set MonthRecord to {theMonth:CurrentMonth, MonthCount:1}
               set end of EventMonthsInfo to MonthRecord
           else
               set MonthCount of MonthRecord to (MonthCount of MonthRecord) + 1
           end if
       end repeat
       
       display dialog (((count SelectedEventsbyYear) as text) & " events in " & theYear & " were found. ") buttons {"OK"} default button 1 giving up after 4 with icon CalendarIcon
   else
       error number -128
   end if
   quit
end tell


set Months4Table to {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}
tell application "Finder"
   set NumbersIcon to (path to resource "AppIcon.icns" in bundle (((path to applications folder) as text) & "Numbers.app") as alias)
   set DocName to "Events-Record"
   set DocNum to ""
   set DocExtension to ".numbers"
   set TheFullPath to ((path to documents folder from user domain) as text) & (DocName & DocNum & DocExtension)
   
   set TargetFiles to (every document file of folder "Documents" of home whose name begins with DocName)
   set FileCount to (count TargetFiles)
   if FileCount = 0 then
       tell application "Finder" to make new document file with properties {name:DocName & DocExtension} at folder "Documents" of home
       tell application "Numbers"
           activate
           delay 0.5
           tell (make new document with properties {name:DocName & DocExtension})
               tell (make new sheet with properties {name:"Selected Events" & "–1"})
                   tell (make new table with properties {name:(EventName & space & "❨" & theYear & "❩"), column count:2, row count:12, header row count:0})
                       set format of every cell to text
                       set {every column's width, every row's height} to {110, 36, 8.949198753E+9}
                       repeat with i from 1 to (count Months4Table)
                           tell row i
                               
                               set value of cell 1 to item i of Months4Table
                               set {alignment of cell 1, vertical alignment of cell 1} to {center, center}
                               set background color of cell 1 to {65535, 42148, 23130}
                               set font name of cell 1 to "Futura-Bold"
                               set font size of cell 1 to 16
                               set text color of cell 1 to {65535, 65535, 65535}
                               
                               set CellValue to formatted value of cell 1
                               
                               repeat with aRec in EventMonthsInfo
                                   if CellValue is theMonth of contents of aRec then set value of cell 2 to MonthCount of contents of aRec
                               end repeat
                               if value of cell 2 is in {"", missing value} then set value of cell 2 to "–"
                               set {alignment of cell 2, vertical alignment of cell 2} to {center, center}
                               set background color of cell 2 to {32639, 49344, 64250}
                               set font name of cell 2 to "Futura-MediumItalic"
                               set font size of cell 2 to 16
                               set text color of cell 2 to {65535, 65535, 65535}
                           end tell
                       end repeat
                   end tell
                   
                   delete (every table whose name does not start with EventName)
                   
               end tell
               
               delete (every sheet whose name does not start with "Selected Events") --deletes the default sheet usually named as "Sheet 1".
               
               close saving in file TheFullPath
           end tell
       end tell
   else
       set TargetFilesNames to name of every document file of folder "Documents" of home whose name begins with DocName
       set Ref2Alises to TargetFiles as alias list
       set DocNum to FileCount as text --for creating new documents only!
       
       display dialog "Similar documents were found." & return & "Would you like to create a new document or add a table to the sheet in the existing document?" with title "Choose between document or sheet" with icon NumbersIcon buttons {"Cancel", "New document", "Add to the existing"} default button 3 cancel button 1
       
       if button returned of result is "Add to the existing" then
           set ChosenDocName to (choose from list TargetFilesNames with title "Select a file" with prompt "Select a workbook from the following workbooks" cancel button name "Cancel") as text
           if result is not "false" then
               repeat with anAlias in Ref2Alises
                   if ((contents of anAlias) as text) ends with ChosenDocName then
                       set TheFullPath to (contents of anAlias) as text
                       tell application "Numbers"
                           open file TheFullPath
                           tell document named ChosenDocName
                               set N to (count (every sheet))
                               repeat with i from 1 to N
                                   if N = 1 then
                                       set j to i
                                   else
                                       set j to N
                                   end if
                                   tell sheet j
                                       
                                       set M to count (every table) --the tables that already present on opening the document
                                       set IdenticalTableNames to (every table whose name begins with EventName and name ends with ("❨" & theYear & "❩"))
                                       if IdenticalTableNames is not {} then set EventName to EventName & "-" & ((count IdenticalTableNames) + 1)
                                       if M < 4 then
                                           tell (make new table with properties {name:EventName & space & "❨" & theYear & "❩", column count:2, row count:12, header row count:0})
                                               set format of every cell to text
                                               set {every column's width, every row's height} to {110, 36, 8.949198753E+9}
                                               
                                               repeat with i from 1 to (count Months4Table)
                                                   tell row i
                                                       
                                                       set value of cell 1 to item i of Months4Table
                                                       set {alignment of cell 1, vertical alignment of cell 1} to {center, center}
                                                       set background color of cell 1 to {65535, 42148, 23130}
                                                       set font name of cell 1 to "Futura-Bold"
                                                       set font size of cell 1 to 16
                                                       set text color of cell 1 to {65535, 65535, 65535}
                                                       
                                                       set CellValue to formatted value of cell 1
                                                       
                                                       repeat with aRec in EventMonthsInfo
                                                           if CellValue is theMonth of contents of aRec then set value of cell 2 to MonthCount of contents of aRec
                                                       end repeat
                                                       if value of cell 2 is in {"", missing value} then set value of cell 2 to "–"
                                                       set {alignment of cell 2, vertical alignment of cell 2} to {center, center}
                                                       set background color of cell 2 to {32639, 49344, 64250}
                                                       set font name of cell 2 to "Futura-MediumItalic"
                                                       set font size of cell 2 to 16
                                                       set text color of cell 2 to {65535, 65535, 65535}
                                                   end tell
                                               end repeat
                                           end tell
                                           exit repeat
                                       else if M = 4 then -- if 4 tables in the sheet then a new sheet is created. The new table goes to the population of this sheet as the first resident.
                                           tell me to MakeNewSheet()
                                           exit repeat
                                       end if
                                   end tell
                               end repeat
                               close saving yes
                           end tell
                       end tell
                       exit repeat --no need to loop for the rest of the list
                   end if
               end repeat
               
           else
               error number -128
           end if
       else
           if button returned of result is not "Cancel" then
               my MakeNewWorkbook()
               
           end if
       end if
   end if
end tell

on MakeNewSheet()
   tell application "Numbers"
       tell document named ChosenDocName
           set ExistingSheetCount to (count (every sheet)) --Existing sheets are being checked.
           set NewSheetIndex to ExistingSheetCount + 1
           tell (make new sheet with properties {name:"Selected Events" & "–" & (NewSheetIndex as text)})
               
               delete (every table whose name does not start with EventName)
               
               tell (make new table with properties {name:EventName & space & "❨" & theYear & "❩", column count:2, row count:12, header row count:0})
                   set format of every cell to text
                   set {every column's width, every row's height} to {110, 36, 8.949198753E+9}
                   repeat with i from 1 to (count Months4Table)
                       tell row i
                           
                           set value of cell 1 to item i of Months4Table
                           set {alignment of cell 1, vertical alignment of cell 1} to {center, center}
                           set background color of cell 1 to {65535, 42148, 23130}
                           set font name of cell 1 to "Futura-Bold"
                           set font size of cell 1 to 16
                           set text color of cell 1 to {65535, 65535, 65535}
                           set CellValue to formatted value of cell 1
                           
                           
                           repeat with aRec in EventMonthsInfo
                               if CellValue is theMonth of contents of aRec then set value of cell 2 to MonthCount of contents of aRec
                           end repeat
                           if value of cell 2 is in {"", missing value} then set value of cell 2 to "–"
                           set {alignment of cell 2, vertical alignment of cell 2} to {center, center}
                           set background color of cell 2 to {32639, 49344, 64250}
                           set font name of cell 2 to "Futura-MediumItalic"
                           set font size of cell 2 to 16
                           set text color of cell 2 to {65535, 65535, 65535}
                       end tell
                   end repeat
               end tell
               
           end tell
           
       end tell
       
   end tell
end MakeNewSheet

on MakeNewWorkbook()
   set DocNum to "–" & ((FileCount + 1) as text)
   set TheFullPath to (((path to documents folder) as text) & (DocName & DocNum & DocExtension))
   tell application "Finder" to make new document file with properties {name:DocName & DocNum & DocExtension} at folder "Documents" of home
   tell application "Numbers"
       activate
       delay 0.5
       tell (make new document with properties {name:DocName & DocNum & DocExtension})
           
           
           
           
           tell (make new sheet with properties {name:"Selected Events" & "–1"})
               
               delete (every table whose name does not start with EventName)
               
               
               tell (make new table with properties {name:EventName & space & "❨" & theYear & "❩", column count:2, row count:12, header row count:0})
                   set format of every cell to text
                   set {every column's width, every row's height} to {110, 36, 8.949198753E+9}
                   repeat with i from 1 to (count Months4Table)
                       tell row i
                           
                           set value of cell 1 to item i of Months4Table
                           set {alignment of cell 1, vertical alignment of cell 1} to {center, center}
                           set background color of cell 1 to {65535, 42148, 23130}
                           set font name of cell 1 to "Futura-Bold"
                           set font size of cell 1 to 16
                           set text color of cell 1 to {65535, 65535, 65535}
                           set CellValue to formatted value of cell 1
                           
                           repeat with aRec in EventMonthsInfo
                               if CellValue is theMonth of contents of aRec then set value of cell 2 to MonthCount of contents of aRec
                           end repeat
                           if value of cell 2 is in {"", missing value} then set value of cell 2 to "–"
                           set {alignment of cell 2, vertical alignment of cell 2} to {center, center}
                           set background color of cell 2 to {32639, 49344, 64250}
                           set font name of cell 2 to "Futura-MediumItalic"
                           set font size of cell 2 to 16
                           set text color of cell 2 to {65535, 65535, 65535}
                       end tell
                   end repeat
               end tell
           end tell
           delete (every sheet whose name does not start with "Selected Events") --deletes the default sheet usually named as "Sheet 1"
           close saving in file TheFullPath
       end tell
   end tell
   
end MakeNewWorkbook

Model: MacBook Pro
AppleScript: 2.7
Browser: Safari 605.1.15
Operating System: macOS 10.13

Last edited by scrutinizer82 (Yesterday 12:21:52 pm)


Currently running Mac OS X 10.7.5, OS X 10.9.5, macOS 10.13.6

Interests: AppleScript, Automator, Unix


Filed under: calendar, table, numbers, Sheet

Offline

 

#2 2019-09-09 05:16:11 pm

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 5005

Re: The script to count specific calendar events ––ready to use

Hi scrutinizer82.

Thanks for posting your script. Since it's a proffered script rather than a query, I'll ask Mark to move it to our Code Exchange forum.

However, a couple of points:
• If there are no similar files in the Documents folder, the script errors when it tries to get their names! The line …

Applescript:

set TargetFilesNames to name of every document file of folder "Documents" of home whose name begins with DocName

… should be in the 'else' section of the following 'if … else' statement.

• Although the script finds my three test events and correctly identifies their month and year, they don't appear in the Numbers table that's created. There's an orange column containing the names of all the months of the chosen year and light blue one containing only dashes. The problem is (with Numbers 6.1 on my machine) that although the values in column 1 are set to the month strings and appear as such in the table, the values which come out of those cells are AppleScript dates! I think this is something to do with the cells' formats being 'automatic' by default and Numbers recognising the names as belonging to months. Three fixes which seem to work are:
1) Set the cells' formats to text before setting their values, or
2) Get the 'formatted value' of each cell 1 instead of its 'value', or
3) Get the value from the 'Months4Table' list again instead of from cell 1.

I'd recommend doing both 1) and 3).

Some efficiencies are possible too, such as setting the properties of entire columns rather than of their individual cells. For instance:

Applescript:

on MakeNewWorkbook()
   set DocNum to "–" & ((FileCount + 1) as text)
   set TheFullPath to (((path to documents folder) as text) & (DocName & DocNum & DocExtension))
   set NewSheetIndex to 1
   tell application "Finder" to make new document file with properties {name:DocName & DocNum & DocExtension} at folder "Documents" of home
   tell application "Numbers"
       activate
       delay 0.5
       tell (make new document with properties {name:DocName & DocNum & DocExtension})
           tell (make new sheet with properties {name:"Selected Events" & "–" & (NewSheetIndex as text)})
               
               tell (make new table with properties {name:EventName & space & "❨" & theYear & "❩", column count:2, row count:12, header row count:0})
                   set {every column's width, every row's height} to {110, 36, 8.949198753E+9} -- There's an extra item here! It's harmless though.
                   -- Set the cells' other properties per column.
                   tell column 1
                       set format to text
                       set {alignment, vertical alignment} to {center, center}
                       set background color to {65535, 42148, 23130}
                       set font name to "Futura-Bold"
                       set font size to 16
                       set text color to {65535, 65535, 65535}
                   end tell
                   tell column 2
                       set {alignment, vertical alignment} to {center, center}
                       set background color to {32639, 49344, 64250}
                       set font name to "Futura-MediumItalic"
                       set font size to 16
                       set text color to {65535, 65535, 65535}
                   end tell
                   -- Now set the cell values.
                   repeat with i from 1 to (count Months4Table)
                       tell row i
                           set CellValue to item i of Months4Table
                           set value of cell 1 to CellValue
                           set noHitsThisMonth to true
                           repeat with aRec in EventMonthsInfo
                               if CellValue is theMonth of aRec then
                                   set value of cell 2 to MonthCount of aRec
                                   set noHitsThisMonth to false
                                   exit repeat
                               end if
                           end repeat
                           if noHitsThisMonth then set value of cell 2 to "–"
                       end tell
                   end repeat
               end tell
               delete (every table whose name does not start with EventName) --deletes the default table usually named as "Table 1"
           end tell
           delete (every sheet whose name does not start with "Selected Events") --deletes the default sheet usually named as "Sheet 1"
           close saving in file TheFullPath
       end tell
   end tell
   
end MakeNewWorkbook


NG

Offline

 

#3 2019-09-10 12:24:10 pm

scrutinizer82
Member
Registered: 2015-08-04
Posts: 5

Re: The script to count specific calendar events ––ready to use

Hello, Nigel

Thanks for your reply and pointed errors, much appreciated. Indeed, I overlooked the potential error raising in the situation where target files produce an empty list; I corrected this segment and re-grouped the statements accordingly.  I also changed the modification date in the heading of the original post to reflect this update.

Speaking of the formatting code I added a line taking care of formatting all of the cells as text.
As for efficiency, I too, agree with you and I think that the code would, probably, run faster. However, I think I leave it as is, as long as it does the job properly.  One reason is that I'm lazy having to re-write much of what is already present, the other one is that I'm charmed by the esthetics as I watch it transforming the original formatting into mine at moderate animation speed: I think I let the graphic card work up the load it takes as the transformation happens.

I also made several tweaks here and there to bring some polish and will continue experimenting.

However, what puzzles me is that at some point "Numbers" would return the error "Invalid name of the table" or smth to the effect of that, other time working through nicely. I haven't found a definite answer, either in existing literature or on the Internet. What would be your thought on why this happens? The error prevented me from testing that cell formatting line though I think it's a pretty simple and obvious addition not to worry about it much.

Last edited by scrutinizer82 (2019-09-10 02:26:45 pm)


Currently running Mac OS X 10.7.5, OS X 10.9.5, macOS 10.13.6

Interests: AppleScript, Automator, Unix

Offline

 

#4 2019-09-10 02:51:07 pm

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 5005

Re: The script to count specific calendar events ––ready to use

scrutinizer82 wrote:

I'm charmed by the esthetics as I watch it transforming the original formatting into mine at moderate animation speed


Fair enough.  smile

However, what puzzles me is that at some point "Numbers" would return the error "Invalid name of the table" or smth to the effect of that, other time working through nicely. […] What would be your thought on why this happens?


I get the error when I attempt to give a table a name already taken by another table in the same sheet.


NG

Offline

 

#5 2019-09-10 05:30:13 pm

scrutinizer82
Member
Registered: 2015-08-04
Posts: 5

Re: The script to count specific calendar events ––ready to use

I got down to cleaning up my script, I added a couple of informational dialogs. I think I've put together the bare framework of its operation by now. As far as I can tell it collects requested data and outputs it to the Numbers table, save some minor harmless errors. The cell format problem still remains, though, in that that Numbers overrides AppleScript actions: telling a table to set all of its cells to text just like telling that rows, columns, makes no difference at the runtime of this script. I quickly created a supplemental script targetting an already existing table of an already existing document and that way I was able to change the cell format from auto to text but not otherwise.

The fact that I cannot make a new table with a similar name in the same sheet is equally odd, to say the least.


NB. On a side note: there's something wrong with iCal AppleScript implementation. It falls flat returning some events: with this script, I was unable to get all of the recurring events originating with the parent event, the majority of them were skipped for no apparent reason, thereby skewing the result.

Last edited by scrutinizer82 (2019-09-11 06:16:03 am)


Currently running Mac OS X 10.7.5, OS X 10.9.5, macOS 10.13.6

Interests: AppleScript, Automator, Unix

Offline

 

#6 2019-09-11 03:17:36 pm

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 5005

Re: The script to count specific calendar events ––ready to use

scrutinizer82 wrote:

NB. On a side note: there's something wrong with iCal AppleScript implementation. It falls flat returning some events: with this script, I was unable to get all of the recurring events originating with the parent event, the majority of them were skipped for no apparent reason, thereby skewing the result.


Hi scrutinizer82.

I've just seen the above addendum to your post.

Yes. The AppleScript implementations of iCal and Calendar only return the base event of any recurring series. Not particularly helpful!

You may find Shane's CalendarLib EC library useful. It uses ASObjC to work directly with the system's "EventKit" framework and provides its own commands. Not only is it much faster (scriptwise) than the Calendar application, but its searches return individual instances of recurring events.


NG

Offline

 
  • Index
  •  » Code Exchange
  •  » The script to count specific calendar events ––ready to use

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)