• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: limits of GUI scripting in numbers?
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: limits of GUI scripting in numbers?


  • Subject: Re: limits of GUI scripting in numbers?
  • From: KOENIG Yvan <email@hidden>
  • Date: Sun, 05 Feb 2012 10:22:28 +0100



On 5/02/2012, at 9:29 PM, KOENIG Yvan wrote:


Le 5 févr. 2012 à 06:57, Yuma Decaux a écrit :


Hi List,

I have successfully created a lengthy and complex script with many variables and layers of repeat loops and it all works seriously fine except for one annoying issue in iwork numbers.

I've created interactions with various cells of columns to come up with an action that places the following formula in every ascending cell

=100-100/(1+((((SUM(E17:E30)/14)*13)+G16)/14)/((((SUM(E17:E30)/14)*13)+G16)/14))


The formula is a concatenation of several variables taking information of names of cells etc and incrementing cel values via other nested loops.

It all works great.

The formula also works fine, but it just doesn't appear as a result when the script is run.

I have to option enter each cell, where the formula is in fact residing, then press enter for the result to appear.

Is there anything like a refresh or other method to get these results to appear without having to manually look into the forumla and press enter? Or if that's the only solution, is there a way to point the selection to a cell and do the manual option enter then enter?

I suppose the gui scripting would go like:

Tell application "System Events"
Tell process "Numbers:
Tell table 1 of sheet 1 of front document
Keycode 36 using (option down)
Keycode 36
End tell
End tell
End tell
And create a repeat to loop throughout the range of cells i want, but that's like sticking an adhesive to a key on the keyboard to have my ultima online avatar to mine and gain xp while i'm off eating noodles.

Any more elegant solution as the rest of the script has elegance in mind?

Thanks for any advise

Yuma

What are you hoping to get ?
There is no link between the application and the script so it does its duty only when we trigger it.
What need for GUI scripting to insert your formula is a cell ?

tell application "Numbers"
tell document dName
tell sheet sName
tell table tName
tell column colNum1
repeat with r from rowNum1 to rowNum2
set theFormula to build_the_formula_here
set value of cell r to theFormula
end repeat
end tell -- column
end tell -- table
end tell -- sheet
end tell -- document
end tell -- Numbers.

If the table is at front and if the formula may resist to the Fill Down treatment
you may use :

tell application "Numbers"
tell document dName
tell sheet sName
tell table tName
set theFormula to build_the_formula_here
set value of cell rowNum1 of column colNum1 to theFormula
set selection range to range (name of cell rowNum1 of column colNum1 &":"&name of cell rowNum2 of column colNum1)
my selectSubMenu("Numbers",5,10,2) -- Edit > Fill > Fill Down
end tell -- table
end tell -- sheet
end tell -- document
end tell -- Numbers.

--=====
(*
my selectSubMenu("Pages",6, 4, 26)
==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
activate application theApp
tell application "System Events" to tell application 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 selectSubMenu

--=====

Le 5 févr. 2012 à 10:04, Yuma Decaux a écrit :

Salut Yvan,

Thanks for that, however i will need to figure what exactly that means as you seem to be pretty advanced at this.

If i understand right, is this about making a selection range of the cells where i filled the formulas, then use gui scripting to access a menu sub item to force numbers to show the results of the formula?


Best regards 
Yuma

No.
I trigger the sumenu
Edit > Fill > Fill Down

But maybe the problem is that you didn't described it completely.

We may set the value of a cell in a table which isn‘t visible but we can't apply GUI Scripting to such a table.
Alas, there is no function allowing us to select a table.
To do that, I built two awful handlers (Nigel GARVEY helped a lot).

Here they are :

--=====
(*
==== Uses GUIscripting ====
 *)
on selectSheet(theDoc, theSheet)
script myScript
property listeObjets : {}
local maybe, targetSheetRow
--+++++
-- set log_report to "point 2 : " & (current date) & return
--+++++
tell application "Numbers"
activate
set theDoc to name of document theDoc (* 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 window theDoc
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"…
--+++++
-- set log_report to log_report & "point 3, cnt = " & cnt & return & (current date) & return
--+++++
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"…
--+++++
-- set log_report to log_report & "point 4 : " & (current date) & return
--+++++
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"…
--+++++
-- return log_report & "point 5 : " & (current date) & return
--+++++
(*
End of the modified piece of code
*)
end script
run myScript
end selectSheet

--=====

on selectTable(theDoc, theSheet, theTable)
local maybe, targetSheetRow, rowIndex, r
try
tell application "Numbers"
activate
set theDoc to name of document theDoc (* useful if the passed value is a number. Checks also that we passed the name of an open doc *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The spreadsheet “" & theDoc & "” is not open !"
else
error "Le tableur « " & theDoc & " » n’est pas ouvert ! "
end if -- my parleAnglais
end try


try
tell application "Numbers" to tell document theDoc
set theSheet to name of sheet theSheet (* useful if the passed value is a number and check the availability of theSheet if it’s a string *)
end tell -- Numbers
on error
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 try


try
tell application "Numbers" to tell document theDoc to tell sheet theSheet
set theTable to name of table theTable (* useful if the passed value is a number and check the availability of theSheet if it’s a string *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The table “" & theTable & "” is unavailable in the sheet “" & theSheet & "”  of the spreadsheet “" & d & "” !"
else
error "La table « " & theTable & " » n’existe pas dans la feuille « " & theSheet & " »  du tableur « " & d & " » ! "
end if -- my parleAnglais
end try


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 window theDoc
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
tell targetSheetRow to set {value of attribute "AXSelected", value of attribute "AXDisclosing"} to {true, true}


-- Get the sheet row's 0-based index + 2 for the following row's 1-based index.
set r to (value of attribute "AXIndex" of targetSheetRow) + 2


repeat until (value of first static text of row r is theTable)
set r to r + 1
end repeat
set value of attribute "AXSelected" of row r to true
end tell -- outline 1 …
end tell -- System Events


end selectTable

--=====

Yvan KOENIG (VALLAURIS, France) dimanche 5 février 2012 10:22:23

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users

This email sent to email@hidden

  • Follow-Ups:
    • Re: limits of GUI scripting in numbers?
      • From: Yuma Decaux <email@hidden>
References: 
 >limits of GUI scripting in numbers? (From: Yuma Decaux <email@hidden>)
 >Re: limits of GUI scripting in numbers? (From: KOENIG Yvan <email@hidden>)
 >Re: limits of GUI scripting in numbers? (From: Yuma Decaux <email@hidden>)

  • Prev by Date: Re: limits of GUI scripting in numbers?
  • Next by Date: Re: limits of GUI scripting in numbers?
  • Previous by thread: Re: limits of GUI scripting in numbers?
  • Next by thread: Re: limits of GUI scripting in numbers?
  • Index(es):
    • Date
    • Thread