OK. This explicitly adds each contact to the specified container (account) and to the specified group there — assuming the Xcode documentation’s correct and I haven’t introduced any more typos when copying from my test script!
use AppleScript version "2.5" -- Mac OS 10.11 (El Capitan) or later.
use framework "Foundation"
use framework "Contacts"
use scripting additions
property preferredAccount : "On My Mac"
property |⌘| : current application
on main()
set roster to {}
set NA to "#N/A"
activate application "Contacts"
tell application "Microsoft Excel"
activate
set Cohort to string value of cell "Edit!A37"
end tell
-- Identify the target account (container) and group, creating the latter if necessary.
set contactStore to |⌘|'s class "CNContactStore"'s new()
set containerID to setContainer(contactStore)
set targetGroup to setGroup(contactStore, containerID, Cohort)
-- Initialise a save request to be added to during the repeat below.
set saveRequest to |⌘|'s class "CNSaveRequest"'s new()
tell application "Microsoft Excel"
repeat with i from 2 to 33
if string value of cell ("Edit!B" & i) is NA then
set j to i - 1
exit repeat
end if
end repeat
end tell
repeat with i from 2 to j
-- Get data from Excel into a record.
set TEMP to {key:i - 1, LastName:"", FirstName:"", MI:"", Rank:"", |Hash|:"", |Nickname|:"", Srvc:"", Corps:"", Addressline1:"", |City|:"", |State|:"", |Zip|:"", HomePhone:"", |Email|:"", ParentMilitaryCommand:"", MobilePhone:"", WorkPhone:""}
tell application "Microsoft Excel"
set LastName of TEMP to string value of cell ("Edit!B" & i)
set FirstName of TEMP to string value of cell ("Edit!C" & i)
set MI of TEMP to string value of cell ("Edit!D" & i)
set Rank of TEMP to string value of cell ("Edit!F" & i)
set |Hash| of TEMP to string value of cell ("Edit!G" & i)
set |Nickname| of TEMP to string value of cell ("Edit!H" & i)
set Srvc of TEMP to string value of cell ("Edit!J" & i)
set Corps of TEMP to string value of cell ("Edit!M" & i)
if Corps of TEMP is NA then set Corps of TEMP to ""
set Addressline1 of TEMP to string value of cell ("Edit!N" & i)
try
if (not cell ("Edit!O" & i) is missing value) and (string value of cell ("Edit!O" & i) ≠ "") then
set Addressline1 of TEMP to Addressline1 of TEMP & ", " & string value of cell ("Edit!O" & i)
--AddressLine2
end if
end try
set |City| of TEMP to string value of cell ("Edit!P" & i)
set |State| of TEMP to string value of cell ("Edit!Q" & i)
set |Zip| of TEMP to string value of cell ("Edit!R" & i)
set HomePhone of TEMP to string value of cell ("Edit!S" & i)
set |Email| of TEMP to string value of cell ("Edit!T" & i)
set ParentMilitaryCommand of TEMP to string value of cell ("Edit!U" & i)
set MobilePhone of TEMP to string value of cell ("Edit!V" & i)
set WorkPhone of TEMP to string value of cell ("Edit!W" & i)
-- (EmailDomain and rightstr() replaced with 'ends with' in makeStudent() below.)
end tell
-- Use the data to create a new "student" (person).
set theStudent to makeStudent(TEMP, Cohort)
-- Add saving the student to the target account and adding it to the target group to the save request.
tell saveRequest to addContact:(theStudent) toContainerWithIdentifier:(containerID)
tell saveRequest to addMember:(theStudent) toGroup:(targetGroup)
-- Add the student's Contacts ID to the end of the roster list.
set the end of roster to (theStudent's identifier()) as text
end repeat
-- When done, execute all the saves stored in the save request. The results appear in Contacts immediately.
tell contactStore to executeSaveRequest:(saveRequest) |error|:(reference)
-- Replace the IDs in 'roster' with the equivalent Contacts 'person' specifiers, which are what it contained in the original script.
tell application "Contacts"
repeat with i from 1 to (count roster)
set item i of roster to person id (item i of roster)
end repeat
end tell
beep
return roster
end main
on setContainer(contactStore)
-- Ask the user to choose a Contacts account. A container doesn't exactly correspond to an account, but they're close enough for the current purposes.
set allContainers to contactStore's containersMatchingPredicate:(missing value) |error|:(missing value)
set containerNames to (allContainers's valueForKey:("name")) as list
set containerChoice to (choose from list containerNames with prompt "Which Contacts account?" default items {preferredAccount})
if (containerChoice is false) then error number -128
-- Get the ID of the container with the chosen name.
set containerFilter to |⌘|'s class "NSPredicate"'s predicateWithFormat:("name == %@") argumentArray:(containerChoice)
set targetContainer to (allContainers's filteredArrayUsingPredicate:(containerFilter))'s firstObject()
return targetContainer's identifier()
end setContainer
on setGroup(contactStore, containerID, groupName)
-- Get the names of the existing groups in this container.
set groupFilter to |⌘|'s class "CNGroup"'s predicateForGroupsInContainerWithIdentifier:(containerID)
set groupsInThisContainer to contactStore's groupsMatchingPredicate:(groupFilter) |error|:(missing value)
set groupNames to groupsInThisContainer's valueForKey:("name")
-- If the name passed to this handler is amongst them, get the corresponding group. Otherwise create a new one.
-- NB. DUPLICATE NAMES ARE ALLOWED IN CONTACTS. IF THE CONTAINER CONTAINS MORE THAN ONE GROUP WITH THE SAME NAME, THIS MAY NOT RETURN THE CORRECT ONE.
if (groupNames's containsObject:(groupName)) then
-- Get the (first) existing group with the given name.
set groupFilter to |⌘|'s class "NSPredicate"'s predicateWithFormat_("name == %@", groupName)
set targetGroup to (groupsInThisContainer's filteredArrayUsingPredicate:(groupFilter))'s firstObject()
else
-- Create a new one.
set targetGroup to |⌘|'s class "CNMutableGroup"'s new()
tell targetGroup to setName:(groupName)
-- Set up a request to save it in the container chosen above.
set saveRequest to |⌘|'s class "CNSaveRequest"'s new()
tell saveRequest to addGroup:(targetGroup) toContainerWithIdentifier:(containerID)
-- Execute the request. The new group appears in the Contacts application instantly (on my machine) without having to quit and reopen it.
tell contactStore to executeSaveRequest:(saveRequest) |error|:(missing value)
end if
-- Return the group (a CNMutableGroup).
return targetGroup
end setGroup
on makeStudent(TEMP, groupName)
-- Make a new contact and set its simpler properties.
set thisPerson to |⌘|'s class "CNMutableContact"'s new()
tell thisPerson to setFamilyName:(TEMP's LastName)
tell thisPerson to setGivenName:(TEMP's FirstName)
tell thisPerson to setMiddleName:(TEMP's MI)
tell thisPerson to setNamePrefix:(TEMP's Rank)
tell thisPerson to setNote:("Cohort: " & groupName & linefeed & "Hash: " & TEMP's |Hash|)
tell thisPerson to setNickname:(TEMP's |Nickname|)
tell thisPerson to setNameSuffix:(TEMP's Srvc)
tell thisPerson to setJobTitle:(TEMP's Corps)
tell thisPerson to setOrganizationName:(TEMP's ParentMilitaryCommand)
-- Set an array for the e-mail.
set emailArray to |⌘|'s class "NSMutableArray"'s new()
set emailAddress to TEMP's |Email|
if (emailAddress > "") then
if ((emailAddress ends with ".mil") or (emailAddress ends with ".gov")) then
set emailLabel to "Work"
else
set emailLabel to "Home"
end if
tell emailArray to addObject:(my makeEmail(emailLabel, emailAddress))
end if
tell thisPerson to setEmailAddresses:(emailArray)
-- An array for the phones.
set phoneArray to |⌘|'s class "NSMutableArray"'s new()
if (TEMP's WorkPhone > "") then tell phoneArray to addObject:(my makePhone("Work", TEMP's WorkPhone))
if (TEMP's MobilePhone > "") then tell phoneArray to addObject:(my makePhone("Mobile", TEMP's MobilePhone))
if (TEMP's HomePhone > "") then tell phoneArray to addObject:(my makePhone("Home", TEMP's HomePhone))
tell thisPerson to setPhoneNumbers:(phoneArray)
-- An array for the address.
set addressArray to |⌘|'s class "NSMutableArray"'s new()
set addressRecord to {street:TEMP's Addressline1, city:TEMP's |City|, state:TEMP's |State|, postalCode:TEMP's |Zip|}
if (addressRecord is not {street:"", city:"", state:"", postalCode:""}) then tell addressArray to addObject:(my makeAddress("Home", addressRecord))
tell thisPerson to setPostalAddresses:(addressArray)
return thisPerson
end makeStudent
on makeEmail(label, emailAddress)
if (emailAddress is "") then return false
return |⌘|'s class "CNLabeledValue"'s labeledValueWithLabel:(label) value:(emailAddress)
end makeEmail
on makePhone(label, phoneNumber)
if (phoneNumber is "") then return false
set thisPhone to |⌘|'s class "CNPhoneNumber"'s phoneNumberWithStringValue:(phoneNumber)
return |⌘|'s class "CNLabeledValue"'s labeledValueWithLabel:(label) value:(thisPhone)
end makePhone
on makeAddress(label, addressRecord)
set emptyValues to {street:"", city:"", state:"", postalCode:"", country:"", countryCode:"", subAdministrativeArea:"", subLocality:""}
set addressRecord to addressRecord & emptyValues
if (addressRecord is emptyValues) then return false
set thisAddress to |⌘|'s class "CNPostalAddress"'s new()
tell thisAddress to setStreet:(addressRecord's street)
tell thisAddress to setCity:(addressRecord's city)
tell thisAddress to setState:(addressRecord's state)
tell thisAddress to setPostalCode:(addressRecord's postalCode)
tell thisAddress to setCountry:(addressRecord's country)
tell thisAddress to setISOCountryCode:(addressRecord's countryCode)
-- The following two properties were introduced in 10.12.4 (Sierra) and still have no equivalents in Contacts 12.0 in Mojave (10.14).
-- Require Mojave or later to set them.
considering numeric strings
if (AppleScript's version ≥ "2.7") then
tell thisAddress to setSubAdministrativeArea:(addressRecord's subAdministrativeArea)
tell thisAddress to setSubLocality:(addressRecord's subLocality)
end if
end considering
return |⌘|'s class "CNLabeledValue"'s labeledValueWithLabel:(label) value:(thisAddress)
end makeAddress
Edit: Modified to make the makeEmail(), makePhone(), and makeAddress() handlers usable more generally. Typos corrected. :rolleyes:
