I spent a half hour trying to learn how to extract a table from a spreadsheet and store the results in a KM variable. But I needed help, so I decided to see if ChatGPT could solve this problem, and it did, instantly. But it seems that the solution is rather complex. Is there a simpler way? Here's the AppleScript code it gave me, which I tweaked to specify my preferred table and sheet. (My sheet is named Names and my table is named Table1.) I can live with this, but I would prefer a simpler solution that doesn't involve loops.
tell application "Numbers"
activate
tell document 1
tell sheet "Names"
tell table "Table1"
set tableData to {}
set numRows to row count
set numCols to column count
repeat with i from 1 to numRows
set rowData to {}
repeat with j from 1 to numCols
set cellValue to the value of cell j of row i
set end of rowData to cellValue
end repeat
set end of tableData to rowData
end repeat
set AppleScript's text item delimiters to ", "
set tableText to ""
repeat with rowData in tableData
set tableText to tableText & (rowData as text) & linefeed
end repeat
set the clipboard to tableText
return tableText
end tell
end tell
end tell
end tell
Not an answer – I'm personally not in the business of cleaning up after approximate retrievals by LLMs – just placing the code between triple backticks (start and end),
to makes its structure visible to others.
tell application "Numbers"
activate
tell document 1
tell sheet "Names"
tell table "Table1"
set tableData to {}
set numRows to row count
set numCols to column count
repeat with i from 1 to numRows
set rowData to {}
repeat with j from 1 to numCols
set cellValue to the value of cell j of row i
set end of rowData to cellValue
end repeat
set end of tableData to rowData
end repeat
set AppleScript's text item delimiters to ", "
set tableText to ""
repeat with rowData in tableData
set tableText to tableText & (rowData as text) & linefeed
end repeat
set the clipboard to tableText
return tableText
end tell
end tell
end tell
end tell
Thanks and sorry. I didn't know that triple backticks would do that! I think I saw triple backticks in GPT's answer but I cut them out because I didn't know what they did.
Truthfully, I never considered the possibility of blank cells, since I know my tables won't include any. I didn't tell GPT whether I wanted blank cells or not, so I suspect the code above will include blank cells.
I was hoping that AppleScript had a way to return a table from Numbers using a single statement. I made some guesses as to what that statement would be, but didn't succeed.
I would personally tend to read the Numbers table in a JSON format – allowing for direct references, within a Keyboard Maestro macro, to particular rows and columns of the table:
return (() => {
"use strict";
// JSON rows from named table on named sheet
// in front Numbers document.
// Rob Trew @2024
// Ver 0.02
const main = () =>
either(
alert("Named numbers table as JSON")
)(
json => json
)(
namedNumbersTableAsJSONLR(
Application("Numbers").documents.at(0)
)(
kmvar.local_Sheet_Name
)(
kmvar.local_Table_Name
)
);
// --------------------- NUMBERS ---------------------
// namedNumbersTableAsJSONLR :: String -> Doc ->
// String -> String -> Either String JSON
const namedNumbersTableAsJSONLR = doc =>
sheetName => tableName => bindLR(
doc.exists()
? Right(doc.sheets.byName(sheetName))
: Left("No document open in Numbers")
)(
sheet => bindLR(
sheet.exists()
? Right(sheet.tables.byName(tableName))
: Left(`No front doc sheet named "${sheetName}".`)
)(
table => table.exists()
? Right(tableJSON(table))
: Left(
[
`No table named "${tableName}" on `,
`sheet named "${sheetName}"`
]
.join("\n")
)
)
);
// tableJSON :: Table -> JSON
const tableJSON = table => {
const
valueRows = table.rows.cells.value(),
iLastCol = Math.max(
...valueRows.map(
row => row.findLastIndex(
x => null !== x
)
)
);
return JSON.stringify(
valueRows.flatMap(
row => row.some(x => null !== x)
? [
row.slice(0, iLastCol)
.map(x => x || "")
]
: []
),
null, 2
);
};
// ----------------------- JXA -----------------------
// alert :: String => String -> IO String
const alert = title =>
s => {
const sa = Object.assign(
Application("System Events"), {
includeStandardAdditions: true
});
return (
sa.activate(),
sa.displayDialog(s, {
withTitle: title,
buttons: ["OK"],
defaultButton: "OK"
}),
s
);
};
// --------------------- GENERIC ---------------------
// Left :: a -> Either a b
const Left = x => ({
type: "Either",
Left: x
});
// Right :: b -> Either a b
const Right = x => ({
type: "Either",
Right: x
});
// bindLR (>>=) :: Either a ->
// (a -> Either b) -> Either b
const bindLR = lr =>
// Bind operator for the Either option type.
// If lr has a Left value then lr unchanged,
// otherwise the function mf applied to the
// Right value in lr.
mf => "Left" in lr
? lr
: mf(lr.Right);
// either :: (a -> c) -> (b -> c) -> Either a b -> c
const either = fl =>
// Application of the function fl to the
// contents of any Left value in e, or
// the application of fr to its Right value.
fr => e => "Left" in e
? fl(e.Left)
: fr(e.Right);
// MAIN
return main();
})();
I think you'll either have to loop through rows and columns to build your own CSV, or export to CSV file(s), since there's no way to directly get table's values in that format.
Yes, you might be able to do something with the flat, unformatted, output of
tell application "Numbers"
tell document 1
tell sheet "Names"
tell table "Table 1"
return value of every cell of cell range
end tell
end tell
end tell
end tell
...but I think
tell application "Numbers"
export document 1 to POSIX file "/tmp/test.csv" as CSV
end tell
...will be easier. Single-sheet documents are exported as a single file, multi-sheet ones are exported as a folder containing one file per table per sheet -- and, in that case, since you know the name of the sheet and table you know you'll be looking for "Names-Table 1.csv".
You can, of course, read the table in JSON format in AppleScript too,
but it's (understandably) messier and less well supported than in JavaScript for Automation.
Expand disclosure triangle to view AppleScript source
use framework "Foundation"
use scripting additions
on run
set kmInst to system attribute "KMINSTANCE"
tell application "Keyboard Maestro Engine"
set sheetName to getvariable "local_Sheet_Name" instance kmInst
set tableName to getvariable "local_Table_Name" instance kmInst
end tell
tell application "Numbers"
activate
tell front document
tell sheet sheetName
tell table tableName
set xs to value of cells of rows
end tell
end tell
end tell
end tell
showJSON(concatMap(rowWithData, xs))
end run
on rowWithData(tableRow)
if notEmpty(tableRow) then
{my gapsAsEmptyStrings(tableRow)}
else
{}
end if
end rowWithData
on gapsAsEmptyStrings(xs)
script go
on |λ|(x)
if missing value is x then
""
else
x
end if
end |λ|
end script
map(go, xs)
end gapsAsEmptyStrings
on notEmpty(cells)
script p
on |λ|(x)
missing value is not x
end |λ|
end script
any(p, cells)
end notEmpty
------------------------- GENERIC ------------------------
-- any :: (a -> Bool) -> [a] -> Bool
on any(p, xs)
-- Applied to a predicate and a list,
-- |any| returns true if at least one element of the
-- list satisfies the predicate.
tell mReturn(p)
set lng to length of xs
repeat with i from 1 to lng
if |λ|(item i of xs) then return true
end repeat
false
end tell
end any
-- concatMap :: (a -> [b]) -> [a] -> [b]
on concatMap(f, xs)
set lng to length of xs
set acc to {}
tell mReturn(f)
repeat with i from 1 to lng
set acc to acc & (|λ|(item i of xs, i, xs))
end repeat
end tell
acc
end concatMap
-- showJSON :: a -> String
on showJSON(x)
set c to class of x
if (c is list) or (c is record) then
set ca to current application
set json to ca's NSJSONSerialization's dataWithJSONObject:x options:1 |error|:(missing value)
if json is missing value then
"Could not serialize as JSON"
else
(ca's NSString's alloc()'s initWithData:json encoding:(ca's NSUTF8StringEncoding)) as text
end if
else if c is date then
"\"" & ((x - (time to GMT)) as «class isot» as string) & ".000Z" & "\""
else if c is text then
"\"" & x & "\""
else if (c is integer or c is real) then
x as text
else if c is class then
"null"
else
try
x as text
on error
("«" & c as text) & "»"
end try
end if
end showJSON
-- map :: (a -> b) -> [a] -> [b]
on map(f, xs)
-- The list obtained by applying f
-- to each element of xs.
tell mReturn(f)
set lng to length of xs
set lst to {}
repeat with i from 1 to lng
set end of lst to |λ|(item i of xs, i, xs)
end repeat
return lst
end tell
end map
-- mReturn :: First-class m => (a -> b) -> m (a -> b)
on mReturn(f)
-- 2nd class handler function lifted into 1st class script wrapper.
if script is class of f then
f
else
script
property |λ| : f
end script
end if
end mReturn
Before I posted my questions, I was actually trying to use the AppleScript phrase "value of" but couldn't guess that the rest of the phrase would be "cells of rows". I suspected it would return JSON, but couldn't get it to work so I couldn't test. I will mark something here as the solution if I can get it to work later today.
For that, as @Nige_S suggests, you need to reach for the Foundation classes over the ObjC bridge, applying NSJSONSerialization's dataWithJSONObject:options:error:
Much easier to use JavaScript's built-in JSON.stringify
Asking, not suggesting! I'm totally lost when it comes to this sort of thing... And it didn't help that I hit a scroll glitch on the AS code above and couldn't see anything below end run!
And now I've seen the rest, I've gone past "lost" and into "missing without trace, without hope"...
You should, I think, generally be able to hover a cursor over the top-right hand corner and click the Copy icon to capture the full source to clipboard.
missing without trace
A flair for comic exaggeration
( but let me know if there's anything in there that seems worth elucidating )
