Setting filter's value in Numbers

AppleScript
#1

Hello,

I wonder if it is possible to set one or several filter values from AppleScript for Numbers?

The only direct reference to filters I can find in the dictionary is:

set filtered to <bolean>

Maybe this operation should rather by scripted with something like:

set <variable> to rows whose value of first cell is <variable>

My goal is to obtain a range for all columns and rows from a table for the corresponding filter value with the orignal text formatting, if possible.

Any suggestion?

Regards

André Tremblay
PhotoGraphex

#2 
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set myKey to "bidule"
	set maybe to rows whose (value of first cell) is myKey
	set maybe2 to address of rows whose (value of first cell) is myKey
end tell
#3

Well thanks for the suggestions! It will be useful!

What I am looking for is a way to copy the Range of the selection to a variable and paste the Range of that variable to a new Table.

If I take your example, it would be something like:

set maybe3 to range of rows whose (value of first cell) is myKey

If this example would work, it would simplify and greatly accelerate to general script!

Thank you for helping!

Regards

André

#4 
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set myKey to "bidule"
	set maybe2 to address of rows whose (value of first cell) is myKey
	set rowBeg to item 1 of maybe2
	set rowEnd to item -1 of maybe2
	
	-- If you want cells of column 1
	set myRange to range ("A" & rowBeg & ":A" & rowEnd)
	-- if you want the entire rows
	set lastCol to name of last column
	set myRange to range ("A" & rowBeg & ":" & lastCol & rowEnd)
	
	-- CAUTION, a range is ALWAYS monolithic. If we select by hand row 7 and row 10, the selection range will be "A7:F10"
	set selection range to myRange
end tell
#5

Well, if I do understand well, by creating a range from the first filtered row to the last filtered row, you end up with a range containing all the rows in between, this result is rendering the “filtering” useless!

Am I wrong? Certainly not as you warned “CAUTION, a range is ALWAYS monolithic”.

Unless a sort is applied the specific column in advance, as “myKey” is looked after in that specific column. This is an intervention I wanted to avoid in the case of this specific project of mine. But it should otherwise work.

Thank you for the suggestion!

Regards

André

#6

I apologizes, I can’t change the design of the application.
Create a new spreadsheet, select, by hand, row 5 and row 10
then execute:

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set theRange to the selection range
end tell

You will get:
range “A5:G10” of table 1 of sheet 1 of document id “D13B2645-1DAD-4866-9D67-D60D71206F8B” of application “Numbers”
Such range include non selected rows.

You may try to study the free CLI name cliclick available at: https://www.bluem.net/en/projects/cliclick/

Maybe it allow you to mimic the manual selection of separate rows.
The problem is that I never found a way to grab the coordinates of a row item but maybe you will find what I missed.

#7

Well, I do hope that Apple’s programmer will!

I’m sorry for the delay for the reply, but meanwhile I gave some thoughts to your suggestions.

Firstly, let me present to context for my search. Some, … many years ago I rapidly developed in emergency a prototype for general accounting with Mac Excel for an external company. Even though I had no personal experience with this software or Macro programming, I was able to provide a workable solution that revolved mostly around calling the “Filter” command from a Macro. My plan was to develop later a better solution with another software and recuperate the templates and the data gathered meanwhile.

Unfortunately, the years passed, I didn’t had time to work on anything new. With every system upgrades the Excel projet became ever more frustrating to use and unstable in its functioning. Once the accounting year had started, it was out of the question to replace it. Every january, I was fearing to suffer Excel for one more long year.

Up to 2020, when I decided that it was enough, not having the available time to program a full application, I thought to give a try to Numbers, for which I had a very superficial experience. My reasoning being that I could emulate Excel’s Macros with AppleScript. After gaining some knowledge on the basic of Number’s AppleScript language support, my first foray was to look for the AppleScript command to setup Filters in Number. I could not conceive it didn’t exist! Thanks Apple!

Secondly, being definately out of Excel and forcibly in Numbers’ bangwagon, I had no choice but the find a workaround to emulate the result of Filter. I did so mainly by using the example shown in the first post to extract the value and format of each individual cells and to reconstruct new tables cell by cell with those values. A long process, but after some tinkering and optimization, it provides the expected results without speediness, but comparable to Excel’s performances.

Thirdly, your suggestion would be much faster, I think the best workaround for the non selected rows would be to do a sort on the table containing the data in advance. In the case this actual project, this isn’t possible on the original table, but making a temporary copy of the table would be just fine and after a sort on the pertinent column, there wouldn’t be this issue of non selected row. Basically it would provide the same range result as a manual filter. In this particular project it would just need to copy the resulting range and paste it to the table relevant sheet. A much faster solution!

Finally, thank you so much for your suggestion, I will try to implement it in a near future! For the time being, I very satisfied with the results and the presentation of the financials statements provided by Numbers. The experience gained with AppleScript and Numbers will definitely be a valuable asset in the future!

My best regards

André