Apple Event: May 7th at 7 am PT

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Best reply

Posted on Jul 13, 2019 1:42 AM

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




SG

Similar questions

5 replies
Question marked as Best reply

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




SG

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

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.

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

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.