The Numbers library for Applescript supports setting the format of a range to several types, including "number", but there does not seem to be a way to set the number of decimals, or the format for negative numbers, or the thousands separator. Can someone help me with a way to do this, perhaps using System Events if there is no better way?
In Applescript, how can I set the format of a range of cells in a Numbers table to a numeric format with a specified number of decimals?
After considerable work, I figured out a solution to my problem, using System Events. Following is a subroutine that will set a range of cells to a numeric format with a specified number of digits after the decimal. It is not very elegant and probably not very stable; but it gets the job done for now. I wish I could just tell application "Numbers" to set decimals of theRange to nDigits, or something similar.
The text field that holds the number of digits is not named, so it is referred to as text field 1. At first I tried to directly set the value of that field, but numbers would not consistently recognize the change, so I resorted to repeatedly clicking the incrementor or decrementor buttons next to the field until I got the desired value.
on setNumberFormat(theTable, theRange, nDigits)
tell application "Numbers"
activate
tell theTable
set selection range to theRange
set format of theRange to number
tell application "System Events" to tell process "Numbers"
tell front window
-- turn on format panel
tell radio button "Format" of radio group 1 of toolbar 1
if value is 0 then click
end tell
-- pick "Cell" format panel
click radio button "Cell" of radio group 1
-- set number of decimals
tell scroll area 4
repeat while (value of text field 1) as number < nDigits
click button 1 of incrementor 1
end repeat
repeat while (value of text field 1) as number > nDigits
click button 2 of incrementor 1
end repeat
end tell
end tell
end tell
end tell
end tell
end setNumberFormat
Correction: My original subroutine crashes when the number of decimals is initially "Auto", so this new version adds one line that checks for that and increments the field to zero before setting the number.
on setNumberFormat(theTable, theRange, nDigits)
tell application "Numbers"
activate
tell theTable
set selection range to theRange
set format of theRange to number
tell application "System Events" to tell process "Numbers"
tell front window
-- turn on format panel
tell radio button "Format" of radio group 1 of toolbar 1
if value is 0 then click
end tell
-- pick "Cell" format panel
click radio button "Cell" of radio group 1
-- set number of decimals
tell scroll area 4
if (value of text field 1) is "Auto" then click button 1 of incrementor 1
repeat while (value of text field 1) as number < nDigits
click button 1 of incrementor 1
end repeat
repeat while (value of text field 1) as number > nDigits
click button 2 of incrementor 1
end repeat
end tell
end tell
end tell
end tell
end tell
end setNumberFormat
Okay, I know this thread is 6 years old but... I need to set the format of a Numbers cell using JXA rather than AppleScript. (I am not an AppleScripter.) Is there a way to invoke the Format>Cell panel and then somehow select "Date & Time" and then set Time to "None"?
I have a spreadsheet used as an input order form for customers, and when they fill in the order date, Number turns it into a "Date & Time" format. As this form is specific to an online shipping platform, they require a specific data upload format, so the time along with the date screws it up.
Any help at all on this super-mega appreciated.
Thanks, Jeff