Write All message IDs from Mail.app to MicrosoftExcel.app

Scripting Forums Code Exchange
#1

I wrote the following script having come across this article: AppleScript finds the same message multiple times.


set {theIDs, theMessageIDs} to {{}, {}}

tell application "Mail"
	repeat with anAccount in (accounts)
		repeat with aMailBox in (mailboxes of anAccount)
			repeat with aMessage in (messages of aMailBox)
				set anID to id of aMessage
				set aMessageID to message id of aMessage
				if anID is not in theIDs then
					set end of theIDs to anID
					set end of theMessageIDs to aMessageID
				end if
			end repeat
		end repeat
	end repeat
end tell

tell application "Microsoft Excel"
	set myExcelWorkbook to (make new workbook)
	set aSheet to active sheet of myExcelWorkbook
	set startRow to 3
	repeat with i from 1 to (count theIDs)
		set anID to item i of theIDs
		set aMessageID to item i of theMessageIDs
		set formula of range ("A" & startRow & ":B" & startRow) of aSheet to [anID, aMessageID]
		set startRow to startRow + 1
	end repeat
end tell
#2

To get rid of the loop in the tell block targeted at Excel…


set {theIDs, theMessageInfo} to {{}, {}}

tell application "Mail"
	repeat with anAccount in (accounts)
		repeat with aMailBox in (mailboxes of anAccount)
			repeat with aMessage in (messages of aMailBox)
				set anID to id of aMessage
				if theIDs does not contain anID then
					set end of theIDs to anID
					set end of theMessageInfo to {anID, message id of aMessage}
				end if
			end repeat
		end repeat
	end repeat
end tell

tell application "Microsoft Excel"
	set myExcelWorkbook to (make new workbook)
	set aSheet to active sheet of myExcelWorkbook
	set startRow to 3
	set value of range ("A" & startRow & ":B" & (startRow + (count of theMessageInfo) - 1)) of aSheet to theMessageInfo
end tell

Since the value of an Excel range is represented in AppleScript by a nested list of lists (each inner list being one row), you can simply create that structure in the loop where you fetch all the IDs and then blast it to the spreadsheet in one fell swoop rather than having to loop through all that data again and target Excel with AppleEvent after AppleEvent. On my machine, just looping through the messages of one mail account took nearly 6 minutes for 3444 messages, so doing that for multiple accounts and then having to re-loop through them all to put them into Excel would probably be a nightmare.

(It would be even faster and easier to do this if we could outsource the handling of duplicates to Excel using the remove duplicates command, but I couldn’t get it to work without throwing up a GUI dialog to select the columns that contain dupes. Oh well.)

#3

Yes, you are right. The repeat loop is not needed a second time, and your script is a clear improvement. But I’m still completely dissatisfied. I will try to improve speed significantly and provide this best solution.

#4

UPDATE.
The following script is much faster than the 2 scripts above:


use AppleScript version "2.4"
use framework "Foundation"
use scripting additions
property NSArray : a reference to current application's class "NSArray"
property NSOrderedSet : a reference to current application's class "NSOrderedSet"

-- GET QUICKLY ALL IDs (nested structure)
tell application "Mail"
	repeat with anAccount in (accounts)
		set MessagesIDs to id of every message of every mailbox of anAccount
		set IDs to message id of every message of every mailbox of anAccount
	end repeat
end tell

-- CONVERT NESTED LISTS TO SIMPLE LISTS
set IDs to ((NSArray's arrayWithArray:(IDs))'s valueForKeyPath:("@unionOfArrays.self")) as list
set MessagesIDs to ((NSArray's arrayWithArray:(MessagesIDs))'s valueForKeyPath:("@unionOfArrays.self")) as list

-- REMOVE ID DUPLICATES
set aSet to NSOrderedSet's orderedSetWithArray:IDs
set IDs to (aSet's array()) as list
set aCount to count IDs
set aSet to NSOrderedSet's orderedSetWithArray:MessagesIDs
set MessagesIDs to (aSet's array()) as list

-- BUILD STRUCTURE TO USE WITH EXCEL
set theMessageInfo to my combineLists({MessagesIDs, IDs})

-- EXPORT TO EXCEL
tell application "Microsoft Excel"
	set aSheet to active sheet of (make new workbook)
	set startRow to 3
	set value of range ("A" & startRow & ":B" & (startRow + aCount - 1)) of aSheet to theMessageInfo
end tell

on combineLists(listOfLists)
	script foo --> speed-up processing
		property theList : listOfLists
	end script
	set nl to {}
	repeat with i from 1 to count foo's theList's item 1
		set nl's end to {}
		repeat with x from 1 to count foo's theList
			set end of nl's item -1 to foo's theList's item x's item i
		end repeat
	end repeat
	nl
end combineLists