Re: limits of GUI scripting in numbers?
Re: limits of GUI scripting in numbers?
- Subject: Re: limits of GUI scripting in numbers?
- From: Yuma Decaux <email@hidden>
- Date: Sun, 05 Feb 2012 22:53:45 +1300
Hi Yvan,
Sorry if not clear about the intention.
The formula is already set in the value of cell x of column y.
It just doesn't appear unless i manually double click on the cell to show the formula and press enter. The formula is properly inserted by applescript.
I will check your handler to try and wake the cell up or something.
Best
Yuma
On 5/02/2012, at 10:22 PM, KOENIG Yvan wrote:
>
>>
>> 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
_______________________________________________
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