AppleScripting Apple's Numbers Spreadsheets

Macro Library
,
#1

Hey Folks,

Make a selection anywhere in a row.

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
------------------------------------------------------------------------------

-Chris

3 Likes
Copying in all iWorks apps often gives No Text in Clipboard
How Do I Update Quicken 2017 from a Variety of Sources, including Apple Numbers?
#2

Hey Chris, this is a great concept!

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.

Thanks for the tip!

#3

@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.

#4

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.

#5

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.

#6

Hey Ed,

Thanks.

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
------------------------------------------------------------------------------

Of course much more complex actions are possible.

-Chris

#7

Being a bit of a noob to Macs I was disappointed there was no fast and efficient alternative to Excel to word mail merge for automating documents.

This single bit of code seems to offer a good (part) solution so thank you.

#8

Hey Mark,

What are you doing? Postal Mail or Email?

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.

-Chris

#9

Hi Mark,

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?

Thanks!
-Pete

#10

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!).