I’m a bit stuck on scripting Numbers. Is it possible to filter a table by a column value using Applescript?
I want to filter column A by three values, then copy the result to a new sheet.
The document I’m working on contains thousands of lines and Numbers handles this a lot faster than Excel on my mac, so if possible, I’d like to find a solution that works in Numbers.
I guess the filter part would look something like this? (although obviously, this isn’t working)
tell application “Numbers”
tell document 1’s sheet 1’s table 1
tell column 1
string = “Adam, Jane, David”
set filtered to true
end tell
end tell
end tell
As far as I know, there is no AppleScript support for the filter feature.
You may mimic it with :
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with i from (count rows) to 1 by -1
if value of cell i of column theColumn is in {"Adam", "Jane", "David"} then
set selection range to range (name of cell i of column 1 & ":" & name of cell i of column 2) # Select two cells in the row
my selectMenuItem("Numbers", 6, 22) # Hide the selected row
end if
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
Before using it you must activate GUIScripting.
Open System Preferences, unlock the Security & Privacy preference, add AppleScript Editor or your application-script in the Privacy Pane’s Accessibility list and check it".
Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) lundi 17 octobre 2016 14:29:36
Just wondering, the script you wrote iterates through the sheet cell-by-cell - but in a sheet with 10,000 rows, this takes some time to complete. Do you know if there’s a way of selecting these rows en-masse and just deleting them?
I used hide because it’s what the official filter feature does.
If you want to remove the rows, use :
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with i from (count rows) to 1 by -1
if value of cell i of column theColumn is in {"Adam", "Jane", "David"} then
set selection range to range (name of cell i of column 1 & ":" & name of cell i of column 2) # Select two cells in the row
my selectMenuItem("Numbers", 6, 7) # Remove the selected row
end if
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) lundi 17 octobre 2016 15:49:34
If I select rows ’ and 8, selection range returns :
[format]range “A4:G8” of table 1 of sheet 1 of document id “ACBA9375-53B3-45C8-B797-911490ECD358” of application “Numbers”[/format]
I guess that it would be a bad idea to remove this range.
You may try this alternate version :
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
set theRows to {}
repeat with aKey in {"Adam", "Jane", "David"}
set theRows to theRows & (row of cells of column theColumn whose value is (contents of aKey))
end repeat
repeat with aRow in reverse of theRows
delete aRow
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
But I’m not sure that it’s more efficient than :
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with i from (count rows) to 1 by -1
if value of cell i of column theColumn is in {"Adam", "Jane", "David"} then
delete row i
end if
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
which is better than my last proposal. No need to build a selection range and trigger GUIScripting to delete it.
If you want better AppleScript support for Numbers, file a request to Apple thru the dedicated menu item of the Numbers menu.
But don’t dream too much, I’m not sure that Numbers is a high priority product at Cupertino
Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) lundi 17 octobre 2016 16:16:31
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with i from (count rows) to 1 by -1
if value of cell i of column theColumn is in {"Adam", "Jane", "David"} then
set selection range to range (name of cell i of column 1 & ":" & name of cell i of column 2) # Select two cells in the row
my selectMenuItem("Numbers", 6, 22) # Hide the selected row
end if
end repeat
set the selection range to the cell range
clear selection range
end tell
my selectMenuItem("Numbers", 6, 23)
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) lundi 17 octobre 2016 16:45:33
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with aRow in reverse of (get every row)
if value of cell theColumn of aRow is not in {"Adam", "Jane", "David"} then
remove aRow
end if
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
or
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with r from (count rows) to 1 by -1
if value of cell theColumn of row r is not in {"Adam", "Jane", "David"} then
remove row r
end if
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) mardi 18 octobre 2016 11:21:51
Is it possible to delete a row that starts with a value, rather than if it matches a value?
For example, I am deleting every row with “Adam” ion the first column using your script, which works brilliantly.
I want to delete stuff like URLs from my sheet. These are all different, but start with the same common value, http or https.
set theColumn to 2
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
my selectMenuItem("Numbers", 6, 23) # Reveal every rows
repeat with r from (count rows) to 1 by -1
# Activate the instruction which fits your needs
-- if value of cell theColumn of row r is not in {"Adam", "Jane", "David"} then
-- if value of cell theColumn of row r does not start with "http" then
if value of cell theColumn of row r starts with "http" then
remove row r
end if
end repeat
end tell
on selectMenuItem(theApp, mt, mi)
tell application "System Events" to tell process theApp
set frontmost to true
tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell
end selectMenuItem
Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) lundi 31 octobre 2016 16:46:49
