0

AppleScript beginner here. Searching high and low hasn't led me to the answer yet.

I'm using AppleScript to help run youth wrestling tournaments. Each division (based on age) is broken down into weight classes. For example: Novice 80 or Cadet 105.

Once a certain group of kids is put into a certain division/weight class, those kids get added to a new sheet that contains their bracket (think March Madness bracket but a small number of kids wrestling instead of playing basketball).

I've figured out how to get a group into a new sheet where they populate the bracket, but when this new sheet is created, I don't know how to make AppleScript change the name of the sheet to the correct division/weight class. I'm sure it has something to do with creating variables based on a list of the divisions/weight classes (that I have), but I can't figure out how to do it. Here's the relevant portion of the code:

tell document 1
        set active sheet to the last sheet
        set thisSheet to make new sheet
        set the name of thisSheet to "[Division variable – Weight class variable]"
        tell thisSheet
            delete every table
        end tell

Any ideas on how to make AppleScript name the sheet like I want?

  • What happens when you run the script that you showed? – matt Mar 28 '17 at 15:18
  • When I run it, the title of the new sheet is [Division variable – Weight class variable], but it needs to be something like Novice 80, Cadet 105, etc. There is typically 30 to 50 different weight classes at a tournament and each sheet will need to be titled manually (which takes a long time) so the results can be put in the right place and updated brackets printed, **or** hopefully I can figure out how to make AppleScript do it for me. – Morris Bird Mar 29 '17 at 16:06
  • But how are _we_ supposed to know what the divisions and weights are? _You_ need to know them. – matt Mar 29 '17 at 16:40
  • @MorrisBird Third example added, much more and I'll be writing you the entire code :( beginning is difficult, but the things you're after are pretty basic so I've also included a link to a site you may not have found yet. –  Mar 29 '17 at 19:58

4 Answers4

0

Try

set thisSheet to make new sheet with properties  {name:"yourname"}

Edit Some explanation: if your struggling on how to adress third party apps try to load it's library and look up the methods you need. In applescript editor you will find it in the (I guess) menu. Then select the desired app to get the library

Pat_Morita
  • 3,355
  • 3
  • 25
  • 36
  • While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-‌​code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Rosário Pereira Fernandes Mar 28 '17 at 21:26
0

To give you a small example that you're able to visualize what you're going after, heres a small snippet. I think its self explanatory, iterate through a list of titles, then apply the names to the sheets.

set divisionNames to {"Novice", "Cadet"} -- How You Grab These Values Matters
set weightClasses to {"80", "105"} -- These Values Too

tell application "Numbers"
    activate
set thisDocument to make new document
tell thisDocument
    repeat with i from 1 to count of divisionNames
        make new sheet with properties {name:item i of divisionNames & space & item i of weightClasses}
    end repeat
end tell
end tell

Alternately, if you're pulling the values from a list as a whole then you could

set sheetTitles to {"Novice 80", "Cadet 105"}

tell application "Numbers"
activate
set thisDocument to make new document
tell thisDocument
    repeat with division in sheetTitles
        make new sheet with properties {name:division}
    end repeat
end tell
end tell

EDIT: In the spirit of helping a low to no budget school/organization.. here's another example answering the second question issued in comments. Again without knowing the structure of your data its hard to give you an exact answer on your specific case. Additionally, here's a link to a site they may help further the advancement on your project. https://iworkautomation.com/numbers/index.html

(*
set sampleKids to {"John Doe", "Jane Doe", "Johnny Foe", "Janie Foe", "Tommy Joe", "Tammy Joe"}
set sampleDivisions to {"Novice-80", "Novice-85", "Cadet-105", "Cadet-110", "Novice-80", "Cadet-105"}

tell application "Numbers"
activate
set thisDoc to make new document with properties {name:"Wrestling Sample"}
tell thisDoc
    set the name of sheet 1 to "Sign In Sheet"
    tell active sheet
        delete every table
        set newTable to make new table with properties {row count:(count of sampleKids) + 1, column count:2, name:"Sign In Sheet"}
        tell newTable
            set value of cell 1 of column "A" to "Name"
            set value of cell 1 of column "B" to "Division"
            set x to 2
            repeat with eachName in sampleKids
                set value of cell x of column "A" to eachName
                set x to (x + 1)
            end repeat
            set x to 2
            repeat with eachDivision in sampleDivisions
                set value of cell x of column "B" to eachDivision
                set x to (x + 1)
            end repeat
        end tell
    end tell
end tell
end tell
*)

--********** IGNORE ABOVE THIS LINE IT'S ONLY BUILDING A SAMPLE TABLE **********--
--********** SAVE ABOVE TO ANOTHER SCRIPT FOR TESTING WITH NEW TABLE **********--

(*
ERROR HANDLING ISN'T PRESENT - AN EMPTY CELL IN FIRST COLUMN OR CHOSEN COLUMN WILL THROW AN 
ERROR SINCE THEY ARE THE IMPORTANT PIECES OF DATA FOR GRABBING LISTS - SAVE SCRIPT
TO NUMBERS SCRIPT FOLDER OF YOUR CHOICE - ENTER ALL INFO ON FIRST TABLE OF FIRST SHEET OF 
DOCUMENT THEN RUN SCRIPT - SCRIPT ACCEPTS ANY AMOUNT OF ROWS OR COLUMNS
*)

tell application "Numbers"
activate
-- Display A Simple Reminder That You're About To Lose Some Existing Data
display dialog "This Script Will Delete All Sheets Except The First Sheet Of This Document Before It Proceeds To Make New Sheets & Tables Based On The First Table Of The First Sheet." buttons {"Cancel", "Start"} default button 2 with icon 1
tell document 1
    -- Get A List of the Sheet Names
    set sheetNames to name of sheets
    -- Start With A Fresh Slate, No Old Sheets
    delete (every sheet whose name is not item 1 of sheetNames)
    tell sheet 1
        -- Grab and Set Future Header Values
        set columnHeaders to value of cell of row 1 in table 1
        -- Display A List Of Possible Choices To Create New Sheets With From The List We Make Above
        set chosenColumn to choose from list columnHeaders with prompt "Which Column Do You Want To Use For New Sheets?" default items item 1 of columnHeaders
        set chosenColumn to chosenColumn as text
        tell table 1
            -- Remove All Empty Rows to Help Prevent Error In Script
            set {row_count, col_count} to {count rows, count columns}
            set blank_row to {}
            repeat with x from 1 to col_count
                set blank_row to blank_row & missing value
            end repeat
            set x to 1
            -- Delete Empty Rows In Reverse, It's Logical
            repeat with y from row_count to 1 by -1
                set row_values to value of cells of row y
                if row_values = blank_row then delete row y
            end repeat

            -- Grab A List of All Divisions for Future Use Depending on Choice From Prompt, excluding the First Row Which Is A Header. If You Selected The First Column, We Have to Handle That Differently
            if chosenColumn is item 1 of columnHeaders then
                set theDivisions to the value of every cell of column "A" whose value is not chosenColumn
            else
                set theDivisions to the value of every cell of column named chosenColumn whose value is not chosenColumn
            end if

        end tell
    end tell
    -- Start the New "Sheet Making" Loop
    repeat with division in theDivisions
        -- Make An Empty Blank List At the Start of Every Loop
        set matchingDivisions to {}
        tell table 1 of sheet 1

            -- Get All Rows Matching the Current Division of the Loop We Are On, to Make New Tables With Later
            repeat with x from 1 to count of cells
                if the value of cell x is division then
                    -- Put All Data About the Rows We Gathered Above Into the Empty List We Made 
                    set the end of matchingDivisions to value of cells of row of cell x
                end if
            end repeat
            -- Reset x, Because I'm Not Creative and Want to Use It Later
            set x to 1
        end tell
        -- If The Sheet Of the Division We Are On, of the Loop, Doesn't Exist, Make It
        if not (exists sheet division) then
            make new sheet with properties {name:division}
            tell sheet division
                -- Start With A Fresh Slate On This New Sheet
                delete every table
                -- Make the Table With All Relevant Parameters 
                set currentDivisionTable to make new table with properties ¬
                    {row count:((count of matchingDivisions) + 1), column count:count of item 1 of matchingDivisions, name:division}
                tell currentDivisionTable
                    set x to 1
                    -- Set The Header Values from A List We Created Earlier
                    repeat with theHeader in columnHeaders
                        set the value of cell x to theHeader
                        set x to (x + 1)
                    end repeat
                    -- Reset x Again, I'm Lazy
                    set x to 1
                    -- Set Starting Point to Start Filling The Table, Compensate For Our Headers
                    set rowIndex to 1
                    set columnIndex to 0
                    -- Start Filling The Table With Data, Which Comes From The List Earlier
                    repeat with x from 1 to count of the matchingDivisions
                        set rowData to item x of the matchingDivisions
                        tell row (rowIndex + x)
                            repeat with i from 1 to the count of rowData
                                tell cell (columnIndex + i)
                                    set value to item i of rowData
                                end tell
                            end repeat
                        end tell
                    end repeat
                end tell
            end tell
        end if
    end repeat
    -- Return To the First Sheet
    set the active sheet to the first sheet
    -- Display Notification That The Tables Are Done Being Made -- OPTIONAL
    display notification "Processing is complete." with title "Numbers Table Converter" subtitle "All Tables Have Been Made." sound name "Hero"
end tell
end tell
  • I think this is getting me close. What if I won't know all the titles beforehand? For example, sometimes it will be Novice 80, sometimes Novice 85, sometimes both, etc, depending on the kids who attend the tournament and we don't know that in advance. Is there a way to do something like: set sheetTitle to {"reference the value in a cell in the Numbers spreadsheet"}? – Morris Bird Mar 29 '17 at 16:22
  • @MorrisBird Without knowing the structure of your data or how you're retrieving it, it's difficult to say. If I'm understanding your end goal correctly, (make a spreadsheet on the fly of kids and their classes) there may be another way of going about it. –  Mar 29 '17 at 16:27
  • (1/3) Thank you. Your examples are incredibly helpful and more than I ever expected. I didn't post my question fishing to have someone write the code for me. You do understand the end goal correctly. – Morris Bird Mar 30 '17 at 15:38
  • (2/3) Right now, our events have around 150 kids. Of these, around 100 will register in advance so I have them in the spreadsheet upfront, but around 50 just show up the morning of the event. For these kids, we have to type their information into the spreadsheet after they weigh-in, then we have to separate out the kids into groups of 2-8 in a way that accounts for their age, weight, and experience to make fair brackets. – Morris Bird Mar 30 '17 at 15:38
  • (3/3) Once I know what kids will be in a group together, I highlight those rows in the spreadsheet then use scripts I created to create the bracket in a new sheet. But naming each sheet (which is necessary for going back during the tournament to input results and print updated brackets) is something I have to do manually (which is time intensive when I need to get the tournament started asap). Maybe there is another way of going about all this, but what I've figured out thus far with AppleScript has been helpful. There's just a few more things I'd like the script to do. Thanks again. – Morris Bird Mar 30 '17 at 15:42
  • @MorrisBird I've updated it with a complete example, although it may not be exactly what you're trying to accomplish, its a decent working example that should help you visualize and pick apart for understanding. I've listed a few of the caveats in the script. Uncomment the top part if you wish to create a sample table to work with, Or save it to another script. Everything is explained in the script I believe. If I've answered your question, accept the answer to be helpful to others in the future. –  Mar 30 '17 at 15:53
  • Thanks so much. I'll have a chance to work with this later tonight. – Morris Bird Mar 30 '17 at 17:06
  • @MorrisBird Hopefully you find it useful in many ways, educational and functional. Final update to make it fully automated, shouldn't require edits from you besides warning text or additional functions you'd like to add. At minimum its an example to learn from. Now back to work for me :) –  Mar 30 '17 at 17:48
  • Just wanted to say thanks again. The tournament was this past Saturday and went really well. Our Executive Director said it was the best tournament we've ever ran. That was good to hear. I was able to vastly improve the system thanks to you. I wasn't able to implement all of your script, but I'll keep making progress. We won't host another tournament for several months so I have time to keep learning. In case you'd like to see what this was all about, [here's a highlight reel from the tournament](https://www.youtube.com/watch?v=dyk_XIazDEk). – Morris Bird Apr 24 '17 at 14:29
0

The full code modify at below:

        set {begCol, endCol} to {2, 17}
        set tgtCol to 1

        tell application "Numbers"
            tell front sheet of front document
                tell active sheet
                    set getVal to rows's cells's value
                    set myOriginalTable to front table
                    set itemCode to {"CardNo", "SAL-DAY", "OT15-Money", 
    "OT2-Money", "OT3-Money", "OTHINC", "P0007", "DILIG", "P0004", "P0003", 
    "SEV_SPE", "P0011", "SI-SSF", "TI-TAXITEM", "P0022", "P0021", "P0025"} -- 
    change to variable 
                    set Amount to {"CardNo", "SAL-DAY", "OT15-Money", 
    "OT2-Money", "OT3-Money", "OTHINC", "P0007", "DILIG", "P0004", "P0003", 
    "SEV_SPE", "P0011", "SI-SSF", "TI-TAXITEM", "P0022", "P0021", "P0025"} -- 
    change to variable 
                    set setCols to 8
                end tell
                set myNewTable to make new table with properties ¬
                    {column count:setCols, row count:(count of itemCode) + 1, 
header column count:0}
                tell myNewTable
                    set value of cell 1 of column "A" to "cardNo"
                    set value of cell 1 of column "B" to "emCode"
                    set value of cell 1 of column "C" to "emName"
                    set value of cell 1 of column "D" to "itemCode"
                    set value of cell 1 of column "E" to "itemName"
                    set value of cell 1 of column "F" to "effDate"
                    set value of cell 1 of column "G" to "amt"
                    set value of cell 1 of column "H" to "remark"
                    set x to 2
                    repeat with eachAmount in Amount
                        set value of cell x of column "G" to eachAmount
                        set x to (x + 1)
                    end repeat
                    set x to 2
                    repeat with eachItemCode in itemCode
                        set value of cell x of column "D" to eachItemCode
                        set x to (x + 1)
                    end repeat
                end tell
            end tell
        end tell

Thank you

  • This could become a good answer if it only were more explained. What happens here? Why does it solves OP's problem? – TobiMcNamobi Mar 02 '18 at 12:23
  • This code is fix variable on itemCode and Amount. So, intention is how can get the value from iWork (numbers) and set to variable on itemCode and Amount. Currently, I has "set itemCodeVal to row 1's (cells bredd_start thru -1)'s value" and "set AmountVal to row 2's (cells bredd_start thru -1)'s value" – s.sasenharn Mar 05 '18 at 02:44
0

According source code above. I found other way for use range select for determine data. But it still incorrect because the code pickup all data to one cell like this. result after ran script

Could some one able to suggest me?

try
    tell application "Numbers" to tell front document to tell active sheet
        set delimiter to ","
        set selected_table to first table whose class of selection range is range
        tell selected_table
            set my_selection to the selection range
            set begCol to address of first column of my_selection
            set endCol to address of last column of my_selection
            set begRow to address of first row of my_selection
            set endRow to address of last row of my_selection
            set getVal to ""
            repeat with j from begRow to endRow
                repeat with i from begCol to endCol
                    set getVal to (getVal & (value of cell j of column i of selected_table) as text) & delimiter

                    set getVal to getVal & return
                end repeat
            end repeat
        end tell
    end tell

    set AmountVal to {getVal}

    tell application "Numbers"
        activate
        tell front sheet of front document
            set myOriginalTable to front table
            set setCols to 8
            set myNewTable to make new table with properties ¬
                {row count:(count of AmountVal) + 1, column count:setCols, header column count:0}
            tell myNewTable
                set value of cell 1 of column "A" to "cardNo"
                set value of cell 1 of column "B" to "emCode"
                set value of cell 1 of column "C" to "emName"
                set value of cell 1 of column "D" to "itemCode"
                set value of cell 1 of column "E" to "itemName"
                set value of cell 1 of column "F" to "effDate"
                set value of cell 1 of column "G" to "amt"
                set value of cell 1 of column "H" to "remark"

                set x to 2
                repeat with eachAmount in AmountVal
                    set value of cell x of column "G" to eachAmount
                    set x to (x + 1)
                end repeat
            end tell
        end tell
    end tell

    display notification "Already Done!" with title "Numbers"
on error
    display dialog "Select a range first and then try again"
end try
  • Instead of posting two answers you should edit the above answer and delete this. – ASR Mar 08 '18 at 07:35