Revisiting an old Excel script for Numbers (fill value down).

Hello, again, everybody.

With the move to Catalina killing off 32 bit apps I chose to walk away from some of the legacy software I’d been using (most notably Excel and the Adobe apps) and try out some of the alternatives that have sprung up.

Numbers has been an interesting beast. frustrating for some things, but sooooo much nicer for others.

I’ve come across a new context for an old script of mine that the folks here were kind enough to help me with back in 2009.

I have a Numbers document with about 48,000 rows where I need to start at the top and fill down values into empty cells and then recognize when the next cell is no longer empty and copy its contents and fill them down until the next non-empty cell…

Example:
B1 “August 12, 2019”
B2
B3 “August 13, 2019”
B4
B5
B6 “August 16, 2019”
B7
B8
B9

would become:
B1 “August 12, 2019”
B2 “August 12, 2019”
B3 “August 13, 2019”
B4 “August 13, 2019”
B5 “August 13, 2019”
B6 “August 16, 2019”
B7 “August 16, 2019”
B8 “August 16, 2019”
B9 “August 16, 2019”

I modified the Excel script and it compiles ok, but get an error "Numbers got an error: Can’t get cell “B10” of table 1 of sheet 1 of document id “66C7C9FE-25F9-405D-A77B-5C59889EEC34”:

tell application "Numbers"
	activate
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set TotalRows to row count
			set cell_No to TotalRows
			set cell_dest to "not empty"
			set cell_source to "not empty"
			repeat until cell_No is "1"
				set cell_source to value of cell ("A" & cell_No)
				set cell_dest to value of cell ("A" & cell_No + 1)
				if cell_dest is "" then
					set value of cell ("A" & cell_No + 1) to cell_source
				end if
				set cell_No to cell_No + 1
			end repeat
		end tell
	end tell
end tell

when i try to streamline things and take it one at a time I get "Numbers got an error: Can’t get cell “B2”:

tell application "Numbers"
	--	tell document 1
	--		tell active sheet
	--			set the selectedTable to (the first table whose class of selection range is range)
	--		end tell
	--		tell selectedTable
	set cell_No to 2
	set cell_contents to "not empty"
	--	repeat until cell_contents is ""
	set cell_contents to value of cell ("B" & cell_No)
	--		if cell_contents is not "" then
	--			set value of cell ("B" & cell_No + 1) to cell_contents
	--		end if
	--		set cell_No to cell_No + 1
	--	end repeat
end tell
--	end tell
--end tell

and if i uncomment the beginning i get “missing value”

tell application "Numbers"
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set cell_No to 2
			set cell_contents to "not empty"
			--	repeat until cell_contents is ""
			set cell_contents to value of cell ("B" & cell_No)
			--		if cell_contents is not "" then
			--			set value of cell ("B" & cell_No + 1) to cell_contents
			--		end if
			--		set cell_No to cell_No + 1
			--	end repeat
		end tell
	end tell
end tell

if feels like i’m missing something insanely obvious. any insights?

(1) your instruction :

repeat until cell_No is "1"

is equivalent to

repeat

because, as cell_No is a number the value “1” (a string) will never be reached.

(2) when I execute your script on a table with 20 rows, the log history
display:

tell application "Numbers"
	activate
	get table 1 of active sheet of document 1 whose class of selection range = range
		--> table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
	get row count of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> 20
	get value of cell "A20" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> error number -1728 from cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
Résultat :
error "Erreur dans Numbers : Il est impossible d’obtenir cell \"A21\" of table 1 of sheet 1 of document id \"47821688-D7F9-4E04-BF6E-CF5BBDF61457\"." number -1728 from cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"

It’s perfectly normal because you are asking for the value of a cell which doesn’t exist.
To get rid of that I added a try / on error / end try block:

tell application "Numbers"
	activate
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set TotalRows to row count
			set cell_No to TotalRows
			set cell_dest to "not empty"
			set cell_source to "not empty"
			repeat until cell_No is 1 --"1"
				set cell_source to value of cell ("A" & cell_No)
				try -- ADDED
					set cell_dest to value of cell ("A" & cell_No + 1)
				on error -- ADDED
					exit repeat -- exit the loop when we ask for a non-existing cell -- ADDED
				end try -- ADDED
				if cell_dest is "" then
					set value of cell ("A" & cell_No + 1) to cell_source
				end if
				set cell_No to cell_No + 1
			end repeat
		end tell
	end tell
end tell

This time the log history become :

tell application "Numbers"
	activate
	get table 1 of active sheet of document 1 whose class of selection range = range
		--> table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
	get row count of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> 20
	get value of cell "A20" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> error number -1728 from cell "A21" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
end tell

We may look at what you are really doing:
You enter the loop with cell_No set to 20 ( the number of rows)
You set cell_source to the value of cell “A20” which is correct
then you try to set cell_dest to the value of cell “A21” which doesn’t exist so an error is logically issued and we exit the loop.

I edited the script to a more logical one:

tell application "Numbers"
	activate
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set TotalRows to row count
			set cell_No to 1 -- EDITED
			set cell_dest to "not empty"
			set cell_source to "not empty"
			repeat with cell_No from 1 to TotalRows - 1 -- EDITED
				set cell_source to value of cell ("A" & cell_No)
				set cell_dest to value of cell ("A" & cell_No + 1)
				if cell_dest is "" then
					set value of cell ("A" & cell_No + 1) to cell_source
				end if
				set cell_No to cell_No + 1
			end repeat
		end tell
	end tell
end tell

This time it scan correctly the entire table with this log history:

tell application "Numbers"
	activate
	get table 1 of active sheet of document 1 whose class of selection range = range
		--> table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
	get row count of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> 20
	get value of cell "A1" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A2" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A2" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A3" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A3" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A4" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A4" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A5" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A5" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A6" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A6" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A7" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A7" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A8" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A8" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A9" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A9" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A10" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A10" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A11" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A11" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A12" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A12" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A13" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A13" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A14" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A14" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A15" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A15" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A16" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A16" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A17" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A17" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A18" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A18" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A19" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A19" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
	get value of cell "A20" of table 1 of sheet 1 of document id "47821688-D7F9-4E04-BF6E-CF5BBDF61457"
		--> missing value
end tell
Résultat :
20

This make visible an other problem.
It appears that you assume that the value of an empty cell is the empty string “”.
It’s perhaps true for Merdosoft Excel, but it’s wrong for Numbers.
With this app, the value of an empty cell is : missing value.
I am speaking of a really empty cell.
If the cells are filled by formulas, it’s the formula which defines what it will store in a cell containing nothing. I saw formulas setting this value to “”, others setting it to " ".
Assuming that your formula uses “”, the test must be edited as :

tell application "Numbers"
	activate
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set TotalRows to row count
			set cell_No to 1 -- EDITED
			set cell_dest to "not empty"
			set cell_source to "not empty"
			repeat with cell_No from 1 to TotalRows - 1 -- EDITED
				set cell_source to value of cell ("A" & cell_No)
				set cell_dest to value of cell ("A" & cell_No + 1)
				if cell_dest is in {"", missing value} then -- EDITED
					set value of cell ("A" & cell_No + 1) to cell_source
				end if
				set cell_No to cell_No + 1
			end repeat
		end tell
	end tell
end tell

Once again the code scan the entire table.
It may be time to re-read your message. You wrote about cells of column “B” but you scan cells of column “A”.
This pushed me to edit again as:

tell application "Numbers"
	activate
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set TotalRows to row count
			set cell_No to 1 -- EDITED
			set cell_dest to "not empty"
			set cell_source to "not empty"
			repeat with cell_No from 1 to TotalRows - 1 -- EDITED
				set cell_source to value of cell ("B" & cell_No) -- EDITED
				set cell_dest to value of cell ("B" & cell_No + 1) -- EDITED
				if cell_dest is in {"", missing value} then -- EDITED
					set value of cell ("B" & cell_No + 1) to cell_source -- EDITED
				end if
				set cell_No to cell_No + 1
			end repeat
		end tell
	end tell
end tell

Now, what a surprise, the script does what it was supposed to do.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 23 mars 2020 19:25:42

D’oh! in the sample i had pasted i had been trying a couple things and didn’t realize i hadn’t changed the A back to B. Sorry if that was confusing. The same is true of the “repeat until cell_No is ‘1’” problem. i had tried working backwards to see if that would make more sense and i forgot to change that one back. Oh. i also just realized that you meant the quotes around the 1 were a problem.

The insight into numbers and excel handling blanks cells differently was very valuable, thank you.

I’m out of the house right now, but I’ll respond more later to see if I have properly learned the lessons you taught. the script you made definitely works though and for that I am very grateful.

I appreciated the “merdosoft excel”. I don’t speak french, but that’s a word that I recognize. hahahah.

-david

Here is a cleaned version :

tell application "Numbers"
	activate
	tell document 1
		tell active sheet
			set the selectedTable to (the first table whose class of selection range is range)
		end tell
		tell selectedTable
			set TotalRows to row count
			tell column 2
				repeat with cell_No from 1 to TotalRows - 1
					set cell_source to value of cell cell_No
					tell cell (cell_No + 1)
						set cell_dest to its value
						if cell_dest is in {"", missing value} then
							set its value to cell_source
						end if
					end tell
				end repeat
			end tell
		end tell
	end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 23 mars 2020 21:53:47