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

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 practice 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.

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 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:

The URL that you will be extracting data from.
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 functiom 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.

So, it looks like the 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:

// : looks for any descendant element.
div : that is a <div> element,
[] : with some specific attribute in the div.
@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']")

Adding the Country Data to Your Google Form

Now, go back to your Google Form and create a new question item at the top.

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.

Now take a preview to see what it looks like:

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.

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 in to the Customize tab and select the Geo drop-down. There will be an option to change the mid range color. 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 what ever you want. I won’t judge).

That looks pretty tight.

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 wont delete even with a cut and paste.

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:

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.

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.

<iframe width="640" height="678" src="https://docs.google.com/forms/d/e/1FAIpQLSctPi4jdPiyUtN0s3N7LkwnpFdoFLWrdtb0xC6MpNrXtEVyzg/viewform?embedded=true" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>
<div align="center"><iframe width="580" height="360" id="chart" seamless="" frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/1M0aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESM8/htmlembed/sheet?gid=905851777&amp;range=A1"><span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span></iframe>
<iframe width="600" height="371" id="chart2" seamless="" frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/1M0aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESM8/htmlembed/sheet?gid=1276589844&amp;range=A1"></iframe>
<script>
window.setInterval("reloadIFrame();", 30000);
function reloadIFrame() {
 document.getElementById("chart").src="https://docs.google.com/spreadsheets/d/1M0aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESM8/htmlembed/sheet?gid=905851777&range=A1";
document.getElementById("chart2").src="https://docs.google.com/spreadsheets/d/1M0aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESM8/htmlembed/sheet?gid=1276589844&range=A1";
}
</script></div>

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

function reloadIFrame() {

document.getElementById("chart").src="https://docs.google.com/spreadsheets/d/1M0aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESM8/htmlembed/sheet?gid=905851777&range=A1";

document.getElementById("chart2").src="https://docs.google.com/spreadsheets/d/1M0aM1Piau7dZF6Sbny73pFbFc45UQvSVDlMBEDgESM8/htmlembed/sheet?gid=1276589844&range=A1";

}

</script></div>

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

Take a squiz at the end result:

Loading…

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 ocmments below.

~Yagi

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

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 practice 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.

Continue reading “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”

Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets

One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace.

Find and Replace is the penicillin of the the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff.

Yeah! Yeah! We all know about Find and Replace.

I know! I thought so too, but then I started to really use and identify how I could use it to quickly:

Change template sheets.
Fix bulk errors in formulas.
Change parts of cells.
Replace values in the whole spreadsheet, one sheet or a selected range.

Before we get started, you need to know the short cut for the Find and Replace tool. This will come in handy in all sorts of programs and apps.

PC – Ctrl + H
Mac – CMD (⌘) + H

I’m going to go ahead and continue the examples using PC, because, you know, Mac.

Continue reading “Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets”

5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets

It’s time to get evangelical peep! Release yourself from the yolk of the menu bar and it’s insidious demands on you coordination and time!

Break free from the right click menu! For is it not anything more than a proxy menu bar?! A veritable wolf in sheep’s clothing with no other design but to bring you back to the fold of the menu-using, mouth-drooling reprobates who stand in the way of efficient progress! Can I get an Amen! I said, can I get an Amen!

No? Fair enough…

Let’s just get on with it then, shall we? After all, this is about efficiency, right?

Here are the 5 main shortcuts that I use in Google Sheets each and every day to save me a tonne of time. I’m going to give you a bit more than just the Keyboard Shortcut, I’m going to demonstrate how I use it with some clear examples.

Continue reading “5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets”

Google Sheets – How to Separate the First Name from a Full Name Cell

left, right, find, length

Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined.

This fairly simple process can be achieved with the Google Sheets formulas left, right and find.

If you are in a hurry, here are the formulas below:

First Names

To get the first name we do the following:

=LEFT(A2,FIND(” “,A2))

Where ‘A2’ is the cell that we have our full name in – in our case Vasco Nunez de Balboa.

When you have multiple formulas in a cell it’s often best to go from the inside out. So let’s first look at what FIND does.

FIND looks inside the cell for the first value that we want to search for. For us, it’s an empty space, ” “. Find then returns the numerical position of that found item. To do this FIND takes two arguments:

=FIND(the item we are searching for, the cell or string the item is in)

For example if we are searching for the location of the space in
Vasco Nunez de Balboa which is in cell A2, we would do the following:

=FIND(” “,A2)

Which would give the result: 6

There are five letters in the first name Vasco the the space would be position 6.

Now that we have the position of the space, lest just grab everything in the cell to the left of that space. We do this with LEFT.

LEFT also take two values. The first is cell location and the second is the number characters we want to take from the left hand side.

LEFT(cell location, number of characters from the left)

Now that we know the first space is character 6, the formula would look like this:

LEFT(A2,6)

We then replace the 6 with our FIND formula and we are good to go.

Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function

Google Apps Script, Custom Functions

Boy, are these titles getting longer.

But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three thing in Google Sheets:

Get the current sheet name. That’s the same sheet name as the cell you are working in.
Get all the sheet names. A full list of all the sheet names.
Get the name of the Spreadsheet file.

The above picture is pretty self explanatory. If you type in:

=SHEETNAME(#)

Where “#” is a number 0, 1 or 2 you will get the results displayed in the picture. Any other number will display an error.

Continue reading “Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function”

Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet.

To me, this seemed like a pretty common task that one might face, so in response I decided to create a template function to easily do this and reuse on the same project or in other projects.

But first, let’s take a look a the problem a bit more.

We want:

the freedom to select any number of columns from one sheet.
to select the same number of different columns from the sheet we want to paste to.
to paste to the bottom (append) the receiving sheet.
to take into account varying header row heights.
to have a “Completed” or “Copied” column in our origin sheet so that we don’t continue to paste all the data instead of simply what has been updated.
have the data with formulas that we only want to paste the values for.

The Example

Let’s say we have a sheet that is updated regularly and we want to be able to paste data from columns A,B and C of this source sheet and append it to columns C,E and A of the destination Sheet.

Copy Sheet 1 Col A -> Append Sheet 2 Col C
Copy Sheet 1 Col B -> Append Sheet 2 Col E
Copy Sheet 1 Col C -> Append Sheet 2 Col A

Take a look at our two Google Sheets

You can see in the Source Sheet (Sheet1) that we have 3 data columns and one column market To Copy. The To Copy column will be updated with “Copied” once the code is run successfully.

In the Destination Sheet (Sheet2) you can see that we already have some data there. We don’t want to overwrite it. We simply want to append our data to the bottom of it.

Google Sheets – Remove The Lowest Grade for Each Student on a Course

Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF

In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course all the weekly assessment is then added together minus the lowest piece of assessment.

For lecturers with small course sizes this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.

Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students. My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student.

I use Google Sheets for this purpose for it’s ease of use and sharability.

This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test.

List of 7 grades for each student - Google Sheets

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program.

Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. Finally copy and paste the top 2 ranked students of each group in a new sheet – print and hand to the boss.

However, with 100 classes this was going to turn into a time consuming chore and one that could be prone to errors.

To solve this problem, I took advantage of Google Sheets RANK.EQ and FILTER formulas. Let’s take a peek at the formula now and go through the steps in detail later.

=RANK.EQ(D2,FILTER(D:D,C:C = C2))

1	=RANK.EQ(D2,FILTER(D:D,C:C = C2))

=RANK.EQ(Grade,FILTER(Grade Column, Group Column = Group))

1	=RANK.EQ(Grade,FILTER(Grade Column, Group Column = Group))

Let’s move on to an example.

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML

One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the same unit. Before I can run my code I need to determine what modules are running and what units we are up to for me to run my automated code.

To do this I created a dialog box when the code is run from the add-on bar. In a few clicks, I can then choose the relevant modules and units and then run the selected code.

Let’s take a look at what the dialog box looks like:

Upon “Submit”, the dialog box returns an array of objects of checked values from the radio buttons that can be uses in the server-side Google Apps Script.

[{
  "orange":"na", 
  "green":3, 
  "blue":"na", 
  "purple":4
}]

[{

"orange":"na",

"green":3,

"blue":"na",

"purple":4

}]

In this tutorial we will look at the following :

Creating a Menu Item and Sub-Item in the Add-on’s menu.
Setting up a Dialog Box.
Preparing a HTML file to set inside the Dialog Box.
Getting the data from the Dialog Box and Sending it Server-side.

Continue reading “Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets”