So it seemed logical that this would be a good place to start applying some Google Apps Script. I am not sure if I will be able rebuild the entire application by July 5th, but I am going to try.
The first, and most basic thing the app has to do is take a wrestlers raw weight and determine that wrestlers weight class. Worth noting is that we drop the decimal (think floor function). For example if a wrestler weighs 106.9, they make 106. This is not typical, but it is how we do things at this camp.
In my excel file, I just used a cell formula to do this, and I could probably do the same thing in a Google Spreadsheet, but it seemed like a good way to experiment with G.A.S.
Here is my initial vision for this script. It will run every time a cell is edited. It will check the column number of the active cell. If it is not = 3, the function will end. If it is, it will validate the data - if it is not a number, a message box will alert the user, and the cell will also get a text string message and yellow background. The function will then determine the weight class and write that weight class in the cell to the right of the cell that was edited.
Here is the script.
Here is an ugly description of my steps/thought process.
The first, and most basic thing the app has to do is take a wrestlers raw weight and determine that wrestlers weight class. Worth noting is that we drop the decimal (think floor function). For example if a wrestler weighs 106.9, they make 106. This is not typical, but it is how we do things at this camp.
In my excel file, I just used a cell formula to do this, and I could probably do the same thing in a Google Spreadsheet, but it seemed like a good way to experiment with G.A.S.
Here is my initial vision for this script. It will run every time a cell is edited. It will check the column number of the active cell. If it is not = 3, the function will end. If it is, it will validate the data - if it is not a number, a message box will alert the user, and the cell will also get a text string message and yellow background. The function will then determine the weight class and write that weight class in the cell to the right of the cell that was edited.
Here is the script.
Here is an ugly description of my steps/thought process.
- Create a new Google Spreadsheet. Put column headings in row 1. Team/Name/Actual Weight/Weight Class.
- Open the script editor. Tools -> Script Editor
- Rename the function. I called it "weightClass". (It's JavaScript so I will stick to camel case)
- Establish variables for the spreadsheet and the sheet. (get.ActiveSpreadsheet and getActiveSheet.) Under SpreadsheetApp there is also getActive. I assume there is no difference between that and getActiveSpreadsheet.
- Set up Trigger. (Triggers are a really cool feature of G.A.S.) For now I am going to have this script run every time a cell is edited. (I know this may be a performance concern.) Later I may change it to run when called from the menu. This is done through Triggers under the Resource Menu. I chose my function and on edit.
- Store the active cell as a variable. sheet.getActiveCell()
- Get the row and column integer of that cell. cell.getRowIndex() and cell.getColumnIndex()
- If statement. Does the column number = 3? If so, proceed with determining the weight class. If not, else - return.
- Back inside if the if statement evaluates to true. Get the actual weight and store it in a variable. cell.GetValue()
- Open a nested if statement to validate and then determine the weight class. First check for a blank cell. the actual weight is blank, then make the weight class blank.
- A couple of notes here. 1) I just realized I didn't establish the "weightClass" variable (a number that I will use to set the value in the Weight Class column) before I assigned it a value here. Apparently that doesn't cause a problem, but I guess I will fix that and establish the variable first. ( I will leave incorrect code below.) 2) I first tried testing to see if actualWeight was equal to null. It is not. I guess I need to read up more on null. I am not sure what happens with a blank cell, but it seems when I do a getValue, if it is empty it doesn't get anything.
- Next (else if) check to see if actualWeight is a number. If not create a message box alerting the user and set the weightClass variable to a string informing the user of their error. Browser.msgBox("Actual Weight must be a number!")
- For the rest of the if block, I use else if statements and less than comparisons to determine the weight class. I thought I might be able to use a switch block, but I quickly learned that that would not work.
- I use cell.Offset(0,1) to get the index of the cell where the weight class will go. The 0 means no offset for the row. The 1 means the column number increases by 1, moving to the right one cell. I realize I could have used my variable "row" and 4 for the column index, but I wanted to try using offset.
- I set the value of the new cell to the value of the variable weightClass. newCellIndex.setValue(weightClass)
- The next block of code is used to add yellow highlighting if actualWeight is not a number. First get the value, then test if it is equal to my string from earlier. If so, set the background color to yellow. newCellIndex.setBackground("yellow");
- I realized that I needed to add an else that would clear the background if the user fixes it.
That's it. I think its pretty cool If you type a number into column 3, the weight class will automatically fill into the cell to the right with the weight class.
I realize my formatting is horrible and this is probably hard to read. I will work on this for future posts. It was good to reflect on this, but it is time consuming. It may have taken me longer to type this up than it did to write the function.
Next I will try to clean this up and also turn the if statement into an independent function that can be called. I would like the option to be able to run the function and set weight classes all at once. This will be necessary if I copy and paste a list of wrestlers and weights.
