Using MailApp and a Form Submit Trigger

So I had grand plans of posting regularly as I learned GAS. Unfortunately I haven't done a good job with the blogging. My excuses are that I had a new son in March, worked tirelessly getting my house ready to sell, did job search, and moved 700 miles back to the Chicago area.  But I am going to try and post again more regularly and document my experiences with GAS.  I have written a bunch of simple scripts over the past year, and I do plan to go back and post about them.

But to get me going again I will start with a simple example. A teacher came in to my office today and asked if there was a way for students to check into the media center, then have their teacher get an email notification. I figured this could be accomplished fairly easily via a Google Form with some Google Apps Script to send the email.

I started by creating a form.

Now I want to create a script that will use the MailApp Class to send an email to the teacher of the student who is checking in or out. There are probably many ways to accomplish this, but my solution was to create another sheet and in column A put the teacher names. These values must match the multiple choice options from the teacher question on the form. Column B is filled with each respective teacher's email address. I will use Google Apps Script to read this data into a JavaScript Object. It was important for me to keep the list of teachers out of the Google Apps Script because I want this to be maintainable by the end user. If there is a change in the list of teachers a user could make the change in the form and the spreadsheet, without having to touch the code. You can see the spreadsheet here.

On to the code.  I start by opening spreadsheet. I chose to open it by id; using getActiveSpreadsheet should also work. Then I open the sheet with the emails by name. Then I get the data and write the values into a 2d array.

Before I talk about the next part, it is important to understand how this script will run. The GAS documentation has an excellent article about execution methods. We want this script to run every someone submits a form. When that form is submitted a parameter (e) is passed to the function. Read about this here. (Form submit events are at the bottom of the page) You can actually access the values by the name of the form field, but I prefer to access them via their array index. For example, e.values[0] is column A, which is always the time stamp in Google Forms.

So I grab the teacher name (e.values[5]), then I loop through the emails array looking for a match to the submitted teacher name. When the loop finds a match, it sets the email variable.

Next I get the rest of the values from the form submit event and send the email.  Sending messages is pretty simple, with 3 parameters, the email address, the subject line and the body. It is possible to create an HTML body, but in this case it isn't necessary.  Another nice feature is that you can set the emails to display with a no reply address.  Without setting this, it will look like the emails will come from you.

 Check out the code below. Please let me know if you have any questions, suggestions or if you catch a mistake.

	function sendEmail(e) {
	//open the spreadsheet and get the email sheet
	ss = SpreadsheetApp.openById("0AuD3WXC51A9gdGVScnVlVVNNZkxFZnlSdTZBVHVNUFE")
	sheet = ss.getSheetByName("emails");
	//read the data into an array
	emailArray = sheet.getDataRange().getValues();
	//get the teacher name from the form submission.
	name = e.values[5];
	//loop through the array of names and email addresses
	//if the name from the form submission matches the name from the array, set the email variable
	//to the corresponding email address
	for (i=0;i < emailArray.length;i++){
	if (name == emailArray[i][0]) {
	email = emailArray[i][1];
	}
	}
	//get the rest of the values from the form submission
	timeStamp = e.values[0];
	inOrOut = e.values[1];
	studentName = e.values[2];
	id = e.values[3];
	period = e.values[4];
	//send the email
	MailApp.sendEmail(email,"Media Center Check In/Out Notice",
	studentName +" checked "+inOrOut+" at "+timeStamp+" during period "+period
	//set this value so the emails do not come from your address
	,{noReply: true}
	);
	}

view raw mediacheckin hosted with ❤ by GitHub

Determining a Wrestling Weight Class with Google Apps Script

I work at a wrestling camp every summer doing registration.  Imagine taking anywhere between 400-800 wrestlers, getting their weigh-in data and building teams.  I have an excel file with tons of VBA to do all kinds of things.  I am pretty proud of it because I built it from scratch and it gets the job done, but I know it is a mess.  I pieced things together, and it isn't very efficient.  Every summer, I modify things and make improvements, but I think it is time to let it die and move on.

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.

	function weightClass() {
	//Establish Spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	//Establish Sheet
	var sheet = ss.getActiveSheet();
	//Get the cell being edited
	var cell = sheet.getActiveCell();
	//Get the indeces of the cell being edited
	var row = cell.getRowIndex();
	var column = cell.getColumnIndex();
	//If statement - only checks column - only if column 1 is edited
	if (column === 3) {
	// Get value from edited cell (actual weight)
	var actualWeight = cell.getValue();
	//if/else - determine weight class
	if (actualWeight === "") {weightClass = "";}
	else if (typeof actualWeight !== "number") {
	Browser.msgBox("Actual Weight must be a number!");
	weightClass = "Actual Weight must be a number!";
	}
	else if (actualWeight < 107) {weightClass = 106;}
	else if (actualWeight < 114) {weightClass = 113;}
	else if (actualWeight < 121) {weightClass = 120;}
	else if (actualWeight < 127) {weightClass = 126;}
	else if (actualWeight < 133) {weightClass = 132;}
	else if (actualWeight < 139) {weightClass = 138;}
	else if (actualWeight < 146) {weightClass = 145;}
	else if (actualWeight < 153) {weightClass = 152;}
	else if (actualWeight < 161) {weightClass = 160;}
	else if (actualWeight < 171) {weightClass = 170;}
	else if (actualWeight < 183) {weightClass = 182;}
	else if (actualWeight < 196) {weightClass = 195;}
	else if (actualWeight < 221) {weightClass = 220;}
	else if (actualWeight < 286) {weightClass = 285;}
	else {weightClass = "Did not make weight";}
	//Use offset to set location of weight class
	var newCellIndex = cell.offset(0, 1);
	//set value of weight class
	newCellIndex.setValue(weightClass);
	//Add highlighting to cell if it is not a number (find a better way)
	var newCellValue = newCellIndex.getValue();
	if (newCellValue === "Actual Weight must be a number!") {
	newCellIndex.setBackgroundColor("yellow");
	}
	else {newCellIndex.setBackground("none");}
	}
	//else statement - do nothing if column is not === 1
	else {
	return;
	}
	}

view raw weightclass.js hosted with ❤ by GitHub

Here is an ugly description of my steps/thought process.

1. Create a new Google Spreadsheet.  Put column headings in row 1.  Team/Name/Actual Weight/Weight Class.
2. Open the script editor.  Tools -> Script Editor
3. Rename the function.  I called it "weightClass".  (It's JavaScript so I will stick to camel case)
4. 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.
5. 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.
6. Store the active cell as a variable.   sheet.getActiveCell()
7. Get the row and column integer of that cell.  cell.getRowIndex() and cell.getColumnIndex()
8. If statement.  Does the column number  = 3?  If so, proceed with determining the weight class.  If not, else - return.
9. Back inside if the if statement evaluates to true.  Get the actual weight and store it in a variable.  cell.GetValue()
10. 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.
11. 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.  
12. 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!")
13. 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.
14. 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.
15. I set the value of the new cell to the value of the variable weightClass.  newCellIndex.setValue(weightClass)
16. 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");
17. 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.

Trying to Learn Google Apps Script

I am writing this blog to journal my process of trying to learn Google Apps Script.  I am a technology specialist at a K-8, and we utilize Google Apps for Education.  I like the idea of using G.A.S. to automate processes and  maybe even build some web apps.

My background in programming is pretty minimal.  As a math major in college I took 1 computer science course in C++.  I remember about as much C++ as I do from taking German in high school. (Okay fine, probably less.)

Since then I have tried learning Python, JavaScript, and Visual Basic (in MS Excel).  I had some success with Python, and I was able to do make a couple of useful scripts, but I didn't use it often enough for it to stick.  What is interesting is how much more quickly I picked up basic JavaScript because of my experience with Python.  Though I still am I beginner in both languages.

So G.A.S seemed like a logical next step because: 1) It is essentially JavaScript, so I hope to be able to pick it up fairly quickly and 2) because we use Google Apps where I work, I hope I will use it more often, which is the best way to learn.

Suggestions are appreciated!  I am a novice and if you read a post and see any area in which I could improve my scripts, please comment.