Numbers, Find substrings (A1, A2,...) replace all with (B1, B2,...)

Hello Guys,

I’m quite new to Applescript, I have combined a whole bunch of scripts from searching and tweaking… However, it’s not compiling.

Here’s what I have so far…



tell application "Numbers"
	activate
	tell application "System Events"
		tell process "Numbers"
			tell "Numbers" to tell front document to tell sheet to tell table
				set rowNumber to 1
				set ay to "A" + rowNumber
				set be to "B" + rowNumber
				set columnA to the value of ay
				set columnB to the value of be
				repeat with incrementValue in {ay, be}
					if value of columnA is equal to "" then
						exit repeat
						
					else
						replaceText(setTrim(strikethroughText(value of columnA)), setTrim(strikethroughText(value of columnB))
						
						set rowNumber to rowNumber + 1
					end if
				end repeat
			end tell
		end tell
	end tell
end tell



on replaceText(find, replace, someText)
   set prevTIDs to text item delimiters of AppleScript
   set text item delimiters of AppleScript to find
   set someText to text items of someText
   set text item delimiters of AppleScript to replace
   set someText to "" & someText
   set text item delimiters of AppleScript to prevTIDs
   return someText
end replaceText

on trim(someText)
	repeat until someText does not start with " "
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with " "
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

on strikethroughText(someSubstring)
	
	 if (character 1 of thisWord is in value of columnB) then
               try
                   thisWord as text
                           set character style of word w to character style "Strikethrough"
               end try
           end if

end strikethroughText

I suppose I should have used a delimiter or grep in there somewhere, but I haven’t figured out how these commands work yet :confused:

Basically, I need this script to copy (sheet 1) Cell A1 first, and find the substring throughout Sheet 2 and then replace all substrings matching (sheet 1) A1 with (sheet 1) Cell B1 with a strikethrough, but I would prefer a change of color. Next, it will automatically go down to the next cell (sheet 1) A2 and do the same, etc. I also want it to be able to keep going until there is no value in column A and then for it to stop.

I have such a script for Microsoft Excel (including changing background color of the cell) already. Does it needs to work in Numbers per se?

Hello

If some of my words seem to be rude, take care to the fact that English is not my main language.
I did my best to don’t hurt but maybe my best is not sufficient.

Being new to Numbers or AppleScript is definitely not a sin.
But it’s not an excuse to try to guess the way those tools are behaving.
As every applications, they have their own language with their own rules.
For AppleScript, most of the rules are defined in AppleScript Language Guide available from :
https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/AppleScriptLangGuide/AppleScriptLanguageGuide.pdf

For Numbers, they are defined in documents reachable thru the application’s Help menu.
For the duo Numbers + AppleScript, the rules are defined by the Numbers AppleScript dictionary which is available thru :
AppleScript Editor > File > Open a Dictionary > then navigate to Numbers;

I’m not sure that I really understand what you want to achieve.
(1) What is sure is that you made an awful mix of vanilla AppleScripting and GUiScripting.
In vanilla AppleScripting we don’t speak to the application process.

(2) reading the user guide is often useful.
I assume that when you wrote : set ay to “A” + rowNumber
you asked Applescript to attach the numerical row number just after the letter A to build a string like “A6”.
Alas, AppleScript can’t use the operator + to do that, it uses the operator & [or the function AND() but today it’s an other story]
Use : set ay to “A” & rowNumber

(3) I assumes that when you wrote : set columnA to the value of ay
you asked AppleScript to return the value of the variable ay.
Alas, AppleScript can’t do that this way.
You must ask it, politely :
set columnA to ay
or
copy ay to columnA
The two instructions aren’t doing exactly the same thing but this is an other other story.

(4) Honestly I I don’t understand what you want to achieve with repeat with incrementValue in {ay, be}
because you don’t use the variable incrementValue after defining it.

(5) I assumes that when you wrote : if value of columnA is equal to “” then
you asked AppleScript to check if the cell whose name is stored in the variable columnA contains the empty string.
Alas AppleScript doesn’t understand that.
You must ask it, always very politely,
if value of cell columnA is equal to “” then
If like me you are lazy, you may ask :
if value of cell columnA = “” then
which means exactly the same thing.

(6) I assumes that when you wrote : replaceText(setTrim(strikethroughText(value of columnA)), setTrim(strikethroughText(value of columnB))
you asked AppleScript to understand that you wanted it to execute the valid instruction :
replaceText(setTrim(strikethroughText(value of cell columnA)), setTrim(strikethroughText(value of cell columnB)))
Doing that, you would have render AppleScript happy during compile time.
Alas, at execution it would fail because you are passing two parameters to your handler replaceText which is built to receive three of them.

(7) I assumes that when you wrote : set rowNumber to rowNumber + 1
you wanted that AppleScript trigger the next row (remember, you started with row number = 1.
Alas, the instructions using rowNumber are outside of the repeat loop so you instruction does nothing useful.

(8) I assume that the handler replaceText is supposed to replace the styled text defined by the variable find by the styled text defined by the variable replace in the styled text defined by the variable someText.
Alas, as far as I know, it doesn’t treat styled text but raw text.

(9) For the handler strikethrough I am really puzzled.
(a) you ask it to work upon the variable thisWord which is not defined
(b) the variable columnB is meaningful in the main body of your script but it isn’t in the handler.
It’s a matter of scope and explaining what it means would be too long.
(c) If you use value of as you did in the case (3) the instruction must be :
if (character 1 of thisWord is in columnB) then
which would compile but is probably not what you want.
If, as I guess, you want it to check if the character exists in the cell whose name is defined by columnB,
it would be useful to ask AppleScript to speak - in the handler - to the document with something like :
tell application “Numbers” to tell front document to tell active sheet to tell table 1

After these long explanations, I made several changes in your script.
I didn’t wanted to build a script achieving your goal, just build a script able to compile.
The result is :


tell application "Numbers"
	activate
	tell front document to tell active sheet to tell table 1
		set rowNumber to 1
		set ay to "A" & rowNumber
		set be to "B" & rowNumber
		set columnA to ay
		set columnB to be
		repeat with incrementValue in {ay, be}
			if value of cell columnA is equal to "" then
				exit repeat
				
			else
				replaceText(setTrim(strikethroughText(value of cell columnA)), setTrim(strikethroughText(value of cell columnB)))
				
				set rowNumber to rowNumber + 1
			end if
		end repeat
	end tell
end tell


on replaceText(find, replace, someText)
	set prevTIDs to text item delimiters of AppleScript
	set text item delimiters of AppleScript to find
	set someText to text items of someText
	set text item delimiters of AppleScript to replace
	set someText to "" & someText
	set text item delimiters of AppleScript to prevTIDs
	return someText
end replaceText

on trim(someText)
	repeat until someText does not start with " "
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with " "
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

on strikethroughText(someSubstring)
	tell application "Numbers" to tell front document to tell active sheet to tell table 1
		if (character 1 of thisWord is in value of cell columnB) then
			try
				thisWord as text
				-- set character style of word w to character style "Strikethrough"
			end try
		end if
	end tell
end strikethroughText

As you may see, I was forced to disable one instruction :
– set character style of word w to character style “Strikethrough”
because, in the late version of Numbers (3.2.2), character style is not defined in the application’s dictionary.

Maybe you are trying to drive an older version (maybe 2.3) but, as I wrote often, I’m not a sooth sayer and can’t guess this kind of thing.

Have fun with Numbers and AppleScript.

Yvan KOENIG (VALLAURIS, France) vendredi 29 août 2014 18:24:14

No offense taken. Thank you for the welcoming, and the pdf file.

I’ve looked over what you’ve stated as your assumptions were very accurate. I tweaked my script a bit more. I changed the strikethrough because of the changes from the previous releases and am trying to have it changed in color instead. I’ve also added sheet 2 as the variable in which I would like to replace all of the text.

I’m currently getting an error that states (error “The replaceWith parameter is missing for replaceText.” number -1701 partial result «handler replaceText» from “replaceWith”) even though I’ve added the parameters.

if I drop this color changing effect, I receive a different error (error "Numbers got an error: Can’t get every text item of table 1 of sheet 2 of document)
I’ve also tried changing the text item to cells, also receiving the same error

Any suggestions?


tell application "Numbers"
	activate
	
	tell front document to tell sheet 2
		
		set sheetTwo to table 1 -- sets sheet 2 table 1 as search variable
		
	end tell
	
	tell front document to tell sheet 1 to tell table 1 -- switches back to sheet 1
		
		set rowNumber to 1
		set columnA to "A" & rowNumber
		set columnB to "B" & rowNumber
		
		repeat until value of cell columnA is equal to "" -- cancels repeat when there's no value in cell
			
			my replaceText((my trim(value of cell columnA)), (my showChangeText(value of cell columnB)), (sheetTwo))
			rowNumber = rowNumber + 1 -- increases cell value before every repeat
			
		end repeat
		
	end tell
	
end tell

-- searches sheet2 for column a cells sheet1 and changes them with column b cells sheet1

on replaceText(find, replaceWith, secondSheet)
	set prevTIDs to text item delimiters of AppleScript
	set text item delimiters of AppleScript to find
	set secondSheet to text items of secondSheet
	set text item delimiters of AppleScript to replaceWith
	set secondSheet to "" & secondSheet
	set text item delimiters of AppleScript to prevTIDs
	return secondSheet
end replaceText

-- Trims the words so there are no spaces before or after

on trim(someText)
	repeat until someText does not start with " "
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with " "
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

-- Highlights all replacement changes in Sheet 2
on showChangeText(someSubstring)
	tell application "Numbers" to tell front document to tell active sheet to tell table 1
		try
			set color index of someSubstring of selection to green
		end try
	end tell
end showChangeText

Before continuing, may you describe exactly what you are wanting to achieve ?
My understanding is that you want to change somewhere strings defined in a cell of column A of table 1 of Sheet 1 of front document by the strings defined in a cell of column B of table 1 of Sheet 1 of front document setting them in green.

At this time I really don’t know what is really what I named “somewhere”.

You named it sheetTwo which is a reference to table 1 of sheet 2 of front document which is not matching the requirements of your replaceText handler.
This one requires a piece of text.
Are you wanting to edit the contents of a single cell or the contents of several cells, maybe all the cells of table 1 of sheet 2 ?

In my late message, I wrote that replaceText apply to raw text, not to styled text so replacing “blah” by “grass” is doable but “grass” will not be green.

About green, I guess that you know what is green but the script doesn’t. It’s your duty to define it using something like :

set green to {24170, 47190, 11003}

run this short sample code:

set green to {24170, 47190, 11003}
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set value of cell "B4" to "grass"
	set text color of cell "B4" to green
	set value of cell "B8" to "How Green Was My Valley"
	set text color of characters 5 thru 9 of cell "B8" to green
end tell

“grass” will correctly appear in green (maybe not the one you want but it’s an other story)
“How Green Was My Valley” will remain in black and you will get the English version of the error message :
→ error “Erreur dans Numbers : Il est impossible de régler characters 5 thru 9 of cell "B8" of table 1 of sheet 1 of document 1 à {24170, 47190, 11003}.” number -10006 from characters 5 thru 9 of cell “B8” of table 1 of sheet 1 of document 1

I know the way to change the color of a subset of a cell’s content thru the Graphical User Interface (GUI) but I don’t know the way to do that using vanilla AppleScript.
To do that,
select the subset of the cell’s contents then define the text color thru the Inspector.

To do that in a script, I would have to use this awful scheme:
use GUI scripting to copy the cell contents
use GUI scripting to paste in a Word Processor
use vanilla AppleScript to search the last occurrence of string grass
use vanilla AppleScript to replace the 4 characters of the word grass by the 6 characters of the string “myGreen”
use vanilla AppleScript to set the text color of these 6 characters to green
repeat the process as long as the text contains the string “grass”
use GUI scripting to copy the entire text
use GUI scripting to paste in the original cell

repeat for every cells to treat.

When I saw in the dictionary that text items were defined as rich text, I hoped that this would allow me to set the value of a cell to a «class RTF » item.

To try that, I copied to the clipboard a string with black and green characters created in TextEdit
then I ran :

set maybe to the clipboard as «class RTF »

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set value of cell "D4" to maybe
end tell

Alas, the result was an awful :
error “Erreur dans Numbers : Il est impossible de rendre «data RTF 7B5C727466315C616E73695C616E7369637067313235325C636F636F61727466313236355C636F636F617375627274663231300A7B5C666F6E7474626C5C66305C666E696C5C666368617273657430204C75636964614772616E64653B7D0A7B5C636F6C6F7274626C3B5C7265643235355C677265656E3235355C626C75653235353B5C726564305C677265656E305C626C7565303B5C726564305C677265656E305C626C75653235353B7D0A5C706172645C74783537375C7478313135355C7478313733335C7478323331315C7478323838395C7478333436375C7478343034355C7478343632335C7478353230315C7478353737395C7478363335375C7478363933355C7478373531335C7478383039315C7478383636395C7478393234375C7478393832355C747831303430335C747831303938315C747831313535395C747831323133375C747831323731355C747831333239335C747831333837315C747831343434395C747831353032375C747831353630355C747831363138335C747831363736315C747831373333395C747831373931375C747831383439355C747831393037325C747831393635305C747832303232385C747832303830365C747832313338345C747832313936325C747832323534305C747832333131385C747832333639365C747832343237345C747832343835325C747832353433305C747832363030385C747832363538365C747832373136345C747832373734325C747832383332305C747832383839385C747832393437365C747833303035345C747833303633325C747833313231305C747833313738385C747833323336365C747833323934345C747833333532325C747833343130305C747833343637385C747833353235365C747833353833345C747833363431325C747833363939305C747833373536375C747833383134355C747833383732335C747833393330315C747833393837395C747834303435375C747834313033355C747834313631335C747834323139315C747834323736395C747834333334375C747834333932355C747834343530335C747834353038315C747834353635395C747834363233375C747834363831355C747834373339335C747834373937315C747834383534395C747834393132375C747834393730355C747835303238335C747835303836315C747835313433395C747835323031375C747835323539355C747835333137335C747835333735315C747835343332395C747835343930375C747835353438355C747835363036325C747835363634305C747835373231385C747835373739365C7061726469726E61747572616C0A0A5C66305C66733234205C636632205C436F636F614C6967617475726530205C2761625C63663320636C6173735C6366322020525446205C2762627D» en type number, date, text, boolean, missing value.” number -1700 from «data RTF 7B5C727466315C616E73695C616E7369637067313235325C636F636F61727466313236355C636F636F617375627274663231300A7B5C666F6E7474626C5C66305C666E696C5C666368617273657430204C75636964614772616E64653B7D0A7B5C636F6C6F7274626C3B5C7265643235355C677265656E3235355C626C75653235353B5C726564305C677265656E305C626C7565303B5C726564305C677265656E305C626C75653235353B7D0A5C706172645C74783537375C7478313135355C7478313733335C7478323331315C7478323838395C7478333436375C7478343034355C7478343632335C7478353230315C7478353737395C7478363335375C7478363933355C7478373531335C7478383039315C7478383636395C7478393234375C7478393832355C747831303430335C747831303938315C747831313535395C747831323133375C747831323731355C747831333239335C747831333837315C747831343434395C747831353032375C747831353630355C747831363138335C747831363736315C747831373333395C747831373931375C747831383439355C747831393037325C747831393635305C747832303232385C747832303830365C747832313338345C747832313936325C747832323534305C747832333131385C747832333639365C747832343237345C747832343835325C747832353433305C747832363030385C747832363538365C747832373136345C747832373734325C747832383332305C747832383839385C747832393437365C747833303035345C747833303633325C747833313231305C747833313738385C747833323336365C747833323934345C747833333532325C747833343130305C747833343637385C747833353235365C747833353833345C747833363431325C747833363939305C747833373536375C747833383134355C747833383732335C747833393330315C747833393837395C747834303435375C747834313033355C747834313631335C747834323139315C747834323736395C747834333334375C747834333932355C747834343530335C747834353038315C747834353635395C747834363233375C747834363831355C747834373339335C747834373937315C747834383534395C747834393132375C747834393730355C747835303238335C747835303836315C747835313433395C747835323031375C747835323539355C747835333137335C747835333735315C747835343332395C747835343930375C747835353438355C747835363036325C747835363634305C747835373231385C747835373739365C7061726469726E61747572616C0A0A5C66305C66733234205C636632205C436F636F614C6967617475726530205C2761625C63663320636C6173735C6366322020525446205C2762627D»

In the old Pages (4.x) the Find-Replace dialog offered an advanced mode allowing us to replace strings with a given style by strings with possibly an other style.
The feature was not available in old Numbers (2.x)
It’s not available in Pages (4.x) and Numbers (3.x)

It’s what is named “progress” in AppleLand.

Yvan KOENIG (VALLAURIS, France) samedi 30 août 2014 19:07:03

Oh man, I shouldn’t have ran that awful script. :stuck_out_tongue: Totally messed up one of my applescript browser.

Yes, I’m trying to have it search and replace all IN sheet 2 of table 1. I can narrow it down to range “A” but I don’t think this will help solve the current issue.

The changing the color script is nice, but doesn’t do what I need it to.
The change of color will indicate which one’s I’ve changed in Sheet 2 so it’s easier to track, however that’s not of priority at this moment. It would be a nice feature to have, but not necessary.

Column A - shows all of the words I need to search for in Sheet 2
Column B - shows all of the words I will use to replace the Column A words in Sheet 2
Sheet 2 - shows thousands of cells of text(a few sentences, a sentence or just couple of words) I need to search through and replace

Example:
Column A Column B Sheet 2 before change Sheet 2 after change


truck F-150 There is a nice truck outside There is a nice F-150 outside
car Corolla The car is yellow The Corolla is yellow
fruit apple That truck looks mean That F-150 looks mean
etc… etc… Purple is a color Purple is a color

So, if a cell in sheet 2 shows the substring of Column A, I want the substring to be replaced with Column B and for it to repeat for all of the cells in sheet 2. Hopefully this makes sense…

Is there a way to find and replace the texts in all cells of sheet 2 and not just a piece of text or a single cell? or can this only be done using GUI script? I’ve made a script to do something like this using just keystrokes, but it takes forever to finish because of each step and the delays put in place.

Is there another way of showing the changes done to Sheet 2 without having to cause more work to remove? So things like adding a small character ((),“”,//) before and after wouldn’t be a very good solution. Maybe bold, italics or underline maybe?

Hello

Here is the first part of a script able to achieve your goal.


# obsolete version, I removed it.

I disabled the instructions used to open the spreadsheet.
As is, the spreadsheet is supposed to be open at front.

I discovered an interesting feature.
Some colors are predefined.
We may call them by their names as strings.
I identified :
“black”
“red”
“orange”
“yellow”
“green”
“blue”
“purple”
“gray”
“white”

I really don’t know where AppleScript get the definition of these colors.

As written in the script, I have no time immediately available to try to code the transfert from the Pages document to the table 1 of sheet 2.
You will have to be patient - or to code it by yourself.

Yvan KOENIG (VALLAURIS, France) dimanche 31 août 2014 14:42:24

Bingo.

From my point of view, it’s awful but it works.


# obsolete version, I removed it.

I’m not sure that the inserted delays are really needed but I choose to play safety.
After testing the script as is to check that it works for you, it would be time - for you - to try to disable these delay instructions.

Oops, I quite forgot an important detail : on my machine, Pages defaults to a blank document.
If you don’t own Pages, let me know that, I would try to replace it by TextEdit which is supposed to be available for everybody.

Yvan KOENIG (VALLAURIS, France) dimanche 31 août 2014 16:21:25

Yvan,

This script is pretty sweet. I see what you’ve done there. By using pages capabilities and instead of keeping it inside of Numbers, you are able to achieve what I wanted.

I found out that I can just copy and paste the whole list of words, sentences and paragraphs directly from pages onto numbers. An issue i’ve noticed immediately is if the sentences are spaced out with the option + return key. In which it will automatically include a line in pages and therefore increase the cell count at the end.

Example:
Numbers before running script
Cell A1:
Today is a cloudy day. ¬
But it is a nice day.
Cell A2:
What a beautiful day!

After the script and copied and pasted back into Numbers:
Cell A1:
Today is a cloudy day.
Cell A2:
But it is a nice day.
Cell A3:
What a beautiful day!

Is there a way to remove the option + return key during this whole process?

I also noticed that when I tried to script the keystroke “c” while {command down} and then keystroke “v” while {command down}, I don’t get all of the cells that’s I’ve pasted, I only see 20 cells or so in the NEW Number doc, so it seems I can only repaste on the old document sheet 2.

Is there a way to expand the cells to whatever the amount of items you have in the document?

I find that with GUIscripting, the delays are necessary otherwise it may skip over a command. This method is still much faster than my GUI script where I make go down each cell and make use of the replace all under the find and replace. :confused:

Otherwise, this script works perfectly fine with changing the document accordingly.

As the version which I posted here is now obsolete, I removed it.

Yvan KOENIG (VALLAURIS, France) lundi 1 septembre 2014 21:13:39

As the version which I posted here is now obsolete, I removed it.

Yvan KOENIG (VALLAURIS, France) lundi 1 septembre 2014 21:12:59

Bingo bis.

Not only I was able to code the case where the target is a multi-columns table but I found the way to paste the edited datas in a single paste.


# I found a way to enhance the handler : handleTheObject
So I remove this version

CAUTION :
applying the required changes into the Pages document may be a bit long.
Be patient.
The job is done only when the Pages document disappear.

Of course, if somebody is able to enhance the code, I am interested.

Yvan KOENIG (VALLAURIS, France) lundi 1 septembre 2014 21:10:54

With a bit of luck I will not find ways to enhance the code.

I enhanced the handler : handleTheObject
I replaced the handler : replaceText by my handler remplace to have a coherent group of three handlers using text item delimiter.

set myDelay to 0.03

-- set leFichier to (path to desktop as text) & "theSpreadsheet.numbers:"

tell application "Numbers"
	--open file leFichier
	tell front document
		# Extracts the contents of the column to edit
		set theTarget to value of cells of rows of table 1 of sheet 2
		# Extract the strings before/after
		set theKeys to value of cells 1 thru 2 of every row of table 1 of sheet 1
	end tell
end tell

# Drop the empty entries before/after
set theUsefulKeys to {}
repeat with aCoupleOfKeys in theKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	# A really empty cell contains missing value but somebody may have set it to ""
	if item 1 of aCoupleOfKeys is not in {missing value, ""} then
		set end of the theUsefulKeys to aCoupleOfKeys
	end if
end repeat

# Drop the empty values of the datas to edit
set theText to {}
if class of item 1 of theTarget is list then
	# Here the target table has more than one column
	repeat with aValue in theTarget
		set aValue to contents of aValue
		# A really empty cell contains missing value but somebody may have set it to ""
		if item 1 of aValue is not in {missing value, ""} then
			set end of theText to my recolle(aValue, tab)
		end if
	end repeat
else
	repeat with aValue in theTarget
		set aValue to contents of aValue
		# A really empty cell contains missing value but somebody may have set it to ""
		if aValue is not in {missing value, ""} then
			set end of theText to aValue
		end if
	end repeat
end if
set nbParagraphs to (count theText) - 1

# Converts the list of values to edit into a text whose values are separated by return chars
set theText to recolle(theText, return)

# Remove possible missing value items which may exist in multi-columns target
set theText to my remplace(theText, "missing value", "")

# Edit the text replacing the strings before by the strings after enclosed between brackets
repeat with aCoupleOfKeys in theUsefulKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	set theText to my remplace(theText, my trim(item 1 of aCoupleOfKeys), "[" & my trim(item 2 of aCoupleOfKeys) & "]")
end repeat

tell application "Pages"
	activate
	make new document
	tell document 1
		set pDoc to its name
		# Fill the document with the edited text
		set body text to theText
		# Apply the color green to every item enclosed between brackets
		my handleTheObject(a reference to body text, pDoc)
	end tell
end tell
tell application "System Events" to tell process "Pages"
	keystroke "a" using {command down}
	tell me to delay myDelay
	keystroke "c" using {command down}
end tell
delay myDelay # MOVED

tell application "Numbers"
	activate # Required for GUIscripting to come
	tell document 1
		set active sheet to sheet 2 # Brings sheet 2 to front for GUIscripting to come
		tell table 1 of active sheet
			set selection range to range "A1"
		end tell
	end tell
end tell

tell application "System Events" to tell process "Numbers"
	keystroke "v" using {command down}
end tell
delay myDelay

tell application "Pages"
	close document pDoc without saving
end tell

#=====

-- Trims the words so there are no spaces before or after

on trim(someText)
	repeat until someText does not start with " "
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with " "
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

#=====

on decoupe(t, d)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

#=====

on recolle(l, d)
	local oTIDs, t
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end recolle

#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

on handleTheObject(target_object, doc_name)
	tell application "Pages" to tell document doc_name
		tell target_object
			set body_text to its contents
			# Set text enclosed between brackets in green
			set enListe to my decoupe(body_text, "[")
			if (count enListe) > 1 then
				set off7 to (count item 1 of enListe) + 2
				repeat with j from 2 to count enListe
					tell me to set off7end to off7 + (offset of "]" in enListe's item j) - 2
					set character (off7end + 1) to ""
					set (color of characters off7 thru off7end) to "green"
					set character (off7 - 1) to ""
					
					set off7 to off7 + (count item j of enListe) - 1
				end repeat
			end if
			
		end tell
	end tell
end handleTheObject

#=====

Yvan KOENIG (VALLAURIS, France) lundi 1 septembre 2014 21:38:26

For those owning Numbers but not Pages, here is a version which use a TextEdit document as temporary one.

set myDelay to 0.03

-- set leFichier to (path to desktop as text) & "theSpreadsheet.numbers:"

tell application "Numbers"
	--open file leFichier
	tell front document
		# Extracts the contents of the column to edit
		set theTarget to value of cells of rows of table 1 of sheet 2
		# Extract the strings before/after
		set theKeys to value of cells 1 thru 2 of every row of table 1 of sheet 1
	end tell
end tell

# Drop the empty entries before/after
set theUsefulKeys to {}
repeat with aCoupleOfKeys in theKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	# A really empty cell contains missing value but somebody may have set it to ""
	if item 1 of aCoupleOfKeys is not in {missing value, ""} then
		set end of the theUsefulKeys to aCoupleOfKeys
	end if
end repeat

# Drop the empty values of the datas to edit
set theText to {}
if class of item 1 of theTarget is list then
	# Here the target table has more than one column
	repeat with aValue in theTarget
		set aValue to contents of aValue
		# A really empty cell contains missing value but somebody may have set it to ""
		if item 1 of aValue is not in {missing value, ""} then
			set end of theText to my recolle(aValue, tab)
		end if
	end repeat
else
	repeat with aValue in theTarget
		set aValue to contents of aValue
		# A really empty cell contains missing value but somebody may have set it to ""
		if aValue is not in {missing value, ""} then
			set end of theText to aValue
		end if
	end repeat
end if
set nbParagraphs to (count theText) - 1

# Converts the list of values to edit into a text whose values are separated by return chars
set theText to recolle(theText, return)

# Remove possible missing value items which may exist in multi-columns target
set theText to my remplace(theText, "missing value", "")

# Edit the text replacing the strings before by the strings after enclosed between brackets
repeat with aCoupleOfKeys in theUsefulKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	set theText to my remplace(theText, my trim(item 1 of aCoupleOfKeys), "[" & my trim(item 2 of aCoupleOfKeys) & "]")
end repeat

tell application "TextEdit"
	activate
	make new document
	tell document 1
		set pDoc to its name
		# Fill the document with the edited text
		set its text to theText
		# Apply the color green to every item enclosed between brackets
		my handleTheObject(pDoc)
	end tell
end tell
tell application "System Events" to tell process "TextEdit"
	keystroke "a" using {command down}
	tell me to delay myDelay
	keystroke "c" using {command down}
end tell
delay myDelay # MOVED

tell application "Numbers"
	activate # Required for GUIscripting to come
	tell document 1
		set active sheet to sheet 2 # Brings sheet 2 to front for GUIscripting to come
		tell table 1 of active sheet
			set selection range to range "A1"
		end tell
	end tell
end tell

tell application "System Events" to tell process "Numbers"
	keystroke "v" using {command down}
end tell
delay myDelay

tell application "TextEdit"
	--	close document pDoc without saving
end tell

#=====

-- Trims the words so there are no spaces before or after

on trim(someText)
	repeat until someText does not start with " "
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with " "
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

#=====

on decoupe(t, d)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

#=====

on recolle(l, d)
	local oTIDs, t
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end recolle

#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

on handleTheObject(doc_name)
	tell application "TextEdit" to tell document doc_name
		set body_text to its text
		# Set text enclosed between brackets in green
		set enListe to my decoupe(body_text, "[")
		if (count enListe) > 1 then
			set off7 to (count item 1 of enListe) + 2
			repeat with j from 2 to count enListe
				tell me to set off7end to off7 + (offset of "]" in enListe's item j) - 2
				set character (off7end + 1) to ""
				set (color of characters off7 thru off7end) to "green" # {8607, 65513, 1548}
				set character (off7 - 1) to ""
				
				set off7 to off7 + (count item j of enListe) - 1
			end repeat
		end if
	end tell
end handleTheObject

#=====

Yvan KOENIG (VALLAURIS, France) mardi 2 septembre 2014 12:51:12

Here is a new version for those wanting to colorize cells of a selected block in any table of any sheet.
The only fixed item is the table containing the strings to find and the replacement ones.

set myDelay to 0.03

-- set leFichier to (path to desktop as text) & "theSpreadsheet.numbers:"

tell application "Numbers"
	--open file leFichier
	# Extracts parameters from the selected bloc of cells
	# We are no longer restricted to entire table 1 of sheet 2
	set {leDocument, laFeuille, laTable, numLigne1, numColonne1, numLigne2, numColonne2} to my get_SelParams()
	(*
	# Alternate scheme if you want to work upon a fixed range of cells :
set leDocument to "name of the doc"
set laFeuille to "name or index of the sheet"
set laTable to "name or index of the table"
set numLigne1 to "row number of the top of the range"
set numColonne1 to "column number of the range's first column (at left)"
set numLigne2 to "row number of the bottom of the range"
set numColonne2 to "column number of the range's last column (at right)"
*)
	tell document leDocument
		# set active sheet to sheet laFeuille # Activate it if you choose to harcode the range
		# Extracts the contents of the column to edit
		set theTarget to value of cells numColonne1 thru numColonne2 of rows numLigne1 thru numLigne2 of table laTable of sheet laFeuille
		# Extract the strings before/after
		set theKeys to value of cells 1 thru 2 of every row of table 1 of sheet 1
	end tell
end tell

# Drop the empty entries before/after
set theUsefulKeys to {}
repeat with aCoupleOfKeys in theKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	# A really empty cell contains missing value but somebody may have set it to ""
	if item 1 of aCoupleOfKeys is not in {missing value, ""} then
		set end of the theUsefulKeys to aCoupleOfKeys
	end if
end repeat

# If we selected several cells from several columns use TAB char as separator
set theText to {}
if class of item 1 of theTarget is list then
	# Here the target table has more than one column
	repeat with aValue in theTarget
		set aValue to contents of aValue
		set end of theText to my recolle(aValue, tab)
	end repeat
else
	repeat with aValue in theTarget
		set aValue to contents of aValue
		set end of theText to aValue
	end repeat
end if
set nbParagraphs to (count theText) - 1

# Converts the list of values to edit into a text whose values are separated by return chars
set theText to recolle(theText, return)

# Replace the possible linefeed embedded in cells by a character which will not cut the cell in two ones when pasting the edited datas.
# You may use an other one if you wish.
set fakeLinefeed to character id 8629 # "↵"
set theText to my remplace(theText, linefeed, fakeLinefeed)

# Remove possible missing value items which may exist in multi-columns target
set theText to my remplace(theText, "missing value", "")

# Edit the text replacing the strings before by the strings after enclosed between brackets
repeat with aCoupleOfKeys in theUsefulKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	set theText to my remplace(theText, my trim(item 1 of aCoupleOfKeys), "[" & my trim(item 2 of aCoupleOfKeys) & "]")
end repeat

tell application "TextEdit"
	activate
	make new document
	tell document 1
		set pDoc to its name
		# Fill the document with the edited text
		set its text to theText
		# Apply the color green to every item enclosed between brackets
		my handleTheObject(pDoc)
	end tell
end tell
tell application "System Events" to tell process "TextEdit"
	keystroke "a" using {command down}
	tell me to delay myDelay
	keystroke "c" using {command down}
end tell
delay myDelay # MOVED

tell application "Numbers"
	activate # Required for GUIscripting to come
	tell document leDocument
		set active sheet to sheet laFeuille # Brings back the original sheet to front for GUIscripting to come
		tell table laTable of active sheet
			set selection range to range (name of cell numLigne1 of column numColonne1)
		end tell
	end tell
end tell

tell application "System Events" to tell process "Numbers"
	keystroke "v" using {command down}
end tell
delay myDelay

tell application "TextEdit"
	--	close document pDoc without saving
end tell

#=====

-- Trims the words so there are no spaces before or after

on trim(someText)
	repeat until someText does not start with " "
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with " "
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

#=====

on decoupe(t, d)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

#=====

on recolle(l, d)
	local oTIDs, t
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end recolle

#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

on handleTheObject(doc_name)
	tell application "TextEdit" to tell document doc_name
		set body_text to its text
		# Set text enclosed between brackets in green
		set enListe to my decoupe(body_text, "[")
		if (count enListe) > 1 then
			set off7 to (count item 1 of enListe) + 2
			repeat with j from 2 to count enListe
				tell me to set off7end to off7 + (offset of "]" in enListe's item j) - 2
				set character (off7end + 1) to ""
				set (color of characters off7 thru off7end) to "green" # {8607, 65513, 1548}
				set character (off7 - 1) to ""
				set off7 to off7 + (count item j of enListe) - 1
			end repeat
		end if
	end tell
end handleTheObject

#=====

# Version pour Numbers 3.2.x
on get_SelParams()
	try
		tell application "Numbers"
			set t to front document's active sheet's first table whose selection range's class is range
			tell t's selection range
				tell first cell to set {firstRowNum, firstColNum} to {its row's address, its column's address}
				tell last cell to set {lastRowNum, lastColNum} to {its row's address, its column's address}
			end tell
			return {front document's name, t's parent's name, t's name, firstRowNum, firstColNum, lastRowNum, lastColNum}
		end tell
	on error
		display dialog "Problem getting values. Did you select cells?" buttons "Cancel"
	end try
end get_SelParams

#=====

I decided to enhance the version using TextEdit because :
(1) TextEdit is available on every machine
(2) applying the changes to a TextEdit document is a bit faster than doing the same changes in Pages.

Yvan KOENIG (VALLAURIS, France) mercredi 3 septembre 2014 18:01:55

EDITED: added the replacement of linefeeds, added instructions which may be used to apply upon a hardcoded range of cells

Excellent!

This does exactly what I want. It’s much faster than using pages.

There is a minor change I made because I noticed that my content may actually use “[” and “]”. I changed it to option + 5 (∞) in place of “[” and “]” to option + 6 (§) because these symbols are less likely used.

I noticed that for this to start, you need to be on the page with the content that needs replacing, albeit sheet 2.

There is a slight issue with what I’ve said before, if the sentences are spaced out with the option + return key. In which it will automatically include a line in pages and therefore increase the cell count at the end.

Example:
Numbers before running script
Cell A1:
Today is a cloudy day. ¬ (option + return in a cell)
But it is a nice day.
Cell A2:
What a beautiful day!

After the script and copied and pasted back into Numbers:
Cell A1:
Today is a cloudy day.
Cell A2:
But it is a nice day.
Cell A3:
What a beautiful day!

Is there a way to remove the option + return key during this whole process?

So to get around this issue, I’ve changed the paste to column2 so it doesn’t overwrite column 1 of sheet 2 to make sure I can put everything back in it’s cell.

Thank you Yvan!

OK, now I understand where is the problem.
I tried to replace the linefeed by character ID 8232 or character ID 8233 hoping that it will not break the cell when pasting back but it breaks.
In fact it breaks immediately when we put the datas in TextEdit.

So, I moved back to the old trick which I use when I insert text datas with embedded linefeed(s), I replaced them by the character id 8629 “↵”

You will find the enhanced script in my preceding message.

I will try to build a piece of code able to restore the original linefeeds when the process is ended.
It requires GUIscripting to drive the Find-Replace dialog.
I dislike that but as far as I know, there is no other track.

I assumed that my explanations were clear saying : “Here is a new version for those wanting to colorize cells of a selected block in any table of any sheet.”.
As far as I know, the only ways to be able to choose a range of cells smaller than the entire table are :

  • to select it which means that the range must be in the active sheet
  • or to hardcode the parameters defining the range with a set of instructions :
    set leDocument to “name of the doc”
    set laFeuille to “name or index of the sheet”
    set laTable to “name or index of the table”
    set numLigne1 to “row number of the top of the range”
    set numColonne1 to “column number of the range’s first column (at left)”
    set numLigne2 to “row number of the bottom of the range”
    set numColonne2 to “column number of the range’s last column (at right)”

I choose the select path which allow to use the script with different ranges.
If it doesn’t fit your need, deactivate the instruction grabbing the parameters from the selection and activate the seven instructions described above and activate the instruction
set active sheet to sheet laFeuille

Yvan KOENIG (VALLAURIS, France) jeudi 4 septembre 2014 09:59:19

ADDED :
I apologize but restoring the linefeeds after the edit process can’t be done.
When I insert the character ↵ in the find field it works
when I paste a linefeed in the replace field, the character is in fact replaced by a space.
Same behaviour if I try to replace by character ID 8232 (LINE SEPARATOR) or character ID 8233 (PARAGRAPH SEPARATOR"
If I try to insert a linefeed by hand, I get a bell signaling an unauthorized action.
One more feature which was available in Numbers v2.3 (I verified before writing that) which is gone with v3.x
I assume that we will get a complete application in 2016 or 2017. Before that we may just play with Numbers.

As I explained in an other thread, three days ago, the Numbers Find & Replace dialog replaced the linefeeds inserted by script in the Replace field by space characters.
Today ” I don’t know for which reason ” it does the correct job so here is the final script (at least I hope it is) which treat as you want cells containing linefeeds.

set myDelay to 0.03

-- set leFichier to (path to desktop as text) & "theSpreadsheet.numbers:"

tell application "Numbers"
	--open file leFichier
	# Extracts parameters from the selected bloc of cells
	# We are no longer restricted to entire table 1 of sheet 2
	set {leDocument, laFeuille, laTable, numLigne1, numColonne1, numLigne2, numColonne2} to my get_SelParams()
	(*
	# Alternate scheme if you want to work upon a fixed range of cells :
set leDocument to "name of the doc"
set laFeuille to "name or index of the sheet"
set laTable to "name or index of the table"
set numLigne1 to "row number of the top of the range"
set numColonne1 to "column number of the range's first column (at left)"
set numLigne2 to "row number of the bottom of the range"
set numColonne2 to "column number of the range's last column (at right)"
*)
	tell document leDocument
		# set active sheet to sheet laFeuille # Activate it if you choose to harcode the range
		# Extracts the contents of the column to edit
		set theTarget to value of cells numColonne1 thru numColonne2 of rows numLigne1 thru numLigne2 of table laTable of sheet laFeuille
		# Extract the strings before/after
		set theKeys to value of cells 1 thru 2 of every row of table 1 of sheet 1
	end tell
end tell

# Drop the empty entries before/after
set theUsefulKeys to {}
repeat with aCoupleOfKeys in theKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	# A really empty cell contains missing value but somebody may have set it to ""
	if item 1 of aCoupleOfKeys is not in {missing value, ""} then
		set end of the theUsefulKeys to aCoupleOfKeys
	end if
end repeat

# If we selected several cells from several columns use TAB char as separator
set theText to {}
if class of item 1 of theTarget is list then
	# Here the target table has more than one column
	repeat with aValue in theTarget
		set aValue to contents of aValue
		set end of theText to my recolle(aValue, tab)
	end repeat
else
	repeat with aValue in theTarget
		set aValue to contents of aValue
		set end of theText to aValue
	end repeat
end if
set nbParagraphs to (count theText) - 1

# Converts the list of values to edit into a text whose values are separated by return chars
set theText to recolle(theText, return)

# Replace the possible linefeed embedded in cells by a character which will not cut the cell in two ones when pasting the edited datas.
# You may use an other one if you wish.
set fakeLinefeed to character id 8629 # "↵"
set theText to my remplace(theText, linefeed, fakeLinefeed)

# Remove possible missing value items which may exist in multi-columns target
set theText to my remplace(theText, "missing value", "")

# Edit the text replacing the strings before by the strings after enclosed between brackets
repeat with aCoupleOfKeys in theUsefulKeys
	set aCoupleOfKeys to contents of aCoupleOfKeys
	set theText to my remplace(theText, my trim(item 1 of aCoupleOfKeys), "[" & my trim(item 2 of aCoupleOfKeys) & "]")
end repeat

tell application "TextEdit"
	activate
	make new document
	tell document 1
		set pDoc to its name
		# Fill the document with the edited text
		set its text to theText
		# Apply the color green to every item enclosed between brackets
		my handleTheObject(pDoc)
	end tell
end tell
tell application "System Events" to tell process "TextEdit"
	keystroke "a" using {command down}
	tell me to delay myDelay
	keystroke "c" using {command down}
end tell
delay myDelay # MOVED

tell application "Numbers"
	activate # Required for GUIscripting to come
	tell document leDocument
		set active sheet to sheet laFeuille # Brings back the original sheet to front for GUIscripting to come
		tell table laTable of active sheet
			set selection range to range (name of cell numLigne1 of column numColonne1)
		end tell
	end tell
end tell

tell application "System Events" to tell process "Numbers"
	keystroke "v" using {command down}
	tell me to delay myDelay
	keystroke "f" using {command down}
	title of window 1
	--> "Rechercher et remplacer"
	tell window result
		set value of first text field to fakeLinefeed
		set value of second text field to linefeed
		title of button -3 # To get the button's name
		--> "Tout remplacer"
		click button result
	end tell
	keystroke "w" using {command down} # Close the dialog
end tell
delay myDelay

tell application "TextEdit"
	close document pDoc without saving
end tell

#=====

-- Trims the words so there are no spaces before or after

on trim(someText)
	repeat until someText does not start with space
		set someText to text 2 thru -1 of someText
	end repeat
	
	repeat until someText does not end with space
		set someText to text 1 thru -2 of someText
	end repeat
	
	return someText
end trim

#=====

on decoupe(t, d)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

#=====

on recolle(l, d)
	local oTIDs, t
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end recolle

#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

on handleTheObject(doc_name)
	tell application "TextEdit" to tell document doc_name
		set body_text to its text
		# Set text enclosed between brackets in green
		set enListe to my decoupe(body_text, "[")
		if (count enListe) > 1 then
			set off7 to (count item 1 of enListe) + 2
			repeat with j from 2 to count enListe
				tell me to set off7end to off7 + (offset of "]" in enListe's item j) - 2
				set character (off7end + 1) to ""
				set (color of characters off7 thru off7end) to "green" # {8607, 65513, 1548}
				set character (off7 - 1) to ""
				set off7 to off7 + (count item j of enListe) - 1
			end repeat
		end if
	end tell
end handleTheObject

#=====

# Version pour Numbers 3.2.x
on get_SelParams()
	try
		tell application "Numbers"
			set t to front document's active sheet's first table whose selection range's class is range
			tell t's selection range
				tell first cell to set {firstRowNum, firstColNum} to {its row's address, its column's address}
				tell last cell to set {lastRowNum, lastColNum} to {its row's address, its column's address}
			end tell
			return {front document's name, t's parent's name, t's name, firstRowNum, firstColNum, lastRowNum, lastColNum}
		end tell
	on error
		display dialog "Problem getting values. Did you select cells?" buttons "Cancel"
	end try
end get_SelParams

#=====

Yvan KOENIG (VALLAURIS, France) samedi 6 septembre 2014 11:07:31