How to get the earliest date object from a list of dates and times

Scripting Forums AppleScript | Mac OS X
#1

I’m stuck on trying to work with this dataset. It’s a three dimensional array. A list of a list of a list.

It’s a list of exam attempts from three students. Each student has multiple attempts. The attempt time (date object) is buried in the item 6 of the last level of the list.

For each student, I’m trying to get AppleScript to return the student number(item 1) of the earliest attempt.

The hierarchy of the data looks like this:

studentData
student1
attempt1
item 1 studentID
item 2 questions answered
item 3 questions correct
item 4 date time string
item 5 time string
item 6 date time object
student1
attempt2…
student2…
attempt1

Here’s the data:


set studentData to {{{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", date "Friday, June 19, 2020 at 4:07:04 PM"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", date "Friday, June 19, 2020 at 4:22:48 PM"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", date "Friday, June 19, 2020 at 4:31:38 PM"}}, {{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", date "Friday, June 19, 2020 at 4:07:04 PM"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", date "Friday, June 19, 2020 at 4:22:48 PM"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", date "Friday, June 19, 2020 at 4:31:38 PM"}}, {{"10701580059", "42", "33", "2020-06-19 16:08:37", "16:08:37", date "Friday, June 19, 2020 at 4:08:37 PM"}, {"10701580059", "48", "39", "2020-06-19 16:33:07", "16:33:07", date "Friday, June 19, 2020 at 4:33:07 PM"}}}
#2

Hi.

set studentData to {{{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", date "Friday, June 19, 2020 at 4:07:04 PM"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", date "Friday, June 19, 2020 at 4:22:48 PM"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", date "Friday, June 19, 2020 at 4:31:38 PM"}}, {{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", date "Friday, June 19, 2020 at 4:07:04 PM"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", date "Friday, June 19, 2020 at 4:22:48 PM"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", date "Friday, June 19, 2020 at 4:31:38 PM"}}, {{"10701580059", "42", "33", "2020-06-19 16:08:37", "16:08:37", date "Friday, June 19, 2020 at 4:08:37 PM"}, {"10701580059", "48", "39", "2020-06-19 16:33:07", "16:33:07", date "Friday, June 19, 2020 at 4:33:07 PM"}}}

set earliestDate to (current date) -- Assuming the script's run after the dates in the lists.
set studentNumber to missing value
repeat with sublist in studentData
	repeat with subsublist in sublist
		set thisDate to item 6 of subsublist
		if (thisDate comes before earliestDate) then
			set earliestDate to thisDate
			set studentNumber to item 1 of subsublist
		end if
	end repeat
end repeat
return {studentNumber, earliestDate}
#3

I read the request differently (“For each student”), so I think this might be closer:

set studentData to {{{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", date "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", date "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", date "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", date "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", date "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", date "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10701580059", "42", "33", "2020-06-19 16:08:37", "16:08:37", date "Friday, June 19, 2020 at 4:08:37 pm"}, {"10701580059", "48", "39", "2020-06-19 16:33:07", "16:33:07", date "Friday, June 19, 2020 at 4:33:07 pm"}}}

set studentNumber to missing value
set theResult to {}
repeat with sublist in studentData
	set earliestDate to (current date) -- Assuming the script's run after the dates in the lists.
	repeat with subsublist in sublist
		set thisDate to item 6 of subsublist
		if (thisDate comes before earliestDate) then
			set earliestDate to thisDate
			set studentNumber to item 1 of subsublist
		end if
	end repeat
	set end of theResult to {studentNumber, earliestDate}
end repeat
return theResult
#4

Hello

As I’m working in French I can’t use the original set of datas so I dropped the word date and choose to work upon the dates using the ISO format.

use AppleScript version "2.5"
use framework "Foundation"
use scripting additions
on dateFromString:aString usingFormat:formatString
	set theFormatter to current application's NSDateFormatter's new()
	theFormatter's setDateFormat:formatString
	set theDate to theFormatter's dateFromString:aString
	return theDate as date
end dateFromString:usingFormat:

set studentData to {{{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10701580059", "42", "33", "2020-06-19 16:08:37", "16:08:37", "Friday, June 19, 2020 at 4:08:37 pm"}, {"10701580059", "48", "39", "2020-06-19 16:33:07", "16:33:07", "Friday, June 19, 2020 at 4:33:07 pm"}}}

set studentNumber to missing value
set theResult to {}
repeat with sublist in studentData
	set earliestDate to (current date) -- Assuming the script's run after the dates in the lists.
	repeat with subsublist in sublist
		set thisDate to item 4 of subsublist
		set thisdate to (its dateFromString:thisDate usingFormat:"yyyy-MM-dd' 'HH:mm:ss")
		if (thisDate comes before earliestDate) then
			set earliestDate to thisDate
			set studentNumber to item 1 of subsublist
		end if
	end repeat
	set end of theResult to {studentNumber, earliestDate}
end repeat
return theResult

The result was :
{{“10909650005”, date “vendredi 19 juin 2020 à 16:07:04”}, {“10909650005”, date “vendredi 19 juin 2020 à 16:07:04”}, {“10701580059”, date “vendredi 19 juin 2020 à 16:08:37”}}

My understanding is that one of the two lists returned for the first student was supposed to be dropped.

I think also that we are supposed to return the item 2 and item 3 (the notes) belonging to the retained exam.

I don’t continue to play with that because the original question was about three students and there are only two in the given datas.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 21 juin 2020 15:45:11

#5

As I am curious I edited the datas to have three different students and to report what I assume to be the useful datas.

use AppleScript version "2.5"
use framework "Foundation"
use scripting additions
on dateFromString:aString usingFormat:formatString
	set theFormatter to current application's NSDateFormatter's new()
	theFormatter's setDateFormat:formatString
	set theDate to theFormatter's dateFromString:aString
	return theDate as date
end dateFromString:usingFormat:

set studentData to {{{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10701580059", "42", "33", "2020-06-19 16:08:37", "16:08:37", "Friday, June 19, 2020 at 4:08:37 pm"}, {"10701580059", "48", "39", "2020-06-19 16:33:07", "16:33:07", "Friday, June 19, 2020 at 4:33:07 pm"}}}
-- modified to have three different students
set studentData to {{{"10909650005", "48", "34", "2020-06-19 16:07:04", "16:07:04", "Friday, June 19, 2020 at 4:07:04 pm"}, {"10909650005", "50", "34", "2020-06-19 16:22:48", "16:22:48", "Friday, June 19, 2020 at 4:22:48 pm"}, {"10909650005", "50", "35", "2020-06-19 16:31:38", "16:31:38", "Friday, June 19, 2020 at 4:31:38 pm"}}, {{"10909650006", "48", "30", "2020-06-19 16:08:04", "16:08:04", "Friday, June 19, 2020 at 4:08:04 pm"}, {"10909650006", "50", "30", "2020-06-19 16:23:48", "16:23:48", "Friday, June 19, 2020 at 4:23:48 pm"}, {"10909650006", "50", "30", "2020-06-20 16:31:38", "16:31:38", "Friday, June 20, 2020 at 4:31:38 pm"}}, {{"10701580059", "42", "33", "2020-06-19 16:08:37", "16:08:37", "Friday, June 19, 2020 at 4:08:37 pm"}, {"10701580059", "48", "39", "2020-06-19 16:33:07", "16:33:07", "Friday, June 19, 2020 at 4:33:07 pm"}}}

set descriptor to missing value
set theResult to {}
repeat with subList in studentData
	set earliestDate to (current date) -- Assuming the script's run after the dates in the lists.
	repeat with subsublist in subList
		set thisDate to item 4 of subsublist
		set thisDate to (its dateFromString:thisDate usingFormat:"yyyy-MM-dd' 'HH:mm:ss")
		if (thisDate comes before earliestDate) then
			set earliestDate to thisDate
			set descriptor to {item 1 of subsublist, item 2 of subsublist, item 3 of subsublist, earliestDate}
		end if
	end repeat --  with subsublist
	set end of theResult to descriptor
end repeat -- with i
return theResult
(*
{{"10909650005", "48", "34", date "vendredi 19 juin 2020 à 16:07:04"}, {"10909650006", "48", "30", date "vendredi 19 juin 2020 à 16:08:04"}, {"10701580059", "42", "33", date "vendredi 19 juin 2020 à 16:08:37"}}
*)

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 21 juin 2020 17:08:13

#6

Ah. I think you’re right. Thanks, Shane.

#7

Thanks everybody. I was able to loop through each item and identify the item with the earliest entry. I got it to return the scores instead of the student ID number. My dataset had a duplicate student because I had a bug in an earlier handler that finds duplicates. I can’t post my code now because I’m on an iPad right now, and there appears to be no app that can open .scpt files.

#8

Here’s the script I used to transfer grades from one Excel sheet to another, that also handles multiple exam attempt.


tell application "Microsoft Excel"
	set students to {}
	tell workbook 1
		tell worksheet 1
			repeat with i from 2 to count of rows of used range
				if the (count of ((value of cell ("P" & i)) as string)) is 15 then #Make sure student ID is 15 digits
					if the value of cell ("J" & i) as integer is greater than 41 then #Make sure at least 42 questions were answered
						set end of students to {value of cell ("P" & i), value of cell ("J" & i), value of cell ("K" & i), value of cell ("D" & i)}
					end if
				end if
			end repeat
		end tell
	end tell
end tell

#find duplicates scores and put it into the list named duplicates
set noDuplicates to {}
set duplicates to {}
repeat with student in students
	if item 1 of student is not in noDuplicates then
		set end of noDuplicates to item 1 of student
	else
		if item 1 of student is not in duplicates then
			set end of duplicates to item 1 of student
		end if
	end if
end repeat

#create a list of students with multiple exam attempts
set duplicatesWithData to {}
repeat with i from 1 to count of duplicates
	set end of duplicatesWithData to {}
	repeat with j from 1 to count of students
		if item i of duplicates is in item 1 of item j of students then
			set end of item i of duplicatesWithData to item j of students
		end if
	end repeat
end repeat

#convert date time string to date object and add it to the list
repeat with duplicates in duplicatesWithData
	repeat with duplicat in duplicates
		set end of duplicat to convertDate(item 4 of duplicat)
	end repeat
end repeat

#get earliest time
set earliest to current date
set earliestData to {}
repeat with i from 1 to count of duplicatesWithData
	set earliest to current date
	repeat with j from 1 to count of item i of duplicatesWithData
		if item 5 of item j of item i of duplicatesWithData comes before earliest then
			set earliest to item 5 of item j of item i of duplicatesWithData
			set end of earliestData to {¬
				item 1 of item j of item i of duplicatesWithData, ¬
				item 2 of item j of item i of duplicatesWithData, ¬
				item 3 of item j of item i of duplicatesWithData}
		end if
	end repeat
end repeat

#add earliest data from duplicate data to final student list to be used to add data to excel

repeat with earliest in earliestData
	set end of students to {¬
		item 1 of earliest, ¬
		item 2 of earliest, ¬
		item 3 of earliest}
end repeat

tell application "Microsoft Excel"
	# Enter scores of all students, will include the latest attempt for duplicates - which will be fixed later.
	repeat with student in students
		tell workbook 2
			repeat with i from 1 to count of worksheets
				tell worksheet i
					try
						set targetRow to first row index of (find used range what (item 1 of student))
						set value of cell ("G" & targetRow) to item 2 of student
						set value of cell ("H" & targetRow) to item 3 of student
						exit repeat
					end try
				end tell
			end repeat
		end tell
	end repeat
end tell

-- Convert date function. Call with string in YYYY-MM-DD HH:MM:SS format (time part optional)
to convertDate(textDate)
	
	set resultDate to the current date
	set the month of resultDate to (1 as integer)
	set the day of resultDate to (1 as integer)
	
	set the year of resultDate to (text 1 thru 4 of textDate)
	set the month of resultDate to (text 6 thru 7 of textDate)
	set the day of resultDate to (text 9 thru 10 of textDate)
	set the time of resultDate to 0
	
	if (length of textDate) > 10 then
		set the hours of resultDate to (text 12 thru 13 of textDate)
		set the minutes of resultDate to (text 15 thru 16 of textDate)
		
		if (length of textDate) > 16 then
			set the seconds of resultDate to (text 18 thru 19 of textDate)
		end if
	end if
	
	return resultDate
end convertDate