More AppleScript with Numbers - simple cell referencing

Question

Level 1

8 points

More AppleScript with Numbers - simple cell referencing

I am trying to build references to a series of cells in a table. Here is a simple example - the table is Politicians with some number of records. I need a script to reference the contents of a series of cells in a single row.

Here, there is a pointer cell called Cursor. Cursor contains the address of the first cell of reference, in this instance B3, which is the ID of Abraham Lincoln's record. From here, I wish to build cell reference for the next 3 columns after ID, to get that record's Name, Position and City. So there are some levels of indirection necessary.

set cursorPtr to "A2" --Pointer to current record

-Get cell addresses of current record

set cellID to value of cell cursorPtr --should be "B3"

--Here seems I need to use row and column identifiers/constructs but I'm stuck manipulating them

set cellName to value of cell X --need to construct X = "B3" + 1 column = "C3"

set cellPosition to value of cell Y --Y = "B3" + 2 columns = "D3"

set cellCity to value of cell Z --Z = "B3" + 3 columns = "E3"

set recordID to value of cell cellID --(B3) should be 895

set recordName to value of cell cellName --(C3) should be "Abraham Lincoln"

set recordPosition to value of cell cellPosition --(D3) should be "Senator"

set recordCity to value of cell cellCity --(E3) should be "Springfield IL"

I tried:

set recordRow to row of (cell cellID) --cellID = "B3"

--would need similar for column

and got:

error "Numbers got an error: Can’t get cell \"B3\"." number -1728 from cell "B3"

How do I get integers for cell references to work with and increment the columns, to reconstruct additional cell references?

MacBook Pro (2017 – 2020)

Posted on Apr 17, 2024 10:47 AM

Reply

Answer 1

Badunit

Level 7

25,988 points

Apr 18, 2024 4:18 PM in response to smrc8081

This is one way to do it. I didn't know how your script started (i.e., how it know which table was selected) so I started it with some code from SGIII to get to the "selected" document, sheet, and table.

tell application "Numbers"
	tell front document's active sheet
		tell (first table whose selection range's class is range)
			set thecell to cell (value of cell "A2")
			set therow to (row of thecell)
			
			set recordID to value of cell 2 of therow
			set recordName to value of cell 3 of therow
			set recordPosition to value of cell 4 of therow
			set recordCity to value of cell 5 of therow
		end tell
	end tell
end tell

Reply

Answer 2

smrc8081 Author

Level 1

8 points

Apr 18, 2024 2:52 PM in response to smrc8081

FYI got this to work:

set ColumnString to "ABCDEFGHIJ" --Probably a better way but this worked

--cell_ID = "B3"

set Row_ID to cell cell_ID's row's address --(3)

set Col_ID to cell cell_ID's column's address --(2)

--Get cell references in "A1" format

set cellName to character (targetCol + 1) of ColumnString & (targetRow as text) --C3

set cellPosition to character (targetCol + 2) of ColumnString & (targetRow as text) --D3

set cellCity to character (targetCol + 3) of ColumnString & (targetRow as text) --E3

Cheers!

Reply

More AppleScript with Numbers - simple cell referencing

Similar questions