Quickly fill in blank cells (Excel equivalent)?

Hi DaverDee,

Here is an AppleScript that does what you want in Numbers 3.

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

tell selection range

set pv to ""

repeat with c in cells

set v to c's value

if v is not missing value then

set pv to v

else

set c's value to pv

end if

end repeat

end tell

end tell

end tell

end tell

To run:

1. Copy-paste script into Script Editor
2. Select range in Numbers where you want to fill in blanks.
3. Click run button in Script Editor

If you want to run this from the menu rather than Script Editor then you can put it in the Numbers script menu (in Library > Scripts > Applications > Numbers) or in an Automator Service (where you could also attach a keyboard shortcut).

SG

Is this code unique to Numbers 3?

Yes, Numbers 3 makes a lot of things easier in AppleScript. I'll see if I can modify for Numbers 2.

SG

Try this with Numbers 2.3:

tell application "Numbers"

tell front document

tell (first sheet whose tables's selection range's class contains range)

tell (first table whose selection range's class is range)

tell selection range

set pv to ""

repeat with c in cells

set v to c's value

if v is not 0 then

set pv to v

else

set c's value to pv

end if

end repeat

end tell

end tell

end tell

end tell

end tell

Result:

When running the script be sure not to have both Numbers 2 and Numbers 3 running at the same time.

SG

Addendum: Haven't tested in Numbers 2 what happens if you have 0's in cells. AppleScript in Numbers 2 treats a blank as 0 rather than "" or missing value, an anomaly corrected in Numbers 3.

Excellent annotation of what I was doing....

On this:

— If the current cell value stored in the variable 'v' is not zero, then copy the value 'v' to the variable 'pv'.

ifvisnot 0 thensetpvtov

This might be a little better:

– If the variable 'v' is not 0 (if the Numbers 2 cell is not blank) then copy the value in 'v' to the variable 'pv'

I used 'pv' as shorthand for 'previous value' or value in the previous cell.

AppleScript is relatively easy to read and tinker with. But as you have discovered there are all kinds of "gotchas" when trying to put together something from scratch that actually does what you want it to do. For example, getting this one to work on both versions of Numbers took a bit of fiddling about in Script Editor to see what works and what doesn't.

SG

Hello

If you wish, you can do it manually as follows in a similar way explained in the linked article on Excel.

E.g., given source table as Table 1, set formula in A3 to =A2 and select A3 and copy it to clipboard:

and apply filter to show only rows with blank cell in column A and select target rows:

and paste from clipboard and unapply filter:

which should result in what you want. (Copy column A and paste values on column A if necessary.)

And just in case, here's a script written for Numbers v2, which can distinguish empty cell from cell with number 0 or formula ="".

In order to use it, select the target range (which may be across multiple columns) and run the script.

_main() on _main() script o property pp : {} on _undo(ctx) tell ctx to activate tell application "System Events" to keystroke "z" using {command down} do shell script "sleep 0.2" end _undo tell application "Numbers" -- v2 tell document 1 tell (sheet 1 whose tables's selection range's class contains range) tell (table 1 whose selection range's class is range) if not (exists) then return tell selection range set {i1, i2} to {row 1's address, row -1's address} set format to text -- set format to text [1] set pp to columns's cells i1 thru i2's value my _undo(it) -- undo format change repeat with j from 1 to count my pp set t to {} repeat with i from i1 to i2 tell column j's cell i if my pp's item j's item (i - i1 + 1) = 0.0 then -- empty if t ≠ {} then set value to t else set t to value end if end tell end repeat end repeat end tell end tell end tell end tell end tell (* [1] An empty cell has value 0.0 even if it is formatted as text in Numbers v2. Using this behaviour, empty cell can be distinguished from cell with number 0 or formula ="". *) end script run script o end _main

E.g.,

Script is tested with Numbers v2.0.5 under OS X 10.6.8.

Hope this may help,

H

Hello SGIII,

Im wondering if you can help me with a Applescript numbers issue.

Im wanting to use Applescript to locate blank cells in Numbers and fill them in with a "."

Is this posible.

I thank you in advance for any help you provide.

Sean

seanlamonby wrote:

Hello SGIII,

Im wanting to use Applescript to locate blank cells in Numbers and fill them in with a "."

Hi Sean,

This script will work with Numbers 3.

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

tell selRng to repeat with c in cells

tell c to if value is missing value then set value to "."

end repeat

end tell

end tell

end tell

As with the scripts above you select cells in Numbers before running the script. It will replace blanks in the selection with .

SG

Amazing, this worked a treat.

Thank you so much 🙂

Sean