I have just started learning AppleScript so that I can automate some business processes and save myself some time. Because of better design and ease of use I use the latest version of iWork and don't even have Office installed on my Mac, nor do I want to install it. However, one area I cannot work out is how to automate the creation of a chart in Numbers using AppleScript.
Is this possible? If so, how, and if not, why not?
There is absolutely nothing in the Numbers's AppleScript dictionary related to charts.
So everything would be driven thru GUI scripting.
Here is a quick and dirty sample.
--{code}
on run
my activateGUIscripting()
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 9) -- Scatter chart
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 3) -- Bars
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 4) -- Stacked Bars
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 3 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 20) -- 3D Pie
end tell
end run
--=====
on activateGUIscripting()
(* to be sure than GUI scripting will be active *)
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true
end tell
end activateGUIscripting
--=====
(*
my selectSubMenu("Pages",6, 4, 26)
==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
tell applicationtheApp
activate
tell application "System Events" to tell process theApp to tell menu bar 1 to ÂŹ
tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms
end tell -- application theApp
end selectSubMenu
--=====
(*
useful to get the indexs of the triggered item
my select_SubMenu("Numbers", 6, 4, 3) (* Table > Chart> Bars *)
*)
on select_SubMenu(theApp, mt, mi, ms)
tell applicationtheApp
activate
tell application "System Events" to tell process theApp to tell menu bar 1
get name of menu bar items
(*{
01 - "Apple",
02 - "Numbers",
03 - "Fichier",
04 - "Ădition",
05 - "Insertion",
06 - "Tableau",
07 - "Format",
08 - "Disposition",
09 - "Présentation",
10 - "FenĂȘtre",
11 - "Partage",
12 - "Aide"}
*)
get name of menu bar itemmt
-- {"Tableau"}
tell menu bar item mt to tell menu 1
get name of menu items
(* {
01 - "Feuille",
02 - missing value,
03 - "Tableau",
04 - "Graphique",
05 - "Figure",
06 - "Zone de texte",
07 - "Fonction",
08 - "Ligne de connexion",
09 - missing value,
10 - "Remplissage",
11 - missing value,
12 - "Rangs copiés",
13 - "Colonnes copiées",
14 - missing value,
15 - "Date et heure",
16 - "Nom du fichier",
17 - "Numéro de page",
18 - "Nombre de pages",
19 - missing value,
20 - "Commentaire",
21 - "Lien",
22 - "Saut de colonne",
23 - "Ăquation MathType",
24 - missing value,
25 - "ChoisirâŠ"}
*)
get name of menu item mi
--> "Graphique"
tell menu item mi to tell menu 1
get name of menu items
(* {
01 - "Colonnes",
02 - "Colonnes empilées",
03 - "Barres",
04 - "Barres empilées",
05 - "Ligne",
06 - "Couches",
07 - "Couches empilées",
08 - "Diagramme circulaire",
09 - "Nuage de points",
10 - "Mixte",
11 - "2 axes",
12 - missing value,
13 - "Colonnes 3D",
14 - "Colonnes 3D empilées",
15 - "Barres 3D",
16 - "Barres 3D empilées",
17 - "Linéaire 3D",
18 - "Couches 3D",
19 - "Couches 3D empilées",
20 - "Circulaire 3D"}
*)
get name of menu item ms
--> "Barres"
clickmenu itemms
end tell
end tell
end tell
end tell -- application theApp
end select_SubMenu
--=====
--{code}
Yvan KOENIG (VALLAURIS, France) samedi 27 août 2011 12:48:23
iMac 21â5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
Many thanks for that Yvan. Why does Pages have its own Charting Applescript, and Numbers use the system's? IS one better, or more powerful than the other?
Answering questions like : how may we do this or that, is what we are supposed to try to achieve in this forum.
Answering questions like yours : why this or that, can't be seriously done here.
Here you ask to end users like you. We don't belong to the teams designing the apps.
We just may try to guess.
To build tables and charts Pages and Numbers share the same pieces of code so there is no technical reasons able to explain the omission.
So I guess that, as Pages is in its 4th version, designers had more time available to embed features in the AppleScript support than those working upon Numbers.
What's funny is that there is a minimal support for charts in Pages (as well as a microscopic support for tables) but there is a correct support for tables (which may be enhanced) in Numbers but nothing for charts.
Honestly, I really don't think that the difference is huge.
I posted my script too fast.
I forgot to insert the handler allowing us to bring a sheet at front which is required to apply GUIScripting.
It's probably the main drawback of the need to use GUIScripting here.
I don't waste time trying to apply some edit tasks to the charts.
I just wanted to fill the gap between Pages and Numbers about scripting charts.
Here is an enhanced version.
--{code}
--[SCRIPT add_charts]
(*
Yvan KOENIG (VALLAURIS, France)
2011/08/27
*)
on run
my activateGUIscripting()
tell application "Numbers" to tell document 1
set dName to its name
set sName1 to name of sheet 1
set sName2 to name of sheet 2
(*
Select sheet 1 of document 1
The doc name may be passed by a number but the sheet must be identified by its name .
As it's just a sample, in the first call I reference the doc by its index *)
my selectSheet(1, sName1)
tell sheet sName1 to tell table 1
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 9) -- Scatter chart
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 3) -- Bars
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 4) -- Stacked Bars
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 3 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 20) -- 3D Pie
end tell
(*
Select sheet 2 of document 1
This time, I reference the document by its name *)
my selectSheet(dName, sName2)
tell sheet sName2 to tell table 1
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 9) -- Scatter chart
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 3) -- Bars
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 2 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 4) -- Stacked Bars
(*
Always define the selection before creating a chart *)
set selection range to range (name of cell 4 of column 3 & " : " & name of cell 8 of column 3)
my selectSubMenu("Numbers", 5, 4, 20) -- 3D Pie
end tell --sheet 2âŠ
end tell -- NumbersâŠ
end run
--=====
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()
(* to be sure than GUI scripting will be active *)
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true
end tell
end activateGUIscripting
--=====
(*
==== Uses GUIscripting ====
*)
on selectSheet(theDoc, theSheet)
script myScript
property listeObjets : {}
local maybe, targetSheetRow
tell application "Numbers"
activate
set theDoc to name of documenttheDoc(* useful if the passed value is a number *)
tell document theDoc to set my listeObjets to name of sheets
end tell -- "Numbers"âŠ
set maybe to theSheet is in my listeObjets
set my listeObjets to {} -- So it will not be saved in the script *)
if not maybe then
if my parleAnglais() then
error "The sheet â" & theSheet & "â is unavailable in the spreadsheet â" & theDoc & "â !"
else
error "La feuille « " & theSheet & " » nâexiste pas dans le tableur « " & theDoc & " » ! "
end if -- my parleAnglais
end if -- not maybe
set maybe to 5 > (system attribute "sys2")
tell application "System Events" to tell application process "Numbers"
tell outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of windowtheDoc
if maybe then (* macOS X 10.4.x
'(value of attributes contains 0)': '(value of attribute "AXDisclosureLevel" is 0)' sometimes works in Tiger, sometimes not.
The only possible instances of 0 amongst the attributes are the disclosure level of a sheet row and the index of the first row, which represents a sheet anyway.
Another possibility is '(value of attribute -1 is 0)', which makes me uneasy. *)
set targetSheetRow to first row where ((value of attributes contains 0) and (value of first static text is theSheet))
else (* macOS X 10.5.x or higher *)
set targetSheetRow to first row where ((value of attribute "AXDisclosureLevel" is 0) and ((groups is {}) and (value of first static text is theSheet)) or (value of first group's first static text is theSheet))
end if -- maybeâŠ
(*
Handler modified to accomodate sheets requiring a lot of time to get the focus
*)
tell targetSheetRow to set value of attribute "AXSelected" to true
set cnt to 0
repeat (*
Must way that Numbers becomes ready to receive the value *)
try
tell targetSheetRow to set value of attribute "AXDisclosing" to true
exit repeat
on error
set cnt to cnt + 1
delay 0.5 -- half a second
end try
end repeat
end tell -- outlineâŠ
end tell -- "System Events"âŠ
tell application "Numbers" to tell document theDoc to tell sheet theSheet to tell table 1
with timeout of 20 * 60 seconds (*
WITH this setting, the script will be able to wait 20 minutes for the asked value.
I hope that the document will not be so huge that this delay prove to be too short. *)
value of cell "A1"
end timeout
end tell -- "Numbers"âŠ
tell application "System Events" to tell application process "Numbers" (*
Do the trick one more time to be sure that the sheet is open *)
tell targetSheetRow to set value of attribute "AXDisclosing" to true
end tell -- "System Events"âŠ
(*
End of the modified piece of code
*)
end script
runmyScript
end selectSheet
--=====
(*
my selectSubMenu("Pages",6, 4, 26)
==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
tell applicationtheApp
activate
tell application "System Events" to tell process theApp to tell menu bar 1 to ÂŹ
tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms
end tell -- application theApp
end selectSubMenu
--=====
(*
useful to get the indexs of the triggered item
my select_SubMenu("Numbers", 6, 4, 3) (* Table > Chart> Bars *)
*)
on select_SubMenu(theApp, mt, mi, ms)
tell applicationtheApp
activate
tell application "System Events" to tell process theApp to tell menu bar 1
get name of menu bar items
(*{
01 - "Apple",
02 - "Numbers",
03 - "Fichier",
04 - "Ădition",
05 - "Insertion",
06 - "Tableau",
07 - "Format",
08 - "Disposition",
09 - "Présentation",
10 - "FenĂȘtre",
11 - "Partage",
12 - "Aide"}
*)
get name of menu bar itemmt
-- {"Tableau"}
tell menu bar item mt to tell menu 1
get name of menu items
(* {
01 - "Feuille",
02 - missing value,
03 - "Tableau",
04 - "Graphique",
05 - "Figure",
06 - "Zone de texte",
07 - "Fonction",
08 - "Ligne de connexion",
09 - missing value,
10 - "Remplissage",
11 - missing value,
12 - "Rangs copiés",
13 - "Colonnes copiées",
14 - missing value,
15 - "Date et heure",
16 - "Nom du fichier",
17 - "Numéro de page",
18 - "Nombre de pages",
19 - missing value,
20 - "Commentaire",
21 - "Lien",
22 - "Saut de colonne",
23 - "Ăquation MathType",
24 - missing value,
25 - "ChoisirâŠ"}
*)
get name of menu item mi
--> "Graphique"
tell menu item mi to tell menu 1
get name of menu items
(* {
01 - "Colonnes",
02 - "Colonnes empilées",
03 - "Barres",
04 - "Barres empilées",
05 - "Ligne",
06 - "Couches",
07 - "Couches empilées",
08 - "Diagramme circulaire",
09 - "Nuage de points",
10 - "Mixte",
11 - "2 axes",
12 - missing value,
13 - "Colonnes 3D",
14 - "Colonnes 3D empilées",
15 - "Barres 3D",
16 - "Barres 3D empilées",
17 - "Linéaire 3D",
18 - "Couches 3D",
19 - "Couches 3D empilées",
20 - "Circulaire 3D"}
*)
get name of menu item ms
--> "Barres"
clickmenu itemms
end tell
end tell
end tell
end tell -- application theApp
end select_SubMenu
--=====
--[/SCRIPT]
--{code}
Yvan KOENIG (VALLAURIS, France) samedi 27 août 2011 15:19:15
iMac 21â5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
Many thanks for your help Yvan. I realise this forum is for users only, but there must be at least some Apple employees who read it from time to time. If they are reading this - please develop Numbers further!
Anyhow, in this instance I wasn't sure if I was missing something since I am only one week into learning AppleScript and up to p 84 in the "AppleScript: A Beginner's Guide" book so was jumping ahead of myself to pretend I was making some real progress! I use Numbers a lot and charting is something I need in my work, so I was also looking ahead to see where I am going.
Is the GUI scripting the same as Steve Jobs' "Core Graphics" he mentions a lot in his presentations I wonder?
(1) GUI scripting is not a new feature.
If I remember well it was introduced as a beta feature in 10.3 and was really embedded (with some syntax changes and several enhancements) in 10.4.
It's in fact a collateral effect of developments which Apple made to support disabled peoples.
They needed to give them alternate ways to trigger the GUI items.
Somebody in Cupertino had the idea to enlarge the use these development.
It's why GUIScripting is activated thru the Universal Access PrefPane.
Many scripters dislike GUIScripting, some hate it.
Maybe because I worked more than 30 years as a craftsman, I thing that it's better to have an imperfect tool than no to tool at all.
Here we have an example : without it, no scripted charts.
(2) Don't hope that your request will be read by an Apple engineer.
Maybe a moderator will read it but I doubt that they exchange often with design teams.
Don't forget that in AppleLand, everything is secret or under Non Disclosure Agreement đ
If you really want to be read, use the dedicated channel :
Go to "Provide Numbers Feedback" in the "Numbers" menu, describe what you wish.
Then, cross your fingers, and wait at least for iWork'11 or 12 ;-)
Yvan KOENIG (VALLAURIS, France) samedi 27 août 2011 16:19:06
iMac 21â5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
Can I chart in Numbers using AppleScript
