Previous 1 2 Next 21 Replies Latest reply: Jun 29, 2010 9:39 PM by Luiz Siqueira Neto
Luiz Siqueira Neto Level 1 Level 1 (0 points)
This script stop to respond more or less in the 15 line – it don't crash, looks like a communication problem or bug. Why this happen, some one have this same kind of problem?


script Register
property |date| : ""
property history : ""
property balance : ""
property category : ""
property subcategory : ""
end script

set |old list| to {}

tell application "Numbers"
tell document "American Express 3"
tell sheet "Folha 9"
set |count| to 0
repeat with |old line| in rows of table "Transações"
if not |count| < 2 then
copy Register to |old register|
set |old register|'s |date| to ((value of cell 2 of |old line|) as Unicode text)
set |old register|'s history to ((value of cell 3 of |old line|) as Unicode text)
set |old register|'s balance to ((value of cell 6 of |old line|) as Unicode text)
set |old register|'s category to ((value of cell 4 of |old line|) as Unicode text)
set |old register|'s subcategory to ((value of cell 5 of |old line|) as Unicode text)
set the end of |old list| to |old register|
end if
set |count| to |count| + 1
end repeat
end tell
end tell
end tell

New iMac 20, Mac OS X (10.5.6)
  • Badunit Level 6 Level 6 (11,400 points)
    The line that seems to be giving it fits for me is "set the end of |old list| to |old register|". Is that the line you are calling line 15? It locks up the Applescript editor for me.

    I changed the line "copy Register to |old register|" to "set |old register| to Register" and it seems to run. I did not do an extensive test to see if it worked as you intend but it does not hang anymore.

    Message was edited by: Badunit

    Message was edited by: Badunit
  • Badunit Level 6 Level 6 (11,400 points)
    I am guessing that the results will not be the same
  • Badunit Level 6 Level 6 (11,400 points)
    One more thing I noticed is that it works as you have written it for very short tables. Sometimes quickly, sometimes it takes several seconds to complete. I really don't know what is going on because I'm not that versed in Applescript. I'm hoping some of my ramblings will help you.

    Here is something that might work instead (you'll have to put your document, sheet, and table names back into it):


    set |old list| to {}
    set |old register| to Register

    tell application "Numbers"
    tell document 1
    tell sheet 1
    set |count| to 0
    repeat with |old line| in rows of table 1
    if not |count| < 2 then
    set |old register|'s |date| to ((value of cell 2 of |old line|) as Unicode text)
    set |old register|'s history to ((value of cell 3 of |old line|) as Unicode text)
    set |old register|'s balance to ((value of cell 6 of |old line|) as Unicode text)
    set |old register|'s category to ((value of cell 4 of |old line|) as Unicode text)
    set |old register|'s subcategory to ((value of cell 5 of |old line|) as Unicode text)
    copy |old register| to end of |old list|
    end if
    set |count| to |count| + 1
    end repeat
    end tell
    end tell
    end tell

  • Level 8 Level 8 (41,790 points)
    I understand that you wished to apply tips given here and there to fasten script execution.
    To achieve that you tried to use a script object and to work on references with a loop without an explicit index.

    Alas, it appears that the second choice is a bad good idea (or, if you prefer, it's a good bad idea).

    It doesn't match the way Numbers behave.

    So, I tested five alternate codes.

    --

    (* version 1 *)
    property |date| : ""
    property history : ""
    property balance : ""
    property category : ""
    property subcategory : ""

    on run
    set startingTime to current date
    repeat 10 times
    set |old list| to {}

    tell application "Numbers"
    tell document "American Express 3.numbers"
    tell sheet "Folha 9"
    set |count| to 0
    tell table "Transações"
    repeat with a_row from 2 to count row
    tell row a_row
    set |date| to value of cell 2
    set history to value of cell 3
    set balance to value of cell 6
    set category to value of cell 4
    set subcategory to value of cell 5
    copy {|date|, history, balance, category, subcategory} to end of |old list|
    end tell
    end repeat
    end tell
    end tell
    end tell
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    |old list|
    end run
    --


    --

    (* version 2 *)

    property a_row : {{}, {}, {}, {}, {}}

    on run
    set startingTime to current date
    repeat 10 times
    set |old list| to {}

    tell application "Numbers"
    tell document "American Express 3.numbers"
    tell sheet "Folha 9"
    set |count| to 0
    tell table "Transações"
    repeat with a_row from 2 to count row
    tell row a_row
    set my a_row to {value of cell 2, value of cell 3, value of cell 6, value of cell 4, value of cell 5}
    copy my a_row to end of |old list|
    end tell
    end repeat
    end tell
    end tell
    end tell
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    |old list|
    end run
    --


    --

    (* version 3 *)

    on run
    set startingTime to current date
    repeat 10 times
    set |old list| to {}

    tell application "Numbers"
    tell document "American Express 3.numbers"
    tell sheet "Folha 9"
    set |count| to 0
    tell table "Transações"
    repeat with a_row from 2 to count row
    tell row a_row
    copy {value of cell 2, value of cell 3, value of cell 6, value of cell 4, value of cell 5} to end of |old list|
    end tell
    end repeat
    end tell
    end tell
    end tell
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    |old list|
    end run
    --


    --

    (* version 4 *)
    property |old list| : {}
    on run
    set startingTime to current date
    repeat 10 times
    set my |old list| to {}

    tell application "Numbers"
    tell document "American Express 3.numbers"
    tell sheet "Folha 9"
    tell table "Transações"
    repeat with a_row from 2 to count row
    tell row a_row
    copy {value of cell 2, value of cell 3, value of cell 6, value of cell 4, value of cell 5} to end of my |old list|
    end tell
    end repeat
    end tell
    end tell
    end tell
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    my |old list|
    end run
    --


    On my machine the first three ran in 40 seconds, the late one in 39 seconds.

    The fifth one is :

    --

    (* version 5 *)
    property |old list| : {}

    on run
    set startingTime to current date
    repeat 10 times
    set my |old list| to {}

    tell application "Numbers"
    tell document "American Express 3.numbers"
    tell sheet "Folha 9"
    tell table "Transações"
    repeat with a_row from 2 to count row
    tell row a_row
    set {|date|, history, category, subcategory, balance} to (value of cells 2 thru 6)
    end tell
    copy {|date|, history, balance, category, subcategory} to end of my |old list|
    end repeat
    end tell
    end tell
    end tell
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    my |old list|
    end run
    --


    This one required only eight seconds.
    Reordering the values to match your original order has no measurable impact.

    Yvan KOENIG (VALLAURIS, France) jeudi 24 juin 2010 09:24:44
  • Hiroto Level 5 Level 5 (6,170 points)
    Hello Yvan KŒNIG,

    I couldn't resist wondering whether something like codes below works or not in Numbers.
    It's mere analogy with AppleWorks. (In AW6, 'columns i thru j of rows n thru m' returns a list of lists which represents a 2-d array of the specified range.)


    --SNIPPETS
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    --cells 2 thru 6 of rows 2 thru -1 -- get reference only ?
    value of cells 2 thru 6 of rows 2 thru -1
    end tell
    end tell
    end tell
    end tell

    --Or
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    --columns 2 thru 6 of rows 2 thru -1 -- get reference only ?
    value of columns 2 thru 6 of rows 2 thru -1
    end tell
    end tell
    end tell
    end tell

    --Or
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    value of range ("B2:F" & (count rows)) -- OK perhaps ?
    end tell
    end tell
    end tell
    end tell
    --END OF SNIPPETS


    ---
    IF (BIG IF) some of the above works, reordering elements in each row as desired can be done very quickly in pure AppleScript without any communication with Numbers.

    Something like this :

    --SCRIPT1
    set aa to {{10, 20, 30, 40, 50, 60}, {"a", "b", "c", "d", "e", "f"}}
    set ii to {1, 2, 6, 3, 4}
    return extractArray(aa, ii) -- {{10, 20, 60, 30, 40}, {"a", "b", "f", "c", "d"}}

    on extractArray(lol, indices)
    (*
    list lol : source 2-d array (list of lists)
    list indices : ordered indices to be extracted from each row of xx
    return list : extracted 2-d array according to given indices
    *)
    script o
    property xx : lol
    property ii : indices
    property pp : {}
    property qq : {}
    property rr : {}
    repeat with p from 1 to count my xx
    set pp to my aa's item p
    set qq to {}
    repeat with i in my ii
    set end of my qq to my pp's item i
    end repeat
    set end of my rr to qq
    end repeat
    return my rr's contents
    end script
    tell o to run
    end extractArray
    --END OF SCRIPT1


    And as a whole, something like the following codes might work :

    --SCRIPT2
    (*
    |old list| will be list of list
    *)
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    set xx to value of columns 2 thru 6 of rows 2 thru -1
    end tell
    end tell
    end tell
    end tell
    set |old list| to extractArray(xx, {1, 2, 5, 3, 4})

    on extractArray(lol, indices)
    (*
    list lol : source 2-d array (list of lists)
    list indices : ordered indices to be extracted from each row of xx
    return list : extracted 2-d array according to given indices
    *)
    script o
    property xx : lol
    property ii : indices
    property pp : {}
    property qq : {}
    property rr : {}
    repeat with p from 1 to count my xx
    set pp to my aa's item p
    set qq to {}
    repeat with i in my ii
    set end of my qq to my pp's item i
    end repeat
    set end of my rr to qq
    end repeat
    return my rr's contents
    end script
    tell o to run
    end extractArray
    --END OF SCRIPT2

    Or

    --SCRIPT2a
    (*
    |old list| will be list of records
    (or list of script objects : not recommended)
    *)
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    set xx to value of columns 2 thru 6 of rows 2 thru -1
    end tell
    end tell
    end tell
    end tell
    set |old list| to extractArray2(xx, {1, 2, 5, 3, 4}, list2reco)
    --set |old list| to extractArray2(xx, {1, 2, 5, 3, 4}, list2scpt)

    on extractArray2(lol, indices, postproc)
    (*
    list lol : source 2-d array (list of lists)
    list indices : ordered indices to be extracted from each row of xx
    handler postproc : handler for post-processing list; e.g. convert list to record
    return list : extracted 2-d array according to given indices and post-processor
    *)
    script o
    property xx : lol
    property ii : indices
    property pp : {}
    property qq : {}
    property rr : {}
    property func : postproc
    repeat with p from 1 to count my xx
    set pp to my aa's item p
    set qq to {}
    repeat with i in my ii
    set end of my qq to my pp's item i
    end repeat
    --set end of my rr to qq
    set end of my rr to my func(qq)
    end repeat
    if (count my rr's scripts) = 0 then -- [1]
    return my rr's contents
    else
    return my rr
    end if
    (*
    [1] Getting rr's contents should fail if rr contains script object.
    *)
    end script
    tell o to run
    end extractArray2

    on list2reco(xx)
    (*
    list xx : source list
    return record : record with given labels and data from source list
    *)
    tell xx
    return {|date|:item 1, history:item 2, balance:item 3, category:item 4, subcategory:item 5}
    end tell
    end list2reco

    on list2scpt(xx)
    (*
    list xx : source list
    return script object : s/o with given properties and data from source list
    *)
    script Register
    property |date| : xx's item 1
    property history : xx's item 2
    property balance : xx's item 3
    property category : xx's item 4
    property subcategory : xx's item 5
    end script
    end list2scpt
    --END OF SCRIPT2a


    Just my lazy thoughts
    Hiroto
  • Badunit Level 6 Level 6 (11,400 points)
    Yvan,

    Why do you think there is such a difference in times between the last one and the others? They are all very similar looking but last one was a whopping 80% faster in your testing.
  • Hiroto Level 5 Level 5 (6,170 points)
    Ooops.

    I noticed previous extractArray() and extractArray2() handlers contain wrong code...

    Correction is as follows.
    The statement :

    set pp to my aa's item p -- WRONG

    should be read as :

    set pp to my xx's item p -- CORRECT


    Sorry for any confusions I may have made.
    Also noticed that the original code by Luiz Siqueira Neto is getting rows 3 through end.

    Thus corrected codes should be as follows :

    --SCRIPT1 CORRECTED
    set aa to {{10, 20, 30, 40, 50, 60}, {"a", "b", "c", "d", "e", "f"}}
    set ii to {1, 2, 6, 3, 4}
    return extractArray(aa, ii) -- {{10, 20, 60, 30, 40}, {"a", "b", "f", "c", "d"}}

    on extractArray(lol, indices)
    (*
    list lol : source 2-d array (list of lists)
    list indices : ordered indices to be extracted from each row of xx
    return list : extracted 2-d array according to given indices
    *)
    script o
    property xx : lol
    property ii : indices
    property pp : {}
    property qq : {}
    property rr : {}
    repeat with p from 1 to count my xx
    set pp to my xx's item p
    set qq to {}
    repeat with i in my ii
    set end of my qq to my pp's item i
    end repeat
    set end of my rr to qq
    end repeat
    return my rr's contents
    end script
    tell o to run
    end extractArray
    --END OF SCRIPT1 CORRECTED



    --SCRIPT2 CORRECTED
    (*
    |old list| will be list of list
    *)
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    set xx to value of columns 2 thru 6 of rows 3 thru -1
    end tell
    end tell
    end tell
    end tell
    set |old list| to extractArray(xx, {1, 2, 5, 3, 4})

    on extractArray(lol, indices)
    (*
    list lol : source 2-d array (list of lists)
    list indices : ordered indices to be extracted from each row of xx
    return list : extracted 2-d array according to given indices
    *)
    script o
    property xx : lol
    property ii : indices
    property pp : {}
    property qq : {}
    property rr : {}
    repeat with p from 1 to count my xx
    set pp to my xx's item p
    set qq to {}
    repeat with i in my ii
    set end of my qq to my pp's item i
    end repeat
    set end of my rr to qq
    end repeat
    return my rr's contents
    end script
    tell o to run
    end extractArray
    --END OF SCRIPT2 CORRECTED



    --SCRIPT2a CORRECTED
    (*
    |old list| will be list of records
    (or list of script objects : not recommended)
    *)
    tell application "Numbers"
    tell document 1
    tell sheet 1
    tell table 1
    set xx to value of columns 2 thru 6 of rows 3 thru -1
    end tell
    end tell
    end tell
    end tell
    set |old list| to extractArray2(xx, {1, 2, 5, 3, 4}, list2reco)
    --set |old list| to extractArray2(xx, {1, 2, 5, 3, 4}, list2scpt)

    on extractArray2(lol, indices, postproc)
    (*
    list lol : source 2-d array (list of lists)
    list indices : ordered indices to be extracted from each row of xx
    handler postproc : handler for post-processing list; e.g. convert list to record
    return list : extracted 2-d array according to given indices and post-processor
    *)
    script o
    property xx : lol
    property ii : indices
    property pp : {}
    property qq : {}
    property rr : {}
    property func : postproc
    repeat with p from 1 to count my xx
    set pp to my xx's item p
    set qq to {}
    repeat with i in my ii
    set end of my qq to my pp's item i
    end repeat
    --set end of my rr to qq
    set end of my rr to my func(qq)
    end repeat
    if (count my rr's scripts) = 0 then -- [1]
    return my rr's contents
    else
    return my rr
    end if
    (*
    [1] Getting rr's contents should fail if rr contains script object.
    *)
    end script
    tell o to run
    end extractArray2

    on list2reco(xx)
    (*
    list xx : source list
    return record : record with given labels and data from source list
    *)
    tell xx
    return {|date|:item 1, history:item 2, balance:item 3, category:item 4, subcategory:item 5}
    end tell
    end list2reco

    on list2scpt(xx)
    (*
    list xx : source list
    return script object : s/o with given properties and data from source list
    *)
    script Register
    property |date| : xx's item 1
    property history : xx's item 2
    property balance : xx's item 3
    property category : xx's item 4
    property subcategory : xx's item 5
    end script
    end list2scpt
    --END OF SCRIPT2a CORRECTED


    All the best,
    H

    Message was edited by: Hiroto
  • Luiz Siqueira Neto Level 1 Level 1 (0 points)
    The 15 line that I say is the table line, more or less in the line 15 the script become slow and then stop to responding.

    I will try your idea.

    Thanks about your help
  • Luiz Siqueira Neto Level 1 Level 1 (0 points)
    Thanks for everybody.

    I will try the ideas here and then I will report if it works.

    Thanks again
  • Level 8 Level 8 (41,790 points)
    Hello

    There is a huge difference.

    In the four first ones, it's Applescript which asks the application to return values calling it one cell at a time.
    The 5th one ask the app to return a range of values so,sending the order and delivering the values is made only once versus 5 times

    And the time vary from 8 to 40.

    My old correspondant (I don't know if he would be OK if I named him 'old friend') HIROTO is proposing a climb one more step grabbing the cells from several rows and columns in a single call.

    He can't test because he don't own the app.
    But I may do that for some one which helped me very often.

    --

    tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
    set maybe to value of cells 3 thru 20 of columns 2 thru 6
    end tell
    --

    returns:

    {{date "lundi 1 février 2010 1:00:00", date "lundi 1 mars 2010 1:00:00", date "jeudi 1 avril 2010 2:00:00", date "samedi 1 mai 2010 2:00:00", date "mardi 1 juin 2010 2:00:00", date "jeudi 1 juillet 2010 2:00:00", date "dimanche 1 août 2010 2:00:00", date "mercredi 1 septembre 2010 2:00:00", date "vendredi 1 octobre 2010 2:00:00", date "lundi 1 novembre 2010 1:00:00", date "mercredi 1 décembre 2010 1:00:00", date "samedi 1 janvier 2011 1:00:00", date "mardi 1 février 2011 1:00:00", date "mardi 1 mars 2011 1:00:00", date "vendredi 1 avril 2011 2:00:00", date "dimanche 1 mai 2011 2:00:00", date "mercredi 1 juin 2011 2:00:00", date "vendredi 1 juillet 2011 2:00:00"}, {"history3", "history4", "history5", "history6", "history7", "history8", "history9", "history10", "history11", "history12", "history13", "history14", "history15", "history16", "history17", "history18", "history19", "history20"}, {"category3", "category4", "category5", "category6", "category7", "category8", "category9", "category10", "category11", "category12", "category13", "category14", "category15", "category16", "category17", "category18", "category19", "category20"}, {"subcategory3", "subcategory4", "subcategory5", "subcategory6", "subcategory7", "subcategory8", "subcategory9", "subcategory10", "subcategory11", "subcategory12", "subcategory13", "subcategory14", "subcategory15", "subcategory16", "subcategory17", "subcategory18", "subcategory19", "subcategory20"}, {42.0, 56.0, 23.0, 14.0, 30.0, 20.0, 73.0, 57.0, 13.0, 34.0, 31.0, 74.0, 32.0, 59.0, 98.0, 31.0, 91.0, 32.0}}

    This structure isn't easy to use

    but with this code :

    --

    tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
    set maybe to value of cells 2 thru 6 of rows 3 thru 20
    end tell
    --


    we get :

    {{date "lundi 1 février 2010 1:00:00", "history3", "category3", "subcategory3", 42.0}, {date "lundi 1 mars 2010 1:00:00", "history4", "category4", "subcategory4", 56.0}, {date "jeudi 1 avril 2010 2:00:00", "history5", "category5", "subcategory5", 23.0}, {date "samedi 1 mai 2010 2:00:00", "history6", "category6", "subcategory6", 14.0}, {date "mardi 1 juin 2010 2:00:00", "history7", "category7", "subcategory7", 30.0}, {date "jeudi 1 juillet 2010 2:00:00", "history8", "category8", "subcategory8", 20.0}, {date "dimanche 1 août 2010 2:00:00", "history9", "category9", "subcategory9", 73.0}, {date "mercredi 1 septembre 2010 2:00:00", "history10", "category10", "subcategory10", 57.0}, {date "vendredi 1 octobre 2010 2:00:00", "history11", "category11", "subcategory11", 13.0}, {date "lundi 1 novembre 2010 1:00:00", "history12", "category12", "subcategory12", 34.0}, {date "mercredi 1 décembre 2010 1:00:00", "history13", "category13", "subcategory13", 31.0}, {date "samedi 1 janvier 2011 1:00:00", "history14", "category14", "subcategory14", 74.0}, {date "mardi 1 février 2011 1:00:00", "history15", "category15", "subcategory15", 32.0}, {date "mardi 1 mars 2011 1:00:00", "history16", "category16", "subcategory16", 59.0}, {date "vendredi 1 avril 2011 2:00:00", "history17", "category17", "subcategory17", 98.0}, {date "dimanche 1 mai 2011 2:00:00", "history18", "category18", "subcategory18", 31.0}, {date "mercredi 1 juin 2011 2:00:00", "history19", "category19", "subcategory19", 91.0}, {date "vendredi 1 juillet 2011 2:00:00", "history20", "category20", "subcategory20", 32.0}}

    And the full set of datas is grabbed in less than a second

    --

    (* version 6 *)
    property |old list| : {}

    on run
    set startingTime to current date
    repeat 10 times
    set my |old list| to {}

    tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
    set my |old list| to value of cells 2 thru 6 of rows 2 thru 45
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    my |old list|
    end run
    --


    is designed to return the same set of datas than my 'old' examples.
    With the editor in report events mode (efficient brake), the full set is returned 10 times in one second.

    I don't know what the OP really want to achieve.
    If he want to swap columns contents, the correct scripte would be

    --

    (* version 7 *)
    property |old list| : {}

    on run
    set startingTime to current date
    repeat 10 times
    set my |old list| to {}

    tell application "Numbers" to tell document "American Express 3.numbers" to tell sheet "Folha 9" to tell table "Transações"
    set my |old list| to value of cells 2 thru 45 of columns 2 thru 6
    end tell
    end repeat
    display dialog "required : " & (current date) - startingTime & " secondes"
    my |old list|
    end run
    --


    Both return a list of lists.

    The version 6 return a list of short lists of values belonging to a row
    and
    version 7 return a list of long lists of values belonging to a column.

    Most of the time, when I discover HIROTO's name in a thread in which I posted, he wrote upon this kind of tip. It seems that he was born in a library dedicated to Applescript. AppleScript behave as its second language. He know subtelties of this one and he is always OK to share its knowledge.

    I'm a fool, I tried using
    set my |old list| to value of cells 2 to 45 of columns 2 to 6
    and when it was rejected by the Editor, I didn't thought to try with thru

    Thank you Hiroto.

    Back to the performances.

    The first scripts where asking for values 10445 times with an execution time of 40 seconds
    the fifth one was asking for values 10*44 times with an execution time of 8 seconds (40/5)
    the new ones are asking only 10 times with an execution time of 1 second. As the time function embedded in Applescript count only the seconds we may admit that the duration was divided by 44.

    Wih give a final factor of 1 to 220.
    Isn't it astounding ?

    Thank you again HIROTO.

    When was our first exchange ? I would say six years ago but I'm not sure.

    Yvan KOENIG (VALLAURIS, France) jeudi 24 juin 2010 22:58:23
  • Badunit Level 6 Level 6 (11,400 points)
    Nice. I learned some more AppleScript tips from this thread. This will come in handy.
  • Hiroto Level 5 Level 5 (6,170 points)
    Hello Yvan,

    Thank you so much for your kind words and testing my untested codes.

    Of course you can call me an 'old friend' of yours !!! I've been very honoured.
    What let you think I'd mind if you do ? Perhaps because I wrote 'Hello Yvan KŒNIG' in lieu of 'Hello Yvan' ? If so, I shouldn't have done that. I just meant to show my respect, for the other day I've read you wrote only your family members call you Yvan (in Vallauris, France). It is the same in Japan, where only my family members call me Hiroto, my first name, well, indeed my last name because we put family name first. We normally use family name or full name with Mr./Ms. to call others in our social life.
    Seemingly here're two men who usually call others by family name trying to adapt themselves to different custom in different language. Funny.

    Oh, please don't take me wrong. I do NOT mind at all if you call me Hiroto.
    And yes, it's been six years I guess. The oldest email of yours in my archive is dated 15 May 2004, about 'lettrine' script in AppleWorks 6. These days AppleWorks forum is so dead quiet that I often stroll in Numbers fora though I don't have and can't play with this beast.

    When I should get a new machine which lets me play with Numbers, I'd surely fiddle with its scripting interface. It seems to me the good old MACRO function to call AppleScript script from spreadsheet cell is the current biggest missing piece. And drastic performance improvement for large document seems also crucial.

    Cordially,
    Hiroto TAOKA

    P.S. Speaking of emails, I've not read them lately. Ha! I'm living like a hermit these days. Hiding from debt collector, er no, manuscript collector, really. I'll be as such at least for next ten and odd days. So please do not worry if by any chance you send me email and don't hear from me soon.

    P.P.S. I thought that one day you wrote KŒNIG is the proper spelling. But my memory fails easily. Does it again ? If so, my sincere apology.
  • Level 8 Level 8 (41,790 points)
    Hello

    Thanks. It was just that as an old fashioned guy I think that to be friends require that both are OK for that.
    Yes the exact spelling of my name is KŒNIG but as it is badly treated on Internet due to encoding oddities, I choose to return to the old time of typewriters.

    When you wrote
    When I should get a new machine which lets me play with Numbers, I'd surely fiddle with its scripting interface. It seems to me the good old MACRO function to call AppleScript script from spreadsheet cell is the current biggest missing piece. And drastic performance improvement for large document seems also crucial.


    I agree 100%.

    Yvan KOENIG (VALLAURIS, France) vendredi 25 juin 2010 07:32:44
  • Hiroto Level 5 Level 5 (6,170 points)
    Hello Luiz Siqueira Neto,

    Regarding the original question, the problematic statement is the following one, as Badunit already pointed out :

    copy Register to |old register|


    Briefly, because AppleScript's copy command does a 'deep' copy and AppleScript's script object contains its entire run-time context behind the scenes, when you executes the statement above repeatedly, |old register| grows exponentially in each iteration (i.e. it will contain its previous self as the context of current Register script object) and eventually makes script to be unresponsive (probably due to its memory manangement overload).

    The simplest fix to your original code is something like the code below, which just avoids the deep copy and uses a script object constructor :

    --SCRIPT3
    (* minimum fix to the original code *)
    on mkRegister() -- # script object consturctor which returns a new script object
    script Register
    property |date| : ""
    property history : ""
    property balance : ""
    property category : ""
    property subcategory : ""
    end script
    end mkRegister

    set |old list| to {}

    tell application "Numbers"
    tell document "American Express 3"
    tell sheet "Folha 9"
    set |count| to 0
    repeat with |old line| in rows of table "Transações"
    if not |count| < 2 then

    --copy Register to |old register| -- ### this eventually cause script to be unresponsive
    set |old register| to my mkRegister() -- # should be OK

    set |old register|'s |date| to ((value of cell 2 of |old line|) as Unicode text)
    set |old register|'s history to ((value of cell 3 of |old line|) as Unicode text)
    set |old register|'s balance to ((value of cell 6 of |old line|) as Unicode text)
    set |old register|'s category to ((value of cell 4 of |old line|) as Unicode text)
    set |old register|'s subcategory to ((value of cell 5 of |old line|) as Unicode text)
    set the end of |old list| to |old register|
    end if
    set |count| to |count| + 1
    end repeat
    end tell
    end tell
    end tell
    --END OF SCRIPT3


    Although I don't think the above code should fail, you can of course make use of the fast methods already explained. Codes below consist of the essence without using extractArray() and extractArray2() handlers :

    --SCRIPT4
    (* returns list of script objects just as the original code does *)
    main()
    on main()
    script o
    property rr : {}
    property |old list| : {}

    tell application "Numbers"
    tell document "American Express 3"
    tell sheet "Folha 9"
    tell table "Transações"
    set rr to value of cells 2 thru 6 of rows 3 thru -1 -- # get list of lists
    end repeat
    end tell
    end tell
    end tell

    repeat with r in my rr
    tell r's contents
    set r1 to {item 1, item 2, item 5, item 3, item 4} -- # re-order items in row
    end tell
    set end of my |old list| to mkRegister(r1) -- # make and append new script object
    end repeat

    return |old list| -- # result
    --return |old list|'s item -1's {|date|, history, balance, category, subcategory} -- # e.g.

    end script
    tell o to run
    end main

    on mkRegister(xx)
    (*
    list xx : source list
    return script object : s/o with given properties and data from source list
    *)
    script Register
    property |date| : xx's item 1
    property history : xx's item 2
    property balance : xx's item 3
    property category : xx's item 4
    property subcategory : xx's item 5
    end script
    end mkRegister
    --END OF SCRIPT4


    Or :

    --SCRIPT4a
    (* returns list of records, which should be easier to handle than list of script objects *)
    main()
    on main()
    script o
    property rr : {}
    property |old list| : {}

    tell application "Numbers"
    tell document "American Express 3"
    tell sheet "Folha 9"
    tell table "Transações"
    set rr to value of cells 2 thru 6 of rows 3 thru -1 -- # get list of lists
    end repeat
    end tell
    end tell
    end tell

    repeat with r in my rr
    tell r's contents
    set r1 to {item 1, item 2, item 5, item 3, item 4} -- # re-order items in row
    end tell
    set end of my |old list| to mkRegister(r1) -- # make and append new record
    end repeat

    return |old list| -- # result

    end script
    tell o to run
    end main

    on mkRegister(xx)
    (*
    list xx : source list
    return record : record with given labels and data from source list
    *)
    tell xx
    return {|date|:item 1, history:item 2, balance:item 3, category:item 4, subcategory:item 5}
    end tell
    end mkRegister
    --END OF SCRIPT4a


    Hope this may help you to get the picture.
    Good luck,
    H
Previous 1 2 Next