Run the script and extract the values of cells in columns A, B, & C of the selected row to Keyboard Maestro variables.
------------------------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2017/02/07 18:15
# dMod: 2017/02/12 18:54
# Appl: Numbers
# Task: Extract Data from the selected row of the front spreadsheet and enter into KM variables.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Numbers, @Extract, @Data, @Numbers, @Spreadsheet, @Keyboard_Maestro
------------------------------------------------------------------------------
tell application "Numbers"
tell document 1
tell active sheet
---------------------------------------------------------------------
# Ensure there IS a selection:
---------------------------------------------------------------------
try
set activeTable to (the first table whose class of selection range is range)
on error
error "Something is wrong with the selection in the front document."
end try
---------------------------------------------------------------------
# Data Extraction
---------------------------------------------------------------------
tell activeTable
set rowList to rows of selection range
set theRow to item 1 of rowList
set rowNum to (address of theRow) as text
set orderID to formatted value of first cell of range ("A" & rowNum)
set purchaseDate to formatted value of first cell of range ("B" & rowNum)
set purchaseName to formatted value of first cell of range ("C" & rowNum)
end tell
---------------------------------------------------------------------
end tell
end tell
end tell
------------------------------------------------------------------------------
# Enter data into Keyboard Maestro variables.
------------------------------------------------------------------------------
tell application "Keyboard Maestro Engine"
setvariable "orderID" to orderID
setvariable "purchaseDate" to purchaseDate
setvariable "purchaseName" to purchaseName
end tell
------------------------------------------------------------------------------
I was looking for a method to loop a macro through an array of file paths and do something to the files. Files are in folders all over the place so “For Each Path” makes a kind of tricky macro, and the action I want to perform depends on the location.
Your approach of using Numbers as an external array is perfect. I can put the path in “A” (or whatever I want to call “A”), and values in the other columns to tell KM what to do. I think I can use Keyboard Maestro to control “item” in the script and my “Repeat” loop can increment the control.
@korm, I don't use Numbers, but from what Chris (@ccstone) as posted, it seems like a PITA app to deal with.
If most of the work is going to be done in KM, then you could use this approach:
Create a text file (or KM Variable) that is a TAB-delimited file like this: Full Path to fileTABAction to Take
Then in a KM Action "For Each Path", parse the data with this simple RegEx: ^.+\t.+$
into KM Variables: File_Path, Action_to_Take
Of course, you can add as many additional fields/variables as you like, just by adding a TAB to the file, and \t.+ just before the "$" in the RegEx.
If this doesn't make sense let me know, and I'll provide a more concrete example.
Thanks for the suggestions, @JMichaelTX -- much appreciated.
I didn't mention above that the sheet in Numbers I would be parsing in KM is the end product of some other modeling I'm doing in Numbers anyway. I could export to a TAB file as you suggest, but since the data are there already I was interested in exploiting @ccstone's example.
No problem. Then adding to your Numbers sheet may be the best way to do.
Looks like @ccstone has figured out how to get data out of Numbers consistently.
I don't use Numbers much, but I learned a few things while helping someone write some scripts for it and thought I'd share.
Here's a means of gathering file paths from a named table into an AppleScript list object.
This particular table has only one column – column A.
------------------------------------------------------------------------------
tell application "Numbers"
tell front document
tell active sheet
tell table "File Paths"
tell column "A"
set filePathList to formatted value of cells where its value is not missing value
end tell
end tell
end tell
end tell
end tell
------------------------------------------------------------------------------
Office for the Mac (I think) still does mail merge.
There are some dedicated apps, but I don't think I've had an app I liked for this job since before macOS X came along.
I will NOT bet that this still works, but it might:
Contacts is scriptable:
--------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2021/03/08 07:12
# dMod: 2021/03/08 07:12
# Appl: Contacts
# Task: Mail Merge Example
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Contacts, @Mail, @Merge, @Example
--------------------------------------------------------
tell application "Contacts"
tell group "Mail Merge"
set mergeList to every person
end tell
# Single example.
set theContact to last item of mergeList
tell theContact
set personName to name
set emailAddress to value of email 1
set mailingAddress to formatted address of address 1
end tell
set AppleScript's text item delimiters to linefeed
set theMailingLabel to {personName, mailingAddress} as text
# Code to Print or Create Emails...
end tell
--------------------------------------------------------
I might have a few more ideas – if you'll provide more details about what you're doing and how many records you're managing.
I'm trying to simplify the process of entering names and addresses into an online form for a work task, and I augmented this script to do the task, row by row. It works wonders, thank you! I have one question... right now I'm selecting the cells, running the script, then moving to the next row and selecting those cells, then running the script, rinse and repeat. Is there a line of code or two I can add that would allow it to do multiple rows automagically, one after another?
Instead of using a script, have you tried gathering your data into a tab- (for each column) and return- (for a new row) delimited block of text, then selecting the first cell to fill and pasting? Many browsers/forms are smart enough to understand what to do with text formatted in that way (and it's really easy to test with some plain text before you head off to KM to automate the gathering/formatting!).