Google Sheets, Forms, WordPress,  HTML5, a touch of Javascript

Note: This is part 2 of a larger project. Each part of the project is self-contained if you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. You can access the beginning of the project here: 

Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project

Where We Left Off

In our previous post, I showed you how to create a Google Form and embed it into a WordPress post. The end result looked like this.

Feel free and complete the survey for fun and the unadulterated joy of surveys!

In this tutorial, we will add a live Google pie chart of our results that updates every 30 seconds so that our viewer, …erh…you, can see the results as they come in.

Here is what our Chart will look like.

To get to the stage of adding this chart to our WordPress post, we need to connect our Google Form to a Google Sheet to store our results, then we will create a chart from the results and embed it into our WordPress post. Ooh! Meta.

Let’s dive in.

Connecting our Google Form to a Google Sheet

One of the beauties of Google Forms is that you can connect your form directly to a Google Sheet. This allows you so much freedom to review and manipulate the data and even create a live analysis as the results of your survey come in.

Connecting to the Form to the Sheet is ridonculously easy.

In your Forms editing dashboard, click the ‘Responses’ tab. In the top right-hand corner you will see a spreadsheet symbol in dark green. Give it a click!

A popup will appear with an option to create a new Google sheet for this Form or add it to a new Sheet. We’ll create a new sheet and let Google assign the naming convention. Click ‘Create’.

Clicking ‘Create’ will open another tab on your browser with your attached Google Sheet. Your new sheet contains a timestamp and your question.

If you had more questions, then they would fill along the column headers in row 1.

Warning!!! It it not advisable to directly edit or add formulas etc to the ‘Form Response’ sheet tab of the spreadsheet. It is recommended that you do all your data manipulation in a separate sheet tab. 

Dummy Data

Before we go on to add our chart let’s go ahead and add some dummy data.

To do this, go back to your form. If you have been playing along since the first tutorial you would have limited the responses to one per user, we’ll temporarily lift this restriction while we add the dummy data.

To do this go to the settings cog in the top right, and un-check ‘Limit 1 response’ and click ‘Save’. Then go into the preview (The eye; top right) and have a cracking good time completing your survey. Just make sure you go back to settings and limit your response again when you are done.

When I am displaying results of a survey  I usually add an equal share of dummy items for each possible response so:

1. I can create a chart to embed.
2. It gives the impression I have… ah… friends. No one likes to see a chart with a sample size of one.

Just make sure you note the number of dummy responses, so you don’t use them for any legit analysis later.

Heading back to our Google Sheet we see the four results we entered straight away.

Creating a Pie Chart From Form Data

Great! We have some data. Let’s create a pie chart. To create the pie chart we want the sum total count of all the users who selected each option.

Now if we just go ahead and select the range B2:B5 then, sure, we would have a pie chart of all the choices to date, but the chart would not expand to take in all the options from all our users who will be responding to our chart later. We need to select the B column instead.

Before we continue. We will create a new sheet tab so we can add our Pie Chart on its own and not interfere with the ‘Form Response 1’ sheet. How about we call our new sheet, ‘PieChart’ (Creative. I know).

Let’s make sure our cursor is in cell A1 (The importance of this will reveal itself later).

Next, we will select the ‘Insert Chart’ icon. A sidebar will appear with the ‘Chart Editor’. Go down to the ‘Data Range’ and click on it. A small popup window will appear. Now, go back to the ‘Form Response 1’ sheet and select column B. Remove the range in the popup and then select column B.

Head back to your PieChart sheet and then go down the bottom and click the ‘Aggregate’ radio button. You will probably get a Bar Chart now. Let’s change that by going back up to the top and clicking on ‘Chart Type’ and scrolling down until we get to our beloved Pie Chart. Click that little darling and you have yourself your pie chart.

Embedding Your Pie Chart into WordPress

We are going to go through two options here. One is an embedded chart that updates every 5 minutes (You also need to refresh the page) and the other is one that updates every 30 seconds. Both are very different in how they need to be displayed.

Embedding a Google Chart that Updates Every 5 minutes (So long as you refresh the page)

You may be wondering why  I am even showing this option. Essentially because it is the most intuitive and the sum total of coding knowledge it requires is how to copy and paste code. Basically, it’s super easy and it might just be all you need.

First, go back to your Google Sheet pie chart. Click on the chart and 3 vertical ellipses will appear in the top right.

Click those bad-boys, and click “Publish Chart.”

A popup screen will appear with a ‘Link’ or ‘Embed’ option. Click ‘Embed’.

Read the information about content visibility. If you haven’t made the sheet public.

Then click publish.

Yet another glorious popup will appear asking for confirmation. May as well, hey.

Google will then generate an iframe HTML for you to copy and paste into WordPress.

Copy that code and head over to your WordPress post.

Classic wordpress mode

Click the tab in the top right of your post editor from Visual to Text and find the location to paste in your chart. Paste it and save. Go and preview. You have yourself an embedded chart that will update after 5 minutes so long as you refresh your page.

Gutenberg wordpress mode

In WordPress, Gutenberg mode add a new block with the plus button “+”. In the popup box, scroll down to the “Formatting” tab and click “Custom HTML”. Paste your Google Chart iframe in and select Preview to make sure everything is working.

The downside of this approach is that although the Chart will update every 5 minutes, it will also require a refresh of the page.

How about we move onto something more dynamic.

Embedding a Google Chart that Updates Every 30 seconds live on your page.

This is so much sexier, but it is a much less intuitive approach in the setup.

Remember way back when I asked you to make sure your chart referenced in cell A1 of the PieChart sheet? That’s because we are now going to reference that particular cell. This is a neat little workaround by Steyn Viljoen.

If we reference the cell and embed the data in it, we will be able to access just the Chart data to embed. When we do it this way, the chart will update every time new data is added, so long as you refresh the chart (We’ll get to this later).

Get the sheet reference from the URL Bar

You’ll need to make sure you have sharing on to public at this point.

First we are going select our ‘PieChart’ tab and then go up to the URL/Search bar and copy it.

Here is what the URL looks like:

https://docs.google.com/spreadsheets/d/t40aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESG7/edit#gid=905851777

First, we will remove the “edit#” part of the URL command.

https://docs.google.com/spreadsheets/d/t40aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESG7/edit#gid=905851777

We will replace the “edit#” with instructions to embed the document: “htmlembed/sheet?”

https://docs.google.com/spreadsheets/d/t40aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESG7/htmlembed/sheet?gid=905851777

The “gid” is a reference to the actual sheet inside the spreadsheet. We don’t want the whole sheet so we will use “&range=A1” to reference the cell that our chart information is connected to.

https://docs.google.com/spreadsheets/d/t40aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESG7/htmlembed/sheet?gid=905851777&range=A1

Lets test it by copying our new URL and pasting it into our browser search bar.

Now every time the page is refreshed it will update automatically and if someone has provided more feedback in between, it will reflect the update.

We will wrap this in an iframe so we can embed it into our WordPress page.

<iframe id="chart" width="600" height="371" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/t40aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESG7/htmlembed/sheet?gid=905851777&range=A1"></iframe>

You may have to play with the width and height of either your iframe or your chart image back in Google Sheets to get it just right for you.

making your chart update every 30 seconds without having to refresh the screen

Okay, we are getting there, but we don’t want to annoy the user by asking them to update the screen every time they want to see if anyone has added new feedback to the form. We just want the chart to update every now and again.

Before we post our newly created iframe into WordPress we are going to add a little Javascript to to make the magic happen. Don’t worry, we’ll walk you through it.

On line 2 we have added an id for our iframe and called it “chart”. We will reference this id in our <script>.

On lines 3-8 we add our Javascript code thanks to a snippet I found on Stackoverflow from SymbianSyMoh.

Line 4 references the window we are working in – in your case, your WordPress post you will paste this code into. The setInterval() methods takes two parameters, a function that will execute and a time that the function will execute in. For us this is:

window.setInterval("reloadIFrame();", 30000);

The crazy 30000 is a reference in milliseconds where 1000 ms = 1 second. So 30000 ms would equal 30 seconds.

This means that we are asking Javascript to run the function   reloadIFrame()  every 30 second. So what does this function do?

reloadIFrame() first looks through our page and finds anything with an id (document.getElementById) of “chart” (Line 6) which is our iframe with our pie chart in it. It then tells that element (our iframe) to update it’s link (src=) to essentially refresh it after 30 seconds.

Cool bananas!

Now that you have the iframe and the Javascript and have changed the url to your own Google Chart we can go ahead and add it to our WordPress post.

Embedding your code into classic wordpress posts

Select everything including and between the <div></div> and copy it. Go to your WordPress post and click the “Text” tab. Find where you want to add the code and paste it in. Save and preview. You will notice that the chart will now reload every 30 seconds.

Noice!!!

embedding your code into  Gutenberg wordpress posts

In Gutenberg, add a block and select Custom HTML from the “Formatting” window and paste in your code. Preview away!

The end result

So here is is in all it’s glory! We have embedded a Google Form along with a Result Google Chart that uploads live every 30 seconds.

 

Pretty cool, huh?

In our next edition of Google Forms in WordPress with Live Chart Project we will add a country selection item in our form with Google Apps Scripts.

So do you think you will try this out? Let me know how you used this tutorial. I really enjoy hearing how you apply what you learn here!

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

~Yagi.