7 Replies Latest reply: Jul 11, 2013 8:50 AM by AlekThunder
AlekThunder Level 1 Level 1
expertise.video
Video

Hello, everyone

I need some help.

I wrote a script what was supposed to do perform following:

I receive a Numbers-format file imported to my Mac from iPad with iTunes

This file is a sort of daily journal. Every row includes info about one particular client. A name of the client is in column 2. The file name is always "Blank.numbers" and the table name is always "Blank". I need to copy this table in a new file and collect the data for each client in a different table . In other word i need to make new tables in a new file and copy client info in its own table.

 

The script is below. It seems to work right (it makes a new file, makes some tables, puts correct info into them) but one thing happens  - it's stuck in a little while after run. Sure, I reloaded my Mac and not ones but nothing at all - zero effect, the same thing happened over again, after the script successfully worked it  was stuck and i just could see "running". My test data file consists of just up to 40 rows, but I've never had this work completely done. 

 

Not much scriptwriter of myself - it's just my very first experience in the subject and I have no clue about why it could be, so I'll appreciate for any suggestion

 

on run

     set FileName to "Blank.numbers"

     set ClientColumn to 2

     set CashFlowSheetName to "Cash Flow"

     

     tell application "Numbers"

          activate

          set FileName to "Blank.numbers"

          set ClientColumn to 2

          set WorkDocument to my makeAnNumbersDoc("Blank.nmbtemplate")  -- creating a new file

          tell document WorkDocument to tell sheet 1

               set name to CashFlowSheetName

               delete table 1

          end tell

          set Clients to my GetListOfClients(FileName, ClientColumn) -- getting full list of the clients

          repeat with i from 1 to count of Clients

               set FullCopy to my GetTable("Blank.numbers", item i of Clients, ClientColumn)  -- choosing data

               

               my MakeTable(WorkDocument, CashFlowSheetName, FullCopy, item i of Clients) -- making table with name of  a client for every client

          end repeat

          

     end tell -- numbers

     return my GetListOfClients(FileName, ClientColumn) -- test line, del me!

     

end run

 

on MakeTable(WorkDocument, WorkSheet, DataList, Client)

     local i, j

     

     tell application "Numbers"

          

          tell document WorkDocument to tell sheet WorkSheet

               make new table with properties {row count:count of DataList, column count:count of item 1 of DataList, name:Client}

               tell table Client

                    repeat with i from 1 to row count

                         repeat with j from 1 to column count

                              set t to value of item j of item i of DataList

                              if class of t is date then set t to t - (time to GMT)

                              set value of cell j of row i to t as text

                              --say "column"

                         end repeat

                    end repeat

               end tell -- table

          end tell -- sheet

          

     end tell -- Numbers

end MakeTable

 

on GetListOfClients(FileName, TargetColumn)

     local i, FullListOfClient

     

     tell application "Numbers"

          

          open ":Users:Master:Documents:" & FileName

          set FullListOfClient to {}

          tell document 1 to tell sheet 1 to tell table 1

               repeat with i from 2 to count row

                    if value of cell TargetColumn of row i is not in FullListOfClient then copy value of cell TargetColumn of row i to the end of FullListOfClient

               end repeat

          end tell

     end tell

     return FullListOfClient

end GetListOfClients

 

---------

 

 

 

on GetTable(DocName, TargetValue, TargetColumn)

     local RowCounter, CopyRow

     

     tell application "Numbers"

          tell document DocName

               tell table 1 of sheet 1

                    set RowCounter to 1

                    set CopyRow to {}

                    copy cells of row 1 to the end of CopyRow

                    repeat with RowCount from 1 to row count

                         if value of cell TargetColumn of row RowCounter is equal to TargetValue then

                              copy cells of row RowCounter to the end of CopyRow

                         end if

                         set RowCounter to RowCounter + 1

                    end repeat

                    return CopyRow

               end tell

          end tell

     end tell

end GetTable

 

 

 

--=====

(*

Creates a new Numbers document from Blank.template

and returns its name.

*)

on makeAnNumbersDoc(myTemplate)

     local t, n

     set theApp to "Numbers"

     set t to ((path to applications folder as text) & theApp & ".app:Contents:Resources:Templates:" & myTemplate & ":") as alias

     tell application "Numbers"

          set n to count of documents

          open t

          repeat

               if (count of documents) > n then

                    exit repeat

               else

                    delay 0.1

               end if

          end repeat

          set n to name of document 1

     end tell -- theApp

     return n

end makeAnNumbersDoc


Numbers 09, OS X Mountain Lion (10.8.4)
Solved by Hiroto on Jul 8, 2013 2:55 AM Solved
Hello Then I have no idea why the script hangs after it has done the job. Some thoughts. • Is it the same in different user account?  • If you force quit the script, is the resulting new document fine? In other words, is the problem limited to the termination of the script? You can quit a running script by means of i) pressing command + period, which will signal to cancel the execution of the script or ii) killing the process (editor or applet) by Activity Monitor.  ---Anyway, I have done some clean up and optimization of your script as listed below although I can see no reason for these changes to solve the current issue. It is just for my testing and liking for low energy consumption.  Script is revised so that - a) it gets the template path indepent of the location of Numbers.app; b) it reduces the number of AppleEvents to send for retrieving data from table by means of range reference form (e.g., rows i thru j) and whose filter reference (e.g., rows whose cell i's value = x); c) its GetTable() handler now returns 2d arary of values instead of cell references so that it can reduce the number of AppleEvents for dereferencing the cell reference later; d) it introduces _main() handler to localise the implicit global variables in run handler (as already explained); e) in _main() handler, code is encapsulated in an script object and the script object is executed by "run script" command, which is a known technique to speed up the execution of script when saved as applet. (Actually this only makes the applet run as fast as compiled script run in editor. Without this, applet runs remarkably slower than run in editor.)  Hope this may be of some help.Good luck,H  on run     _main() end run on _main()     script o         set FileName to "Blank.numbers"         set ClientColumn to 2         set CashFlowSheetName to "Cash Flow"                 tell application "Numbers"             activate             set WorkDocument to my makeAnNumbersDoc("Blank.nmbtemplate") -- creating a new file             tell document WorkDocument's sheet 1                 set name to CashFlowSheetName                 delete table 1             end tell             set Clients to my GetListOfClients(FileName, ClientColumn) -- getting full list of the clients             --return Clients                         repeat with i from 1 to count of Clients                 set FullCopy to my GetTable(FileName, item i of Clients, ClientColumn) -- choosing data                 --return FullCopy                                 my MakeTable(WorkDocument, CashFlowSheetName, FullCopy, item i of Clients) -- making table with name of a client for every client             end repeat         end tell -- numbers         return Clients -- test     end script     run script o -- # this will dramatically speed up the execution of script when saved as an applet end _main on MakeTable(WorkDocument, WorkSheet, DataList, Client)     local utc_offset, rk, ck         set utc_offset to time to GMT     set Client to Client as string -- for safety         tell application "Numbers"         set rk to count DataList         set ck to count DataList's item 1                 tell document WorkDocument's sheet WorkSheet             make new table with properties {row count:rk, column count:ck, name:Client}             tell table Client                 repeat with i from 1 to rk                     repeat with j from 1 to ck                         set t to DataList's item i's item j                         if class of t is date then set t to t - utc_offset                         set row i's cell j's value to t as text                     end repeat                 end repeat             end tell -- table         end tell -- sheet     end tell -- Numbers end MakeTable on GetListOfClients(FileName, TargetColumn)     local FullListOfClient         tell application "Numbers"                 --open (":Users:Master:Documents:" & FileName) as alias         open ((path to documents folder from user domain as text) & FileName) as alias         --open ((path to desktop as text) & FileName) as alias -- for test                 set FullListOfClient to {}         tell document 1's sheet 1's table 1             repeat with c in (get value of cell TargetColumn of rows 2 thru -1)                 set c to c's contents                 if c is not in FullListOfClient then set end of FullListOfClient to c             end repeat         end tell     end tell     return FullListOfClient end GetListOfClients on GetTable(DocName, TargetValue, TargetColumn)     local CopyRow         tell application "Numbers"         tell document DocName's sheet 1's table 1             set CopyRow to value of cell of rows whose cell TargetColumn's value = TargetValue             set CopyRow's beginning to value of cell of row 1             return CopyRow         end tell     end tell end GetTable --===== (* Creates a new Numbers document from Blank.template and returns its name. *) on makeAnNumbersDoc(myTemplate)     local t, n     tell application "Numbers"         set t to (((path to resource "Templates") as string) & myTemplate) as alias         set n to count documents         open t         repeat until (count documents) > n             delay 0.1         end repeat         return name of document 1     end tell end makeAnNumbersDoc
Reply by Hiroto on Jul 7, 2013 6:47 AM Helpful
Hello Do you have any empty cell in column B? If yes, script will fail because empty cell is retrieved as 0.0 by poor Numbers scripting interface and script will try to create new table with name = 0.0, which should fail. In my environment (Numbers 2.0.5 under 10.6.5), a statement: make new table with properties {name:0.0}  raises error and yet Numbers creates a new table and tries to set its name to 0.0 and hangs when I try to manipulate the table. However, script is not "stuck" but termitanted by the error. So this may no be relevant to your issue; or possibly behaviours in newer versions of Numbers and OS may vary? Other than that, I don't see any suspicious parts which can make the script hang. Regards,H PS. Just in case, you may also try replacing your run handler: on run     -- codes end run  with on run     _main() end run on _main()     -- codes end _main  This way, all implicit global variables in run handler become local varibales in _main() handler and thus will not be saved in the script, which can eliminate possibility for script to hang in saving things at the end of its execution. (Any global variables including top level properties are saved in compiled script.)

All replies

  • Hiroto Level 5 Level 5

    Hello

     

    Do you have any empty cell in column B? If yes, script will fail because empty cell is retrieved as 0.0 by poor Numbers scripting interface and script will try to create new table with name = 0.0, which should fail.

     

    In my environment (Numbers 2.0.5 under 10.6.5), a statement:

     

    make new table with properties {name:0.0}
    

     

    raises error and yet Numbers creates a new table and tries to set its name to 0.0 and hangs when I try to manipulate the table. However, script is not "stuck" but termitanted by the error. So this may no be relevant to your issue; or possibly behaviours in newer versions of Numbers and OS may vary?

     

    Other than that, I don't see any suspicious parts which can make the script hang.

     

    Regards,

    H

     

    PS. Just in case, you may also try replacing your run handler:

     

    on run
        -- codes
    end run
    

     

    with

     

    on run
        _main()
    end run
    
    on _main()
        -- codes
    end _main
    

     

    This way, all implicit global variables in run handler become local varibales in _main() handler and thus will not be saved in the script, which can eliminate possibility for script to hang in saving things at the end of its execution. (Any global variables including top level properties are saved in compiled script.)

  • AlekThunder Level 1 Level 1
    expertise.video
    Video

    Hello Hiroto,

     

    Thank you a lot for your message and especially for suggestion about the handler. I don't now if it helps, (I check it up as soon as I get my computer) but nevertheless I added it in my armory. 

    Column "B" is not the idea. They are aways filled with text

  • AlekThunder Level 1 Level 1
    expertise.video
    Video

    Hello again

     

    Unfortunately an idea with changing the handler  doesn't work either

  • Hiroto Level 5 Level 5

    Hello

     

    Then I have no idea why the script hangs after it has done the job.

     

    Some thoughts.

     

    • Is it the same in different user account?

     

    • If you force quit the script, is the resulting new document fine? In other words, is the problem limited to the termination of the script? You can quit a running script by means of i) pressing command + period, which will signal to cancel the execution of the script or ii) killing the process (editor or applet) by Activity Monitor.

     

     

    ---

    Anyway, I have done some clean up and optimization of your script as listed below although I can see no reason for these changes to solve the current issue. It is just for my testing and liking for low energy consumption.

     

    Script is revised so that -

    a) it gets the template path indepent of the location of Numbers.app;

     

    b) it reduces the number of AppleEvents to send for retrieving data from table by means of range reference form (e.g., rows i thru j) and whose filter reference (e.g., rows whose cell i's value = x);

     

    c) its GetTable() handler now returns 2d arary of values instead of cell references so that it can reduce the number of AppleEvents for dereferencing the cell reference later;

     

    d) it introduces _main() handler to localise the implicit global variables in run handler (as already explained);

     

    e) in _main() handler, code is encapsulated in an script object and the script object is executed by "run script" command, which is a known technique to speed up the execution of script when saved as applet. (Actually this only makes the applet run as fast as compiled script run in editor. Without this, applet runs remarkably slower than run in editor.)

     

     

    Hope this may be of some help.

    Good luck,

    H

     

     

    on run
        _main()
    end run
    
    on _main()
        script o
            set FileName to "Blank.numbers"
            set ClientColumn to 2
            set CashFlowSheetName to "Cash Flow"
            
            tell application "Numbers"
                activate
                set WorkDocument to my makeAnNumbersDoc("Blank.nmbtemplate") -- creating a new file
                tell document WorkDocument's sheet 1
                    set name to CashFlowSheetName
                    delete table 1
                end tell
                set Clients to my GetListOfClients(FileName, ClientColumn) -- getting full list of the clients
                --return Clients
                
                repeat with i from 1 to count of Clients
                    set FullCopy to my GetTable(FileName, item i of Clients, ClientColumn) -- choosing data
                    --return FullCopy
                    
                    my MakeTable(WorkDocument, CashFlowSheetName, FullCopy, item i of Clients) -- making table with name of a client for every client
                end repeat
            end tell -- numbers
            return Clients -- test
        end script
        run script o -- # this will dramatically speed up the execution of script when saved as an applet
    end _main
    
    on MakeTable(WorkDocument, WorkSheet, DataList, Client)
        local utc_offset, rk, ck
        
        set utc_offset to time to GMT
        set Client to Client as string -- for safety
        
        tell application "Numbers"
            set rk to count DataList
            set ck to count DataList's item 1
            
            tell document WorkDocument's sheet WorkSheet
                make new table with properties {row count:rk, column count:ck, name:Client}
                tell table Client
                    repeat with i from 1 to rk
                        repeat with j from 1 to ck
                            set t to DataList's item i's item j
                            if class of t is date then set t to t - utc_offset
                            set row i's cell j's value to t as text
                        end repeat
                    end repeat
                end tell -- table
            end tell -- sheet
        end tell -- Numbers
    end MakeTable
    
    on GetListOfClients(FileName, TargetColumn)
        local FullListOfClient
        
        tell application "Numbers"
            
            --open (":Users:Master:Documents:" & FileName) as alias
            open ((path to documents folder from user domain as text) & FileName) as alias
            --open ((path to desktop as text) & FileName) as alias -- for test
            
            set FullListOfClient to {}
            tell document 1's sheet 1's table 1
                repeat with c in (get value of cell TargetColumn of rows 2 thru -1)
                    set c to c's contents
                    if c is not in FullListOfClient then set end of FullListOfClient to c
                end repeat
            end tell
        end tell
        return FullListOfClient
    end GetListOfClients
    
    on GetTable(DocName, TargetValue, TargetColumn)
        local CopyRow
        
        tell application "Numbers"
            tell document DocName's sheet 1's table 1
                set CopyRow to value of cell of rows whose cell TargetColumn's value = TargetValue
                set CopyRow's beginning to value of cell of row 1
                return CopyRow
            end tell
        end tell
    end GetTable
    
    --=====
    (*
    Creates a new Numbers document from Blank.template
    and returns its name.
    *)
    on makeAnNumbersDoc(myTemplate)
        local t, n
        tell application "Numbers"
            set t to (((path to resource "Templates") as string) & myTemplate) as alias
            set n to count documents
            open t
            repeat until (count documents) > n
                delay 0.1
            end repeat
            return name of document 1
        end tell
    end makeAnNumbersDoc
    
  • AlekThunder Level 1 Level 1
    expertise.video
    Video

    Hello Hiroto,


    I feel really awkward. I mean, hardly had I started complitely  rewriting the script when you sent  really the excellent thing to me. Not only works it, it runs like a rocket! Thank you very much. You've done my job  yourself.

    Additional thanks for your comments. They are very useful.

     

    All the best and thank you again!

  • Hiroto Level 5 Level 5

    Hello AlekThunder,

     

    You're quite welcome! I'm very glad to know it helps.

    Like I said, I can see no reason for the revised code to fix the issue. In my testing, both scripts, the original and the revised, work without hanging. But like they say, all's well that ends well.

     

    Best wishes from Japan,

    Hiroto

  • AlekThunder Level 1 Level 1
    expertise.video
    Video

    Hello Hiroto,

    Finally I've found the time to get through your script in details. I have to say it's very clever, there are a lot of beautiful solutions. Thank you for a pleasure I've got reading it.

     

    Best,

    Alexander