Google Form to Video Display

JSON display from spreadsheet

This is another SPLOT-ish tool that takes videos uploaded through a Google Form and then displays them.1 This particular proof of concept was built in about ten minutes as a result of a conversation with our World Language faculty who are going to be doing student-to-student video work with people from other countries. We’re likely to use Zoom as the platform. It makes recording the video easy and the compression of the video is pretty impressive. Initially I was concerned about file size but between that and seeing that Google allows up to 10GB file uploads via their vanilla forms. The caveat there is that the “file upload option is only available for G Suite customers with a Google Form shared within their organization.” Good enough for our purposes but a bit limited if you wanted to use it more broadly.

Form Stuff

I opted to auto-log the VCU email address since the file upload required that anyway and only used two other elements- a file upload piece and a single categorical element. You could get much more complex if you had the desire. Just make sure you’ve created a results spreadsheet and published it to the web.

The Pieces

My form is here.
The spreadsheet is here.NOTE: Google now creates two IDs when you publish this. There’s an ID element in this published sheet as a web page in addition to the ID for the SS itself.
The JSON is here. NOTE: The JSON URL uses the ID from the SS NOT the published page.

These pieces come together in the CodePen example here letting us display the videos and hide/show them by categories.

I’m going to break this down fairly thoroughly in that this might have value for people new to this and it’ll help me expose elements I don’t understand.

Get the Data

Once I’ve published the spreadsheet I always verify my URL to the JSON works. The only piece you’d need to change from below is the spreadsheetID. You can just cut/paste the URL from the console into the browser address bar to verify it works. You should see some JSON when you go there. I also recommend using the Chrome extension JSON formatter to clean up what you see. It’ll help you see the structure of the data.

var spreadsheetID = "1Bx5a84VLnwEa0i7Cj-8x0qhwMLmoPRmGr_4u1hRrU-I"; // Make sure it is published to the web AND get the ID from the SS rather than the published URL
var dataURL = "https://spreadsheets.google.com/feeds/list/" +  spreadsheetID +  "/1/public/values?alt=json";
console.log(dataURL);

Once you’ve verified there is data to fetch, it’s time to get it. This little piece just makes sure there is data to get and that the data is JSON.

//get the data
fetch(dataURL).then(function(response) {
  var contentType = response.headers.get("content-type");//what kind of data is it?
  if (contentType && contentType.indexOf("application/json") !== -1) {
//if it's JSON return it and then . . . 
    return response.json().then(function(json) {
console.log(json);//spitting it out into console can let you see what it looks like in whole before you break it down -- same as at the URL we used above 

For Each Chunk of Data

Now that we’ve got a chunk of data, we need to find the elements we want and break down the pieces for display.

JSON display from spreadsheet

If you take a look at the JSON above, you’ll see that Google makes each row an entry. I collapsed a bunch of other information so you could see one full entry and the start of the second entry. They’re both children of the ‘feed’ element and they have their own sub-elements. To make this work we need to cycle through each entry.

You may notice that the entry element is followed by a square bracket which indicates the data inside it is an array – “entry”: [

That means we can count how many entries are there using array.length. In my case, I’ve set it so that the array is named data and address looping through it like so for (i = 0; i < data.length; i++) {stuff that is done}2 For each entry we’re going to want sub-elements like the video URL, the category, the email. Each element has a name gsx$uploadyourvideofile and the actual value is held in a subelement of that element called $t. So you get the actual information you want with the full path to that like “data[i].gsx$uploadyourvideofile.$t – you can see a couple different elements getting assigned to variables with that method in the portion below.

When you build a spreadsheet like this the questions from the form become the column titles and end up in the JSON as long strings without punctuation or spaces. So “What theme are you addressing?” in the form becomes the header for column C and then shows up in the JSON as gsx$whatthemeareyouaddressing which is ugly but works fine and will be left as-is to keep things simple.

 if (json.feed.entry) { //making sure we have at least one entry
        var data = json.feed.entry; //skipping down to the entry elements which gives us an array of entries that we'll navigate
        for (i = 0; i < data.length; i++) {
          var file = data[i].gsx$uploadyourvideofile.$t; //get the video file URL
          var email = data[i].gsx$emailaddress.$t; //gets the email
          var category = data[i].gsx$whatthemeareyouaddressing.$t; //gets the category

From Variables to HTML

Now that we’ve got the raw data assigned to variables we have to clean it up a bit and then write back out as HTML.

For instance, Google’s video embed function uses a different structure than the URL given in the spreadsheet. We really only want the ID from that URL. I assign the value of that URL to the file variable with var file = data[i].gsx$uploadyourvideofile.$t; and then chop off the front 33 characters that I don’t need with var fileId = file.substring(33);

To create the iframe structure I just append strings in the video variable while working in my clean file ID. I’m also sticking the category into a div and doing a few other things. It’s very much like a mail merge and it writes out all the HTML that’s ended up in the allFrames variable to the div with the id of videos.

for (i = 0; i < data.length; i++) {
          var file = data[i].gsx$uploadyourvideofile.$t; //get the video file URL
          var fileId = file.substring(33); //get just the ID from that URL
          var email = data[i].gsx$emailaddress.$t; //gets the email
          var category = data[i].gsx$whatthemeareyouaddressing.$t; //gets the category isn't that an ugly descriptor?
          category = category.replace(/\s/g, "-"); //get rid of any spaces in the category name
          category = category.toLowerCase(); //make the category lowercase because it's prettier and consistency is always a good thing
          var video =
            '<div class="' +
            category +
            ' video"><iframe src="https://drive.google.com/file/d/' +
            fileId +
            '/preview" width="640" height="480"></iframe><div class="info">' +
            category +
            " by " +
            email +
            "</div></div>";
          allFrames = allFrames+video; //build the iframe embed pattern that Google users and push it to the allFrame array to spit out below
        }
        document.getElementById("videos").innerHTML = allFrames; //write all the iframes to the videos div

That’s pretty much all there is to the display portion although it might be helpful to see it all together as it is in the codepen example.

See the Pen world language demo display from form by Tom (@twwoodward) on CodePen.


1 This one actually works consistently, unlike my recent attempt at silent form submissions– which I’ll return to at some point or another.

2 It might have made more sense to name it entry rather than data.