Google Form to Google Sheets to Vue Website

Posted on by Tom Woodward

This post from Keegan kicked of this demo.1

The goal is to create a pretty flexible template for using Google Forms to Google Sheets to simple website. I opted to do this one in Vue but may get around to doing it in plain JS or jquery if time allows.

The first thing you need to do is make the form and put in some entries. You’ll be happier if your form prompts aren’t super long. Make them readable just keep them concise.2 Once you’ve made a few entries so you have some data to work with, publish your spreadsheet as HTML. File>Publish to the Web. Click OK and what not. Don’t bother going to that link as Google has made that a fake promise. You just need the ID of the document in your current view. It’ll be something painful like 12WsyLvtfIPJkCXgEXsRkrI6dS0_K6brJikZqzkQa8TU and is visible in the URL after the /d/ but before the /edit. You can see the spreadsheet here. It doesn’t really matter but !full transparency!

With that ID in hand, you want to go to a URL like so. You could manipulate the number in the URL to hit different pages in the spreadsheet.

https://spreadsheets.google.com/feeds/list/12WsyLvtfIPJkCXgEXsRkrI6dS0_K6brJikZqzkQa8TU/1/public/values?alt=json

You should see JSON data there. If you don’t hate yourself, you’ll install Chrome’s JSON formatter extension so it breaks down in kind ways for humans.

The data is a bit messy. There’s a big chunk at the beginning that we’ll essentially ignore.

Then we move down to entry. Each entry is the equivalent to one row in the spreadsheet. You address various elements by navigating the structure of the JSON file. You can see some of that annotated on the image below.

Now that we know we have live data and we’ve seen a bit about the structure of that data, let’s look at how Vue grabs it and how we can integrated it into the website.

The entire script is below. The el portion is where the content is going to be applied. In this case a div with the id of ‘blog.’ The data received from our Google sheets JSON url is going to be stored in the entries element in data but we’re setting that to null initially.

For Google sheets, we’re going to get the data but then move through all that initial stuff to the entry array.
self.entries = self.entries.feed.entry
Now we’re got just the entry data stored and ready for use. It will have a data structure that parallels the columns your spreadsheet has. This whole piece can be used on any Google Sheet without changing things (assuming you’re putting your content in a div with the id of blog – if not just change that).

var blog = new Vue({
  el: '#blog',
  
  data: {
		entries: null
	},
  
  watch: {
		currentPage: 'fetchData'
	},

  created: function () {
    this.fetchData()
  },
  
  methods: {
    fetchData: function () {
      var xhr = new XMLHttpRequest()
      var self = this
      xhr.open('GET', sheetUrl )
      xhr.onload = function () {        
        self.entries = JSON.parse(xhr.responseText)
        self.entries = self.entries.feed.entry        
        console.log(self.entry)
      }
      xhr.send()
    },
//functions

  },  
  
})

Now we just need to take the data and loop it through for display. Vue makes this easy in the template structure. The loop is made by having a div with template elements in it. v-for=”entry in entries” indicates the chunk of html and variables that will be repeated for all of the elements in entries (our data element from earlier which is filled by all the data in the entry array in our Google Sheets JSON). This automatically loops through as many times as needed.

You have a variety of options to pull your data from the entry (which is basically each row of the spreadsheet). This is Vue’s template syntax. This is where you’ll customize things depending on what your data is named.

If you want to put it plainly into your HTML as we’re doing in the H2 element, you wrap it with two curly brackets and reference the column data you want. In this case, {{entry.gsx$title.$t}} – with entry being generic for each row -> gsx$title gets us to the correct column and -> $t is the entry data for that row/column combination.

Another way to display content so that raw HTML gets parsed correctly is this pattern.

<div class="content" v-html="entry.gsx$moretext.$t"></div>

You can see we’re now referencing the gsx$moretext column and using v-html to input the contents ($t) as HTML.

You can also see the provisioning of the img element with the URL from our form. It’s a bit weird feeling but overall very similar in pattern. 

<img :src="entry.gsx$imageurl.$t" class="img-fluid" />

The whole template structure ends up looking like this. 

 <div v-for="entry in entries" class="col-md-4 entry">
            <h2>{{entry.gsx$title.$t}}</h2>
            <div class="content" v-html="entry.gsx$moretext.$t"></div>
              <img :src="entry.gsx$imageurl.$t" class="img-fluid" />
      </div>

The whole thing is available on Codepen.

See the Pen Google Sheets/Form Website – Vue Version by Tom (@twwoodward) on CodePen.

I’ve got some more complicate examples focused on the WordPress API that are on Codepen as well.

1 It also made me feel old. I can’t believe I’ve been doing this for so long.

2 There are ways to deal with that but they add steps so I’m ignoring them in this post.