Record software licences in Numbers

I am partway through my exodus from 1Password, brought on by their change to a cloud-based subscription service. 

One of the features of 1Password I used was the recording of Software Licence details.

I decided to create a process whereby I can record the details of software licenses in Numbers, automating as much as possible the extraction of details from the emails I receive on making software purchases.

Generally, software licenses are granted using the name of the purchaser and an email address. This workflow will extract the Date (typically this will be the purchase date), Recipient Name (or default name if not found), Recipient Email address, and a link to the email message and then create a new record in the Numbers document.

The email message will be tagged via MailTags (Purchases in my case) and then moved to a folder for archiving. The Mail window will switch to the folder used to archive the messages.

Mail and Numbers will then be displayed in a split-screen via a Shortcut so the additional data (eg Software name, Licence, etc)  can be entered into Numbers. 

This process is actually easier than using 1Password due to the automation and can be easily be adapted for other email archiving requirements.

The code is below if you would like to try out this process. Click on the following links to get the Numbers template and Shortcut.

The video below demonstrates the process

	##############################################
	# Title: Record Software Licences in Numbers
	##############################################
	# Iain Dunn
	# Logic2Design
	# www.logic2design.com
	# logic2design@icloud.com
	# Last update: 28 January 2022
	# Version: 2 - created MailTag option
	# Contributors and sources
	#
	##############################################
	# Configuration
	##############################################
	set MailTags to 0 -- set to 1 to use MailTags
	property assignedKeywords : {"Purchases"} -- MaiTtag that you would like to allocate
	set purchaser to "Your name" -- Purchaser's Name
	set mail_Account to "iCloud"
	set mail_Archive to "Test"
	##############################################
	# Code
	##############################################
	# Select Mail Message(s)
	tell application "Mail"
	--Get the messages currently selected in Mail
	set the_Messages to selection as list
	if the_Messages is {} then -- check empty list
	display alert "Please select some message first"
	return
	else
	--Iterate over the selected messages
	repeat with the_Message in the_Messages
	set mdate to date sent of the_Message
	set mrec to address of first recipient of the_Message
	set mnam to name of first recipient of the_Message
	set murl to "message://%3C" & message id of the_Message & "%3e"
	if mnam = missing value then
	set mnam to purchaser
	end if
	set the clipboard to (murl as string)
	# Open Numbers
	tell application "Finder"
	--activate
	open (path to home folder as text) & "Library:Mobile Documents:com~apple~Numbers:Documents:Software Licences.numbers"
	end tell
	delay 3
	tell application "Numbers"
	tell front document
	tell active sheet
	set the selectedTable to the first table
	end tell
	tell selectedTable
	add row below last row
	tell last row
	--set value of cell 1 to short date string of (current date)
	set value of cell 1 to short date string of (mdate)
	set format of cell 1 to date and time
	set value of cell 5 to mnam
	set format of cell 5 to automatic
	set value of cell 6 to mrec
	set format of cell 6 to automatic
	set format of cell 7 to automatic
	activate
	my makeMailLink() -- Required to make Mail URL in Numbers clickable
	end tell
	end tell
	end tell
	end tell
	# Archive the Message
	tell application "Mail"
	set flagged status of the_Message to false
	--set mailbox of the_Message to mailbox "Archive" of account mailbox
	move the_Message to mailbox mail_Archive of account mail_Account
	end tell
	if MailTags = 1 then
	#Tag the Message - optional for Mailtag users
	using terms from application "SmallCubed MailSuite"
	set keywords of the_Message to assignedKeywords
	end using terms from
	end if
	end repeat
	end if
	end tell
	# Process new Registrations
	-- Open Archive Folder
	tell application "Mail"
	activate
	set theMailbox to mailbox mail_Archive of account mail_Account
	set selected mailboxes of front message viewer to theMailbox
	--set sort column of front message viewer to date received column -- Not working due to Mail app bug
	--set sorted ascending of front message viewer to false
	end tell
	--Split Screen with the Mail and Numbers app, makes it easier to record additional information from the mail message
	tell application "Shortcuts Events"
	run the shortcut named "Software Registration"
	end tell
	##############################################
	# Functions
	##############################################
	to makeMailLink()
	tell application "System Events"
	keystroke "v" using command down
	end tell
	end makeMailLink

view raw Software Registration - Multiple messages.applescript hosted with ❤ by GitHub