I’m wondering if it’s possible to search a Numbers document for a string, let’s say, “hello” and then have it return the cell identifier for where that string exists. For example, if “hello” is in cell C2 in a numbers document, the script would return C2 when it finds the string “hello” in it.
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set theValues to value of every cell
set theKey to "hello"
set found to "key unavailable"
set nbColumns to count columns
set indx to 0
repeat with r from 1 to count rows
repeat with c from 1 to nbColumns
set indx to indx + 1
if item indx of theValues = theKey then
set found to (name of cell r of column c)
exit repeat
end if
end repeat
end repeat
end tell
found
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) samedi 23 mai 2020 18:48:58
Thank you Ivan. So it looks like to search or to find anything in a Numbers document, I first have to retrieve the values as a list (array) in AppleScript first and then do further processing in the script editor, outside of a Numbers document.
Model: MacBook
AppleScript: 2.7
Browser: Safari 605.1.15
Operating System: macOS 10.14
First, may you take care to my real first name.
Since 1943/12/31, it’s Yvan and I’m accustomed to it.
I didn’t wrote that we must work outside of the Numbers document.
It’s just that searching in a list grabbed in a single call as I did is faster than checking the content of cells one by one.
If you look carefully, the late instruction : end tell urge the script to speak to Numbers.
It’s a pity, it’s a rare case where I didn’t commented such instructions.
Below is the same script with commented instructions:
tell application "Numbers"
tell document 1
tell sheet 1
tell table 1
set theValues to value of every cell
set theKey to "hello"
set found to "key unavailable"
set nbColumns to count columns
set indx to 0
repeat with r from 1 to count rows
repeat with c from 1 to nbColumns
set indx to indx + 1
if item indx of theValues = theKey then
set found to (name of cell r of column c)
exit repeat
end if
end repeat
end repeat
-- here, found contains the name of the cell
end tell -- to table 1
end tell -- to sheet 1
end tell -- to document 1
end tell -- to Numbers
found -- forces the Editor to display the content of the variable
For large tables, it would be fasten:
property theValues : missing value
tell application "Numbers"
tell document 1
tell sheet 1
tell table 1
set my theValues to value of every cell
set theKey to "hello"
set found to "key unavailable"
set nbColumns to count columns
set indx to 0
repeat with r from 1 to count rows
repeat with c from 1 to nbColumns
set indx to indx + 1
if item indx of my theValues = theKey then
set found to (name of cell r of column c)
exit repeat
end if
end repeat
end repeat
-- here, found contains the name of the cell
end tell -- to table 1
end tell -- to sheet 1
end tell -- to document 1
end tell -- to Numbers
found -- forces the Editor to display the content of the variable
And below is the script checking the content of cells one by one:
tell application "Numbers"
tell document 1
tell sheet 1
tell table 1
set theValues to value of every cell
set theKey to "hello"
set found to "key unavailable"
repeat with r from 1 to count rows
repeat with c from 1 to count columns
if value of cell r of column c = theKey then
set found to (name of cell r of column c)
exit repeat
end if
end repeat
end repeat
-- here, found contains the name of the cell
end tell -- to table 1
end tell -- to sheet 1
end tell -- to document 1
end tell -- to Numbers
found -- forces the Editor to display the content of the variable
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 1 juin 2020 10:18:14
Another approach is to get Numbers return the cell name using a ‘whose’ filter. These can be quite slow in some applications, but this doesn’t seem too bad with with a large spreadsheet in Numbers 10.0 on my Mojave machine:
tell application "Numbers"
tell table 1 of sheet 1 of document 1
try
set targetCell to name of first cell whose value is "hello"
on error number -1719
set targetCell to "Not found"
end try
end tell
end tell
Thank you Nigel
There are so many cases where the whose clause fail that I forgot to test it.
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 1 juin 2020 12:38:58
On a table with 21205 rows, 11 columns, finding a string available in cell J21184 took 11,11 seconds with my faster proposal but too only 6,69 seconds with Nigel’s one.
When the key is available in cell G61, the results were quite the same: 11,01 seconds versus 6,36 seconds.
In French we say : “Il n’y a pas photo”
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 1 juin 2020 17:25:04
The OP appears only to want the first matching cell but I was curious how long the search would take to create a list of every matching cell. So, I modified Nigel’s script and Yvan’s second script in post 4 to work this way. Nigel’s script took 0.15 seconds to complete with a 535 row spreadsheet with 254 matches, and Yvan’s script took 1.4 seconds. I also tested Yvan’s third script in post 4–which Yvan acknowledges is very slow–and it took 18.9 seconds to run.
Sorry about the naming mixup, Yvan.
I was originally looking for something that would simulate a Command-F search function in Numbers.
I noticed when you have a line line:
set theValues to value of every cell
It imports the data from Numbers into memory, and then does the search - kind of like an intermediary step.
With Nigel’s code (whose function), it searches the Numbers document directly without the intermediary step - closer to typing command-F.
I later modified my code to only search one column, because that’s where the data was, skipping the need to search the entire table.
Thanks for the help.
As far as efficiency and timings are concerned, it’s best to get a feel for the kind of things that can happen and then experiment to see what actually does happen in the case for which you’re scripting, rather than assuming something’s always faster than something else.
whose filters are implemented in the applications being scripted, so how fast they are depends on the applications. In the current case, it appears that with Numbers, a ‘whose’ filter filtering for a single instance of single property and a single value is faster than dumping all the values to the script and having it do the thinking. With other applications, or with more complex filters, or in cases where individual things have to be done with several identified elements, or on different systems, the situation may be very different.
If Yvan had known right at the beginning that the value to be sought would always be in a certain column, he’d have written a faster script which would only have fetched the values from that column and wouldn’t have needed nested repeats.