MBP, Mac OS X (10.6.3)
--[SCRIPT fillBlanks]
(*
Enregistrer le script en tant que Script : fillBlanks.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 une plage de cellules à remplir..
aller au menu Scripts , choisir Numbers puis choisir fillBlanks
Les cellules vides de la sélection seront remplie avec la chaîne saisie.
--=====
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 : fillBlanks.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 a range of cells to fill.
go to the Scripts Menu, choose Numbers, then choose fillBlanks
The script will fill every blank cell with the given string .
--=====
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/02/06
2010/06/11
*)
--=====
property t_Name : "testIfEmpty"
property ISBLANK_loc : ""
--=====
on run
set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} ¬
to my getSelParams()
(* Here we know the starting point of the destination area. *)
tell application (path to frontmost application as string)
if my parleAnglais() then
set msg to text returned of (display dialog ¬
"Tapez la chaîne à utiliser pour remplir les cellules vides" default answer "plein")
else
set msg to text returned of (display dialog ¬
"Enter the string which will fill the empty cells" default answer "filled")
end if
end tell
my initBlankChecker(dName, sName)
repeat with r from rowNum1 to rowNum2
repeat with c from colNum1 to colNum2
if my isItBlank(dName, sName, tName, r, c, sName) then
tell application "Numbers" to tell document dName
tell sheet sName to tell table tName
set value of cell r of column c to msg
end tell -- sheet …
end tell -- Numbers …
end if -- my isItBlank(…
end repeat -- c
end repeat -- r
tell application "Numbers" to tell document dName to tell sheet sName
delete table t_Name
end tell
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(d_Name, s_Name, t_Name, item 1 of two_Names)
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(d_Name, s_Name, t_Name, item 2 of two_Names)
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(docName,sheetName,tableName,cellRef)
apply to named row or named column !
*)
on decipher(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t
return {address of row of cell n, address of column of cell n}
end tell
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
--=====
(*
Initialization process required before calling isItBlank.
Both require :
property t_name : "testIfEmpty"
property ISBLANK_loc : ""
my initBlankChecker(dName,sheetForTheTempTable)
*)
on initBlankChecker(doc2test, sheet4test)
set ISBLANK_loc to my getLocalizedFunctionName("Numbers", "ISBLANK")
tell application "Numbers" to tell document doc2test to tell sheet sheet4test
if not (exists table t_Name) then
make new table with properties {name:t_Name, row count:1, column count:1}
end if
set tProps to get properties of table t_Name
set nbr to (row count of tProps) - 1
if nbr > 0 then
repeat nbr times
remove row 1 of table t_Name
end repeat
end if
set nbr to (column count of tProps) - 1
if nbr > 0 then
repeat nbr times
remove column 1 of table t_Name
end repeat
end if
end tell -- Numbers
end initBlankChecker
--=====
(*
Check if a cell is Blank and returns status accordingly.
An initialization process must be done before calling this handler.
Both require :
property t_name : "testIfEmpty"
property ISBLANK_loc : ""
if my isItBlank(dName, sReport, tAssignments, 1, c,sheetForTheTempTable) then exit repeat
*)
on isItBlank(doc2test, sheet2test, table2test, row2test, col2test, sheet4test)
local status
tell application "Numbers" to tell document doc2test
set sheet2test to (get name of sheet sheet2test)
tell sheet sheet2test
set table2test to (get name of table table2test)
tell table table2test
set cellRef to (get name of cell row2test of column col2test)
set status to value of cell cellRef
end tell -- table table2test
end tell -- sheet sheet2test
if status is not 0.0 then
set status to false
else
set sheet4test to (get name of sheet sheet4test)
tell sheet sheet4test to tell table t_Name
set value of cell "A1" to "=" & ISBLANK_loc & ¬
"('" & sheet2test & "' :: '" & table2test & "' :: " & cellRef & ")"
set status to value of cell "A1"
end tell
end if -- flag
end tell
return status
end isItBlank
--=====
on get_iWorkNum(a)
local verNum
tell application a to set verNum to item 1 of my decoupe(get version, ".")
if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
return "09"
else
return "11"
end if
end get_iWorkNum
--=====
(*
Useful to get function's localized name if we need to build formulas
examples:
set OFFSET_loc to my getLocalizedFunctionName("Numbers", "OFFSET")
set ADDRESS_loc to my getLocalizedFunctionName(theApp, "ADDRESS")
set INDIRECT_loc to my getLocalizedFunctionName(theApp, "INDIRECT")
*)
on getLocalizedFunctionName(theApp, x)
return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & ¬
my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")
end getLocalizedFunctionName
--=====
on getLocalizedName(a, x, f)
tell application a to return localized string x from table "Localizable" in bundle file f
end getLocalizedName
--=====
--[/SCRIPT]
--
if my parleAnglais() then
set msg to text returned of (display dialog ¬
"Tapez la chaîne à utiliser pour remplir les cellules vides" default answer "plein")
else
set msg to text returned of (display dialog ¬
"Enter the string which will fill the empty cells" default answer "filled")
end if
--
if my parleAnglais() then
set msg to text returned of (display dialog ¬
"Enter the string which will fill the empty cells" default answer "filled")
else
set msg to text returned of (display dialog ¬
"Tapez la chaîne à utiliser pour remplir les cellules vides" default answer "plein")
end if
--find and replace on empty cells