Audience ranking questions

I’m helping to run a workshop this week and we realized that instead of using post-it notes through the day to capture “burning questions” we could use a tool that both collects questions and allows the audience to vote on them.

After the first day of post-it notes I volunteered to get us set up with one of the many systems that exist that do this. I went back to my hotel room and did the usual google searching, finding tools I had used (including the one that the Physics Education Research Conference organizers used earlier this month at their opening session) and others I hadn’t heard of. Unfortunately it seemed that most had a free level that only worked with 7 participants but it got expensive after that. Also most of the tools offered all kinds of extra bells and whistles, like tying in content or finding other ways to foster community, that we didn’t need.

I knew I could use the Q&A feature built into Google Slides (don’t know about that? Check it out!) but I knew there were two major issues: 1) it’s not really built to be open all day. In my experimentation in the past I noticed that the URL changes when you go back to the Q&A window a few hours later. Really that’s because it’s meant to be used in a typical 1-2 hour lecture, rather than a place to keep questions for a longer time. 2) while anyone can submit questions, you have to be logged into your Google account in order to be able to vote on questions. I’m not sure that’s a huge deal, but it always gives me pause when I’m dealing with an audience from diverse places. Certainly since my school has all our email powered by Google, it would be my go-to choice. Really it’s the first issue above that caused me to go down a different road for this workshop.

So as I was taking a shower after that disappointing google searching, it occurred to me that I might have the skills necessary to write my own solution. This post is about that.

Let’s first see it in action (there’s no sound, just trying to show all the features):

What are you seeing?

  • A big box at the top to add your question.
    • It has a placeholder encouraging you to type in your question. I find this much cleaner than a label above the box.
    • It clears the box out after the submission has been accepted (typically ~2 seconds)
  • A ranked list of questions that gets updated in real time
    • If the question is from you, you can’t “upvote” it
    • You can only vote once on a question
    • You can’t downvote or unvote a question
  • A quick description at the bottom indicating the limitations

If you care, the rest of this post explains how you could build this yourself. Why not just use mine? Because of the limitations listed at the bottom of the vid: it can only handle 100 simultaneous users. That’s totally fine for this workshop, but I can’t just provide this tool to everyone. Instead, all you need is:

  1. A google account (you need one, your users don’t)
  2. A pusher.com free-level account (this enables the webSocket technology and has the 100 simultaneous user limitation)
  3. A burning desire to help your fellow human beings

Set up Pusher

  • Sign up for a pusher.com account, specifying the “sandbox” plan.
  • Under “Channels” create an app
You should probably choose a name that’s easier to remember than the default one they’ll prefill that field with
  • Hit “Create app”
  • Navigate to the new app and click on “App Keys”
You’ll need all 4 of these in your Google Apps Script below.
  • You’re done with pusher!
  • Now on to setting up your google apps script

Set up the spreadsheet

You only two tabs:

  • “questions”
    • id
    • question
    • date
  • “votes”
    • id
    • date

Then open the App script window:

You don’t have to call your google sheet “pusher q&a sheet”, of course

In the app script window you should have a code.gs file. Add a main.html file and a pusher.gs file:

You can name your script whatever you want. Also note that after you hit the “+” button it asks what type of file you want so you don’t have to add the extension.

Here’s the code for Code.gs:

var funcs=[];
function doGet()
{
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("questions");
  var data=sheet.getDataRange().getValues();
  data.shift();
  var vsheet=ss.getSheetByName("votes");
  var vdata=vsheet.getDataRange().getValues();
  vdata.shift();
  var vobj={};
  data.forEach(d=>vobj[d[0]]=0);
  vdata.forEach(v=>
  {
    // if(!vobj[v[0]]) vobj[v[0]]=0;
    vobj[v[0]]++
  })
  var arr=data.map(m=>[m[0],m[1],vobj[m[0]]]);


  
  var t=HtmlService.createTemplateFromFile("main");
  t.funcs=funcs;
  t.funcnames=t.funcs.map(f=>f.name);
  t.globals={data:data, votes:vobj, arr:arr, myvotes:[]};
  return t.evaluate().setTitle("Sorted Questions");
}

const init=()=>{
  var cook=document.cookie;
  var str=cook.match(/votes=(.*)/);
  if (!str)
  {
    var list=[];
  } else {
    var list=str[1].split(",").map(m=>Number(m));
  }
  myvotes=[...myvotes,...list];
  displayQs();
}
funcs.push(init)

const displayQs=()=>
{
  arr.sort((a,b)=>b[2]-a[2]);
  // html=`<h1>Sorted Questions</h1>`;
  // html+=`<div><textarea rows="4" cols="50" id="newQuestion" placeholder="enter your question"></textarea></div><div><button onclick="newQlocal()">submit</button></div><hr/>`;
  var html="";
  html+=arr.map(a=>
  {
    var h=`<p>(${a[2]} votes) ${a[1]}`;
    if (!myvotes.includes(a[0]))
    {
      h+=` <button onclick="vote(this, ${a[0]})">upvote</button>`;
    } else {
      h+=` You upvoted`
    }
    h+="</p>";
    return h;
  }).join(" ");
  document.getElementById("main").innerHTML=html;
}
funcs.push(displayQs);

const newQlocal=()=>
{
  var question=document.getElementById("newQuestion").value;
  google.script.run.withSuccessHandler(newQBack).sendNewQuestion(question);
}
funcs.push(newQlocal);

const newQBack=(id)=>
{
  myvotes.push(id);
  var str=myvotes.join(",");
  document.cookie = "votes="+str+"; SameSite=none; secure";
  document.getElementById("newQuestion").value="";
  displayQs();
}
funcs.push(newQBack);

const vote=(el,id)=>
{
  // myvotes.push(id);
  google.script.run.withSuccessHandler(newVBack).sendVote(id);
}
funcs.push(vote);

const newVBack=(id)=>
{
  myvotes.push(id);
  var str=myvotes.join(",");
  document.cookie = "votes="+str+"; SameSite=none; secure";
  displayQs();
}
funcs.push(newVBack);


function sendNewQuestion(question) 
{
  var sheet=SpreadsheetApp.getActive().getSheetByName("questions");
  var data=sheet.getDataRange().getValues();
  data.shift();
  if (data.length==0)
  {
    var newId=1;
  } else {
    var newId=Math.max(...data.map(m=>m[0]))+1;
  }
  Logger.log(`new id is ${newId}`)
  var d=new Date();
  sheet.appendRow([newId,question,d]);
  sendToPusher("newQ", {row: [newId,question,0]});
  return newId
}

function sendVote(id)
{
  var sheet=SpreadsheetApp.getActive().getSheetByName("votes");
  var d=new Date();
  sheet.appendRow([id,d]);
  sendToPusher("newV", {id:id});
  return id;
}

Here’s the html for main.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">

    <script src="https://js.pusher.com/5.1/pusher.min.js"></script>

  </head>
  <body onload="init()">
    <div class="container">
      <div>
         <h1>Sorted Questions</h1>
  <div><textarea rows="4" cols="50" id="newQuestion" placeholder="enter your question"></textarea></div><div><button onclick="newQlocal()">submit</button></div><hr/>
      </div>
      <div id="main">
      </div>
      <div>
        <hr/>
        <p>If your vote isn't registered after ~10 seconds, it means
        the system lost your vote. Feel free to try again. The system
        has a limit of 30 simultaneous votes and 100 connected users.</p>
    </div>

<script>
var globals = <?!= JSON.stringify(globals) ?>;
Object.keys(globals).forEach(key=>window[key]=globals[key]);
var funcnames=<?!= JSON.stringify(funcnames) ?>;
var funcs=[<?!= funcs ?>];
funcnames.forEach((fn,i)=>window[fn]=funcs[i]);

var pusher = new Pusher(key, {
    cluster: 'us3',
    forceTLS: true
  });

var channel = pusher.subscribe('my-channel');
channel.bind('newQ', function(data) {
  arr.push(data.row)
  displayQs();
  });
channel.bind('newV', function(data)
{
  var row=arr.find(f=>f[0]==data.id);
  row[2]++;
  displayQs();
})

</script>

  </body>
</html>

And here’s the code for pusher.gs:


var app_id = "YOUR APP_ID HERE";
var key = "YOUR KEY HERE";
var secret = "YOUR SECRET HERE";
var cluster = "YOUR CLUSTER HERE";


function sendToPusher(event,data) {
  var pvals={
    appId: app_id,
    key: key,
    secret: secret,
    cluster: cluster,
    encrypted: true
  };
  
  var url = `https://api-${pvals["cluster"]}.pusher.com/apps/${pvals["appId"]}/events`;
  var body = {"name":event,"channels":["my-channel"],"data":JSON.stringify(data)};
  var bodystring = JSON.stringify(body);
  var now=new Date();
  var d = Math.round(now.getTime() / 1000);
  var auth_timestamp = d;
  var auth_version = '1.0';
  var bodymd5 = byteToString(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, bodystring));
  var wholething = `POST
/apps/${pvals["appId"]}/events
auth_key=${pvals["key"]}&auth_timestamp=${auth_timestamp}&auth_version=${auth_version}&body_md5=${bodymd5}`;
  var wholethingencrypt = byteToString(Utilities.computeHmacSha256Signature(wholething,pvals["secret"]));
  Logger.log(wholethingencrypt);
  

  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    // Convert the JavaScript object to a JSON string.
    'payload' : bodystring,
    'muteHttpExceptions' : true
  };
  var urltry = UrlFetchApp.fetch(url+`?auth_key=${pvals["key"]}&auth_timestamp=${auth_timestamp}&auth_version=${auth_version}&body_md5=${bodymd5}&auth_signature=${wholethingencrypt}`, options);

  
  }
  
function byteToString(byte) {
  var signature = byte.reduce(function(str,chr){
    chr = (chr < 0 ? chr + 256 : chr).toString(16);
    return str + (chr.length==1?'0':'') + chr;
  },'');
  return signature;
  }

Make sure you make the changes in the top 4 lines before moving forward.

Next you have to create a web app. Make sure to set it to execute as you but be available to everyone.

The first time you should choose “new deployment” and choose “web app”

Now you can go to the url provided and it should be working!

How it works

When someone goes to the url, google sends them all the questions along with the vote tally for each. It also checks the local users cookies to see if they’ve supplied any votes. If they have, it doesn’t let them vote again. This prevents ballot box stuffing, but you should know that it’s pretty easily defeated using incognito windows.

The user can enter questions or “upvote” existing questions. When they do, an AJAX call is made to the google server (any time you see google.script.run… that’s what’s happening) where google either saves the new question (making sure to give it a unique id) or saves the vote. In either case it saves the timestamp.

After updating the spreadsheet, the “sendtopusher” function runs, sending along either the new question (along with its id and an initial vote count of zero) or the id of the new vote. That uses the webSocket that pusher has set up to send that info to all connected devices.

If a device receives a new question from pusher it adds it to the list and re-displays all the questions. If a new vote comes in from pusher it adds to the vote count for that id and re-displays all the questions (this also involves sorting based on the vote count so that the highest voted question is always at the top).

Your thoughts?

I’d love to hear your thoughts. Here are some starters for you:

  • Why do you sometimes capitalize google and other times you don’t capitalize Google?
  • This is cool, can it also . . .
  • This is a rip-off of my cool idea. You can send checks to . . .
  • This is dumb. How does this preserve the time honored tradition of the first person asking not so much a question as a 10 point rebuff of everything they heard?
  • You really can’t draw very straight arrows. It’s almost as if you’re writing this post on a chromebook on your lap at LAX
  • I can tell you made that vid on Loom. Why didn’t you just embed that instead of downloading it from loom, posting it on youtube, and then embedding that?
  • I was at this workshop and I found this very useful.
  • I was at this workshop and this was incredibly distracting
  • Gross, I didn’t need to know that you had this idea in the shower.
  • Here’s another way to protect against ballot box stuffing . . .

About Andy Rundquist

Professor of physics at Hamline University in St. Paul, MN
This entry was posted in Google Apps Script, syllabus creation and tagged . Bookmark the permalink.

2 Responses to Audience ranking questions

  1. Bret Benesh says:

    Andy=Superhero

  2. Pingback: Classroom photo sharing app | SuperFly Physics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s