Applescript Filter Column by string Numbers

Hi there,

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

Thanks in advance for any help you guys can give!

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

Hey Yvan, thanks for the reply! That works!

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?

Or perhaps, hide rows → select all → delete - > unhide rows ?

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

Thanks :slight_smile:
Do you know how I can mimic just pressing cmd+a and then delete ?
That will delete the unwanted data, then i can un-hide.

I don’t know a way to select no contiguous rows.

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

Yvan KOENIG running Sierra 10.12.0 in French (VALLAURIS, France) lundi 17 octobre 2016 16:16:31

Hey thanks for all your help today :slight_smile:

So, I took your awesome show/hide script, hid the lines I wanted to keep (which number only like 30 rows)
Then I used

tell application “Numbers” to tell document 1 to tell sheet 1 to tell table 1
set the selection range to the cell range
clear selection range
end tell

Then I used your script again to show all the hidden rows.

It works beautifully now, thank you so much for your help!!

You may do that in a single call :

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

You may also try :

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

Hi Yvan,

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.

Is there a way to do this using Applescript?

Thanks!

Just edit one instruction

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

Thanks Yvan,

Really helpful as always :slight_smile: