Link Two Google Docs for Translating

An interesting Stack Overflow question popped up about auto-translating one document into another when edits are made. Most Google services have things called “triggers” which run functions after some kind of event – opening the doc, making a change, etc.

A Google Doc does not have access to the onEdit trigger, so there is no way to automatically run the translation unless you put it on a timer trigger, and that’s a waste of resources, especially if you’re not constantly updating the document. But, you can link two documents together via the ID and push changes made using a custom menu.

Grab a copy of the template with instructions.


Tracking Missing IDs with a Google Sheet

Like many schools, our students are asked to wear their student ID’s when they come into the building each day. During the day, they don’t need to have them on…just in the morning so we can make sure people coming in are part of our student body. If a student doesn’t have their ID, we issue a temporary and track how many times they don’t have one when they come to school. This process used to be done by hand each morning. A queue would form and a teacher would write down ID numbers (they all have them memorized) and names. This caused several problems:

  1. If a student gave a fake ID number, office staff wouldn’t know until much later in the day.

  2. The paper copy of the ID list was given to a secretary to transcribe into a spreadsheet.

  3. Transcribing meant looking up the ID in our SIS and then manually entering the number of times the student didn’t have their ID.

  4. When benchmarks were hit (5, 8, 11), disciplinary action was issued and followed up on by an assistant principal.

I spoke with the head secretary and we worked out a custom Google Sheet to do all of this automatically. Now, the duty station is equipped with a Chromebook so the teacher issuing IDs can quickly check veracity of the given ID and have all of the tally work done. This (mega) post outlines the sheet structure and custom code used to do the work.

The template and source are linked at the bottom of the post.

The Sheet

The Google Sheet is split into four tabs:

  1. Raw: Blank sheet with Timestamp, ID, and Name.

  2. Lookup: database of student ID numbers and the associated name/grade.

  3. Aggregate: ID, Name, Grade, Count, Cons 1, Cons 2, Cons 3.

  4. Daily groups. Filter, ID, Name, Grade.

Raw Input

This is used by the duty station. The date and student ID numbers are entered as students are given temporary bands. The Name column is populated by an ID lookup using =index(lookup!B:B, match(B2,lookup!A:A)). Both VLOOKUP and INDEX/MATCH are notorious for mistakes with large data sets, which is why the lookup tab is filtered by ID number and not alphabetically.


The same student can have multiple dates of entry and this sheet aggregates by student ID. Using UNIQUE, it pulls each ID as a single column. Then, I used another index-match function to populate the name and grade. To count the number of missing ID instances, a COUNTIF function worked well to count the number of times the ID number shows up in the raw sheet.


At this point, much of the work of the sheet was moved over to Apps Scripts. We needed some dynamic results and using a script to sort through the volume of information was much faster and more reliable than using regular Sheets functions. I’m breaking the code into chunks to better explain the purpose. In the actual sheet, all functions are in the same file.

Function 1: Globals and menu item

I use a couple global variables (not super efficiently, though). I also create a custom menu to run sheet functions. This takes care of creating those items. I have mine at the top of the script, but they can go anywhere.

Function 2: Listing dates a student was missing the ID

For reporting, it was helpful to know which dates a student was missing an ID. Rather than adding n columns to fill with dates, a script was used to look up the student ID number and then add a note to the cell with the dates.

Function 3: Populating a daily list of students

Each day, the secretary checks for students who have hit a benchmark: 5, 8, or 11 missing IDs. Searching through the aggregate list isn’t feasible, so a script does the search and then returns the results dynamically.

This checks two conditions: A) The number of missing IDs is equal to or greater than the target and B) there is no consequence filled in the appropriate column. If the consequence has been assigned, it’s in the SIS and doesn’t need to be entered by the secretary.

Function 4: Assigning consequences

When the office staff pulled the daily list, they go into our SIS and update the disciplinary action or other notes. They would still have to go back and document that consequence being assigned in the aggregate sheet. Rather than scroll through the list, the appropriate consequence for the target is now filled in when the list is generated. The daily list isn’t cleared until the script is run again with a new target.

If, by chance, there are no students to assign a consequence to, a popup is shown to let the user know that no students meet the criteria.

The Result

In the end, we’ve removed two steps from the administrative process, but they were the most time-intensive steps. Rather than looking each student up and then remarking a spreadsheet, the staff member needs to simply pull the list of students for that day.

Computers are great at repetitive tasks, so let’s use them to do those tasks. There’s definitely some optimization that can be done, especially in the last two functions as they pass those arrays around like a cold. If you make updates, please comment and share back. The code is hosted on GitHub, so you can fork and update as much as you’d like.

You can look at a copy of the template or just make a copy for yourself and start poking away.

The entire source (not broken up) is hosted on GitHub Gists.

Drawing Notes

I used to make fill-in-the-blank notes for my students to complete while watching a video. For a particular subset of students, that works well. Helping lower the barrier for learning by providing a construct for information gathering led to more engagement when it came time to use the information.

Then I took on AP Biology.

Some very wise people told me to teach the material and not provide so much structure. I wouldn’t be able to put in the amount of time it would take to get everything pre-made. And boy, were they right. (I’ve taught AP Chem, but that was a long time ago. I needed their reminders.)

I’ve also wanted to move to a more free-form video…not as structured. More fluid. Focused more on deep content. Trying to write while I spoke at the same time was difficult to maintain. So, in response, I’ve moved to drawing out the lesson notes, scanning it, and talking over the pictures.

flickr photo shared by bennettscience under a Creative Commons ( BY ) license

flickr photo shared by bennettscience under a Creative Commons ( BY ) license

This has helped my students improve their own visual representations of the concepts. It’s also helped me tell a better story (all science is a story anyways. Now it’s an illustrated story).

I sketch the notes…maybe 20 minutes to get everything sorted, and then scan it in to the computer. I drop the image into Camtasia and go for it.

Low key, but early indications are that it’s just as effective as fill in the blank. But now, students can fill in their own blanks.

Losing Stories

Startup culture ate one of my stories this year and I just found out about it this week.

I check for broken links on my blog. It’s mainly because I don’t like getting emails that such-and-such link doesn’t work anymore from a reader. I try to repair what I can and I remove what’s dead (sites and apps die a little too frequently).

One of my favorite posts from a couple of years back had a broken link, so I went to check it. Turns out, Storehouse, a great storytelling app I used for a while, closed shop in July. Along with it went my photos, videos, and stories published. No notice, no warning. Just gone.

The story embedded on the page? Gone. Replaced with the sad face.


The video of my daughter learning to crawl? Gone.

Shame on me for not backing up better. Shame on us for pouring our stories, images, and videos into a culture that packs up and moves on with no warning.

Building a Custom Web App from Google Sheets

I’m working with a group of teachers to run a mock election this week. They had a basic Form set up for students to vote along with two other “pie-in-the-sky” ideas:

  1. Assign electoral votes by homeroom to simulate the Electoral College.

  2. Have a live-update view of the election teachers could show in their classrooms.

## Electoral Votes

To set up the electoral system, we created a database sheet in the workbook. We listed each teacher in the school and then assigned an electoral value. The next eight columns are used to tally votes per candidate per homeroom. I was able to do this using the `COUNTIFS` function to match the candidate **and** the homeroom teacher.

Another sheet was added to do the electoral tallies. This formula is a little more complex. I needed to grab the data from each row, find the max, and then check which candidate matches the max column. My final formula was:


Broken down:

  1. If all candidates are 0, leave as 0 (it was assigning electoral votes to all three before this)

  2. If 1 is false, check if the `MAX` value for that row is the same as the candidate header.

  3. If 2 is true, assign the electoral votes. If false, leave as zero.

It works well and calculates instantly, which is really nice. The columns are then summed by candidate.

## Popular vote

The electoral vote took care of the presidential candidates, but I still needed popular vote totals for all of the candidates. That was a simple `SUM` function pulled from Sheet 2 which aggregated votes by homeroom. I didn’t need to worry about cross-checking against candidates because I was just interested in the totals.

Now that the tallying is done, I turned my attention to the live updates. My first testing was with a published chart from the spreadsheet. You can publish them without exposing the underlying data, which makes sharing results much simpler. But, it turns out, that leaving the chart options as “automatically publish updates” means anywhere from 10-30 minutes later. Not great for a live-update.

After some digging, I settled on the fact that a custom chart built with the Google Visualization API would be the best way to immediately show poll data.

## Coding the Chart

This took much longer than I expected because the Google Visualization API expects you to send a `QUERY` call to get data from a live spreadsheet to draw the chart. I’ve not used the Query language much, and in the end, my data wasn’t structured well enough in the spreadsheet to easily get what I wanted.

Using Google Apps Script, I was able to grab the spreadsheet data and create my own array to then give to the API for the chart.

## Displaying Popular Votes

The teachers also wanted to display the popular vote for all of the elections (President, Governor, Senate, House) next to the electoral data. This wasn’t too bad and I was able to set up a simple HTML table (using CSS `display: table`) to load the live data using AJAX.

Instead of having teachers reload the page every few minutes, the last two lines on the client-side code will re-run each script with fresh data, updating the site on it’s own.

## Why use a web app?

This is definitely more complicated than it could have been. But, I went with a web app approach for two main reasons:

  1. Simplicity for the _user_

  2. Data security

They’re collecting some demographic information that they don’t want published, which is one of the problems with just sharing the sheet out with the entire school staff. They also wanted to make sure the vote wasn’t damaged by someone accidentally changing some of the cell calculations.

The nice thing about running a web app is that I can still require a school district login to see the page. Additionally, even if the page were public, it hides the original spreadsheet key because you don’t need it in the client-side scripts. So that spreadsheet data is relatively safe from prying eyes.

Watch for a follow up post with the election results and a wrap up on how using the app went.

    • *The featured image is a [flickr photo]( “Mock elections 2010”) shared by [Two2Travel]( under a [Creative Commons ( BY-NC-SA ) license](

Looking for Help with Canvas LMS

We’re using Canvas this year in our district and I’m trying to figure out how to actively (and successfully) utilize the Learning Mastery gradebook alongside the traditional gradebook…sort of a hybrid SBG.

I have Outcomes set for the entire year (the AP Bio curriculum) and I’m assigning Outcomes to each assignment. This is transferring nicely into the Learning Mastery gradebook. At the same time, coursework (labs, tests, etc) are being entered as normal assignments in the traditional gradebook. What I’m trying to figure out is how to weigh the Learning Mastery gradebook into the traditional.

Right now, as far as I can tell, you cannot have the Mastery gradebook results pushed directly into the traditional gradebook. So, I would have to copy each individual Outcome I’m assessing as an individual assignment in the course. I can do it, but it’s far from ideal.

I’m sure there are some of you out there who also use Canvas and hopefully, the Learning Mastery portion of the gradebook. What advice do you have? Any tips?