I have a script that it pulling data from excel and inputting into Things to do as a scheduled project. It works great, but I would like to take it a step further:
- if there is data in column F (normally a date), then it should ignore the corresponding data in Column E
- if no data in Column F, then it should record the corresponding data in Column E
BUT…I need it, when ignoring Column E, to change the range_value to “blind” so that it doesnt create a To Do or screw up titles of my other To Dos.
See current script below:
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)
if class of theDate is date then
make new to do at end of to dos of project ProjectName with properties {due date:theDate, name:(item i of summaryList) & " - " & ProjectName}
end if
end repeat
end tell
on calcDate(d)
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 calcDate