I have searched through this forum and found a few threads by Bluenote and others that cover very similar to my needs. However, I am getting stuck trying to utilise those portions of scripts.
I need get each row of a daily Excel file into an existing iCal ‘DailyImport’ calendar as separate To Do items.
The boss will then drag (schedule) each task to each of the other calendars.
Each daily Excel file does contain jobs that are new and jobs that are existing, so if any matching jobs are already in the To Do Items, then they should not get added, only the new (non-mathing) ones.
The Excel file is simple:
Due Job Title Qty Customer Salesperson
20/01/12 15001 Test Job Title One 2,000 Client One John Smith
13/01/12 15002 Test Job Title Two 15,000 Client Two Mary Smith
etc.
The resulting To Do item should be processed out of Excel to look like this:
Title of To Do Item: 15001 ¢ Test Job Title One ¢ 2,000 ¢ Client One ¢ John Smith
Scheduled for: 20 January 2012
I would be extremely grateful if someone can help.
Also, I will be calling this script with Automator, to run on a dropped Excel file in a watched folder.
property calendarName : "Job"
tell application "Microsoft Excel"
set usedRange to used range of active sheet
set {ASTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, " ¢ "}
repeat with i from 2 to (count rows of usedRange)
set valueList to (value of row i of usedRange)
set valueList to item 1 of valueList
set {theDate, theJob} to items 1 thru 2 of valueList
set item 2 of valueList to theJob as integer
set theTitle to (items 2 thru -1 of valueList) as text
my createToDo(theDate, theTitle)
end repeat
set AppleScript's text item delimiters to ASTID
end tell
on createToDo(dueDate, theSummary)
tell application "iCal"
tell calendar calendarName
make new todo at end of todos with properties {due date:dueDate, summary:theSummary}
end tell
end tell
end createToDo
That gets the following error:
error “iCal got an error: Can’t get date (date "Thursday, 26 April 2012 12:00:00 AM") of calendar "Job".” number -1728 from date (date “Thursday, 26 April 2012 12:00:00 AM”) of calendar “Job”
The cells were formatted as ‘date’ (dd/mm/yyyy). The year is 4 digit, but displayed as two.
Also noticed the formatting for the quantity is screwy. Instead of “30,000” it comes out as “3.0E+4”, example:
15003 ¢ Test Job Title Three ¢ 3.0E+4 ¢ Client Three ¢ Paul Smith
Test Excel file is:
Due Job Title Qty Customer Salesperson
26/04/12 15001 Test Job Title One 2,000 Client One John Smith
13/05/12 15002 Test Job Title Two 15,000 Client Two Mary Smith
15/05/12 15003 Test Job Title Three 30,000 Client Three Paul Smith
OK, I misinterpreted the error.
The column is indeed formatted as date, so delete the date specifier in the make new todo line
The error occurred when the cell was empty. What should happen in this case?
Is the comma in 2,000 the decimal separator? If yes, is the integer portion sufficient?
the appearance of the number format depends on the date format settings in the international pref pane.
Here in Switzerland the comma is the decimal separator.
This version of the script reads the date(s) separately and considers all other values as string values,
the formatting should be displayed correctly now
The date error occurs when the content of the current date cell is empty or no valid date
property calendarName : "Job"
tell application "Microsoft Excel"
set usedRange to used range of active sheet
set {ASTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, " ¢ "}
repeat with i from 2 to (count rows of usedRange)
set theDate to value of cell 1 of row i of usedRange
set valueList to (string value of row i of usedRange)
set valueList to item 1 of valueList
set theTitle to (items 2 thru -1 of valueList) as text
my createToDo(theDate, theTitle)
end repeat
set AppleScript's text item delimiters to ASTID
end tell
on createToDo(dueDate, theSummary)
tell application "iCal"
tell calendar calendarName
make new todo at end of todos with properties {due date:dueDate, summary:theSummary}
end tell
end tell
end createToDo
The script filters the used range of the Excel sheet, probably there are used cells below the last due date.
Insert the last line of this snippet, it exits the loop in case of an empty cell
.
repeat with i from 2 to (count rows of usedRange)
set theDate to value of cell 1 of row i of usedRange
if class of theDate is not date then exit repeat
.
Got it. I just discovered the Replies option in AppleScript Editor.
I am very grateful, Stefan.
AppleScript certainly opens up a universe of possibilities
I am going to try to figure out matching the rows and excluding duplicates.
property calendarName : "DailyImport"
tell application "Microsoft Excel"
set usedRange to used range of active sheet
set {ASTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, " ¢ "}
repeat with i from 2 to (count rows of usedRange)
set theDate to value of cell 1 of row i of usedRange
if class of theDate is not date then exit repeat
set valueList to (string value of row i of usedRange)
set valueList to item 1 of valueList
set theTitle to (items 2 thru -1 of valueList) as text
my createToDo(theDate, theTitle)
end repeat
set AppleScript's text item delimiters to ASTID
end tell
on createToDo(dueDate, theSummary)
tell application "iCal"
tell calendar calendarName
make new todo at end of todos with properties {due date:dueDate, summary:theSummary}
end tell
end tell
end createToDo
Although working with a few lists in AS is fine, when I need to deal with a large data set I import it into sqlite3 (already installed) first. Manipulating data is much easier in a database.
I had originally intended to use Filemaker, but discovered that iCal and AppleScript had all that’s needed for a task like this. Just I had no idea about running AppleScripts… but keen to learn.
I want to edit this to remove duplicate To Do’s, not Events.
Can I just change references for “events” to “todo” in the following?
tell application "iCal"
-- set sourceCal to first calendar whose title is (item 1 of (choose from list (title of (every calendar) as list) with prompt "Choose calendar to delete duplicates"))
set theCalendars to every calendar
set testThen to (current date) -- time tester
end tell
set n to 0
repeat with sourceCal in theCalendars
tell application "iCal"
set mySumms to summary of events of sourceCal
set myStarts to start date of events of sourceCal
set myEnds to end date of events of sourceCal
set myUIDs to uid of events of sourceCal
end tell
script myStuff
property aSumms : {}
property aStarts : {}
property aEnds : {}
property aUIDs : {}
property aDeletes : {}
end script
set myStuff's aSumms to mySumms
set myStuff's aStarts to myStarts
set myStuff's aUIDs to myUIDs
set myStuff's aEnds to myEnds
set myLength to length of myStuff's aStarts
repeat with aNum from 1 to (myLength - 1)
set thisSumm to (item aNum of myStuff's aSumms)
set thisStart to (item aNum of myStuff's aStarts)
set thisEnd to (item aNum of myStuff's aEnds)
repeat with bNum from (aNum + 1) to myLength
set thatSumm to (item bNum of myStuff's aSumms)
set thatStart to (item bNum of myStuff's aStarts)
set thatEnd to (item bNum of myStuff's aEnds)
if thisSumm is equal to thatSumm and thisStart is equal to thatStart and thisEnd is equal to thatEnd then
set the end of myStuff's aDeletes to (item bNum of myStuff's aUIDs)
exit repeat
end if
end repeat
end repeat
set n to n + (count of myStuff's aDeletes)
tell application "iCal"
repeat with myDel in myStuff's aDeletes
delete (every event of sourceCal whose uid is myDel)
end repeat
end tell
end repeat
tell application "iCal"
display dialog (n & " duplicates deleted in " & ((current date) - testThen) & " seconds") as text -- time tester=20
end tell
property calendarName : "DailyImport"
tell application "iCal" to set allTodos to every todo of calendar calendarName
set duplicates to findDuplicates(allTodos)
repeat with anItem in duplicates
tell application "iCal" to delete anItem
end repeat
on findDuplicates(x)
set origs to {}
set dels to {}
script a
property theList : x
property originals : origs
property itemsToDelete : dels
end script
repeat with anItem in a's theList
if elementIsInList(anItem, origs) then
set a's itemsToDelete's end to contents of anItem
else
set a's originals's end to contents of anItem
end if
end repeat
return dels
end findDuplicates
-- two todos are equal if both due dates and summaries are equal
on elementIsInList(anElement, theList)
using terms from application "iCal"
repeat with anItem in theList
if (due date of anItem is equal to due date of anElement) and (summary of anItem is equal to summary of anElement) then
return true
end if
end repeat
return false
end using terms from
end elementIsInList
Really cool stuff here, that saved me some time. I have merged the bits a bit and updated it to the Reminders App in 10.11.
Feel free to use it as you see fit. (My Excel columns are: Title, Priority(3-0), Person(not used in script), Due Date, Status, Notes)
property todoAccount : "iCloud"
property todoList : "Work"
property doCleanup : true
tell application "Microsoft Excel"
set usedRange to used range of active sheet
set {ASTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, " ¢ "}
repeat with i from 2 to (count rows of usedRange)
set theDate to value of cell 4 of row i of usedRange --as date
if class of theDate is not date then exit repeat
set theStatus to value of cell 4 of row i of usedRange
if theStatus is not "Done" then
set thePriority to value of cell 2 of row i of usedRange
set theName to value of cell 1 of row i of usedRange
set valueList to (string value of row i of usedRange)
set valueList to item 1 of valueList
set theTitle to (items 2 thru -1 of valueList) as text
my createToDo(theDate, theTitle, thePriority, theName)
end if
end repeat
my doTheCleanup()
set AppleScript's text item delimiters to ASTID
end tell
on doTheCleanup()
tell application "Reminders" to set allTodos to every reminder of list todoList of account todoAccount
set duplicates to findDuplicates(allTodos)
repeat with anItem in duplicates
tell application "Reminders" to delete anItem
end repeat
end doTheCleanup
on findDuplicates(x)
set origs to {}
set dels to {}
script a
property theList : x
property originals : origs
property itemsToDelete : dels
end script
repeat with anItem in a's theList
if elementIsInList(anItem, origs) then
set a's itemsToDelete's end to contents of anItem
else
set a's originals's end to contents of anItem
end if
end repeat
return dels
end findDuplicates
-- two todos are equal if both due dates and summaries are equal
on elementIsInList(anElement, theList)
using terms from application "Reminders"
repeat with anItem in theList
if (name of anItem is equal to name of anElement) then --and (due date of anItem is equal to due date of anElement)
return true
end if
end repeat
return false
end using terms from
end elementIsInList
on createToDo(dueDate, theSummary, thePriority, theName)
tell application "Reminders"
tell list todoList of account todoAccount
make new reminder with properties {due date:dueDate, body:theSummary, name:theName, priority:thePriority}
end tell
end tell
end createToDo
You are a guru.