Macro to Navigate the Cumbersome UI of Numbers.app

Questions & Suggestions
,
1

Good day to all.

Navigating the cumbersome UI of Numbers app can be a drag... KM is helping me A LOT to minimize this effect.

One example is formatting dates - I like to see the dates in Brazilian format = dd/mm/yy - no time shown. Frequently, when importing from Excel, the date comes up in american format = mm/dd/yyyy with time showm.

To change this is an avalanche of clicks - format/cell/automatic/date and time/date, and then select desired format - if you're not dead yet... and another bunch of clicks to remove the time stamp.

Since most of this UI elements can be only reached by clicks, the macro I wrote is replete of mouse clicking actions, with all its perils and dangers. So here is the point:

  • is there a script that could directly select the desired format, without all these tedious clicks, macro or not?

Thank you all for your help

2

It would also be useful to find an applescript to:

  • select a column/row in Numbers
  • add a column/row after
  • select a generic range

thanks

3

Hey Ricardo,

You can play around with click at found image and such like, but this sort of thing is where AppleScript excels.

This macro is a working example of selecting a specific range.

It also contains code to select a specific column or row.

The meat of the script looks like this:

tell application "Numbers"
   if document 1 exists then
      tell document 1 to tell active sheet
         set theTables to every table
         tell table 1
            # set the selection range to column "B" --> Column
            # set the selection range to row "1" -->    Row
            set selection range to range "A1:B17" --> Range
         end tell
      end tell
   end if
end tell

See the commented-out lines? They're how you select a column or row.

All the extra code in the script is to allow you to run the script again to make your selection in the next table if there is one.

-Chris

Numbers -- Examples of Selecting a Column- Row- and Range.kmmacros (6.4 KB)

Keyboard Maestro Export.png697×724 69.9 KB

2 Likes
4

Wonderful Christopher, thank you so much!!! :wave::wave:

Do you have any ideas on the formatting? I found the script below, but it is limited - you cannot give the date, number or percent the desired format, like setting number of decimals, thousands separator, sequence of day, month, year, etc.

tell application "Numbers"
   set the Formats to {text, currency, date and time, number, percent}
   tell table 1 of sheet 1 of document 1
      set format of cell "D10" to item 4 of Formats -- set the format of the first cell to currency 
   end tell
end tell

Thanks again, you rock!!

5

Hey Ricardo,

Unfortunately as far as I can see there is no way to automate a more granular format with AppleScript.

-Chris

1 Like
6

Thanks Chris

7

Hello Chris,

I think I found a way to format the decimals and thousand separator - follows:

tell application "Numbers"
   activate

   tell front document's active sheet
      tell (first table whose class of selection range is range)
         set the selectionRange to the selection range
         tell application "System Events" to keystroke "c" using {command down}
      end tell
      set format of selectionRange to automatic
      set value of cells of selectionRange to "(0.000.000,00)" -- the desired format
      set format of selectionRange to number
      tell application "System Events" to keystroke "v" using {command down, shift down, option down}
   end tell
   
end tell
8

Hey Ricardo,

Interesting.

That format doesn’t work for me, but if I use commas and a dot decimal point it does.

(I'm guessing this is due to the US-English language macOS.)

I'll have to experiment further.

Thanks.

-Chris

1 Like
9

exactly Chris,

sorry about that - the script I posted above has the format in Portuguese, just use English format and will work fine.

I am still struggling though, to get the number formatted with no decimals and with thousand separators...