Sending Charts from Google Forms

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.