AppleScript read a Numbers file for multiple instances of the same fomatted value and the cell above fromatted value

Not sure what you mean by "print" (so I just logged it in the Replies panel) or what your Numbers table looks like, but this should give you some ideas:

The script:

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set theKey to "0.00%"
	set theValues to the formatted value of every cell of every column --> list of lists
	set numRows to length of item 1 of theValues --> number of rows is length of a list
	repeat with aColumn in items of theValues -- each item is a column
		tell aColumn
			repeat with itemNum from 1 to numRows
				if (item itemNum as text) is theKey then
					set valAbove to item (itemNum - 1) as text
					tell me to log valAbove & return & "this is 0.00%"
				end if
			end repeat
		end tell
	end repeat
end tell

SG

FINALLY! An AppleScript that doesn't cough up an indecipherable error! That's great after bouncing off days worth of errors being flagged by every other example. Even another of your older scripts failed me. :(

You'd think there'd be plenty of scripts available to interact with Numbers, but it seems to be a fat mystery.

So, I've reversed the column and row extraction order since all my spreadsheets are left to right, so in my sheet, rows grow with additional data, but columns are fixed. Is that not typical behavior? I don't know.

But do you have a trick to extract both values and formulas row by row? That would be a dream come true :^)

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1

	set theKey to "$555,000"

	set theValues to the formatted value of every cell of every row --> list of lists

	set numColumns to length of item 1 of theValues --> number of rows is length of a list

	repeat with aColumn in items of theValues -- each item is a column

		tell aColumn

			repeat with itemNum from 1 to numColumns

				if (item itemNum as text) is theKey then

					set valAbove to item (itemNum - 1) as text

					tell me to log valAbove & return & "this is 0.00%"

				end if

			end repeat

		end tell

	end repeat

end tell

Jakib wrote:

But do you have a trick to extract both values and formulas row by row?

For example? It's hard to visualize the use case for that. You can post a screenshot with shift-command-4, drag to select area with cross-hairs, release, start new post here and use the mountains-and-moon 'Image insertion' icon beneath the compose window to insert the screenshot file from the Desktop.

Some useful examples and explanations of how to use AppleScript with Number can be found here.

SG

Hmm, I'm wanting to use it to convert the numbers logic to another language. As a spreadsheet, Numbers has it limitations in that at some point, any change to logic becomes labor intensive.

Here's a page segment from investment sheet. Note that each row is its own set of interrelated calculations.

Hi SG,

Was my example useful?

I literally don't understand what you said here. Are you ChatGPT in disguise?