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.

Geo Heatmap Google

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:

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

Chrome Dev Dashboard Element Inspection

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:

  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']")

IMPORTXML Google Sheets

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.

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

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

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

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: 

  1. Get the current sheet name.  That’s the same sheet name as the cell you are working in.
  2. Get all the sheet names. A full list of all the sheet names. 
  3. Get the name of the Spreadsheet file. 
Get Sheet Names and Spreadsheet Names Only - Google Apps Script

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:

  1. the freedom to select any number of columns from one sheet.
  2. to select the same number of different columns from the sheet we want to paste to.
  3. to paste to the bottom (append) the receiving sheet.
  4. to take into account varying header row heights.
  5. 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.
  6. 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 

SourceSheet - Google Sheets
DestinationSheet - 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. 

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

Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.

Google Apps Script, DriveApp

I had an unexpected need to change some non-native file names – in my case, MS Word docs – to something else to appease my masters. Being the resident Igor, I delighted in the task. 

The Problem

I had a folder of MS Word documents on my Google Drive that needed to all be renamed. They all needed the same name with an index number to distinguish that they were different files. 

There were, however other file types in the folder. 

The problem with non-native files in Google Drive is that it’s a little tricky to find their ID. Besides I just wanted to change ALL files that were MS Word in the folder. 

What I would need to do is search for all files inside my selected folder that are of MS Word type and rename them with the same name but with a counter at the end. For example:

Old File NameNew File Name
boring.doccranberrySauce.doc
sad.doc cranberrySauce1.doc
yawn.doc cranberrySauce2.doc
Continue reading “Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.”

Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck

Google Apps Script

One of my recent projects in Google Apps Script required me to search for a file by name and get its ID. This can be problematic in Google Drive because you can have multiple files of the same name in multiple locations. My solution was to also check the file’s parent folder name as well. 

I created a function getFileByName() to handle this. The function takes the following parameters:

  • fileName – The name of the file you are looking for. (required)
  • fileInFolder – The parent folder of the file your are searching for. (optional)

The file path would look a little something like this:

.../fileInFolder/fileName

getFileByName() returns an object containing :

  1.  the ID of the file if the file exists or false if it does not.
  2. the ERROR if there is an error or false if there is not. 

The returned object would look like the following:

Successful

Error

getFileByName() reports the following errors:

  1. If no parent folder name is given and there are more than one file with the same file name. 
  2. There is more than one parent folder and file combination with the same name.
  3. There are multiple files with the same name but none are in the folder parent name provided.
  4. No file with the file name provided exists.

Continue reading “Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck”

Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget

Javascript, WordPress, Your Affiliate Program’s Campaign Link

The Story

When I created Yagisanatode.com my goal was to provide a resource for myself and others to reference on all the coding projects I work on. Since it’s beginnings in October 2017 I have seen a huge rise in my readership and am so please to see a growing community in my comments sections. 

Your support has really helped me to produce more and, I hope, produce better content. Thanks.

Just take me to the code!!!

Continue reading “Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget”

Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script

Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or want to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.

Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.

Custom Function Decorators Google Apps Script

Let’s look at the creation process through a recent example I created:

Continue reading “Google Apps Script – How to make a Custom Function to Use in Google Sheets”

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions

Google Sheets, Google Apps Script

I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.

It goes a little like this. If I want to build a 5 digit random number I would:

RANDBETWEEN Google Sheets

But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?

via GIPHY

 

No…

So instead I decided to make a custom function with Google Apps Script to do this job for me…

Continue reading “Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions”

Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names

Google Apps Script

sighisoara Google Apps Script Tutorial
This tutorial comes from a comfy spot in the center of Sighisoara.

Well, that title is such a mouthful. So I think it needs some explaining…

I’ve recently come across a tasks that requires me to access a particular folder in a directory based on the information in a google Sheet. This means:

  1. I don’t have the folder or the sub-folder ID.
  2. I can get the directory path information from information supplied in a Google Sheet.

Basically what I needed to do was create a Report maker and store it in the following directory path:

My Drive(root) >> Year >>  Quarter >> Unit+ "Report"

For example:

MyDrive >> 2018 >> Q4  >> Unit 4 Report

Boring!!!! Take me to the code!!!

The Problem

Unfortunately, I could not simply change the last folder name from say, Unit 4 Report to Q4 Unit 4 Report 2018 so it is easily searchable and unique. The other problem is that there are other Unit 4 Reports in other years and quarters so I did not want to accidentally call them instead of the exact one I wanted.

So, what to do..?

The Solution

Continue reading “Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names”