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.
- 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 - 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.
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
- 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 - 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
--=====
-- - 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
