Search code examples
applescriptautomator

Populating Applescript choose from list with Excel column data

I'm using Applescript to send emails with outlook on Mac and pulling data from an excel file with the following structure:

enter image description here

My goal is to use all non-empty cells in columns A:C to populate a drop-down list allowing me to select the recipient (placing the corresponding three values for columns A, B, and C into variables I can use later), but I'm receiving this message when I try to run my current script:

“Microsoft Excel got an error: Can’t make {"", "", ""} into type string.”

Here's my Applescript as it currently stands:

on run {input, parameters}
        
    tell application "Microsoft Excel"
        activate
        open file "Macintosh HD:Users:FAKEUSER:Desktop:automations:testlist.xlsx"
        
        set recipientList to (value of range "A100:C100")
        
        set recipientPick to choose from list recipientList with prompt "Select recipient: default items {item 1 of recipientList}"
        recipientPick
                
    end tell
    
    return input
end run
Solution

  • The choose from list requires a list as input, but your variable recipientList if a list of list: a list as as many items as Excel rows of data and each item is a list of 3 strings {surname, givenname, email}.

    You need to adjust your script to build a new list made of concatenation of surname & given name, then you can use that list of string "surname_givenname" in the choose from list command. A second list will be used just base on range C1:C100 to store emails Once the user has made the selection, you must find the number of that selection and take the same item number in the email list.

    Please also note that value of range A100:C100 will just give you a list of 1 item (data the row 100) which is a list {surname, givenname, email}. Instead, you should use range A1:C100 (if no headers).

    tell application "Microsoft Excel"
    activate
    set surname to (value of range "A1:A4")
    set givenname to (value of range "B1:B4")
    set xmail to (value of range "C1:C4")   
end tell

-- build the list for selection
set globalname to {}
repeat with i from 1 to count of surname
    set end of globalname to (i & " " & item i of surname & " " & item i of givenname) as string
end repeat
set recipientPick to choose from list globalname with prompt "Select recipient: default items {item 1 of recipientList}"

-- get number selected assuming single selection !
repeat with i from 1 to count of globalname
    if item i of globalname is (item 1 of recipientPick) then set myindex to i
end repeat

-- get email of same index
set selectedemail to item myindex of xmail
log selectedemail

    variant: you can also use the 1st word of selection (=index) to get email directly