Q: Using Applescript to copy cells from one document to another

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

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

I posted this in another community, but I thought I would share this here if it helped generate more ideas and solutions.

When i posted it earlier, user "Camelot" brought up two main issues:

1. referencing the data directly instead of using the clipboard; and

2. eliminating the need to delete rows after they are pasted to the new document by taking them out before import

Possible solutions I came up with for both:

1. referencing the data directly