Onward with Google Scripts

As I continue to work toward a useable gradebook in Google Sheets, I am tweaking the scripts along with it. One of the things I want this gradebook to do is to create progress reports or report cards based on 1st, 2nd, 3rd, or 4th 9 weeks, with no coding from me. To facilitate that, I have created global variables for the entire cumulative gradebook sheet. Here is an example of the code for those global variables.

The numbers correspond to column numbers in the spreadsheet. For the global variables, there are about 125 lines of code. This includes the templates for the progress reports, i.e. 1st, 2nd, etc.

My favorite part of this script is the Custom Menu. I have created a function for each of the nine weeks progress reports. Then in the menu, I have submenus which allow you to run each of the functions (separate scripts). Here's what the menu looks like:

The customizability of this function means that as a coder (VERY novice), you can create functions in Google Docs which will allow "non-techie" users to feel comfortable using them. This gets me excited! As long as I am comfortable creating (or stealing) code for some function, I can do pretty much anything I want in Google Docs.

The Best Learning I’ve Ever Experienced

A key event in my personal learning/PLN was hearing Will Richardson speak at OTA back in February 2009. In his keynote, he talked a bit about blogging and connecting with others. Most of what he was talking about was in the context of students and being in my 2nd year of teaching I was VERY impressionable. I thought a lot about myself during that presentation and how I could impact my students in ways that would be meaningful, while using emergent technology. I left the conference with some validation on the things I was beginning and thinking about starting in my classes and on fire for technology integration. One quote which impacted me was, “Kids will never be knowledge navigators if we never allow them to navigate knowledge.” I took that to heart and started to put students in my class into the position of being required to use technology tools to navigate, locate, and synthesize knowledge. We started a class wiki, which blossomed into what I had hoped would become an online Open Source Textbook for high school physics (bear in mind every single page except the front page was student-created). However, I left the public education classroom before my students got very much on the site.

That was a seminal point in my educational technology journey. Currently, in that same journey, I am pursuing the PhD in Education with an educational technology focus. So, PLEASE, attend conferences! You just never know where a conference session will end up! I have presented at that conference a few times over the last 5 years and I consider OTA (now OTA-Encyclomedia) my ‘home’ conference. While it doesn’t typically draw ‘research’ presentations, it offers a ton of sessions in which people are successfully engaging students using new technologies.

At about that same time, I had recently discovered twitter and was beginning to meet many new people in the Oklahoma Education Community, specifically those who were early twitter adopters. Those connections led to job opportunities at the state level and some great friendships with people from all aspects of education and educational technology. Also, through those connections, I have been invited to speak at small district-level conferences. However, the learning goes far beyond the ‘exposure’ I got to others. I was trying new things in my classes, reflecting about it on my blog, and sharing experiences and learning from others through comments on blog posts and twitter interactions. This was even beyond the boundaries of Oklahoma. I have met people from all over the country (and some in other countries) through twitter and not met them face to face until attending the annual conference for the International Society of Technology in Education (ISTE).

That said, the most important aspect of getting involved in a PLN, e.g. twitter, was the ability I had to connect with people who were trying the same things I was, or who had already tried it. I could learn from them and they learned from me. Twitter, as a PLN, made the education world MUCH smaller for me. The best part about it was this was free and I could personalize it to fit my needs and interests. It was (and still continues to be) an ‘a la carte’ professional development experience for me. Joining twitter for the express purpose of using it as a PLN was probably the best thing I have ever done as far as professional development goes, certainly in my first several years of teaching. I would highly recommend it. I have blogged about twitter previously here.

For those of you who are going into public education, you will have plenty of opportunities for professional development. Generally, there is enough built-in PD so that you do not even have to go to outside PD and you get your required hours. However, I can attest that the most meaningful development I have gotten over the last 8 years has been through conference, online communities, and informal learning that occurs when teachers collaborate together. I would advise anyone to get involved in some kind of community which interests you. Start a blog and reflect on what you do, start following some hashtags on twitter (e.g. #oklaed, #edtech, and/or #edchat), check with people you know who innovate. Don’t ask the teachers who are in their 30th time of teaching the exact same lesson, the exact same way. They likely won’t have much to share. Find the people who are trying new things. Those who are making mistakes. Those who fail at trying  something. (this is very different from people who constantly fail!) These are the people who will have experience to share which will be valuable.

Celebrating Success #1 - Progress Reports

I've not ever been very good at celebrating my successes. In fact, it has only been recently that I have allowed myself to acknowledge any sort of success in my professional or academic life. I've only ever considered my immediate family in terms of success and that has not been very long that I have done that either! 

I consider today a very successful day at work. Not that it was spectacular in terms of productivity (although I did get some things done), but today was the culmination of a couple of weeks of working on a project. The project was one in which I created a progress report workflow and tested it out on the automotive service program. 

When I say I created a progress report, I mean I took what my Student Services Director created and made it into something that would be automatically created from a spreadsheet in GoogleSheets. If you aren't familiar with GoogleSheets, you can check out Google's Getting Started with Sheets here. 

Anyway, over the last couple of weeks, I've been working on learning Javascript over at Codecademy so I can leverage Google Scripts (within Google Apps for Education, aka Google Drive, Calendar, etc.) 

When I say leverage, I mean automate. The project I've been working on is one in which faculty enter grades into a spreadsheet (among other things - employability ratings, attendance, etc.) and then the script populates a template document and creates a progress report for the student. Here's an example: 

Image credit: Jody Bowie
Identifying information removed

Bear in mind this was created automatically. I like to think of it as my internet minions doing my work. Granted, could have typed up these 53 progress reports, but this is just a test. We have far more than 53 students. Once the teachers learn to use the spreadsheet, progress reports then become easy. It's a matter of clicking on the administration menu (which is created by the script) and selecting the function you want to run. Or these could run on a specific date. The triggers for scripts are customizable. 

This is a brief overview, hopefully at some point I'll have the opportunity to present on this in the future. I've been working on this project, but also on a transportation form which auto-crates calendar events on specific calendars, a professional development form which creates a yearly transcript for instructors, and a daily work log in which student enter what they have done for the day and the teacher goes in and enters a rating on several factors - the script then moves that grade to the students' own spreadsheet. Imagine putting the daily grade form and this progress report together. Basically you have a gradebook which will can create documents for individual students. Scripts can also send email (like the results of a test or even send the progress report to a parent's email). The possibilities aren't endless, but I sure haven't plumbed their depths yet. 

Hopefully, I'll be posting more successes as I learn more, but I'm sure glad today for this one!