TL;DR:
I had an old script that used to work flawlessly with excel and ical 4 on Snow Leopard.
Since upgrading to Lion, iCal 5 will not play nicely.
Can’t be bothered with iCal anymore, switching to new app called “Things”.
Figured out how to get the excel data into things properly for new projects.
Need help figuring out how to get the existing projects to update with new data.
Full Story:
I had an old script that used to work flawlessly with excel and ical 4 on Snow Leopard. the script takes a schedule created in excel, and sends the data to iCal as a list of to dos, based on project. If a project with same name existed, then it would simply updated any NON-completed todos.
Since upgrading to Lion, iCal 5 will not play nicely. It works good for new projects. But for updating old projects sometimes it will work correctly, while many other times it will ignore the original and instead create a new project list with the name just adding a 1 or 2, etc.
So…I have moved on to the application called “Things”. Was hoping some kind soul can help me fine tune this script to get it working properly:
I successfully have the script importing the data from excel, and creating new projects in Things.
The problem I run into is when a project in Things exists, and the excel data needs to update some of the to dos. When the project exists, it will ignore original to dos and just duplicate the to dos.
Any ideas?
Below is my current full script.
and you can download a sample excel file from http://db.tt/sG2jUhm1
property summaryList : {"Rough Ceramics", "Tooling Quote", "Casting Ceramic", "Tooling PO", "Paint Photos (MASS)", "Package Info", "Production Quote", "Vendor Confirmed", "Paint Master", "Decals (MASS)", "Paint Photos (CL)", "Decals (CL)", "1st Shots (New Figures)", "EP Shots (Old Figures)", "Blister Layout", "PO Breakdown", "Mockups", "EP Shots (New Figures)", "1st Deco", "PO", "Packaging Files", "Chromalins", "Blister Release", "Injection Release", "Deco Release", "Press Proofs", "Printing Release", "PP"}
tell application "Microsoft Excel"
set ProjectName to the value of cell "$B$2"
set range_value_List to (get value of range "E4:E38")
set range_value to {}
repeat with i from 1 to (count range_value_List)
set end of range_value to item 1 of (item i of range_value_List)
end repeat
set {RoughCeramics, blind, ToolingQuote, CastingCeramic, blind, ToolingPO, blind, PaintPhotosMASS, DieLines, ProductionQuote, VendorConfirmed, PaintMaster, DecalsMASS, DecalsCL, PaintPhotosCL, FirstShotsNew, EPShotsOld, BlisterLayout, POBreakdown, Mockups, EPShotsNew, firstDeco, PO, blind, blind, PackagingFiles, Chromalins, BlisterRelease, InjectionRelease, blind, DecoRelease, PressProofs, blind, PrintingRelease, PP} to range_value
close front window
end tell
tell application "Things"
if not (exists project ProjectName) then
tell (make new project at end of projects with properties {name:ProjectName})
end tell
end if
set dateList to {RoughCeramics, ToolingQuote, CastingCeramic, ToolingPO, PaintPhotosMASS, DieLines, ProductionQuote, VendorConfirmed, PaintMaster, DecalsMASS, DecalsCL, PaintPhotosCL, FirstShotsNew, EPShotsOld, BlisterLayout, POBreakdown, Mockups, EPShotsNew, firstDeco, PO, PackagingFiles, Chromalins, BlisterRelease, InjectionRelease, DecoRelease, PressProofs, PrintingRelease, PP}
repeat with i from 1 to count summaryList
set theDate to my calcDate(item i of dateList)
set {theTodo, isCompleted} to my check_Todo(ProjectName, ((item i of summaryList) & " - " & ProjectName))
if class of theDate is date then
if theTodo is false then
make new to do of project ProjectName with properties {due date:theDate, name:(item i of summaryList) & " - " & ProjectName}
else
if isCompleted is false then set due date of theTodo to theDate
end if
end if
end repeat
end tell
on calcDate(d)
tell application "iCal"
if class of d is date then return d
if d is "" or d is "N/A" or d is "NA" or d is "on hold" then return false
set delim to item (((d contains "/") as integer) + 1) of {".", "/"}
set {TID, text item delimiters} to {text item delimiters, delim}
try
set {mn, dy, yr} to text items of d
set yr to yr mod 1000 + 2000
set text item delimiters to TID
tell (current date) to set d to it - (its time)
tell d to set {its day, its month, its year} to {dy as integer, mn as integer, yr as integer}
return d
on error
set text item delimiters to TID
return false
end try
end tell
end calcDate
on check_Todo(cal, param)
tell application "iCal"
tell calendar cal
repeat with tt in (get todos)
tell contents of tt
if summaryList begins with param then
set c to completion date
if c is missing value then
return {it, false}
else
return {it, true}
end if
end if
end tell
end repeat
end tell
return {false, false}
end tell
end check_Todo