I'm trying find a way to speedup my AppleScript solution, I need adjust data downloaded from my Bank as a Excel table and than append to my financial sheet.
I have a solution to it with AppleScript but it is too slow. To solve this I'm wondering if is possible copy a range to clipboard then work with this data and then past back to Numbers. Some one know if it is possible and how to do it?
New iMac 20, Mac OS X (10.7.2)
Here is a skeleton which may be helpful.
--{code}
--=====
on run
my activateGUIscripting()
(*
Get infos upon the table in which is the range which we want to treat.
Here we are just interested by dName, sName, tName
*)
set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()
tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
set leBloc to (name of cell rowNum1 of column colNum1 & " : " & name of cell rowNum2 of column colNum2)
set selection range to rangeleBloc
end tell -- Numbers
set the clipboard to ""
my raccourci("Numbers", "c", "c")
repeat
if (the clipboard as text) is not "" then exit repeat
delay 0.1
end repeat
set mesValeurs to paragraphs of (the clipboard as text)
set nbLignes to count of mesValeurs
repeat with i from 1 to nbLignes
set itemi of mesValeurs to my decoupe(itemi of mesValeurs, tab)
end repeat
(*
{{,Castorama différé, carte 9,90 fin juillet, },
{, Pass (portable), 1,23, },
{2 sept. 2011, retraite UGRR (trim), , 234,56},
{3 sept. 2011, retraite Pro Btp (trim), , 456,78},
{10 sept. 2011, retraite AVA, , 1 234,56},
{10 sept. 2011, retraite CRAM, , 345,67},
{10 sept. 2011, retraite, , 123,45}}
Work upon the list of lists mesValeurs *)
--
(*
Fill the clipboard with the edited values *)
set nouvellesValeurs to {}
repeat with i from 1 to nbLignes
copy my recolle(itemi of mesValeurs, tab) to end of nouvellesValeurs
end repeat
set the clipboard to my recolle(nouvellesValeurs, return)
(*
Reselect the first cell of the range in case we changed the selected area while the script worked *)
set sTarget to "other sheet"
set tTarget to "other table"
set rowTarget to 3
set colTarget to 2
my selectTable(dName, sTarget, tTarget)
tell application "Numbers" to tell document dName to tell sheet sTarget to tell table tTarget
set selection range to range (name of cell rowTarget of column colTarget)
end tell
(*
Paste matching style *)
my raccourci("Numbers", "v", "cas")
end run
--=====
(*
set { dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()
tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
*)
on get_SelParams()
local d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2
tell application "Numbers" to tell document 1
set d_Name to its name
set s_Name to ""
repeat with i from 1 to the count of sheets
tell sheet i to set maybe to the count of (tables whose selection range is not missing value)
if maybe is not 0 then
set s_Name to name of sheet i
exit repeat
end if -- maybe is not 0
end repeat
if s_Name is "" then
if my parleAnglais() then
error "No sheet has a selected table embedding at least one selected cell !"
else
error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"
end if
end if
tell sheet s_Name to tell (first table where selection range is not missing value)
tell selection range
set {top_left, bottom_right} to {name of first cell, name of last cell}
end tell
set t_Name to its name
tell cell top_left to set {row_Num1, col_Num1} to {address of its row, address of its column}
if top_left is bottom_right then
set {row_Num2, col_Num2} to {row_Num1, col_Num1}
else
tell cell bottom_right to set {row_Num2, col_Num2} to {address of its row, address of its column}
end if
end tell -- sheet…
return {d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2}
end tell -- Numbers
end get_SelParams
--=====
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 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 recolle(l, d)
local t
set AppleScript's text item delimiters to d
set t to l as text
set AppleScript's text item delimiters to ""
return t
end recolle
--=====
on activateGUIscripting()
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true(* to be sure than GUI scripting will be active *)
end tell
end activateGUIscripting
--=====
(*
==== Uses GUIscripting ====
*)
(*
This handler may be used to 'type' text, invisible characters if the third parameter is an empty string.
It may be used to 'type' keyboard raccourcis if the third parameter describe the required modifier keys.
I changed its name « shortcut » to « raccourci » to get rid of a name conflict in Smile.
*)
on raccourci(a, t, d)
local k
tell applicationa to activate
tell application "System Events" to tell application process a
set frontmost to true
try
t * 1
if d is "" then
key codet
else if d is "c" then
key codetusing {command down}
else if d is "a" then
key codetusing {option down}
else if d is "k" then
key codetusing {control down}
else if d is "s" then
key codetusing {shift down}
else if d is in {"ac", "ca"} then
key codetusing {command down, option down}
else if d is in {"as", "sa"} then
key codetusing {shift down, option down}
else if d is in {"sc", "cs"} then
key codetusing {command down, shift down}
else if d is in {"kc", "ck"} then
key codetusing {command down, control down}
else if d is in {"ks", "sk"} then
key codetusing {shift down, control down}
else if (d contains "c") and (d contains "s") and d contains "k" then
key codetusing {command down, shift down, control down}
else if (d contains "c") and (d contains "s") and d contains "a" then
key codetusing {command down, shift down, option down}
end if
on error
repeat with k in t
if d is "" then
keystroke (k as text)
else if d is "c" then
keystroke (k as text) using {command down}
else if d is "a" then
keystrokekusing {option down}
else if d is "k" then
keystroke (k as text) using {control down}
else if d is "s" then
keystrokekusing {shift down}
else if d is in {"ac", "ca"} then
keystroke (k as text) using {command down, option down}
else if d is in {"as", "sa"} then
keystroke (k as text) using {shift 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 is in {"ks", "sk"} then
keystroke (k as text) using {shift 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}
else if (d contains "c") and (d contains "s") and d contains "a" then
keystroke (k as text) using {command down, shift down, option down}
end if
end repeat
end try
end tell
end raccourci
--=====
on selectTable(theDoc, theSheet, theTable)
local maybe, targetSheetRow, rowIndex, r
try
tell application "Numbers"
activate
set theDoc to name of documenttheDoc(* 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 sheettheSheet(* 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 tabletheTable(* 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 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
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
--=====
--{code}
Yvan KOENIG (VALLAURIS, France) lundi 2 janvier 2012
iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2
My iDisk is : http://public.me.com/koenigyvan
Please : Search for questions similar to your own before submitting them to the community
For iWork's applications dedicated to iOS, go to :
https://discussions.apple.com/community/app_store/iwork_for_ios
Thanks, I will take a look at home.
Cut and past ranges between tables using AppleScript
