AppleScript Numbers: Get formula from cell (as modifiable text)

Question

Level 1

15 points

AppleScript Numbers: Get formula from cell (as modifiable text)

Environment:

Mojave MacOS 10.14.5
Numbers 6.1 (6369)
Automator 2.9 (444.42)
- Both Numbers and Automator are set via System Preferences > Security & Privacy > Accessibility

Background:

From another posting I was able to create an AppleScript Service for Numbers that copies the content of the cell immediately to the left of the current cell into the current cell

Sample Table:

Sample Code:

on run {input, parameters}
  tell application "Numbers"
    tell table 1 of active sheet of front document
      set firstCell to the first cell of the selection range
      set sourceCell to value of firstCell's row's cell ((firstCell's column's address) - 1)
      set value of cell "E10" to sourceCell
    end tell
  end tell
  return input
end run

Issue:

The next piece of the puzzle is how to get, modify, and then paste the formula from the cell immediately to the left of the current cell into the current cell.

I've seen a number of examples for setting formulas in cells, but I haven't been able to find anything that [clearly, to me] shows how to retrieve a formual from a cell with AppleScript.

As near as I can tell, cells do not have a 'formula' property, only 'column', 'name', 'row', and 'value' - the last of which only seems (through my trials) to copy the resulting value of the formula, not the formula itself.

Can someone shed some light (for me) on how one can retrieve the formula from a specified cell, as text (so I can modify it) with AppleScript?

MacBook Air 13", macOS 10.14

Posted on Jul 12, 2019 6:59 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,550 points

Jul 13, 2019 1:42 AM in response to astoller

how one retrieves the formula from a specified cell.

formula is a property of a cell. It can be retrieved with something like this.

tell application "Numbers"
	tell active sheet of front document
		tell (first table whose selection range's class is range)
			return formula of cell "B2"
		end tell
	end tell
end tell

If there is no formula in the cell the value returned is missing value.

SG

Reply

Answer 2

astoller Author

Level 1

15 points

Jul 14, 2019 3:03 PM in response to SGIII

Ah - well, that's what I get for looking at https://iworkautomation.com/numbers/cell.html - it doesn't list formula as a property of cell so I didn't try it. As it is, it made me think a bit outside the box and I think I came up with better code for it.

Thanks for following through on this.

Reply

Answer 3

astoller Author

Level 1

15 points

Jul 15, 2019 7:50 AM in response to SGIII

Didn't know about the Script Editor - Library thing. I was using Automator which doesn't seem to have that information (though it certainly accepts the code)

Reply

Answer 4

astoller Author

Level 1

15 points

Jul 12, 2019 4:46 PM in response to astoller

I figured out a work-around for this issue by putting elements of the formula into cells in the row (earlier column) or column (earlier row) to construct the formula and insert it into the selected cell. So the priority of this (for me) is significantly lower than when I originally posted it.

I'm still curious though how one retrieves the formula from a specified cell.

Reply

Answer 5

SGIII

Level 8

37,550 points

Jul 14, 2019 3:12 PM in response to astoller

astoller wrote:

https://iworkautomation.com/numbers/cell.html - it doesn't list formula as a property of cell

That's an excellent resource. And I would guess your are doing this already, but don't forget to search/browse the Dictionary (in Script Editor, File > Open Dictionary ... and choose Numbers.app.) Here is the relevant entry for the properties of a cell:

SG

Reply

AppleScript Numbers: Get formula from cell (as modifiable text)

Similar questions