Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp
In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend.
Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.
Take a look at the example below (click to expand the image):
If all the seats have been booked for a seminar, then that seminar will not appear on the form. If all seats have been booked for all seminars, then the Google Form will close.
We will even create a live list of attendees that we can embed on our website using Google Sheets.
Google Forms can’t do this for you out of the box. We will need to use Google Sheets to store our bookings and do some manipulation of the data. Then, we will use Google Apps Script to update the Google Form with the number of seats each time the form is submitted.
The Google Apps Script script has been prepared in a way that someone with limited coding skills can use it as a template by plugging in a few global variables. Alternatively, a more advanced user can incorporate the code into a larger project.
I’ll assume you have the basic abilities to create a simple Google Form and have used Google Sheets.
This tutorial can be followed as a useful step-by-step guide or if you are more confident, you can simply jump to the code using the contents below.
Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this:
You probably don’t want to mess with this tab because the Form is still live.
Instead, you decide to create a new Google Sheet tab that you want to automatically transfer all the data into, including the current form response data and any new form responses you might get.
The Common Mistake
A common, though mistaken, approach to this is to do a cell-by-cell transfer of data. For example, we would grab the first data cell of our “Form responses 1” Sheet tab and in a new sheet tab cell we would write:
'Form responses 1'!A2
We would then drag that cell across to the right to cover all the columns. Then all the way down to the bottom of the page to cover the current responses and any new responses added.
That might look a little like this:
This looks like it might work, right? Let’s test it out by adding in a new form response. For me, it will be the 6th response and will appear on row 7 of the ‘Form Responses 1’ sheet tab.
Google Forms, Google Sheets (IMPORTXML), XML Path, WordPress
After creating and embedding a Google Form into our WordPress post, in our last tutorial, we added a graph of all the results from the survey that updates every 30 seconds.
In this tutorial, we are going to add a country selection to our form and then embed a country heat map into our post.
Creating a Country Drop-Down List in Google Forms
The Countries List
Getting The Country Data
First off, we need to find a list of countries. I’m going to grab that from https://www.listofcountriesoftheworld.com. I could probably just copy the list and paste them in but I might want to use the list of countries again as a reference for other calculations in my sheet so I am going to go to my Google Sheet that is connected to my form and create a new Sheet tab named Countries.
Welcome to Part 1 in our Google Forms in WordPress with Live Chart Project. By the end of our project our goal is to embed a Google Form survey into a WordPress post.
In this post, we will look at how to create a simple Google Form to run a survey asking my viewers what paid version of Google Suite they use or if they simply use the free Google Apps services like Google Sheets, Gmail, Google Docs, Google Drive, etc.
The end result of this stage will look like this:
Feel free and complete the survey for fun and happiness.