Exit tickets with Datastudio

I love playing with different kinds of visualizations with data and especially when it’s done simple by using Google forms/sheets/datastudio.

If you don’t want to read all the boring parts you can just head over to the form or the datastudio report. You can answer the form and update the datastudio report, if you are logged into your Google account. The datastudio report is in edit mode so plz don’t haxx it. I’ve shared it in edit mode for you to be able to copy it and update the data.

So here is how I did it…

Google forms

First create your form. If you want to follow my guide and not change anything, do it exactly as I have done it.

Connect your form to a sheet to have somewhere to store your data.

Google sheet

This is the more trickier part of the setup. (Click here to look at my sheet)

1. Name your answer tab “Answers”.
2. Create a new tab in your connected sheet and name it “INDATA”.
3. In cell A1 paste “=Arrayformula(Answers!A1:F1)"
4. In cell A2 paste the following formula: “=Sort(arrayformula(Answers!A2:F),1,false)”
5. Create a new tab in you connected sheet and name in “Datastudio”
6. Go to Tools -> Script editor

Google apps script

Google apps scripts are quite easy to work with, especially if you have some experience with Javascripts (which I don’t).

Here is the function I wrote to rearrange the data in the sheet for Datastudio to be able to visualize it as a spread. (Page 2 in the datastudio report)

Remove the “myFunction{“ and all other text and paste my function instead, save…

function datastudio() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Datastudio’);
var inputdata1 = sheet.getRange(‘INDATA!C2’).getValue(); //Get q value
var inputdata2 = sheet.getRange(‘INDATA!D2’).getValue(); //Get q value
var lastRow = sheet2.getLastRow() +1; //Add an extra row
var lastRow2 = sheet2.getLastRow() +2; //Add two extra rows
if (inputdata1 === 1) { //Test inputdata1
sheet2.appendRow([“”,””,””,”1", “0”, “0”, “0”, “0”]);
} else if (inputdata1 === 2) {
sheet2.appendRow([“”,””,””,”0", “1”, “0”, “0”, “0”]);
} else if (inputdata1 === 3) {
sheet2.appendRow([“”,””,””,”0", “0”, “1”, “0”, “0”]);
} else if (inputdata1 === 4) {
sheet2.appendRow([“”,””,””,”0", “0”, “0”, “1”, “0”]);
} else if (inputdata1 === 5) {
sheet2.appendRow([“”,””,””,”0", “0”, “0”, “0”, “1”]);
}
sheet.getRange(“INDATA!A2”).copyTo(sheet2.getRange(lastRow,1), {contentsOnly:true}); //Get date and send it to other tab
sheet.getRange(“INDATA!B2”).copyTo(sheet2.getRange(lastRow,2), {contentsOnly:true}); //Get email and send it to other tab
sheet.getRange(“INDATA!C1”).copyTo(sheet2.getRange(lastRow,3), {contentsOnly:true}); //Get question and send it to other tab
if (inputdata2 === 1) {
sheet2.appendRow([“”,””,””,””,””,””,””,””,”1", “0”, “0”, “0”, “0”]);
} else if (inputdata2 === 2) {
sheet2.appendRow([“”,””,””,””,””,””,””,””,”0", “1”, “0”, “0”, “0”]);
} else if (inputdata2 === 3) {
sheet2.appendRow([“”,””,””,””,””,””,””,””,”0", “0”, “1”, “0”, “0”]);
} else if (inputdata2 === 4) {
sheet2.appendRow([“”,””,””,””,””,””,””,””,”0", “0”, “0”, “1”, “0”]);
} else if (inputdata2 === 5) {
sheet2.appendRow([“”,””,””,””,””,””,””,””,”0", “0”, “0”, “0”, “1”]);
}
sheet.getRange(“INDATA!A2”).copyTo(sheet2.getRange(lastRow2,1), {contentsOnly:true});
sheet.getRange(“INDATA!B2”).copyTo(sheet2.getRange(lastRow2,2), {contentsOnly:true});
sheet.getRange(“INDATA!D1”).copyTo(sheet2.getRange(lastRow2,3), {contentsOnly:true});
}

If you want to add more scale questions you have to append the script. Add another inputdata variable, another IF-statement, another lastrow variable and use it to copy the timestamp/email/question. I would look something like this. Of course you have to change the a1Notation (read more here) to match your sheet.

var inputdata3 = sheet.getRange(‘INDATA!E2’).getValue(); //Get Q value
var lastRow3 = sheet2.getLastRow() +3; //Add an extra row

if (inputdata3 === 1) {
sheet2.appendRow([“”,””,””,””,””,“”,””,””,””,””,””,””,””,”1", “0”, “0”, “0”, “0”]);
} else if (inputdata3 === 2) {
sheet2.appendRow([“”,””,””,””,””,“”,””,””,””,””,””,””,””,”0", “1”, “0”, “0”, “0”]);
} else if (inputdata3 === 3) {
sheet2.appendRow([“”,””,””,””,””,“”,””,””,””,””,””,””,””,”0", “0”, “1”, “0”, “0”]);
} else if (inputdata3 === 4) {
sheet2.appendRow([“”,””,””,””,””,“”,””,””,””,””,””,””,””,”0", “0”, “0”, “1”, “0”]);
} else if (inputdata3 === 5) {
sheet2.appendRow([“”,””,””,””,””,“”,””,””,””,””,””,””,””,”0", “0”, “0”, “0”, “1”]);
}
sheet.getRange(“INDATA!A2”).copyTo(sheet2.getRange(lastRow2,1), {contentsOnly:true});
sheet.getRange(“INDATA!B2”).copyTo(sheet2.getRange(lastRow2,2), {contentsOnly:true});
sheet.getRange(“INDATA!E1”).copyTo(sheet2.getRange(lastRow2,3), {contentsOnly:true});
}

Before we leave the scripting part, we have to set a trigger to use with the project.

1. Go to “Edit” -> “Current projects triggers”
2. Add new trigger

Visualize it in Datastudio

Copy symbol in Datastudio

Copy my Exit ticket — Datastudio from here: Exit ticket — Datastudio by using the Copy symbol. (You have to be logged in to a Google account)

When copying to a new report choose your own sheets/tabs which you have connected to your Google form.

You may also have to choose what kind of data type the different columns should represent. In my example you have one date value several numerical values and some text values.

Here is the Datastudio tab from the sheet (Nummer = number, Summa = Sum)

Here is the INDATA tab from the sheet. (Genomsn.. = Average)

PLEASE don’t edit my datastudio… To update data or being able to copy it I had to share it writable.

Update data symbol in Datastudio

Remeber: Every time you have new data from your sheet you have to press update data symbol in Datastudio to update your data.