Jimforxst

Q: Applescript for Compiling Numbers Documents

Hi!

 

I just finished completing my first "Real" Applescript to do something that I thought would be fairly simple.

It only took me 5 days to figure out!

 

Anyway, I regularly create new Numbers documents every time I make a money deposit to the campus bookkeeper

(I am a teacher/middle school band director and am constantly receiving payments from kids for t-shirts, band uniform fees, trip costs, etc.)

 

I have tried for years to find a way to compile all the deposit slips into one document to keep track of who has paid for what over the course of the school year and I have always just ended up having to manually copy and paste the data from each doc into the master list (Last year I had over a hundred different deposit slip files in a folder on my desktop).

 

Long story short, I discovered Automator and Applescript a few weeks ago and have been playing around with them. Who knew these tools had been on my mac the whole time!

 

If someone (much more skilled than I) could take a look at the code I came up with below to see if it is sound, of if there is a more efficient way of doing this, I would very much appreciate it!

 

The code works for now, but if there are any weaknesses or something that might break in a future update, I'd like to know about it.

 

CODE BELOW:

-------------------------------------------------

     -- This is my attempt at creating an AppleScript for copying data from multiple Numbers documents and compiling it into one database for use within a single master Numbers Document.

     -- The way this is setup now, every time a new data document is added to the source Folder, I have to double click an icon on the desktop which will run this script.


 

set sourceFolder to (POSIX file "/Users/my_name/Library/2016-2017 Financial Documents/TO BE ADDED/" as alias)

     -- source folder for documents to be added to master list

 


set targetFolder to (POSIX file "/Users/my_name/Library/2016-2017 Financial Documents/DONE/" as alias)

     -- place for documents to go after they are added to the master list.

 


set theFile to (choose file with prompt "Choose a Numbers Document to be copied to the Master List." as text default location sourceFolder)

     -- The script will prompt me to select a file out of the source folder to copy

 

 


tell application "Numbers"

activate

  set doc to open theFile

  tell table 1 of active sheet of doc

  set thisRangeName to "3:37"

       -- the range i want to copy

  set the selection range to range thisRangeName

       -- selects the range i want to copy

  end tell

end tell

tell application "System Events" to keystroke "c" using command down

     -- copies the selected range to the clipboard.

 

 

property docTarget : "/Users/my_name/Library/2016-2017 Band Documents/2016-2017 Student List.numbers"

     --Location of the Master File

 

property today : current date

     --variable to be used when entering information in the master file. This will aid in sorting objects added to the master file.

 


tell application "Numbers"

open docTarget

  tell table 1 of sheet "MONIES COLLECTED" of front document

  add row below last row

       --creates a new row to receive the new information.

  set the value of last row's first cell to (current date) as string

       --enters current date in first column of table

  set selection range to last row's second cell

       --moves the selected cell to the one that will recieve the data from the clipboard.

  tell application "System Events" to keystroke "v" using command down

       --pastes data from clipboard

  tell application "System Events" to keystroke "ESC"

       --clears the currently selected cells

  end tell

 


--we will now check for empty rows that were pasted from the source document to clean up our Master Document.

 

 

tell table 1 of sheet "MONIES COLLECTED" of front document

  set rowCount to (every row of selection range)

       --how many rows to check? Row Count is determined by the amount from the previous selection range.

  repeat with i from 3 to the count of rowCount

       --skips the first 2 rows, which are header rows, from the count

  set maybe to (value of fifth cell in last row)

       --checks the fifth column (reason for activity) value to see if it is empty.

  if maybe is missing value then set gone to last row

       --starting with the last row of the document, if the fifth column is empty, it is tagged for deletion.

  remove gone

       --remove the empty row

  end repeat

  set done to (value of fifth cell in last row)

       --when the repeated deletions are finished, if there is an additional empty row remaining, this will look for it.

  if done is missing value then remove last row

       --the if statement that will remove the last extra row

  end tell

 

end tell

quit application "Numbers"

     --closes all Numbers documents and quits the application

 

tell application "Finder"

move file theFile to folder targetFolder

      -- moves the Source File out of the "TO BE ADDED" Folder and into the "DONE" Folder.

end tell


--------------------------------------------------


Thanks for the help and advice to an Applescript Newbie!

- Jimforxst

MacBook Pro, OS X El Capitan (10.11.6)

Posted on Sep 4, 2016 5:52 PM

Close

Q: Applescript for Compiling Numbers Documents

  • All replies
  • Helpful answers

  • by Camelot,

    Camelot Camelot Sep 4, 2016 9:56 PM in response to Jimforxst
    Level 8 (47,233 points)
    Mac OS X
    Sep 4, 2016 9:56 PM in response to Jimforxst

    First and foremost, if the script works for you, then that's good enough

     

    Even experienced/veteran AppleScripters can spend an inordinate amount of time 'perfecting' scripts, so it's important to balance how much time it will take to get to a 'perfect' solution.

     

    If you're looking for pointers of what might work 'better', here are a couple of thoughts.

     

    First, your script relies on literally copying and pasting the data from the source file to the master file. I'm never a fan of this approach. While it (clearly) does work, it is prone to errors, and changes the system's state - you don't know if what was on the clipboard when the script started was wanted, and the operator (if it weren't you) might not expect that running the script would change the clipboard contents. In general I think it's better to reference the data directly ('set data2Copy to (get rows 1 through 37 of table 1...)'), although this works for now.

     

    Also, your script is somewhat dependent on the specifics of the source and master files. There's no error checking to make sure the source data is valid before copying it.

    On a similar subject you have a significant amount of code to check if column 5 is blank. I'd consider doing this check up front - in other words only copy the data that's needed, rather than copy more and have to clean up afterwards. Again, the specifics of this depend somewhat on the data.

     

    Lastly, for now, the script runs on a single file at a time - opens the source file, opens the master file, copies the data, closes the files and quits Numbers.

    What if there were multiple source files to process? You might consider looping through the files, copying the data and just closing/quitting at the end. That would save some time.

  • by Jimforxst,

    Jimforxst Jimforxst Sep 5, 2016 8:32 AM in response to Camelot
    Level 1 (8 points)
    Mac OS X
    Sep 5, 2016 8:32 AM in response to Camelot

    Camelot,

     

    Thanks for the tips!

    I don't know much about coding, even with something fairly simple like applescript, so your advice is appreciated.

    I've mostly just been using trial and error (with a little bit of reading sites like this one and watching youtube videos) to figure out what I need to do.

     

    On the idea of referencing the data directly instead of using the clipboard, what command would I give to add the "data2Copy" to the master file? I have tried the following Code (listed down below) but it only causes an error (which causes Script Editor to lock up completely):

     

              Error: Numbers got an error: Can’t make {HERE IT LISTS EVERY ROW of Table 1 of Sheet of Document ID ""} into type number, date, text, boolean or missing value.

     

    Code I tried:

    --------------------------------------

     

    tell application "Numbers"

    activate

      set doc to open theFile

      set data2Copy to (get rows 3 through 37 of table 1 of active sheet of doc)

    end tell

     


    property docTarget : "/Users/my_name/Library/2016-2017 Band Documents/2016-2017 Student List.numbers"

         --Location of the Master File

     

    property today : current date

         --variable to be used when entering information in the master file. This will aid in sorting objects added to the master file.


     

    tell application "Numbers"

    open docTarget

      tell table 1 of sheet "MONIES COLLECTED" of front document

      add row below last row

           --creates a new row to receive the new information.

      set the value of last row's first cell to (current date) as string

           --enters current date in first column of table

      set the value of last row's second cell to data2Copy

           --NOT SURE WHAT IS HAPPENING HERE. THIS CODE DOESN'T WORK YET.

     

      end tell


     

    --------------------------------------


    Thanks!

    -Jimforxst

  • by Jimforxst,

    Jimforxst Jimforxst Sep 5, 2016 6:31 PM in response to Jimforxst
    Level 1 (8 points)
    Mac OS X
    Sep 5, 2016 6:31 PM in response to Jimforxst

    Also, to eliminate the need to delete rows after copying, I have tried the following:

     

    tell application "Numbers"

    activate

      set doc to open theFile

     

      tell table 1 of active sheet of doc

     

      set rowCount to (every row of table 1 of active sheet of doc)

      --how many rows to check? all of the ones in the table.

     

      --[  set newRowCount to (rows with data in the fourth column)  ]

     

      repeat with i from 3 to the count of rowCount

      if (value of fourth cell of row i is missing value) then {}  --remove that row from the rowCount????  

      --if the fourth cell of the row is empty, it must be ignored from the selection that will become data2Copy - HOW TO DO THIS????

      end repeat

      set data2Copy to (get newRowCount)

     

      end tell

    end tell

  • by Camelot,

    Camelot Camelot Sep 6, 2016 10:28 AM in response to Jimforxst
    Level 8 (47,233 points)
    Mac OS X
    Sep 6, 2016 10:28 AM in response to Jimforxst

    On the idea of referencing the data directly instead of using the clipboard, what command would I give to add the "data2Copy" to the master file?

     

    My apologies. I admit I spoke without actually trying it myself since it seems like such a simple/obvious thing to do. However, upon investigation, there doesn't seem to be any clear direct way to copy ranges of data from one sheet to another. This seems like a huge omission to me.

    The only way I can see to get it to work is to copy cell-by-cell, which is probably unwieldy and overkill for your purposes, so copy/paste may be the way to go here.

    Hopefully someone on the Numbers team are listening and can fix this.

     

    As for the validation of data before copying, the specifics of that depend somewhat on your source data. How many rows are you copying in a typical run? 1 row? many rows?

    Ultimately you'll need to iterate through the rows since Numbers doesn't seem to support 'whose' filtering.

  • by Jimforxst,

    Jimforxst Jimforxst Sep 6, 2016 7:33 PM in response to Camelot
    Level 1 (8 points)
    Mac OS X
    Sep 6, 2016 7:33 PM in response to Camelot

    Camelot,

     

    on a typical run, I will frequently use the whole sheet (all rows 3 through 37) but at times, there could be a few empty rows, and at other times (especially at certain times of the year) there may only be 3 or 4 rows with data to be copied.

     

    So, my question is how should I iterate through the rows to see what needs to be copied?

    Is there some way to remove specific empty rows from the selection range before it is copied?

     

    what do you mean by 'iterate through the rows'?

    I am guessing this has to do with:

    repeat with i from 3 to the count of rowCount , but how do I check for blank rows and remove them from the selection range?

     

    Thanks for the advice!

    -Jimforxst

  • by Hiroto,

    Hiroto Hiroto Sep 7, 2016 7:10 AM in response to Jimforxst
    Level 5 (7,276 points)
    Sep 7, 2016 7:10 AM in response to Jimforxst

    Hello

     

    If I understand it correctly, you might try something like the following script.

     

    I assumed source sheet index is 1 and used the index in lieu of "active sheet", which is not available in Numbers v2 at hand. And although I left the documents paths as you have defined, ~/Library is really not the directory to store user documents of this sort and you should use ~/Documents instead.

     

    It is also assumed that master table has enough columns to copy the source data.

     

    Briefly tested with Numbers v2.0.5 under OS X 10.6.8 but no warranties. Please make sure you have complete backup of the original files and directories before running this sort of script. Better test on small subset first.

     

    Good luck,

    H

     

     

    --APPLESCRIPT
    _main()
    on _main()
        script o
            property |DOCS| : (path to library folder from user domain)'s POSIX path & "2016-2017 Financial Documents/"
            --property |DOCS| : (path to documents folder from user domain)'s POSIX path & "2016-2017 Financial Documents/"
            
            property |SRC_DIR| : |DOCS| & "TO BE ADDED" -- POSIX path
            property |DST_DIR| : |DOCS| & "DONE" -- POSIX path
            property |MASTER_FILE| : |DOCS| & "2016-2017 Student List.numbers" -- POSIX path
            property |MASTER_SHEET| : "MONIES COLLECTED" -- name or index
            property |MASTER_TABLE| : 1 -- name or index
            property |SRC_SHEET| : 1 -- name or index
            property |SRC_TABLE| : 1 -- name or index
            property |SRC_ROW_RANGE| : {3, 37} -- start and end row indicies, inclusive
            property |CHK_CELL| : 4 -- cell index to be checked blank for filtering out empty rows
            property |TIME_STAMP| : current date
            property ff : {} -- source alias list
            property aa : {} -- source rows cells value
            
            set {i0, i1} to |SRC_ROW_RANGE|
            set ff to choose file of type {"numbers"} ¬
                with prompt ("Choose Numbers Document(s) to be copied to the Master List.") ¬
                default location |SRC_DIR| as POSIX file ¬
                with multiple selections allowed
            
            repeat with f in ff -- for each source file
                set f to f's contents -- alias
                -- get source rows of data
                tell application "Numbers"
                    open f
                    tell (document 1 whose its path = (f's POSIX path))
                        tell sheet |SRC_SHEET|'s table |SRC_TABLE|
                            set aa to rows i0 thru i1's cells's value
                        end tell
                        close saving no
                    end tell
                end tell
                set jx to count aa's item 1 -- source column count
                
                -- filter out empty rows, where emptiness is determined by cell |CHK_CELL| being blank
                repeat with i from 1 to count my aa
                    if my aa's item i's item |CHK_CELL| is in {missing value, 0.0} then set my aa's item i to false
                end repeat
                set aa to aa's lists -- using class filter
                
                -- populate master table
                tell application "Numbers"
                    --activate
                    open |MASTER_FILE| as POSIX file
                    tell (document 1 whose its path = |MASTER_FILE|)
                        tell sheet |MASTER_SHEET|'s table |MASTER_TABLE|
                            repeat with i from 1 to count my aa -- for each row index in source
                                add row below row -1 -- append row
                                tell row -1 -- appended row
                                    set cell 1's value to |TIME_STAMP| as string
                                    repeat with j from 1 to jx -- for each column index in source
                                        set cell (j + 1)'s value to my aa's item i's item j
                                    end repeat
                                end tell
                            end repeat
                        end tell
                        if modified then save
                    end tell
                end tell
                
                -- move source file to destination directory
                tell application "Finder" to move f to |DST_DIR| as POSIX file
            end repeat
            
            -- save and close master file
            tell application "Numbers"
                tell (document 1 whose its path = |MASTER_FILE|)
                    if modified then save
                    close
                end tell
            end tell
        end script
        tell o to run
    end _main
    --END OF APPLESCRIPT
    
  • by Jimforxst,

    Jimforxst Jimforxst Sep 8, 2016 3:43 AM in response to Hiroto
    Level 1 (8 points)
    Mac OS X
    Sep 8, 2016 3:43 AM in response to Hiroto

    Hiroto,

     

    Thanks a bunch for this! I really appreciate how you explained the code throughout. It helps me figure this stuff out.

     

    The code is not working at this point, though.

    It keeps saying it can't find document 1's path

    (stopping at . . .  set aa to rows i0 thru i1's cells's value ( . . . underneith . . .  open f . . . )).

    Is there something I need to change in the code there?

     

    My documents are actually in iCloud, that is why they are in a library path

    (/Users/my_name/Library/Mobile Documents/com~apple~CloudDocs/2016-2017 Financial Documents)

    Any suggestions for adjusting the code to compensate for the documents in iCloud?

    Do I just need to reference the files directly for now?

     

    (I did try moving the folders to my Documents folder to see if that helped, but it still got the same error as before.)

     

     

     

    Also, how do you know when to use:  the word its in  . . . whose its path . . .

    and, same question: using the word my in . . . . count my aa . . . .

     

    Thanks again for the advice.

    I'm slowly getting there in understanding how Applescript works.

    Hopefully this won't break when we get to the new MacOS.

     

    -Jimforxst

  • by Hiroto,

    Hiroto Hiroto Sep 8, 2016 5:02 AM in response to Jimforxst
    Level 5 (7,276 points)
    Sep 8, 2016 5:02 AM in response to Jimforxst

    Hmm. Perhaps something has been changed in Numbers 3's scripting behaviour regarding document object and its path property. Or annoying timing issue...

     

    Anyway then, you might replace:

     

                    open f
                    tell (document 1 whose its path = (f's POSIX path))
    

     

     

    with:

     

                    tell (open f)
    

     

     

    and replace:

     

                    open |MASTER_FILE| as POSIX file
                    tell (document 1 whose its path = |MASTER_FILE|)
    

     

     

    with:

     

                    tell (open |MASTER_FILE| as POSIX file)
    

     

     

     

    Revised script will be something like this:

     

     

    --APPLESCRIPT
    _main()
    on _main()
        script o
            property |DOCS| : (path to library folder from user domain)'s POSIX path & "2016-2017 Financial Documents/"
            --property |DOCS| : (path to documents folder from user domain)'s POSIX path & "2016-2017 Financial Documents/"
            
            property |SRC_DIR| : |DOCS| & "TO BE ADDED" -- POSIX path
            property |DST_DIR| : |DOCS| & "DONE" -- POSIX path
            property |MASTER_FILE| : |DOCS| & "2016-2017 Student List.numbers" -- POSIX path
            property |MASTER_SHEET| : "MONIES COLLECTED" -- name or index
            property |MASTER_TABLE| : 1 -- name or index
            property |SRC_SHEET| : 1 -- name or index
            property |SRC_TABLE| : 1 -- name or index
            property |SRC_ROW_RANGE| : {3, 37} -- start and end row indicies, inclusive
            property |CHK_CELL| : 4 -- cell index to be checked blank for filtering out empty rows
            property |TIME_STAMP| : current date
            property ff : {} -- source alias list
            property aa : {} -- source rows cells value
            
            set {i0, i1} to |SRC_ROW_RANGE|
            set ff to choose file of type {"numbers"} ¬
                with prompt ("Choose Numbers Document(s) to be copied to the Master List.") ¬
                default location |SRC_DIR| as POSIX file ¬
                with multiple selections allowed
            
            repeat with f in ff -- for each source file
                set f to f's contents -- alias
                -- get source rows of data
                tell application "Numbers"
                    tell (open f)
                        tell sheet |SRC_SHEET|'s table |SRC_TABLE|
                            set aa to rows i0 thru i1's cells's value
                        end tell
                        close saving no
                    end tell
                end tell
                set jx to count aa's item 1 -- source column count
                
                -- filter out empty rows, where emptiness is determined by cell |CHK_CELL| being blank
                repeat with i from 1 to count my aa
                    if my aa's item i's item |CHK_CELL| is in {missing value, 0.0} then set my aa's item i to false
                end repeat
                set aa to aa's lists
                
                -- populate master table
                tell application "Numbers"
                    --activate
                    tell (open |MASTER_FILE| as POSIX file)
                        tell sheet |MASTER_SHEET|'s table |MASTER_TABLE|
                            repeat with i from 1 to count my aa -- for each row index in source
                                add row below row -1 -- append row
                                tell row -1 -- appended row
                                    set cell 1's value to |TIME_STAMP| as string
                                    repeat with j from 1 to jx -- for each column index in source
                                        set cell (j + 1)'s value to my aa's item i's item j
                                    end repeat
                                end tell
                            end repeat
                        end tell
                        if modified then save
                    end tell
                end tell
                
                -- move source file to destination directory
                tell application "Finder" to move f to |DST_DIR| as POSIX file
            end repeat
            
            -- save and close master file
            tell application "Numbers"
                tell (open |MASTER_FILE| as POSIX file)
                    if modified then save
                    close
                end tell
            end tell
        end script
        tell o to run
    end _main
    --END OF APPLESCRIPT
    
  • by Hiroto,

    Hiroto Hiroto Sep 8, 2016 7:47 AM in response to Jimforxst
    Level 5 (7,276 points)
    Sep 8, 2016 7:47 AM in response to Jimforxst

    Hello

     

    Missed to answer your questions about "its" and "my".

     

     

    1) As for "its" in a whose clause, the following filter reference form:

     

    <object specifier> whose its <property> = <value>
    

     

     

    is basically equivalent to:

     

    <object specifier> whose <property> = <value>
    

     

     

    and "its" is optional unless there's terminology conflict between <property> name and a class name. If <property> name is also defined as a class name, you need to use "its" to hint AppleScript compiler that it is property and not class, and failing that, compiler yields wrong byte code which will result in run-time resolution error.

     

     

    E.g., the following code works:

     

    tell application "TextEdit"
        set doc to make new document with properties {name:"TEMP"}
        tell (window 1 whose its document = doc) -- with "its"
            return id
        end tell
    end tell
    

     

     

    whereas the following does not:

     

    tell application "TextEdit"
        set doc to make new document with properties {name:"TEMP"}
        tell (window 1 whose document = doc) -- without "its"
            return id
        end tell
    end tell
    

     

     

    I used "whose its path" in lieu of "whose path" here because I'm not sure whether there's terminology conflict on term "path" in Numbers v3 which I don't use and so played it safe. However, since you're getting error in that statement even with "its", I know now there's other cause of failure than terminology conflict.

     

     

    2) As for the use of "my" to a global list variable, which includes property list variable, it is a known technique to increase list processing performance.

     

    You may compare the performances of the following two scripts:

     

     

    --SCRIPT A
    set aa to {}
    repeat with i from 1 to 10000
        set aa's end to i
    end
    set bb to {}
    repeat with i from 1 to count aa
        set bb's end to aa's item i * -1
    end
    return bb
    --END OF SCRIPT A
    

     

     

    --SCRIPT B
    set aa to {}
    repeat with i from 1 to 10000
        set my aa's end to i
    end repeat
    set bb to {}
    repeat with i from 1 to count my aa
        set my bb's end to (my aa's item i) * -1
    end repeat
    return bb
    --END OF SCRIPT B
    

     

     

    One thing to note is that "my" can be only applied to global or property list. That is the reason I define script object in _main() handler which processes virtually large list and define the list as a property of the script object.

     

     

    Hope this explains some backgrounds of the coding.

    H

  • by SGIII,

    SGIII SGIII Sep 8, 2016 11:44 AM in response to Jimforxst
    Level 6 (10,622 points)
    Mac OS X
    Sep 8, 2016 11:44 AM in response to Jimforxst

    Despite disadvantages cited by Camelot, I find moving blocks of data into a Numbers table is far more efficient (about 10x as fast) via the clipboard.

     

    Below is an approach, less sophisticated than Hiroto's, whose handlers (subroutines) may give you some ideas.  It assumes that in the rest of the script you have has successfully opened a source file per Hiroto's suggestions.

     

     

     

    SG

     

    -- doc, sheet, table inputs

    set srcDoc to 1 -- number, or name in double quotes

    set srcSht to 1 -- number, or name in double quotes

    set srcTbl to 1 -- number, or name in double quotes

    set tgtDoc to 1 -- number, or name in double quotes

    set tgtSht to 1 -- number, or name in double quotes

    set tgtTbl to 2 -- number, or name in double quotes

     

    -- main script

    set vv to srcDataToLst(srcDoc, srcSht, srcTbl)

    tabbedToClipboard(vv)

    clpToTbl(tgtDoc, tgtSht, tgtTbl)

     

    -- handlers

    to srcDataToLst(d, s, t) -- read source table data into list of lists (2d-array), skipping blank rows

      tell application "Numbers"

      tell document d's sheet s's table t

      set vv to (rows whose first cell's value is not missing value)'s cells's value

      end tell

      end tell

    end srcDataToLst

     

    to tabbedToClipboard(lstOfLsts) -- place tab-delimited data on system clipboard

      set pasteStr to ""

      set text item delimiters to tab

      repeat with r in lstOfLsts

      set pasteStr to pasteStr & r & return -- or linefeed

      end repeat

      set the clipboard to pasteStr

    end tabbedToClipboard

     

    to clpToTbl(d, s, t) -- paste data into target table at first empty row, preserving style in table

      tell application "Numbers"

      activate

      tell document d's sheet s's table t

      tell last row to if first cell's value is not missing value then add row below it

      set selection range to (first row whose first cell's value is missing value)'s first cell

      tell application "System Events" to keystroke "v" using {command down, shift down, option down}

      end tell

      end tell

    end clpToTbl