Applescript: setting checkbox and pop-up cells

Question

Level 1

0 points

Applescript: setting checkbox and pop-up cells

This is a simple question, but I've been unable to find an answer by searching. I'm attempting to drive a Numbers '09 spreadsheet via Applescript, setting the values of cells. For most cell types, this works, but I can't seem to manage it with checkbox or pop-up cells. Here's a contrived example:

You have a tiny spreadsheet, in which cell B2 has automatic format (as shown by the cells inspector), C2 is a checkbox, and D2 is a pop-up menu with possible values "apple", "orange" and "banana". Here's a trivial script:

*tell application "Numbers"*
*tell first table of first sheet of document of front window*
*set value of cell "B2" to "test"*
*set value of cell "C2" to true*
*set value of cell "D2" to "orange"*
*end tell*
*end tell*

This successfully sets B2, but the other two cells are unchanged. For the checkbox, I've tried 0 and 1 (with and without quotes), true and false (with and without quotes), and "Checked" and "Unchecked" (mixed case and lower case), all to no effect. For the pop-up menu cell, I've tried the strings, and small integers (guessing that the menu choices might be encoded that way), to no effect.

Unfortunately, it seems that Numbers isn't recordable, so I can't use Script Editor's record function to figure this out. Short of scripting the GUI, is there any way to set the values of checkbox and pop-up cells?

Mac Pro (early 2008), iMac (late 2006), MacBook (late 2006), Mac OS X (10.5.6)

Posted on Feb 6, 2009 6:33 AM

Reply

Answer 1

Best reply

KOENIG Yvan

Level 8

41,807 points

Feb 6, 2009 7:07 AM in response to Sheepless09

Bug already reported.

Your tracking number for this issue is Bug ID# 6524403.

Hello

When I trigger this handler

--=====

on doYourDuty(c, r, t, s, d,v) (*
c = columnIndex
r = rowIndex
t = table's name
s = sheet's name
d = document's name
v = value to set *)
set theDate to (current date) as text
tell application "Numbers" to tell document d to tell sheet s to tell table t
set value of cell r of column c to v
end tell
end doYourDuty

--=====

behaves flawlessly on most of the cells but it fails if the cell contains a popup menu whose v is a menu item.

Same problem if we try to change the status of a check box.
In this case, I assumed that
set value of cell r of column c to not (value of cell r of column c)
would switch the status of the box but it does nothing.

Yvan KOENIG (from FRANCE vendredi 6 février 2009 16:06:07)

Reply

Answer 2

Sheepless09 Author

Level 1

0 points

Feb 6, 2009 7:25 AM in response to KOENIG Yvan

Thanks. That's a shame. I get the feeling that the scriptability of Numbers is rather unfinished.

Reply

Answer 3

KOENIG Yvan

Level 8

41,807 points

Feb 6, 2009 8:05 AM in response to Sheepless09

Sure
but as a first version with scripting features, it's not so bad.

Here is a list of requested features:

• a clean way to get the selection
• the ability to pick correctly the status of a blank cell.
• the ability to get the localized names of functions. They are required to create formulas in a script.
• kill the bug linked to date_time:
set t to value of cell x on a cell containing a date_time returns the GMT time
but
to set the value of a cell to a date_time, we must pass the local time.

For these four features workaround are available but they require really ugly code.

• a move feature able to move a range of cells
• a duplicate feature able to duplicate a range of cells
• the ability to set the value of items like checkbox, popups, steppers and sliders

As far as I know, no workaround available for these three ones.

But, we are lucky, they "forgot" to insert tables scripting in Pages 😟

Yvan KOENIG (from FRANCE vendredi 6 février 2009 17:05:17)

Reply