Benches Uncategorized

5

Cell Formatting with applescript

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

Is there any way to set the number of decimal places a cell will display with applescript? Looking through the dictionary I found the 'format' property of a range of cells, but all it seems to be able to do is specify 'number' or 'currency' or another default format. I want to limit the number of decimals that show up for irrational numbers and all of the default formats just seem to fill the entire cell with numbers. Any ideas?

Thanks,

Chad

5 replies

Sign in or join Beachball Help to reply.

  1. 0

    Barry

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    The format menu contains "Copy Style" and "Paste Style" items with keyboard shortcuts. Copying the style from a cell with the format set to a fixed number of places after the decimal, then pasting that style into another cell did set the target cell to the same nuber of places. If you can set the number of places you want in one cell, you may be able to use AppleScript to select and paste the style into one cell or a range of cells.

    Regards,

    Barry

  2. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Which is exactly your goal ?

    (1) leave the internal cell contents irrational and display only a given number of decimal digits

    (2) store in the cell a value with no more that a given decimal digits.

    Case two may be treated with vanilla Applescript. Insert in the cell a ROUNDing formula

    Case one requires the use of GUI scripting. It's what is implicit in the Barry's responce.

    User uploaded file

    Cells D2 and D3 contain the same formula : =B/C

    The format of cell D2 is set to display 3 decimal digits.

    The posted skeleton copy D2's style and paste it to D3.

    --


    (* just a skeleton *)
    --=====
    on run
    my activateGUIscripting()

    tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1

    set selection range to range "D2"
    my shortcut("Numbers", "c", "ac") -- Copy Style
    set selection range to range "D3"
    my shortcut("Numbers", "v", "ac") -- Paste Style
    end tell
    end run
    --=====
    on activateGUIscripting()
    tell application "System Events"
    if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure that GUI scripting will be active *)
    end tell
    end activateGUIscripting
    --=====
    (*
    ==== Uses GUIscripting ====
    *)
    on shortcut(a, t, d)
    local k
    tell application a to activate
    tell application "System Events"
    tell application process a
    set frontmost to true
    try
    t * 1
    if d is "c" then
    key code t using {command down}
    else if d is in {"ac", "ca"} then
    key code t using {command down, option down}
    else if d is in {"sc", "cs"} then
    key code t using {command down, shift down}
    else if d is in {"kc", "ck"} then
    key code t using {command down, control down}
    else if (d contains "c") and (d contains "s") and d contains "k" then
    key code t using {command down, shift down, control down}
    end if
    on error
    repeat with k in t
    if d is "c" then
    keystroke (k as text) using {command down}
    else if d is in {"ac", "ca"} then
    keystroke (k as text) using {command down, option down}
    else if d is in {"sc", "cs"} then
    keystroke (k as text) using {command down, shift down}
    else if d is in {"kc", "ck"} then
    keystroke (k as text) using {command down, control down}
    else if (d contains "c") and (d contains "s") and d contains "k" then
    keystroke (k as text) using {command down, shift down, control down}
    end if
    end repeat
    end try
    end tell
    end tell
    end shortcut
    --=====
    --

    Yvan KOENIG (VALLAURIS, France) mercredi 27 janvier 2010 12:34:04

  3. 0

    Chad_Huard

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Wow! Thank you so much. I didn't even think to go about it in that way, but in the end it makes perfect sense. I updated my script and it works perfectly.

    The script I am working on (it is my first) is to produce a table containing some basic statistic measures of a selection set. I am in a statistics class at school and we are suposed to be using MINItab, but I figured I would get by with Numbers for as long as I can keep up. One of the commands in MINItab is 'basic stats' which spits out a table of some basic measures for your data set, and I found myself making this chart too frequently so... time for a script. I would be grateful if you could take a look at what I have come up with and make any suggestions that come to mind.

    --

    on run

    --======Setup=========

    --make array of basic statistics to print...
    --first element is the "name" second is the numbers formula command
    set statsToPrint to {{"N", "COUNT"}, {"Sum", "SUM"}, {"Min", "MIN"}, {"Max", "MAX"}, {"Mean", "AVERAGE"}, {"Median", "MEDIAN"}, {"Mode", "MODE"}, {"Std Dev", "STDEV"}}

    my activateGUIscripting()

    --=====End Setup======

    tell application "Numbers"
    activate
    tell first document
    tell first sheet
    --only the first table with a selection will be used.
    set sourceTable to the first table whose selection range is not missing value
    tell sourceTable
    set selectionArgument to "(" & the name of sourceTable & " :: " & the name of the selection range & ")" as string
    set selection range to the first cell of the selection range
    my shortcut("Numbers", "c", "ac") -- Copy Style
    end tell
    set newTable to make new table with properties {name:"Basic Statistics", row count:2, column count:(length of statsToPrint) + 1}
    tell newTable
    delete first column
    repeat with i from 1 to length of statsToPrint
    set the value of cell i of row 1 to item 1 of item i of statsToPrint as string
    set the value of cell i of row 2 to "=" & item 2 of item i of statsToPrint & selectionArgument as string
    end repeat
    set selection range to row 2
    my shortcut("Numbers", "v", "ac") -- Paste Style
    end tell
    end tell
    end tell
    end tell
    end run
    --=====
    on activateGUIscripting()
    tell application "System Events"
    if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure that GUI scripting will be active *)
    end tell
    end activateGUIscripting
    --=====
    (*
    ==== Uses GUIscripting ====
    *)
    --Arguments: a=program; t=key; d=simultaneously pressed keys (a=option, c=command, s=shift, k=control)
    on shortcut(a, t, d)
    local k
    tell application a to activate
    tell application "System Events"
    tell application process a
    set frontmost to true
    try
    t * 1
    if d is "c" then
    key code t using {command down}
    else if d is in {"ac", "ca"} then
    key code t using {command down, option down}
    else if d is in {"sc", "cs"} then
    key code t using {command down, shift down}
    else if d is in {"kc", "ck"} then
    key code t using {command down, control down}
    else if (d contains "c") and (d contains "s") and d contains "k" then
    key code t using {command down, shift down, control down}
    end if
    on error
    repeat with k in t
    if d is "c" then
    keystroke (k as text) using {command down}
    else if d is in {"ac", "ca"} then
    keystroke (k as text) using {command down, option down}
    else if d is in {"sc", "cs"} then
    keystroke (k as text) using {command down, shift down}
    else if d is in {"kc", "ck"} then
    keystroke (k as text) using {command down, control down}
    else if (d contains "c") and (d contains "s") and d contains "k" then
    keystroke (k as text) using {command down, shift down, control down}
    end if
    end repeat
    end try
    end tell
    end tell
    end shortcut
    --=====
    --
    --

    Thanks a lot for all the help!

    -Chad

  4. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    If you look at the scripts which I posted in this forum, you may see that I take care of localisation requirements.

    Your script assumes that it will be executed on a machine running Numbers in English.

    I'm not fond of this kind of design.

    My preferred choice is to take care of the numerous localised versions.

    It's quite easy.

    --


    --=====
    on run
    --======Setup=========
    (* grabs the decimal delimiter and the parameters one *)
    (* in your script you don't need them but they are useful in more complcated formulas *)
    set {deci, delim} to getLocalizedDelimiter()

    set COUNT_loc to my getLocalizedFunctionName("Numbers", "COUNT")
    set SUM_loc to my getLocalizedFunctionName("Numbers", "SUM")
    set MIN_loc to my getLocalizedFunctionName("Numbers", "MIN")
    set MAX_loc to my getLocalizedFunctionName("Numbers", "MAX")
    set AVERAGE_loc to my getLocalizedFunctionName("Numbers", "AVERAGE")
    set MEDIAN_loc to my getLocalizedFunctionName("Numbers", "MEDIAN")
    set MODE_loc to my getLocalizedFunctionName("Numbers", "MODE")
    set STDEV_loc to my getLocalizedFunctionName("Numbers", "STDEV")

    --make array of basic statistics to print...
    --first element is the "name" second is the numbers formula command
    set statsToPrint to {{"N", COUNT_loc}, {"Sum", SUM_loc}, {"Min", MIN_loc}, {"Max", MAX_loc}, {"Mean", AVERAGE_loc}, {"Median", MEDIAN_loc}, {"Mode", MODE_loc}, {"Std Dev", STDEV_loc}}
    (* to be continued *)

    end run
    --=====
    (*
    Useful to get function's localized name if we need to build formulas
    *)
    on getLocalizedFunctionName(theApp, x)
    local p2bndl
    set p2bndl to (path to application support as text) & "iWork '09:Frameworks:SFTabular.framework:Versions:A:Resources:"
    return my getLocalizedName(theApp, x, p2bndl)
    end getLocalizedFunctionName
    --=====
    on getLocalizedName(a, x, f)
    tell application a to return localized string x from table "Localizable" in bundle file f
    end getLocalizedName
    --=====
    (*
    Set the parameter delimiter which must be used in Numbers formulas
    *)
    on getLocalizedDelimiter()
    local periodOrComma
    set periodOrComma to character 2 of (0.5 as text)
    if periodOrComma is "." then
    return {periodOrComma, ","}
    else
    return {periodOrComma, ";"}
    end if
    end getLocalizedDelimiter
    --=====
    --

  5. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Here is an enhanced version of my original script (described as a skeleton).

    --


    --[SCRIPT copyPasteStyle]
    (*
    Enregistrer le script en tant que Script : copyPasteStyle.scpt
    déplacer le fichier ainsi créé dans le dossier
    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
    Sélectionner la cellule dont le style doit être copié.
    menu Scripts > Numbers > copyPasteStyle
    Cliquer le bouton "Copier le style"
    Le script récupère le style de la cellule sélectionnée.
    ou
    Sélectionner les cellules dont le style doit être fixé.
    menu Scripts > Numbers > copyPasteStyle
    Cliquer le bouton "Coller le style"
    Le style précédemment copié est appliqué à toutes les cellules sélectionnées .
    --=====
    L'aide du Finder explique:
    L'Utilitaire AppleScript permet d'activer le Menu des scripts :
    Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
    Cochez la case "Afficher le menu des scripts dans la barre de menus".
    --=====
    Save the script as a Script: copyPasteStyle.scpt
    Move the newly created file into the folder:
    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
    Select the cell whose style must be copied.
    menu Scripts > Numbers > copyPasteStyle
    Click button "Copy Style"
    The script grabs the style of the selected cell.
    or
    Select the cells whose style must be defined.
    menu Scripts > Numbers > copyPasteStyle
    Click the button "Paste Style"
    The grabbed style is applied to every selected cells.
    --=====
    The Finder's Help explains:
    To make the Script menu appear:
    Open the AppleScript utility located in Applications/AppleScript.
    Select the "Show Script Menu in menu bar" checkbox.
    --=====
    Yvan KOENIG (VALLAURIS, France)
    2009/01/31
    *)
    --=====
    property source : ""
    --=====
    on run
    my activateGUIscripting()
    if my parleAnglais() then
    set what to display dialog "What to do ?" buttons {"Exit", "Paste Style", "Copy style"} default button 3
    else
    set what to display dialog "Que faire ?" buttons {"Renoncer", "Coller le style", "Copier le style"} default button 3
    end if
    set what to button returned of what
    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
    if what is in {"Copy Style", "Copier le style"} then
    set selection range to range (name of cell rowNum1 of column colNum1)
    my shortcut("Numbers", "c", "ac") -- Copy Style
    else
    set selection range to range ((name of cell rowNum1 of column colNum1) & ":" & (name of cell rowNum2 of column colNum2))
    my shortcut("Numbers", "v", "ac") -- Paste Style
    end if
    end tell -- application
    end run
    --=====
    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name, col_Num1, row_Num1, col_Num2, row_Num2
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to my decipher(item 1 of two_Names, d_Name, s_Name, t_Name)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to my decipher(item 2 of two_Names, d_Name, s_Name, t_Name)
    end if
    return {d_Name, s_Name, t_Name, r_Name, row_Num1, col_Num1, row_Num2, col_Num2}
    end getSelParams
    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(cellRef,docName,sheetName,tableName)
    apply to named row or named column !
    *)
    on decipher(n, d, s, t)
    tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
    end decipher
    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection
    --=====
    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe
    --=====
    on parleAnglais()
    local z
    try
    tell application "Numbers" to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z is not "Annuler")
    end parleAnglais
    --=====
    on activateGUIscripting()
    tell application "System Events"
    if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure that GUI scripting will be active *)
    end tell
    end activateGUIscripting
    --=====
    (*
    ==== Uses GUIscripting ====
    *)
    on shortcut(a, t, d)
    local k
    tell application a to activate
    tell application "System Events"
    tell application process a
    set frontmost to true
    try
    t * 1
    if d is "c" then
    key code t using {command down}
    else if d is in {"ac", "ca"} then
    key code t using {command down, option down}
    else if d is in {"sc", "cs"} then
    key code t using {command down, shift down}
    else if d is in {"kc", "ck"} then
    key code t using {command down, control down}
    else if (d contains "c") and (d contains "s") and d contains "k" then
    key code t using {command down, shift down, control down}
    end if
    on error
    repeat with k in t
    if d is "c" then
    keystroke (k as text) using {command down}
    else if d is in {"ac", "ca"} then
    keystroke (k as text) using {command down, option down}
    else if d is in {"sc", "cs"} then
    keystroke (k as text) using {command down, shift down}
    else if d is in {"kc", "ck"} then
    keystroke (k as text) using {command down, control down}
    else if (d contains "c") and (d contains "s") and d contains "k" then
    keystroke (k as text) using {command down, shift down, control down}
    end if
    end repeat
    end try
    end tell
    end tell
    end shortcut
    --=====
    --[/*SCRIPT]
    --

    Yvan KOENIG (VALLAURIS, France) dimanche 31 janvier 2010 17:59:39