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 quickchart.io 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
function processFormValues(row) {
// The form structure doesn't change, so we can slice the values array directly.
let obj = {
"email": row[1],
"tk": row.slice(2, 8),
"pk": row.slice(8, 15),
"tpk": row.slice(15, 21),
"tpck": [row[row.length-1]],
}
return calculate(obj)
}
// calculate the average for the list of scores
function calculate(obj) {
let avgObj = {}
for(var key in obj) {
if(key === "email") {
avgObj["email"] = obj[key]
} else {
var length = obj[key].length;
// Form responses are strings, so convert each to a number while calculating the average.
var avg = (obj[key].reduce((prev, curr) => Number(prev) + Number(curr), 0)) / length;
avgObj[key] = avg.toFixed(2) // limit the float
}
}
return avgObj;
}
function getChart(chartData) {
// Use chart.js to avoid race conditions building charts in the sheet.
const baseUrl = `https://quickchart.io/chart?bkg=white&c=`
const params = {
"type": "horizontalBar",
"data": {
"labels": ["tk","pk","tpk","tpck"],
"datasets": [
{
"label": "Self Assessment",
"backgroundColor": "rgba(54, 162, 235, 0.5)",
"borderColor": "rgb(54, 162, 235)",
"data": [ chartData[1], chartData[2], chartData[3], chartData[4] ]
}
]
},
"options": {
"plugins": {
"datalabels": {
"display": true,
"anchor": "end",
"align": "end",
"color": "#000",
}
},
"elements": {
"rectangle": {
"borderWidth": 2
},
},
"responsive": true,
"scales": {
"xAxes": [{
"ticks": {
"display": true,
"beginAtZero": true,
"suggestedMax": 6,
"stepSize": 1,
},
"gridLines": {
"display": true,
}
}],
"yAxes": [{
"gridLines": {
"display": false,
}
}]
}
}
}
try {
let blob = UrlFetchApp.fetch(baseUrl + encodeURIComponent(JSON.stringify(params))).getAs('image/png');
return {'url': baseUrl + encodeURIComponent(JSON.stringify(params)), 'blob': blob};
} catch(e) {
return e.message
}
}
// Add an installable trigger to run on each form submit
function onFormSubmit(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const calcs = ss.getSheetByName('calculations');
const headers = calcs.getRange(1, 1, 1, calcs.getLastColumn()).getValues()[0];
try {
let scores = processFormValues(e.values);
// Save the averaged data to a sheet in case something goes wrong
var writeRow = []
for(var i=0; i<headers.length; i++) {
writeRow.push(scores[headers[i]]);
}
// Assign to a variable because we'll write to the sheet later.
let nextRange = calcs.getRange(calcs.getLastRow()+1, 1, 1, writeRow.length).setValues([ writeRow ])
// Get the chart
let chart = getChart(writeRow)
// if there was good data then send the email
if(chart) {
let emailImages = {};
emailImages['chart'] = chart['blob'];
let emailBody = `...your email template...`;
// append the image as a blob
emailBody += `<p align='center'><img src='cid:${emailImages["chart"]}'></p>`;
MailApp.sendEmail({
to: writeRow[0],
subject: 'Your survey results!',
htmlBody: emailBody,
inlineImages: emailImages
})
// Set the chart URL in a sheet in case we need it again
calcs.getRange(nextRange.getRow(), 6).setValue(chart['url']);
}
} catch(err) {
calcs.getRange(calcs.getLastRow()+1, 1).setValue(err)
throw new Error(err) // put it in the console
}
}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.
