AppleScript Numbers: Formatting cells for currency - with options?

Question

Level 1

15 points

AppleScript Numbers: Formatting cells for currency - with options?

Environment: Mojave, Numbers 6.1, Automator 2.9, Both Numbers and Automator are set via System Preferences > Security & Privacy > Accessibility

I know I can format a cell for currency with:

set the format of myCell to currency

What I want to know, and haven't been able to discover yet, is how to add the options of:

"Thousands Separator"
"Accounting Style"

Or, perhaps alternatively, how to specify a custom format such as:

Name: MyAccounting

Type: Number

If the rules below aren't met: -.#

Rule 1: If greather than or equal to 0: $#,###0.00

Rule 2: If less than 0: ($#,###0.00)

Again, I've searched the web but was unable to find any examples of how to define and use a custom format in AppleScript.

The only thing I've seen is where I have to make sure I have one cell explicitly formatted the way I want and then do something (I didn't track all the details yet) to copy-style / paste-style, but that just seems a bit hack-ish. I'm hoping to find a cleaner solution.

MacBook Air 13", macOS 10.14

Posted on Jul 14, 2019 6:34 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

Jul 14, 2019 7:17 AM in response to astoller

AppleScript can set the format, but as you've discovered can't directly control the separator, number of decimal places, etc.

So you're left with trying to gui-script the Inspector interface or, probably better, as you were thinking, format a cell the way you want it and gui-script Paste and Match Style.

SG

Reply

Answer 2

astoller Author

Level 1

15 points

Jul 14, 2019 3:09 PM in response to SGIII

Hmm, seems a bit like an omission / missing feature...

For now I just [re-] formatted all the cells after running my script to populate everything. Since it's mostly static data I don't really have to worry about it too much.

I'll think about setting up the source-format-cell with copy/paste-format code sometime down the road.

Thanks for letting me know so I don't spend too much time trying to find it ;-)

Reply

Answer 3

SGIII

Level 8

37,532 points

Jul 14, 2019 3:16 PM in response to astoller

If you come across a way, please be sure to post here. Meanwhile, you could ask Apple to add more support for scripting formats in Numbers via Numbers > Provide Numbers Feedback in your menu.

SG

Reply

Answer 4

astoller Author

Level 1

15 points

Jul 15, 2019 8:43 AM in response to astoller

Unfortunately, using:

=DOLLAR(SUMIFS(...))

and then pasting the resultant value over the formula leaves text of the form:

$1,234.50

which is not a number and thus multiple cells of such a format cannot be sum'd - so it's not as good a substitute as I originally thought.

Reply

Answer 5

SGIII

Level 8

37,532 points

Jul 15, 2019 7:57 AM in response to astoller

Thanks for pointing out the DOLLAR function. Never knew it existed.

SG

Reply

Answer 6

astoller Author

Level 1

15 points

Jul 15, 2019 7:44 AM in response to SGIII

Thanks for the suggestion - I just submitted it.

Interestingly, I actually found a way to get almost what I want - by changing my initial cell formula (that gets replaced with the resultant value) from

=SUMIFS(...)

to

=DOLLAR(SUMIFS(...))

This does the Thousands Separator and Accounting Style - parentheses around negative numbers, BUT the '$' is snug to the number instead of being left-justified while the digits are right-justified (the entire string is left-justified). So it's closer, but not quite "right"

Reply

AppleScript Numbers: Formatting cells for currency - with options?

Similar questions