AppleScript Numbers: modifying individual cells within a selected range

Question

Level 1

15 points

AppleScript Numbers: modifying individual cells within a selected range

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:

Working on automating process to fill a range of cells with distinct formula based on information in the current cell's row (different columnt) and column (different row).

Sample Table:

Current Code:

on run {input, parameters}
  tell application "Numbers"
    tell table 1 of active sheet of front document
      repeat with i from 1 to the count of cells of the selection range
        set currentCell to cell i of the selection range
        my logit("CELL: [" & i & "]: " & name of currentCell)
        my processCell(currentCell)
      end repeat
    end tell
  end tell
  return input
end run

on processCell(targetCell)
  tell application "Numbers"
    tell table 1 of active sheet of front document
       -- code omitted for brevity, works as desired
      set value of targetCell to regexString
    end tell
  end tell
end processCell

to logit(log_string)
  do shell script ¬
    "echo `date '+%Y-%m-%d %T: '`\"" & log_string & "\" >> $HOME/Library/Logs/Finance.log"
end logit

Issue:

Before trying to iterate over the range of selected cells, I just processed the first one - that worked.

When I added the iteration it failed with the error message:

The action "Run AppleScript" encountered an error: "Nubers got an error: Can't get cell 2 of selection range of table 1 of active sheet of document 1. Invalid index."

In such a case (where I selected D4:D8) my log would show:

2019-07-12 19:27:43: CELL: [1]: D4

If I comment out the call to 'processCell' (making the code a no-op) I get a log like:

2019-07-12 19:27:58: CELL: [1]: D4
2019-07-12 19:27:58: CELL: [2]: D5
2019-07-12 19:27:58: CELL: [3]: D6
2019-07-12 19:27:58: CELL: [4]: D7
2019-07-12 19:27:58: CELL: [5]: D8

My assumption is that, because I'm modifing the contents of each cell as it is processed, the selection range goes away (or becomes just a single cell) afer the cell is processed and thus the full range no longer exists (and thus index 2 is no longer valid).

So - how do I process a range of cells where I am modifying each one as I go through the list?

Apple Watch Series 4, watchOS 5

Posted on Jul 12, 2019 4:53 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,306 points

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

astoller wrote:

So - how do I process a range of cells where I am modifying each one as I go through the list?

One way is to set selection range to a variable, like this:

tell application "Numbers"
	tell active sheet of front document
		tell (first table whose selection range's class is range)
			set selRng to selection range
			repeat with currentCell in cells of selRng
				-- code to do stuff to currentCell	
				set currentCell's value to 500
			end repeat
		end tell
	end tell
end tell

You can also make it a little shorter by using 'get selection range', something like this:

tell application "Numbers"
	tell active sheet of front document
		tell (first table whose selection range's class is range)
			repeat with currentCell in cells of (get selection range)
				set currentCell's value to 100
			end repeat
		end tell
	end tell
end tell

SG

Reply

Answer 2

astoller Author

Level 1

15 points

Jul 13, 2019 7:46 AM in response to SGIII

I tried the first method yesterday, but I think I must have included some extra verbiage / directives such that it didn't work (I was getting an error message that showed all the properties of all the cells in the selection range). It works now (as you wrote it).

Once again - you're a major help - thanks much SG.

I think I'm almost done - and will probably post a summation as I think this code has some merit for folks other than myself (I could be wrong, but...)

Reply

AppleScript Numbers: modifying individual cells within a selected range

Similar questions