Searching iWorks Numbers using Apple Script

I want to search for specific text in a Numbers file using Apple Script. I want to search Column “C” for specific text.
Any ideas how I can do this, or if it’s even possible?

I’m looking for duplicate information between files. So, I’m pulling the contents of a cell in File 1 to search in File 2.

I am currently using iWorks '09.

Thanks.

I tested this script in Numbers v3.5.3
With Numbers v2.x, you will have to edit the two instructions :
if maybe is not in {missing value, “”} then
If my memory is right, when a cell is empty, we don’t get missing value but 0.0.

tell application "Numbers"
	tell document 1 to tell sheet 1 to tell table 1
		set theValues1 to value of cells of column 3
	end tell
	tell document 2 to tell sheet 1 to tell table 1
		set theValues2 to value of cells of column 3
	end tell
end tell
# Now you have two lists containing the values of cells of both columns C.
# You may examine them as you want.
set existingInBothTables to {}
set existingOnlyInFile1 to {}
set existingOnlyInOneFile to {}
set existingOnlyInFile2 to {}
repeat with i from 1 to count theValues1
	set maybe to item i of theValues1
	if maybe is not in {missing value, ""} then
		if maybe is in theValues2 then
			set end of existingInBothTables to {maybe, i}
		else
			set end of existingOnlyInFile1 to {maybe, i}
			set end of existingOnlyInOneFile to {maybe, i}
		end if
	end if
end repeat
repeat with i from 1 to count theValues2
	set maybe to item i of theValues2
	if maybe is not in {missing value, ""} then
		if maybe is in theValues1 then
			set end of existingInBothTables to {maybe, i}
		else
			set end of existingOnlyInFile2 to {maybe, i}
			set end of existingOnlyInOneFile to {maybe, i}
		end if
	end if
end repeat

{existingInBothTables, return, existingOnlyInFile1, return, existingOnlyInOneFile, return, existingOnlyInFile2}
--> {{{56.0, 5}, {23.0, 6}, {56.0, 4}, {23.0, 6}}, "
", {{1.0, 2}, {12.0, 3}, {99.0, 4}}, "
", {{1.0, 2}, {12.0, 3}, {99.0, 4}, {102.0, 2}, {4.0, 3}, {45.0, 5}}, "
", {{102.0, 2}, {4.0, 3}, {45.0, 5}}}

As you may see, for every value reported I report also the rowNum which may be useful for what you want to achieve.

Yvan KOENIG running Yosemite 10.10.5 in French (VALLAURIS, France) vendredi 4 septembre 2015 21:02:20

Thank you for your help. I apologize for not responding sooner. I added a little to the end to put in TextWrangler and clean up (to make readable). My portion of the code is a kinda sloppy. (I’m new to AppleScript.) But maybe it can help someone else anyway.
There are some issues with my results after cleaning up the file. Like when the line started with a number but it got me most of the way there. Good enough for now. I added a tab between the text and the cell reference number, and a return after.

Thank you again. Your code is great.

Here’s the script I added.

global Search1, Search2, Replace1, Replace2, FileName, SearchNumber

set text item delimiters of AppleScript to {“/”}
set existingOnlyInFile1 to existingOnlyInFile1 as text
NewFile(existingOnlyInFile1)

on NewFile(Var)
tell application “TextWrangler”
set NewDocName to document 1 of window 1
activate
set text of NewDocName to Var
end tell
end NewFile

FileCleanUp()

on FileCleanUp()
set FileName to “untitled text”
set SearchNumber to 0
repeat until SearchNumber > 9
ReplaceText()
set SearchNumber to SearchNumber + 1
end repeat
set SearchNumber to 0
repeat until SearchNumber > 9
FixText()
set SearchNumber to SearchNumber + 1
end repeat
end FileCleanUp

on ReplaceText()
set Searches to “/”
set Search1 to Searches & SearchNumber as text
set Search2 to (SearchNumber as text) & Searches
set Replace1 to “\t” & SearchNumber as text
set Replace2 to (SearchNumber as text) & “\n”
tell application “TextWrangler”
activate
replace Search1 using Replace1 searching in text 1 of text document FileName options {starting at top:true}
replace Search2 using Replace2 searching in text 1 of text document FileName options {starting at top:true}
end tell
end ReplaceText

on FixText()
set Search1 to “\r” & SearchNumber as text
set Replace1 to “\t” & SearchNumber as text
tell application “TextWrangler”
activate
replace Search1 using Replace1 searching in text 1 of text document FileName options {starting at top:true}
end tell
end FixText

Thanks for the feedback but I am a bit puzzled.
The original question was about Numbers and now you post a script driving TextWrangler !

Yvan KOENIG running Yosemite 10.10.4 in French (VALLAURIS, France) jeudi 10 septembre 2015 10:18:11

I apologize for the confusion. I do appreciate you posting the code. It worked well. I did want to compare columns in two numbers files. The problem I had was that the result had more items than I expected, so I wanted to make it easier to read. To do that I put it in TextWrangler. I didn’t know how to do it in Numbers or pages either for that matter.

Thanks again.