AppleScript Numbers: Get value from cell specified with variables

Question

Level 1

15 points

AppleScript Numbers: Get value from cell specified with variables

Environment:

Mojave MacOS 10.14.5
Numbers 6.1 (6369)
Automator 2.9 (444.42)

Issue:

Using an AppleScript run as a Service from within Numbers I am trying to access the value of a cell immediately to the left of the current cell with a custom function with parameters for the current cell's row and column information. (I intend to get the formula from the cell, modify it, and then place the modified version into the current cell - but first things first).

Whenever I try to get the value of the desired cell with the parameterized variables - or even a hard-coded cell reference (e.g.: "B3") I get this error message:

The action "Run AppleScript" encountered an error: "The operation couldn't be completed. (com.apple.Automator error -212.)"

A slightly stripped down version of the code:

on run {input, parameters}
  tell application "Numbers"
    tell table 1 of active sheet of front document
      set currentcell to the first cell of the selection range
      set cCol to name of column of currentcell -- [*A*]
      set aa to getCellValueFromLeft(3, id of cCol) of me
      --      display notification "AA = '" & aa & "'" -- [*B*]
      set value of cell "E10" to aa -- [*C*]
    end tell
  end tell
  return input
end run

on getCellValueFromLeft(r, c)
  try
    set ch to character id (c - 1)
    set cellName to ch & r
    display notification "New Cell Name: '" & cellName & "'" -- [*D*]
    set cv to value of cell "B3"
    --or--    set cv to value of cell r of column ch
    --or--    set cv to value of cell cellName
    display notification "'" & c & " - 1' => '" & ch & "'" -- [*E*]
    return ch -- [*F*]
  end try
end getCellValueFromLeft

Notes:

[*A*] I could use '.. to address of ..' instead and then could just pass cCol as-is rather than using 'id of ...', I've been doing lots of variations of the code and haven't settled on that decision yet.
[*B*] If this 'display' is not commented out I'll get an error about 'aa' not being set if the getCellValueFromLeft() function doesn't return a value, so it's commented out for now.
[*C*] Temporary to verify I'm retrieving what I want.
[*D*] Works just fine. If I start from "C4" it says "New Cell Name: 'B4'"
[*E*] Works just fine IFF I comment out the setting of 'cv', thus this posting
[*F*] Eventually this will be [a modified version of] 'cv'

If I comment out the setting of 'cv' the code "works" but doesn't do what I ultimately want.

All my attempts to get the value of a cell using variables (and hard-coding a column-row specification) seem to fail. I cannot figure out what I'm doing wrong. Hoping someone here can enlighten me.

MacBook Air 13", macOS 10.14

Posted on Jul 11, 2019 4:03 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,306 points

Jul 11, 2019 7:25 PM in response to astoller

Below is one way to get the value of the cell immediately to the left of the current cell.

Does that do what you want?

SG

tell application "Numbers"
	tell table 1 of active sheet of front document
		set currentCell to the first cell of the selection range
		-- to get the value of the cell immediately to the left:
		set valueFromCelltoLeft to value of currentCell's row's cell ((currentCell's column's address) - 1)
	end tell
end tell

Reply

Answer 2

astoller Author

Level 1

15 points

Jul 12, 2019 6:53 AM in response to SGIII

I should mention that both Numbers and Automator are set in System Preferences > Security & Privacy > Accessibility.

I got the same error - even if I put the code directly into the main code block (instead of custom function).

I rebooted my Mac, and then it worked - if the code was in the main block, but not if I put it into a custom function.

Do I have to reset the context in the custom function (e.g.: repeat the 'tell table 1 of ...')?

Reply

Answer 3

astoller Author

Level 1

15 points

Jul 12, 2019 2:10 PM in response to astoller

I've managed to answer my own question at the end - the answer is "yes" I have to reset the context as in:

on getCellValueFromLeft(targetCell)
  tell application "Numbers"
    tell table 1  document
      return value of targetCell's row's cell ((targetCell's column's address) - 1)
    end tell
  end tell 
end getCellValueFromLeft

I haven't re-tested this specific function, but I'm playing around with some alternative code and ran into the same issue and re-declaring the context appears to have fixed that issue.

Many thank (again) SG!

Reply

Answer 4

astoller Author

Level 1

15 points

Jul 11, 2019 6:42 PM in response to astoller

I meant to include an image of an example of the spreadsheet - here it is:

Reply

AppleScript Numbers: Get value from cell specified with variables

Similar questions