at the bottom of this post is my current script.
Is there anyone that can help me add something that I have no clue on how to add:
I need for the script to check whether this schedule (excel sheet) is regarding a sports item by checking ProjectName to see if it contains any keywords, such as NBA, NHL, NFL, or NBA.
If it doesn’t, then it would make the ical schedule as I have below.
If it does, then it needs to make the ical schedule as below, with the addition of the following ToDo’s:
ChasePaintMaster
ChasePaintPhotos
ChaseDecal
These would all be due 2 weeks after FirstShot date.
Also these would all need to be included in the updating process where if the calendar exists, and it is not completed, then the date needs to be updated for that todo. if it is completed (or does not exist [non-sports items])…then its ignored.
any help is appreciated
property summaryList : {"Tooling PO", "Paint Photos", "Decals", "Paint Guides", "Layout", "Vendor", "PaintMasters", "1st Shot", "Mock-up", "Packaging Files", "Chromalins", "EP", "1st Deco", "Proofs", "PO", "PP"}
tell application "Microsoft Excel"
set ProjectName to the value of cell "$F$4"
set ReviseDate to the value of cell "$Z$4"
set range_value to item 1 of (get value of range "J12:U12")
set {ToolDate, PaintPhotos, Blister, blind, VendorDate, PaintMaster, FirstShot, Mockup, Artwork, EP, firstDeco, PODate} to range_value
set PPDate to the value of cell "$Y$12"
copy PaintPhotos to DecalDate
copy PaintPhotos to GuideDate
copy Artwork to Chromalins
copy Artwork to Proofs
close front window
end tell
tell application "iCal"
if not (exists calendar ProjectName) then
tell (make new calendar at end of calendars with properties {name:ProjectName, description:ReviseDate as text})
set its color to {37500, 0, 0}
end tell
end if
set dateList to {ToolDate, PaintPhotos, DecalDate, GuideDate, Blister, VendorDate, PaintMaster, FirstShot, Mockup, Artwork, Chromalins, EP, firstDeco, Proofs, PODate, PPDate}
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)
if class of theDate is date then
if item i of summaryList is "Proofs" then set theDate to theDate + weeks * 2
if theTodo is false or (item i of summaryList is "Proofs" and isCompleted is false) then
if item i of summaryList is "Proofs" and theTodo is not false then delete theTodo
make new todo at end of todos of calendar ProjectName with properties {due date:theDate, description:ProjectName, summary:item i of summaryList}
else
if isCompleted is false then set due date of theTodo to theDate
end if
end if
end repeat
end tell
on calcDate(d)
if class of d is date then return d
if d is "" or d is in {"N/A", "NA", "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
if yr as integer < 10 then set yr to (yr as integer) + 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 calcDate
on check_Todo(cal, param)
tell application "iCal"
tell calendar cal
repeat with tt in (get todos)
tell contents of tt
if summary is param then
set c to completion date
try
c
return {it, true}
on error
return {it, false}
end try
end if
end tell
end repeat
end tell
return {false, false}
end tell
end check_Todo