Excel / ical scripting question

i have a script (see below) to read my milestones and add the events to ical.


on adding folder items to this_folder after receiving added_items
	tell application "Microsoft Excel"
		repeat with anitem in added_items
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4" as string
			tell (get value of range "D12:Z12") to set these_Dates to list 1
			my Schedule(ProjectName, ReviseDate, these_Dates)
			close front window
		end repeat
	end tell
end adding folder items to

on Schedule(ProjectName, ReviseDate, these_Dates)
	set the_titles to {"All Tooling Patterns in HK", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling PO in HK", "Tooling Start", "Paintmaster in HK", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "Packaging Artwork in HK", "EP Samples", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}
	set tc to count the_titles
	
	tell application "iCal"
		if (exists calendar ProjectName) then delete calendar ProjectName
		set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
		
		tell theCal to repeat with i from 1 to tc
			set t_date to item i of these_Dates
			if (t_date is not "N/A" and t_date is not "" and t_date is not "NA" and t_date is not "on hold") then ¬
				tell (make new event at end of events with properties {start date:t_date, summary:item i of the_titles, location:ProjectName, allday event:true, status:confirmed, description:ReviseDate})
					make new display alarm at end of display alarms with properties {trigger interval:-10080}
				end tell
		end repeat
		quit
	end tell
end Schedule

  1. How can i modify the script to take one of the dates in the cells and add 7 days, then make new event from this with certain properties? For example…if cell F10 has 5/10/07 as the data. How can i tell applescript to take that data, add 7 days (5/17/07), and make new event with alarm and name, etc.

  2. The alarms are becoming useless…as i have 50 alarms every day. Instead of adding an alarm for every event, is there a way to just make all events of a specific day (like 7 days in advance) to be listed in a new email? This way i can scan thru the email and remove the unneeded alarms

Hi bluenote,

my solution filters all data and creates one list for each date with the matching titles of the titles list
then you have only one event (seven days later) per occuring date. I didn’t change the kind of the alarm,
I think, you are able to change it to a mail alarm :wink:

property the_titles : {"All Tooling Patterns in HK", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling PO in HK", "Tooling Start", "Paintmaster in HK", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "Packaging Artwork in HK", "EP Samples", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}

on adding folder items to this_folder after receiving added_items
	tell application "Microsoft Excel"
		repeat with anitem in added_items
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4" as string
			set these_Dates to (get value of range "D12:Z12")
			set {dateList, valueList} to {{}, {}}
			repeat with i from 1 to count these_Dates
				set t to item 1 of item i of these_Dates
				if t is not "N/A" and t is not "" and t is not "NA" and t is not "on hold" then
					if t is in dateList then
						repeat with j from 1 to count dateList
							if t is item j of dateList then
								set item j of valueList to item j of valueList & return & item i of the_titles
								exit repeat
							end if
						end repeat
					else
						set {end of dateList, end of valueList} to {t, {item i of the_titles}}
					end if
				end if
			end repeat
			my Schedule(ProjectName, ReviseDate, dateList, valueList)
			close front window
		end repeat
	end tell
end adding folder items to

on Schedule(ProjectName, ReviseDate, dL, vL)
	tell application "iCal"
		if (exists calendar ProjectName) then delete calendar ProjectName
		set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
		tell theCal to repeat with i from 1 to count dL
			set t_date to item i of dL
			set t_descr to ReviseDate & ":" & return & item i of vL
			tell (make new event at end of events with properties {start date:t_date + 7 * days, end date:t_date + (7 * days) + minutes, summary:ReviseDate, location:ProjectName, allday event:true, status:confirmed, description:t_descr})
				make new display alarm at end of display alarms with properties {trigger interval:-10080}
			end tell
		end repeat
		quit
	end tell
end Schedule

so i altered your script a bit to meet my requirements:


property the_titles : {"All Tooling Patterns in HK", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling PO in HK", "Tooling Start", "Paintmaster in HK", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "Packaging Artwork in HK", "EP Samples", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}

on adding folder items to this_folder after receiving added_items
	tell application "Microsoft Excel"
		repeat with anitem in added_items
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4" as string
			set these_Dates to (get value of range "D12:Z12")
			set {dateList, valueList} to {{}, {}}
			set TriDate to the value of cell "$T$12" as string
			set TriTitle to the value of "TRI Packaging Artwork in HK"
			repeat with i from 1 to count these_Dates
				set t to item 1 of item i of these_Dates
				if t is not "N/A" and t is not "" and t is not "NA" and t is not "on hold" then
					if t is in dateList then
						repeat with j from 1 to count dateList
							if t is item j of dateList then
								set item j of valueList to item j of valueList & return & item i of the_titles
								exit repeat
							end if
						end repeat
					else
						set {end of dateList, end of valueList} to {t, {item i of the_titles}}
					end if
				end if
			end repeat
			my Schedule(ProjectName, ReviseDate, dateList, valueList)
			close front window
		end repeat
	end tell
end adding folder items to

on Schedule(ProjectName, ReviseDate, dL, vL)
	tell application "iCal"
		if (exists calendar ProjectName) then delete calendar ProjectName
		set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
		tell theCal to repeat with i from 1 to count dL
			set t_date to item i of dL
			set t_descr to ReviseDate & ":" & return & item i of vL
			tell (make new event at end of events with properties {start date:t_date, end date:t_date, summary:ReviseDate, location:ProjectName, allday event:true, status:confirmed, description:t_descr})
				make new display alarm at end of display alarms with properties {trigger interval:-10080}
			end tell
			tell (make new event at end of events with properties {start date:TriDate + 7 * days, end date:TriDate + (7 * days), summary:ReviseDate, location:ProjectName, allday event:true, status:confirmed, description:TriTitle})
				make new display alarm at end of display alarms with properties {trigger interval:-10080}
			end tell
		end repeat
		quit
	end tell
end Schedule

But…alas its not working…now excel loads the milestone file…but then doesn’t do anything else

BTW…i dont want to do an email alarm for each event, but rather do 1 email each day that combines in list format ALL events for the 7th day from now. for example…today if i opened entourage, it would have a new message opened with a list of all events for the 5/16. Then i can manually edit that email to make my team aware of the important things…

sorry about this, without having a source file with the proper data structure
it’s hard to simulate it. :wink:

linked below is a sample milestone that would need to work

http://www.bluenotedesigns.com/projectonemilestone.xls

each stage should be entered into ical or entourage calendars (which ever you can do).
The “packaging artwork in HK” is the one where i need an additional date entered 7 days later with an alternate title “TRI Packaging Artwork Due in HK”.

BTW…if you are bored:
i also want to minimize the events by combining the same date events into 1 event for each project. this means ProjectONE events that all fall on 5/10 would appear as 1 event on 5/10, even if there is 4 or 5 stages, and the title of the event would be changed to “TOOLINGS” or something like that effect

The requested URL /projectonemilestone.xls was not found on this server.

if you right click and select to save as…;it should work for below link:

http://www.bluenotedesigns.com/ProjectONEmilestone.xls

weird that the caps matter for this…as i thought it wouldnt

Now I could download the file.
If you have consecutive dates, my idea to filter the dates is nonsense.
So I came back to your original script.

But I’m very sorry, I don’t get the plot of the whole thing.
Your added TriDate event makes as many new same events as numbers of the_titles.???

This version works at least with your Excel file,
I removed the handler because there were too many parameters to pass.

Note: if you define the same value of the start date and end date, the events are not really visible in iCal

property the_titles : {"All Tooling Patterns in H.K.", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling P.O.s in H.K.", "Tooling start", "Paintmaster & Artwork in H.K.", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "Packaging Artwork in HK", "EP Samples", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}

on adding folder items to this_folder after receiving added_items
	repeat with anitem in added_items
		tell application "Microsoft Excel"
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4" as string
			set these_Dates to item 1 of (get value of range "D12:Z12")
			set {dateList, valueList} to {{}, {}}
			set TriDate to the value of cell "$T$12"
			set TriTitle to "TRI Packaging Artwork in HK"
		end tell
		
		tell application "iCal"
			if (exists calendar ProjectName) then delete calendar ProjectName
			set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
			set tc to count the_titles
			tell theCal to repeat with i from 1 to tc
				set t_date to item i of these_Dates
				if (t_date is not "N/A" and t_date is not "" and t_date is not "NA" and t_date is not "on hold") then ¬
					tell (make new event at end of events with properties {start date:t_date, end date:t_date, summary:ReviseDate, location:ProjectName, allday event:true, status:confirmed, description:ReviseDate})
						make new display alarm at end of display alarms with properties {trigger interval:-10080}
					end tell
				tell (make new event at end of events with properties {start date:TriDate + 7 * days, end date:TriDate + (7 * days), summary:ReviseDate, location:ProjectName, allday event:true, status:confirmed, description:TriTitle})
					make new display alarm at end of display alarms with properties {trigger interval:-10080}
				end tell
			end repeat
		end tell
		tell application "Microsoft Excel" to close front window
	end repeat
	quit application "iCal"
end adding folder items to