Hello All,
I am currently in development of an AppleScript that reads identifying numbers, dates, and start and end times from an Excel workbook and creates events in Apple Calendar with said information.
I have a working script that does what I need, provided the relevant Excel workbook is already open, however trying to automate this script has proven to be difficult. (This code assumes column C of sheet titled ‘Storage’ stores identifying number, column D stores date, E stores start time and F stores end time)
My first idea was to create a script within Excel VBA using the ‘AppleScriptTask’ function that would ‘call’ my AppleScript from within the workbook when run. I could not get this to work even with a greatly simplified code. I ensured that my script was saved in ‘~/Library/Application Scripts/com.microsoft.Excel/’ but still no luck, I kept running into ‘Run-time error ‘5’: Invalid procedure call or argument’.
My second idea was to export my AppleScript to an application and simply run it from my desktop or dock while the relevant workbook was open, however this too proved to be difficult. Once exported, running my new application prompted a few permission requests that I was able to allow. The app then gets stuck in a recursive loop with a pop-up window asking for permission to add events to Apple Calendar. If I click ‘Allow’ on this window another the exact same pops up in its place. If I click ‘Do Not Allow’ a new window pops up stating I don’t have permission to add events to AppleCalendar and the app stops running. [Note: I went through settings and for my application I manually allowed permissions for automation, accessibility, files and folders, calendar, and full disk access. This made no difference in outcome.]
I am very new to AppleScript development and have used GPT and other AI’s to help me with development so far, but I can’t seem to get past the mentioned roadblocks.
I am running macOS Sonoma 14.1, ScriptEditor 2.11, Apple Calendar 14.0, and Excel for Mac 16.83
Below is my functional AppleScript
property workbookName : "YourWorkbookName.xlsm"
tell application "Microsoft Excel"
activate
set workbookOpened to false
-- Check if the workbook is already open
try
set theWorkbook to workbook workbookName
set workbookOpened to true
on error
display dialog "YourWorkbookName.xlsm is not open, please ensure the workbook is open and try again." buttons {"OK"} default button 1
return
end try
-- If workbookOpened is true, the workbook is open
if workbookOpened then
set theSheet to worksheet "Storage" of theWorkbook
set lastRow to get count of rows of used range of theSheet
set deliveryList to {}
repeat with i from 3 to lastRow
set poNumber to value of cell ("C" & i) of theSheet as text
set deliveryDateText to value of cell ("D" & i) of theSheet as text
set startTimeDecimal to value of cell ("E" & i) of theSheet
set endTimeDecimal to value of cell ("F" & i) of theSheet
if poNumber ends with ".0" then set poNumber to text 1 thru -3 of poNumber
if poNumber is not "" and deliveryDateText is not "" and startTimeDecimal is not "" and endTimeDecimal is not "" then
set end of deliveryList to {poNumber:poNumber, date:deliveryDateText, startTime:startTimeDecimal, endTime:endTimeDecimal}
end if
end repeat
end if
end tell
tell application "Calendar"
set myCalendar to calendar "YourCalendarName"
if myCalendar is missing value then
display dialog "Specified calendar not found."
return
end if
repeat with aDelivery in deliveryList
set poNumber to poNumber of aDelivery
set deliveryDateText to date of aDelivery
set startTimeDecimal to startTime of aDelivery
set endTimeDecimal to endTime of aDelivery
set eventTitle to "PO# " & poNumber & " Delivery"
-- Convert dateText and decimal times to date objects
set deliveryDate to my formatDate(deliveryDateText)
set startDateTime to my convertToDateTime(deliveryDate, startTimeDecimal)
set endDateTime to my convertToDateTime(deliveryDate, endTimeDecimal)
-- Check for existing events to avoid duplicates
set sameDayEvents to (every event of myCalendar where its summary contains eventTitle and its start date ≥ deliveryDate and its start date < (deliveryDate + (1 * days)))
if (count of sameDayEvents) is equal to 0 then
tell myCalendar
set newEvent to make new event with properties {summary:eventTitle, start date:startDateTime, end date:endDateTime, allday event:false}
tell newEvent
-- Add an alarm to the event for 1 day before
make new display alarm at end with properties {trigger interval:-1440} -- -1440 minutes for 1 day before
end tell
end tell
else
display notification "An event for " & eventTitle & " on " & deliveryDate & " already exists. Skipping."
end if
end repeat
end tell
-- Helper functions for date conversion
on formatDate(excelDateText)
try
return date excelDateText
on error
display dialog "Failed to convert date: " & excelDateText
return current date
end try
end formatDate
on convertToDateTime(eventDate, decimalTime)
set totalMinutes to decimalTime * 1440
set hours to totalMinutes div 60
set minutes to totalMinutes mod 60
set adjustedDateTime to current date
tell adjustedDateTime
set its year to year of eventDate
set its month to month of eventDate
set its day to day of eventDate
set its hours to hours
set its minutes to minutes
set its seconds to 0
end tell
return adjustedDateTime
end convertToDateTime
Any help, insight, or feedback is greatly appreciated, I’ve been having a blast working on this and learning about AppleScript and its capabilities, but I’m just stuck on how to proceed. Apologies if I’ve formatted anything incorrectly or overlooked anything obvious.
Thanks in advance,
-BadNoviceCoder