Cant get item 2 of date???

bluenote · September 17, 2010, 4:38am

having trouble updating my script for transferring excel milestones into ical todo’s. Below is my script, and below that is the error log. The excel milestone is formated correctly, just like my previous versions that were working. The only thing i changed was going from a horizontal date list, to a vertical date list. Any help ASAP is appreciated.

Here is my code:

property summaryList : {"Rough Ceramics", "Tooling Quote", "Casting Ceramic", "Tooling PO", "Paint Photos (MASS)", "Blister Layout", "Production Quote", "Vendor Confirmed", "Paint Master", "Decals (MASS)", "Paint Photos (CL)", "Decals (CL)", "1st Shots (New Figures)", "EP Shots (Old Figures)", "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 to item 1 of (get value of range "E4:E37")
	set {RoughCeramics, blind, ToolingQuote, CastingCeramic, blind, ToolingPO, blind, PaintPhotosMASS, BlisterLayout, ProductionQuote, VendorConfirmed, PaintMaster, DecalsMASS, DecalsCL, PaintPhotosCL, FirstShotsNew, EPShotsOld, 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 "iCal"
	if not (exists calendar ProjectName) then
		tell (make new calendar at end of calendars with properties {name:ProjectName})
			set its color to {13500, 28500, 48500}
		end tell
	end if
	set dateList to {RoughCeramics, ToolingQuote, CastingCeramic, ToolingPO, PaintPhotosMASS, BlisterLayout, ProductionQuote, VendorConfirmed, PaintMaster, DecalsMASS, DecalsCL, PaintPhotosCL, FirstShotsNew, EPShotsOld, 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)
		if class of theDate is date then
			if theTodo is false then
				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
		set yr to yr mod 1000 + 2000
		-- old line for above-- 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
						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

and here is my error log:

tell application "Microsoft Excel"
	get value of cell "$B$2"
		--> "Project Alpha"
	get value of range "E4:E37"
		--> {{date "Monday, September 27, 2010 12:00:00 AM"}, {date "Thursday, September 30, 2010 12:00:00 AM"}, {date "Monday, October 11, 2010 12:00:00 AM"}, {date "Monday, October 11, 2010 12:00:00 AM"}, {date "Thursday, October 14, 2010 12:00:00 AM"}, {date "Thursday, October 14, 2010 12:00:00 AM"}, {date "Thursday, October 21, 2010 12:00:00 AM"}, {date "Tuesday, October 26, 2010 12:00:00 AM"}, {date "Tuesday, October 26, 2010 12:00:00 AM"}, {date "Tuesday, November 2, 2010 12:00:00 AM"}, {date "Tuesday, November 9, 2010 12:00:00 AM"}, {date "Tuesday, November 9, 2010 12:00:00 AM"}, {date "Tuesday, November 9, 2010 12:00:00 AM"}, {"N/A"}, {"N/A"}, {date "Tuesday, November 30, 2010 12:00:00 AM"}, {date "Thursday, December 2, 2010 12:00:00 AM"}, {date "Monday, December 20, 2010 12:00:00 AM"}, {date "Tuesday, December 21, 2010 12:00:00 AM"}, {date "Tuesday, December 21, 2010 12:00:00 AM"}, {date "Friday, December 24, 2010 12:00:00 AM"}, {date "Monday, December 27, 2010 12:00:00 AM"}, {date "Friday, December 31, 2010 12:00:00 AM"}, {date "Friday, December 31, 2010 12:00:00 AM"}, {date "Friday, December 31, 2010 12:00:00 AM"}, {date "Wednesday, January 5, 2011 12:00:00 AM"}, {date "Wednesday, January 5, 2011 12:00:00 AM"}, {date "Wednesday, January 5, 2011 12:00:00 AM"}, {date "Friday, January 7, 2011 12:00:00 AM"}, {date "Wednesday, January 12, 2011 12:00:00 AM"}, {date "Wednesday, January 12, 2011 12:00:00 AM"}, {date "Monday, January 17, 2011 12:00:00 AM"}, {date "Wednesday, January 19, 2011 12:00:00 AM"}, {date "Monday, January 31, 2011 12:00:00 AM"}}
Result:
error "Can't get item 2 of {date \"Monday, September 27, 2010 12:00:00 AM\"}." number -1728 from item 2 of {date "Monday, September 27, 2010 12:00:00 AM"}

StefanK · September 17, 2010, 8:05am

Hi,

in the error message you can see that the value of range_value is a list containing lists
with one element. So there is never an item 2.

As the list elements contain only text, you can flatten the list with for example

set {TID, text item delimiters} to {text item delimiters, return}
set range_value to range_value as text
set text item delimiters to TID
set range_value to paragraphs of range_value

Nigel_Garvey · September 17, 2010, 8:59am

If I can rephrase Stefan’s explanation without stepping on his toes, in this section of code.

tell application "Microsoft Excel"
¨	set ProjectName to the value of cell "$B$2"
¨	set range_value to item 1 of (get value of range "E4:E37")¨
	set {RoughCeramics, blind, ToolingQuote, CastingCeramic, blind, ToolingPO, blind, PaintPhotosMASS, BlisterLayout, ProductionQuote, VendorConfirmed, PaintMaster, DecalsMASS, DecalsCL, PaintPhotosCL, FirstShotsNew, EPShotsOld, 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

. ‘(get value of range “E4:E37”)’ returns the list of lists (each containing a date) you see in your log. But ‘range_value’ is only set to item 1 of that list ” namely the list {date “Monday, September 27, 2010 12:00:00 AM”}.

In the following line, you try to set all those variables to the items of ‘range_value’, which only has one item. The error occurs when AppleScript tries to set ‘blind’ to the non-existent item 2.

You probably just need to drop ‘item 1 of’ from the ‘set range_value’ line. That way, each of the variables in the following line will be set to one of the date-containing lists from the range.

I haven’t looked at the rest of the script.

bluenote · September 17, 2010, 2:38pm

thanks to both of you…but still confused. I apologize for not being very good with this stuff.
The funny thing is … StefankK is the one who basically wrote this script for me a long time ago :P.
i tried modifying it to our new milestone template, and the wierd thing is thats the area i didnt modify really (except for the range). But the range_value is the same data as was grabbed from previous versions… setting range_value to item 1

anyways…
– i tried removing “item 1 of” but it results in a new calendar with no todos added.
– i tried changing to “items of” but same result.

and im not sure for Stefans suggestion if i add all those lines somewhere? or replace something? so sorry guys.

StefanK · September 17, 2010, 3:11pm

The little but important difference between the old script and this one is the arrangement of the cells.
In the former one the range is horizontal, in this one the range is vertical.

The best way is to flatten the nested list with a repeat loop


tell application "Microsoft Excel"
   set ProjectName to the value of cell "$B$2"
	set range_value_List to (get value of range "E4:E37")
	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, BlisterLayout, ProductionQuote, VendorConfirmed, PaintMaster, DecalsMASS, DecalsCL, PaintPhotosCL, FirstShotsNew, EPShotsOld, POBreakdown, Mockups, EPShotsNew, firstDeco, PO, blind, blind, PackagingFiles, Chromalins, BlisterRelease, InjectionRelease, blind, DecoRelease, PressProofs, blind, PrintingRelease, PP} to range_value
	close front window
.