Export a calendar into an excel sheet

Hi!

I´m new to Applescript and need some help. Not sure about the possibilities, but I´ll describe my dream scenario and maybe someone can correct me or help me :slight_smile:

Every Friday at 5 PM a specific calendar is exported. It includes all the events in that actual week, from Monday to Friday. The export includes Date, Start time, End Time, hh.mm, Description and eventual attendees of every event. This information is sent to certain excel file/sheet and ads to the previous week in pre-defined columns.

Is that doable? Whats your reflections?

Thanks,

/Fabian

Model: MacBook Air
AppleScript: 2.5
Browser: Safari 537.36
Operating System: Mac OS X (10.10)

Here is just a starting point :

property forTests : true

on run
	if forTests then
		"4/12/2015 17:00"
		set today to date result
	else
		set today to current date # You wrote that your script is ran on Friday
	end if
	
	set weekBeg to today - (4 * days)
	set time of weekBeg to 0
	copy today to weekAfter
	set time of weekAfter to 0
	
	tell application "Calendar"
		activate
		set eventsFound to events of calendar "Work" whose start date > weekBeg and start date < weekAfter
	
		
		set eventsProps to {}
		repeat with anEvent in eventsFound
			tell anEvent
				set {sDate, eDate, aSummary, aDescription} to {start date, end date, summary, description} # You may extract different properties
			end tell
			set sDate to short date string of sDate & space & time string of sDate
			set eDate to short date string of eDate & space & time string of eDate
			set end of eventsProps to {sDate, eDate, aSummary, aDescription}
		end repeat
	end tell
	# Sort the list by ascending start date of the events
	set eventsProps to my classort(eventsProps, cmpAscLists)
	
	# Now it's your duty to drive Excel
end run

#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

(*
Tri par classement
-----------------
Peut s'employer sur les petites listes <= 20 éléments à cause de son code compact
Implémentation: L. Sebilleau & D. Varlet
*)

on classort(lista, fcmp)
	if (count lista) < 2 then return lista
	
	script listb
		property liste : lista
		property Compare : fcmp
	end script
	
	repeat with i from 2 to count of listb's liste
		set cle to item i of listb's liste
		repeat with j from i - 1 to 1 by -1
			if listb's Compare(cle, item j of listb's liste) then
				set item (j + 1) of listb's liste to item j of listb's liste
			else
				set j to j + 1
				exit repeat
			end if
		end repeat
		set item j of listb's liste to cle
	end repeat
	return listb's liste
end classort

----------- les fonctions de comparaison ------------

on cmpAsc(n1, n2) -- pour tri ascendant des nombres et des chaînes # USED
	return n1 < n2
end cmpAsc

on cmpDesc(n1, n2) -- pour tri descendant des nombres et des chaînes
	return n1 > n2
end cmpDesc

on cmpLengthAsc(n1, n2) -- pour tri par longueur croissante
	return (count (n1 as text)) < (count (n2 as text))
end cmpLengthAsc

on cmpLengthDesc(n1, n2) -- pour tri par longueur décroissante # USED
	return (count (n1 as text)) > (count (n2 as text))
end cmpLengthDesc

on cmpAscLists(L1, L2) -- pour tri ascendant des listes suivant leur 1er élément # USED
	return L1's item 1 < L2's item 1
end cmpAscLists

on cmpDescLists(L1, L2) -- pour tri descendant des listes suivant leur 1er élément # USED
	return L1's item 1 < L2's item 1
end cmpDescLists

---------------------------------------------

Yvan KOENIG running El Capitan 10.11.1 in French (VALLAURIS, France) dimanche 6 décembre 2015 16:08:56

Wow! Thanks!
Is there a way to put a comma between the date and the time so that I can put that info in different columns in Excel? I also tried to ad Invitees as well but it didn´t work.

Regards,
/Fabian

I dislike the use of comma as delimiter because it doesn’t apply when running in french.
Here I use TAB to separate the values.

property forTests : true

on run
	if forTests then
		"11/12/2015 17:00"
		set today to date result
	else
		set today to current date # You wrote that your script is ran on Friday
	end if
	
	set weekBeg to today - (4 * days)
	set time of weekBeg to 0
	copy today to weekAfter
	set time of weekAfter to 0
	
	tell application "Calendar"
		activate
		set eventsFound to events of calendar "Work" whose start date > weekBeg and start date < weekAfter
		
		set eventsProps to {}
		repeat with anEvent in eventsFound
			tell anEvent
				set {sDate, eDate, aSummary, aDescription} to {start date, end date, summary, description} # You may extract different properties
				set itsAttendees to {}
				set theAttendees to its attendees
				repeat with anAttendee in theAttendees
					tell anAttendee
						# CAUTION, here I get an odd result. The grabbed values aren't the waited ones.
						set aDisplayName to display name
						-- log aDisplayName
						set anEmail to email
						-- log anEmail
						set aParticipationStatus to participation status
						-- log aParticipationStatus
					end tell
					set aRecord to my recolle({aDisplayName, anEmail, aParticipationStatus}, " - ")
					set end of itsAttendees to aRecord
				end repeat
				set itsAttendees to my recolle(itsAttendees, " | ")
			end tell
			set sDate to short date string of sDate & space & time string of sDate
			set eDate to short date string of eDate & space & time string of eDate
			set end of eventsProps to my recolle({sDate, eDate, aSummary, aDescription, itsAttendees}, tab) # You may replace tab by ","
		end repeat
	end tell
	# Sort the list by ascending start date of the events
	--set eventsProps to my classort(eventsProps, cmpAscLists) # Used when it was a list of lists
	set eventsProps to my classort(eventsProps, cmpAsc) # Now the list is a list of strings
	# Concatenate the events descriptors with linefeed separators
	set eventsProps to my recolle(eventsProps, linefeed)
	# Now it's your duty to drive Excel
end run

#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

(*
Tri par classement
-----------------
Peut s'employer sur les petites listes <= 20 éléments à cause de son code compact
Implémentation: L. Sebilleau & D. Varlet
*)

on classort(lista, fcmp)
	if (count lista) < 2 then return lista
	
	script listb
		property liste : lista
		property Compare : fcmp
	end script
	
	repeat with i from 2 to count of listb's liste
		set cle to item i of listb's liste
		repeat with j from i - 1 to 1 by -1
			if listb's Compare(cle, item j of listb's liste) then
				set item (j + 1) of listb's liste to item j of listb's liste
			else
				set j to j + 1
				exit repeat
			end if
		end repeat
		set item j of listb's liste to cle
	end repeat
	return listb's liste
end classort

----------- les fonctions de comparaison ------------

on cmpAsc(n1, n2) -- pour tri ascendant des nombres et des chaînes # USED
	return n1 < n2
end cmpAsc

on cmpDesc(n1, n2) -- pour tri descendant des nombres et des chaînes
	return n1 > n2
end cmpDesc

on cmpLengthAsc(n1, n2) -- pour tri par longueur croissante # USED
	return (count (n1 as text)) < (count (n2 as text))
end cmpLengthAsc

on cmpLengthDesc(n1, n2) -- pour tri par longueur décroissante # USED
	return (count (n1 as text)) > (count (n2 as text))
end cmpLengthDesc

on cmpAscLists(L1, L2) -- pour tri ascendant des listes suivant leur 1er élément # USED
	return L1's item 1 < L2's item 1
end cmpAscLists

on cmpDescLists(L1, L2) -- pour tri descendant des listes suivant leur 1er élément
	return L1's item 1 < L2's item 1
end cmpDescLists

---------------------------------------------

#=====

on recolle(l, d)
	local oTIDs, t
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end recolle

#=====

I’m puzzled because I have no idea to explain why the properties of attendees aren’t the correct ones.

Yvan KOENIG running El Capitan 10.11.1 in French (VALLAURIS, France) dimanche 6 décembre 2015 22:14:09