Sending Charts from Google Forms

We’re getting ready to launch a digital teaching and learning course for staff that is based on the TPACK framework. A few years ago, we started our 1:1 training by coaching teachers through using SAMR to design lessons which really use the technology in new ways. This time, we’re having teachers do more metacognitive reflection on their own practice with TPACK as a model for thinking through content, pedagogy, and technology’s impact on their practice.

Matt Kholer has collected several surveys which can be used to gauge each component of TPACK. We modified a 2009 instrument written by Kohler (and several others) into a Google Form for teachers to take before and after the program. For our course, we’re only focusing on technology and pedagogy and coaching people through applying their content knowledge.

Our goal was to have teachers compelte the form and then receive an email with their survey results. Once the form was submitted, the sheet needed to average each domain, create a chart, generate an email, and fire it off with the chart included in the body of the email.

I started with Martin Hawksey’s posts on email dashboards from Google Sheets using Apps Script. The main issue I ran into is that the radar chart we were hoping to use had to be rendered into the spreadsheet to get all the labels and formatting correct. Even calling the image directly from its URL was problematic. Specifically, if multiple people submitted at once, the charts could get combined in unexpeected ways, sending people wrong information. I looked into using Apps Script’s LockServiceto limit execution, but honestly, I couldn’t spend a whole lot of time on getting this running.

Martin suggested using to generate charts through an API endpoint. It’s based on Chart.js, which I’ve used in the past for an action research summary.

This ends up working better because I don’t need to read the sheet twice to generate a chart. I can take the processed data object right from the form responses, hit the API with some config in a JSON string and get an image which is appended into the email.

The Code

The result

As form submissions come in, the sheet generates the average for each category and then uses those values to send a request to the quickchart API. The image blob is captured in an object and sent inline to the user who submitted. It runs in 7-8 seconds consistently, with the lag coming from sending the email.

Our email template includes an explainer on what each domain is meant to describe so participants can think through their survey before coming to the live session. We’ll use the same form at the end so they can compare their results as part of the final reflection.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.