Creating a Google Sheet Geo Map From Form Data and Posting it to WordPress – Part 3 of Google Forms in WordPress with Live Chart Project (Updated Feb, 2022)

Google sheets Geo Chart Completed

Google Forms, Google Sheets (IMPORTXML), XML Path, WordPress

Note: This is part 3 of a larger project. Each part of the project is self-contained should 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

Embedding a Live Google Sheet Graph that Updates Every 30 Seconds into a WordPress Post – Part 2 of Google Forms in WordPress with Live Chart Project

Where We Left Off

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.

Geo Heatmap Google

Creating a Country Drop-Down List in Google Forms

Hire me for our next Google Workspace project.

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.

In our A1 Cell of Countries, I am going to use the formula IMPORTXML to get the country names from the List of Countries of The World website.

IMPORTXML takes two arguments:

  1. The URL that you will be extracting data from.
  2. The extraction parameters using a XML Path query.

What the Bloody Bloody is a XML Path query?!

Okay, hold up. Let’s briefly look at XML Path first. XML has a very similar syntax to HTML. It uses the same style for its elements like this:

  • HTML: <div></div>
  • XML: <bananas></bananas>

The difference being that HTML elements have a specific purpose or function while XLM Elements are more like labels (I know someones gonna complain about this oversimplification). XML can take all of the same attributes (e.g. id, class, style) as HTML.

The XML Path, XPath, is the query language used to search for items nested in XML  and even HTML documents. A handy cheat-sheet for XPath queries can be found at DEVHINT.IO.

Back to our Google Sheet IMPORTXML …

Load the List of Countries of The World site in your Chrome browser and hit CTRL + SHIFT + C . This will open the Developer Dashboard and allow you to hover over elements in the page to inspect them. Hover over the country names to take a look at them.

Chrome Dev Dashboard Element Inspection

So, it looks like each country name is surrounded by a <div> with an id of "ctry". Cool. We can use that in our XPath query to extract all the countries on this page. Our query will look a little like this:

"//div[@id='ctry']"

Let’s break this down:

  1. // : looks for any descendant element.
  2. div : that is a <div> element,
  3. [] : with some specific attribute in the div.
  4. @id='ctry' : with an id of ‘ctry’.

Alright, we have everything. Time to put it into our formula:

=IMPORTXML("http://www.listofcountriesoftheworld.com", "//div[@id='ctry']")

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

IMPORTXML Google Sheets

Adding the Country Data to Your Google Form

Now, go back to your Google Form and create a new question item. The button will be on the menu beside the first question you created.

To the right of your form editor, there will be a thin selection menu with a “+”. Click that to add a new item. Add the title. Then go back to your sheet and copy the Countries list and paste it into the Form options. The list items will paste in each option automatically. Finally, set the question to required.

Copy Paste Country

 

Google Form Required

Now take a preview to see what it looks like:

Form Preview with Country

Now go back to your attached Google sheet and take a look at the Form Responses 1. You will see that a new column has been added with our new question, “What country do you live in?”, and Australia selected.

Google Sheet Form Response Updated

Creating a Geo Chart in Google Sheets

Before we create our chart, populate it with a few examples using the Forms preview so you can see what your Geo chart will look like.

It’s okay. I’ll wait…

Okay Cool. So in your Form Responses 1 Sheet tab you should have something like this:

To create the Geo Chart select column C and click on the Chart icon. Change the chart type to Geo chart.

This looks pretty good out of the box. But between the max range and the min range the country will be shaded white…just like the background. That’s not going to work. Go into the Customize tab and select the Geo drop-down. There will be an option to change the mid range colour. I reckon orange is a nice mid colour so I’ll go with that.

This is looking betting, but we could really highlight the map with a bit of blue in the oceans. In the Chart Style drop-down change the background to blue (or whatever you want. I won’t judge).

That looks pretty tight.

Creating a Geo Chart in Google Sheets

Next, create a new Sheet tab named CountryChart then copy the Geo Chart and paste it into cell A1. We will reference this cell later to select our chart when putting it into WordPress. You will have to go back and delete the original chart in Form Responses 1 because it won’t delete even with a cut and paste.

Google sheets Geo Chart Completed

Embedding Your Google Sheets Geo Chart into Your WordPress Post

In our Part 2 of Google Forms in WordPress with Live Chart Project we learnt how to embed a chart that updates every 30 seconds without having to refresh the page.

We are going to add our Geo Chart to this so it does the same thing. I suggest you head back to that tutorial if you want more information. Right now though just click on the CountryChart sheet and select the Spreadsheet ID and the Sheet ID and paste it into the following code:

Google Sheets URL data

The Template

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

The Example

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

Now, if you have been following along from the previous parts of this tutorial, go into your WordPress post and locate your Form and result Chart and select Text and scroll to the HTML.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Select the HTML fragment above and add it into your code in your WordPress post just above the <script> element you used to run the 30-second iframe refresh. See line 3 below.

Great! You should now have your Geo Chart added to your WordPress post.

Take a squiz at the end result:


 

 

That’s it. Done. Looks pretty nifty, huh?

So, what do you think? Reckon you are going to use this in your own project? Go any cool ideas for application? Let me know in the comments below.

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.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Leave a Reply