2 Replies Latest reply: Jan 3, 2012 1:19 PM by Luiz Siqueira Neto
Luiz Siqueira Neto Level 1 Level 1 (0 points)

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)
  • Level 8 Level 8 (41,790 points)

    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 range leBloc

              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 item i of mesValeurs to my decoupe(item i 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(item i 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 application a to activate

              tell application "System Events" to tell application process a

                        set frontmost to true

                        try

                                  t * 1

                                  if d is "" then

      key code t

                                  else if d is "c" then

      key code t using {command down}

                                  else if d is "a" then

      key code t using {option down}

                                  else if d is "k" then

      key code t using {control down}

                                  else if d is "s" then

      key code t using {shift down}

                                  else if d is in {"ac", "ca"} then

      key code t using {command down, option down}

                                  else if d is in {"as", "sa"} then

      key code t using {shift down, option down}

                                  else if d is in {"sc", "cs"} then

      key code t using {command down, shift down}

                                  else if d is in {"kc", "ck"} then

      key code t using {command down, control down}

                                  else if d is in {"ks", "sk"} then

      key code t using {shift down, control down}

                                  else if (d contains "c") and (d contains "s") and d contains "k" then

      key code t using {command down, shift down, control down}

                                  else if (d contains "c") and (d contains "s") and d contains "a" then

      key code t using {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

      keystroke k using {option down}

                                            else if d is "k" then

      keystroke (k as text) using {control down}

                                            else if d is "s" then

      keystroke k using {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 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

     

    --=====

    --{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

  • Luiz Siqueira Neto Level 1 Level 1 (0 points)

    Thanks, I will take a look at home.