4 Replies Latest reply: Nov 15, 2012 7:41 AM by Gwyn Plem
Gwyn Plem Level 2 Level 2 (160 points)

I am using the grade book template with numbers. However, I might need to get some applescript help.

 

The default template has a Report page, and it includes one student. I can  change the students name to change the values. This works brilliantly. However I have 300 pupils that I need to generate this report for. I want to have one of those reports for each student automatically generated and printable so that I can give them to the students at the end of the term.

 

Obviously, I don't want to generate each report myself. Applescript seems like the solution, but I don't know how to use it.

 

Can anyone lend a hand?


MacBook Pro, Mac OS X (10.7.2)
  • Jeff Shenk Level 4 Level 4 (2,655 points)

    I think AppleScript probably is the best way to go here, but you probably ought to learn something about using it because it is often difficult to write and test a script on one system when it has to work on a different one. Yvan does a pretty good job of that, but my AppleScript skills are not that advanced.

     

    My difficulties with your problem include:

    1. I don't know what changes you might have made to the stock template in Numbers
    2. I am using Snow Leopard, not Lion
    3. I believe UI scripting is needed to duplicate a sheet, and I don't own the tools to determine how to select a sheet (if that is possible).

    So, working with the unmodified template on my system, this is a script that I think does what you need (but it would probably have to be edited to suit your situation):

     

    (*

    To run this script, UI scripting must be enabled. Numbers must be running with the Grade Book document open and the sheet "Reports" selected.

    *)

    tell application "Numbers"

      activate

              set theDoc to front document

              set sourceTable to table "Student Grades" of sheet "Student Data" of theDoc

              tell theDoc

                        repeat with i from 4 to ((row count of sourceTable) - 1)

                                  set theName to value of cell i of column 1 of sourceTable

                                  tell application "System Events"

                                            tell process "Numbers"

                                                      click menu item "Duplicate" of menu 1 of menu bar item "Edit" of menu bar 1

                                            end tell

                                  end tell

                                  set theName to value of cell i of column 1 of sourceTable

                                  tell sheet (i - 2) of theDoc

                                            set its name to (theName & " Report")

                                            set value of cell 1 of row 3 of table "Student Lookup" to theName

                                  end tell

                        end repeat

      delete last sheet

              end tell

    end tell

  • Gwyn Plem Level 2 Level 2 (160 points)

    Thank Jeff - This looks like a great solution - but after creating a sheet for Arthur I get this error -

     

    "Numbers got an error: Can’t set table "Student Lookup" of sheet 2 of document "gradebook" to "Arthur Albert"."

     

    I am unsure what can be done!

     

    I have been a little cheeky and am crossing my fingers that you don't mind - and am inlcuding a link on dropbox to my file as is - as you mentioned the problem will be with how I have adapted it!

     

    https://dl.dropbox.com/u/738503/Pupil_SS_Score.numbers

     

    In hope - and thank you

     

    Gwyn

  • Jeff Shenk Level 4 Level 4 (2,655 points)

    Since your modification changed the names of sheets and tales and added rows to the table on the first sheet, all of which were things I hard coded, some changes are obviously needed. Your error message would seem to indicate that you were trying the unaltered script on the original template, and the words "value of cell 1 of row 3 of" are missing from the sixth to last line.

     

    I modified the script, opened your file and selected the sheet "Report" in the sheets pane and ran the script; the result I got is linked here .

     

    This is the modified script:

     

     

    (*

    To run this script, UI scripting must be enabled. Numbers must be running with the Grade Book document open and the sheet "Report" selected.

    *)

    tell application "Numbers"

      activate

              set theDoc to front document

              set sourceTable to table "Pupil Grades" of sheet "Class Data" of theDoc

              tell theDoc

                        repeat with i from 3 to ((row count of sourceTable) - 5)

                                  set theName to value of cell i of column 1 of sourceTable

                                  tell application "System Events"

                                            tell process "Numbers"

                                                      click menu item "Duplicate" of menu 1 of menu bar item "Edit" of menu bar 1

                                            end tell

                                  end tell

                                  tell sheet (i - 1) of theDoc

                                            set its name to (theName & " Report")

                                            set value of cell 1 of row 3 of table "Individual Pupils" to theName

                                  end tell

                        end repeat

      delete last sheet

              end tell

    end tell

  • Gwyn Plem Level 2 Level 2 (160 points)

    Thank you so much. This works perfectly! Exactly what I wanted.

     

    Absolutely brilliant!

     

    Thanks again

     

    Gwyn