Hi,
I need to work with a Numbers file that have some products. The thing is in the way it is exported from the system, it doesn’t repeat the name in all rows. I mean, it looks like this:
Product name Description Component
Name 1 Description 1 Component 1
… Component 2
… Component 3
… Component 4
Name 2 Description 2 Component 5
… Component 6
… Component 7
… Component 8
My script fill the blank cells with the previous value. So it looks like:
Name 1 Description 1 Component 1
Name 1 Description 1 Component 2
Name 1 Description 1 Component 3
Name 1 Description 1 Component 4
Name 2 Description 2 Component 5
Name 2 Description 2 Component 6
Name 2 Description 2 Component 7
Simple. But I need to run in 3500 rows, and it takes some time testing the cells. Is there a way to make it work just on empty cells?
This is what I have:
set myRowNumber to first row's address of last row
repeat with i from 3 to myRowNumber
repeat with c from 1 to 4
set currentCell to (item c of columnLetters) & i
if value of cell ((item c of columnLetters) & i) is missing value then
set value of cell ((item c of columnLetters) & i) to value of cell ((item c of columnLetters) & (i - 1))
end if
end repeat
end repeat
Hi Luiz.
This can be quite interesting as it’s possible to set a whole range of cells to a single value. So assuming that the gaps are in columns A and B and that they’re reliably three out of every four rows, you could do something like this:
tell application "Numbers"
activate
tell document 1
tell table 1 of sheet 1
repeat with r from 1 to (row count) by 4
tell column "A"
set value of cells (r + 1) thru (r + 3) to value of cell r
end tell
tell column "B"
set value of cells (r + 1) thru (r + 3) to value of cell r
end tell
end repeat
end tell
end tell
end tell
However, it takes over a minute to do 3,500 rows on my slower machine — not just because of the repeated settings but because Numbers insists on doing fancy things with the display on each setting.
The fastest way I know to tackle the problem is to grab the relevant current values and create a CSV text containing what’s actually wanted, which is then pasted into the document over the relevant columns. It doesn’t assume groups of four entries, but it does use scripted keystrokes, which you may need to allow in “System Settings”.
tell application "Numbers"
activate
tell document 1
tell table 1 of sheet 1
set rowCount to row count
set selection range to range ("A1:B" & rowCount)
set everything to value of cells 1 thru 2 of rows -- Columns A & B.
end tell
end tell
end tell
set {currentAVal, currentBVal} to everything's beginning
set everything's item 1 to currentAVal & "," & currentBVal
repeat with r from 2 to rowCount
set {aVal, bVal} to my everything's item r
if (aVal is missing value) then
set aVal to currentAVal
else
set currentAVal to aVal
end if
if (bVal is missing value) then
set bVal to currentBVal
else
set currentBVal to bVal
end if
set my everything's item r to quote & aVal & quote & "," & (quote & bVal & quote)
end repeat
set the clipboard to join(everything, linefeed)
tell application "System Events"
set frontmost of application process "Numbers" to true
keystroke "v" using {shift down, option down, command down}
end tell
on join(lst, delim)
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to delim
set txt to lst as text
set AppleScript's text item delimiters to astid
return txt
end join
Wow, this is great, took a second to fill all the rows.
I was thinking yesterday that maybe I could make a list of “all cells with missing values” and make the script work just on them, but your solution is much faster, thank you.
In the second part of the same script I’m using keystroke too. What I did was sort the table by a column named “Language”, so all the products of each language will be together. After that, it checks if current value is different from the previous (what means a new language started), and set a range for copy. It copies this range and paste in a tab just for that language.
set inicialR to 2
repeat with i from 3 to myRowNumber
set currentCellLanguage to "C" & i
set currentCellLanguagePrev to "C" & i - 1
set myLingua to value of cell currentCellLanguage
set myLinguaPrev to value of cell currentCellLanguagePrev
if myLingua is not equal to value of cell currentCellLanguagePrev then
set theRange to "A" & inicialR & ":L" & i - 1
set selection range to (range theRange)
set inicialR to i
tell application "System Events" to keystroke "c" using command down
delay (1)
tell thisDocument to set active sheet to sheet myLinguaPrev
add row above row 2
set selection range to range "A2"
tell application "System Events" to keystroke "v" using {command down}
delay (1)
tell thisDocument to set active sheet to sheet 1
end if
end repeat
I always thought using Copy/Paste was kind of a cheat from my part, but you showed me it make sense sometimes.
Thank you again,
Luiz
I was interested by the approach taken by Nigel to prevent word-splitting across spreadsheet cells and verified my understanding with the script included below. Testing for NSNull had me stumped, but a forum search led me to a solution from Shane. Instead of copying the modified string to the clipboard, it can be saved in a CSV file and opened by Numbers, although pasting the clipboard seems to work better.
use framework "Foundation"
use scripting additions
tell application "Numbers" to set theList to formatted value of every cell of every row of table 1 of sheet 1 of document 1
display dialog "The contents of the frontmost spreadsheet will be edited and the result placed on the clipboard"
set theArray to current application's NSMutableArray's arrayWithArray:theList
set newArray to current application's NSMutableArray's new()
repeat with aSubarray in theArray
if ((aSubarray's objectAtIndex:0)'s isKindOfClass:(current application's NSNull)) then
(aSubarray's replaceObjectAtIndex:0 withObject:valueA)
(aSubarray's replaceObjectAtIndex:1 withObject:valueB)
else
set valueA to (aSubarray's objectAtIndex:0)
set valueB to (aSubarray's objectAtIndex:1)
end if
set aString to (aSubarray's componentsJoinedByString:"\",\"")
set aString to current application's NSString's stringWithFormat_("\"%@\"", aString)
(newArray's addObject:aString)
end repeat
set theString to (newArray's componentsJoinedByString:linefeed)
set theString to theString's stringByReplacingOccurrencesOfString:"<null>" withString:""
set the clipboard to (theString as text)
Hi peavine.
Thanks for your interest. Your ASObjC script has some interesting features and seems to take around the same time as my effort with 100 rows or so, although it’s somewhat slower with 3500. It assumes that the first two cells in any particular row will have the same empty/non-empty state as each other, but I guess that’s OK for the situation described by Luiz.
I’m not so sure about the assumption that aSubarray will be mutable. It is in my tests, but I don’t recall reading anything which says this is guaranteed.
However, sweeping that under the carpet for now 
, here’s a variation on your script. It has no practical advantage for either time or effectiveness. It’s just for interest.
use framework "Foundation"
use scripting additions
tell application "Numbers" to set theList to formatted value of every cell of every row of table 1 of sheet 1 of document 1
display dialog "The contents of the frontmost spreadsheet will be edited and the result placed on the clipboard"
set theArray to current application's NSArray's arrayWithArray:theList
set theIndices to current application's NSIndexSet's indexSetWithIndexesInRange:{0, 2} -- Two indices starting at 0.
set newArray to current application's NSMutableArray's new()
repeat with aSubarray in theArray
if (aSubarray's firstObject()'s isKindOfClass:(current application's NSNull)) then
(aSubarray's replaceObjectsAtIndexes:theIndices withObjects:valuesAandB)
else
set valuesAandB to (aSubarray's objectsAtIndexes:theIndices)
end if
set aString to (aSubarray's componentsJoinedByString:"\",\"")
set aString to current application's NSString's stringWithFormat_("\"%@\"", aString)
(newArray's addObject:aString)
end repeat
set theString to (newArray's componentsJoinedByString:linefeed)
set theString to theString's stringByReplacingOccurrencesOfString:"<null>" withString:""
set the clipboard to (theString as text)
Hmm. Now I’m in this deep, I may as well attempt a version which doesn’t assume aSubarray’s mutable. 
use framework "Foundation"
use scripting additions
tell application "Numbers" to set theList to formatted value of every cell of every row of table 1 of sheet 1 of document 1
display dialog "The contents of the frontmost spreadsheet will be edited and the result placed on the clipboard"
set theArray to current application's NSArray's arrayWithArray:theList
set indexSet1 to current application's NSIndexSet's indexSetWithIndexesInRange:{0, 2} -- Two indices starting at 0.
set indexSet2 to current application's NSIndexSet's indexSetWithIndexesInRange:{2, (theArray's firstObject()'s |count|()) - 2}
set newArray to current application's NSMutableArray's new()
repeat with aSubarray in theArray
if (aSubarray's firstObject()'s isKindOfClass:(current application's NSNull)) then
set aSubarray to (valuesAandB's arrayByAddingObjectsFromArray:(aSubarray's objectsAtIndexes:indexSet2))
else
set valuesAandB to (aSubarray's objectsAtIndexes:indexSet1)
end if
set aString to (aSubarray's componentsJoinedByString:"\",\"")
set aString to current application's NSString's stringWithFormat_("\"%@\"", aString)
(newArray's addObject:aString)
end repeat
set theString to (newArray's componentsJoinedByString:linefeed)
set theString to theString's stringByReplacingOccurrencesOfString:"<null>" withString:""
set the clipboard to (theString as text)
Thanks Nigel for looking at my script and the suggestions.
I unnecessarily duplicated three lines of code in the if statement–I should have caught that. I’ve never much used index sets, but your script is a great example of their use.
As regards aSubarray being mutable, that was an error on my part, and I’m surprised the script works. It’s odd that the subarray of an immutable array is mutable. Your solution works great, and I’ve included two other alternatives below (plus some miscellaneous testing). I’ve edited my script above to fix the duplication and error.
use framework "Foundation"
use scripting additions
--as expected
set theArray to current application's NSArray's arrayWithArray:{"a", "b"}
theArray's className() as text -->"__NSArrayI"
--as expected
--set theList to {{"a", "b"}, {"c", "d"}}
--set theArray to current application's NSArray's arrayWithArray:theList
--theArray's className() as text --> "__NSArrayI"
--not expected
--set theList to {{"a", "b"}, {"c", "d"}}
--set theArray to current application's NSArray's arrayWithArray:theList
--set theSubarray to theArray's objectAtIndex:0
--theSubarray's className() as text -->"__NSArrayM"
--my first solution
--set theList to {{"a", "b"}, {"c", "d"}}
--set theArray to current application's NSArray's arrayWithArray:theList
--set theSubarray to (theArray's objectAtIndex:0)'s mutableCopy()
--theSubarray's className() as text -->"__NSArrayM"
--my second solution (assumes making an array mutable makes its subarrays mutable)
--set theList to {{"a", "b"}, {"c", "d"}}
--set theArray to current application's NSMutableArray's arrayWithArray:theList
--set theSubarray to (theArray's objectAtIndex:0)
--theSubarray's className() as text -->"__NSArrayM"
--just to check
--set theList to {"a", "b", "c", "d"}
--set theArray to current application's NSArray's arrayWithArray:theList
--theArray's className() as text -->"__NSArrayI
--just to check
--set theList to {"a", "b", "c", "d"}
--set theArray to current application's NSMutableArray's arrayWithArray:theList
--theArray's className() as text -->"__NSArrayM"
--just to check
--set theList to {"a", "b", "c", "d"}
--set theArray to (current application's NSArray's arrayWithArray:theList)'s mutableCopy()
--theArray's className() as text -->"__NSArrayM"
I did a little research to better understand the above.
When a cell in a Numbers spreadsheet is empty, the missing value constant is used. The documentation defines this term as:
Missing value Constant. The missing value constant is a placeholder for missing or uninitialized information
When a list containing a missing value is bridged to an array, the NSNull class is used in place of missing value. NSNull is defined in the documentation as:
NSNull. A singleton object used to represent null values in collection objects that don’t allow nil values.
To test for NSNull, you use isKindOfClass, which is a method in the NSObject class. I guess in a very-generalized sense, the use of isKindOfClass in this instance is functionally equivalent to the isEqualToString method in NSString. From the documentation:
isKindOfClass. Returns a Boolean value that indicates whether the receiver is an instance of given class or an instance of any class that inherits from that class.
So, this all seems to make sense. The fact that NSNull is a class all by itself seems odd, but I suspect there’s a good reason for this. The following is a simple demonstration of the above:
use framework "Foundation"
set theList to {"a", missing value, "b"}
set theArray to current application's NSMutableArray's arrayWithArray:theList
if (theArray's objectAtIndex:1)'s isKindOfClass:(current application's NSNull) then
theArray's replaceObjectAtIndex:1 withObject:"OK"
end if
theArray as list --> {"a", "OK", "b"}