7 Replies Latest reply: Jul 11, 2013 8:50 AM by AlekThunder
AlekThunder Level 1 Level 1 (0 points)

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)
  • Hiroto Level 5 Level 5 (5,675 points)

    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 (0 points)

    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 (0 points)

    Hello again

     

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

  • Hiroto Level 5 Level 5 (5,675 points)

    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 (0 points)

    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 (5,675 points)

    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 (0 points)

    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