Home » Blog » Intro to Awesome Tables 3: Mapping user submitted data with Google Forms

Intro to Awesome Tables 3: Mapping user submitted data with Google Forms

In this tutorial we will learn how to create an online Google Form that feeds data into a Google Spreadsheet which will then be attached to an Awesome Table which will be configured to display the form entries as points on a map. These map markers will have popup tool tips that display other data from the spreadsheet. All of this will be dynamic, updating every time a new entry is submitted.

This part 3 of a four part series where we learn about Awesome Tables:

Here’s the finished product, be sure to click on the map icons to see a popup:


Remember, this is all data that is generated by users who fill out a Google Form! Cool, let’s make it happen.

Planning

For this project we are going to be creating a map of self organized potlucks. Before we start we need to plan out data structure. Basically we are mapping out the column headers which will be generated by our form. Google forms automagically dump entries into Google Spreadsheets, matching the form entry titles with column headers in the connected spreadsheet.

So what information do we need? First off, who is filling out the form? It’s going to the potluck host and it will be participants viewing the map. Thus we need to ask the host all the information that a participant will need to attend. The potlucks have to happen somewhere, so a location address for starters. We want people to be able to contact the host, so some kind of contact info should be provided. Let’s also keep in mind what information is required (let’s mark it with an asterisk * in our list) and what, if any, info is optional. To that end an email can be required while a phone number can be optional. When contacting someone you typically want to know their name, so let’s add that too! The host might want to leave a note for participants so let’s offer an optional description for the event. Potlucks typically have a start time and end time as well as a date. Perhaps a host keeps a vegan kitchen and wants to let people know not to bring meat, but that can go in the description. Cool, I can’t think of anything else so this should do it. Here’s the final list:

  • Host’s name*
  • Location address*
  • Email*
  • Phone
  • Description
  • Start time*
  • End time
  • Date*

Now we are ready to get started!

Outline

This is the tutorial outline, details for each step are below.

  • Create a new spreadsheet
  • Go to Tools > Create a form
  • A new window with a form will open, fill it out based on our plan
    • Give the form a name and description
    • Host’s name – (title) Name – (input type) Short answer – required
    • Location address – Address – Paragraph – required
    • Email – Email – Short answer – required
      • Add data validation (click three dots at the bottom right)
      • Set validation to text and email address then add an error message
    • Phone – Phone – Short answer
    • Description – Notes – Paragraph
      • Add a description (click three dots at the bottom right)
    • Start time – Start time – Time – required
    • End time – End time – Time
    • Date – Date – Date
    • (Optional) Set a theme by clicking the painter palette at the top
  • Now that the form is setup go back to your spreadsheet, notice that a new sheet has been created named form responses
  • Change the sheet name from form responses to form
  • Test your form by clicking the eye icon to preview and fill out the form (maybe do a few)
  • Add geolocation to the spreadsheet and form
    • Click Add-ons > Get add-ons
    • Search “geocode” and select Geocode by Awesome Table
    • Add the script by clicking + Free button on the top right
    • Authorize the app
  • Configure the geocode script on the spreadsheet
    • Make sure you’ve added at least one entry via your form
    • Click Add-ons > Geocode by Awesome Table > Start Geocoding
    • On the right sidebar check the settings (default should work)
      • Current sheet set to form (or whatever your form entry sheet is named)
      • Address column set to—you guessed it—Address
      • Click blue Geocode! Button at the bottom.
    • You should see two new columns (Latitude & Longitude) to the right of Address
    • Click Add-ons > Geocode by Awesome Table > Geocode on form submit
    • Click the blue Activate trigger button on pop-up, the close box
    • You can also close the sidebar now
  • Setup a new blank Awesome Table view (see Part 1: Setting up your Awesome Table)
    • Select your spreadsheet
    • Under Create new view select form under Select a sheet
    • Double check Range to make sure it covers all the column headers
    • Create the view
    • Under View configuration set Visualization type to Map with table
  • Configure the filter & parameters on your spreadsheet
    • Filters & parameters are added to the 2nd row of a column
      • Create a new row under row 1 (above the test entry)
    • Reference available filters & available parameters
    • Add the following filters & parameters under the column headers on row 2
      • Timestamp: Hidden
      • Name: NoFilter
      • Address: MapsAddress – StringFilter
      • Latitude: MapsLat – Hidden
      • Longitude: MapsLong – Hidden
      • Email: NoFilter
      • Phone: NoFilter
      • Notes: NoFilter
      • Start Time: NoFilter
      • End Time: NoFilter
      • Date: DateFilter
    • Go back to your Awesome Table view and refresh, wow right?
  • Setup map tooltip popup on spreadsheet
    • Add a new column header “Tooltip” to the form spreadsheet
    • Set the row 2 under it to “MapsTooltip – Hidden
    • Create a new sheet called “template” (or just rename “sheet 1”)
    • Name the first cell (A1) “Tooltip” (must be the same as above)
    • In the cell under (A2) add the template text (example below)
  • Configure Awesome Table view
    • Under Data Source update Range to include new column (A1:L)
    • Under Advanced parameters set Template range to “template!A1:2

Create a new spreadsheet

Make your new Google Spreadsheet. Click the title to give it a name.

Setup the form

Go to Tools > Create a form

A new window with a form will open, fill it out based on our plan.

Give the form a name and description

Add new input fields by clicking the plus button on the right.

Make required by flipping the switch at the bottom.

Input fields

Host’s name – (title) Name – (input type) Short answer – required

Location address – Address – Paragraph – required

Email – Email – Short answer – required

Add data validation (click three dots at the bottom right)

Set validation to text and email address then add an error message

Phone – Phone – Short answer

Description – Notes – Paragraph

Add a description (click three dots at the bottom right)

Start time – Start time – Time – required

End time – End time – Time

Date – Date – Date

(Optional) Set a theme by clicking the painter palette at the top

Now that the form is setup go back to your spreadsheet, notice that a new sheet has been created named form responses

Change the sheet name from form responses to form

See how the input titles are automagically added as column headers? nice.

Test your form

Go back to your form and click the eye icon to preview and fill out the form (maybe do a few). Be sure to use real addresses so they show up on the map.

Back on your spreadsheet the data will appear as if by magic.

Add geolocation to the spreadsheet and form

Click Add-ons > Get add-ons

Search “geocode” and select Geocode by Awesome Table

Add the script by clicking + Free button on the top right

Authorize the app, click Allow

Configure the geocode script on the spreadsheet

Make sure you’ve added at least one entry via your form

Click Add-ons > Geocode by Awesome Table > Start Geocoding

On the right sidebar check the settings (default should work)

  • Current sheet set to form (or whatever your form entry sheet is named)
  • Address column set to—you guessed it—Address
  • Click blue Geocode! Button at the bottom.

You should see two new columns (Latitude & Longitude) to the right of Address

Click Add-ons > Geocode by Awesome Table > Geocode on form submit

Click the blue Activate trigger button on pop-up, the close box

You can also close the sidebar now

Create new Awesome Table view

Setup a new blank Awesome Table view (see Part 1: Setting up your Awesome Table)

  • Select your spreadsheet
  • Under Create new view select form under Select a sheet
  • Double check Range to make sure it covers all the column headers
  • Create the view
  • Under View configuration set Visualization type to Map with table

Configure the filter & parameters on your spreadsheet

Filters & parameters are added to the 2nd row of a column

Return to your spreadsheet and create a new row below row 1 (above the form entry)

Reference available filters & available parameters

Add the following filters & parameters under the column headers on row 2

  • Timestamp: Hidden
  • Name: NoFilter
  • Address: MapsAddress – StringFilter
  • Latitude: MapsLat – Hidden
  • Longitude: MapsLong – Hidden
  • Email: NoFilter
  • Phone: NoFilter
  • Notes: NoFilter
  • Start Time: NoFilter
  • End Time: NoFilter
  • Date: DateFilter

Go back to your Awesome Table view and refresh, wow right?

Setup map tooltip popup on spreadsheet

Add a new column header “Tooltip” to the form spreadsheet

Set the row 2 under it to “MapsTooltip – Hidden

Create a new sheet called “template” (or just rename “sheet 1”)

Switch to the template sheet.

Name the first cell (A1) “Tooltip” (must be the same as above)

In the cell under (A2) add the template text, here’s the text:

<h3>{{Name}} is hosting a potluck on {{Date}}.</h3>
<strong>Time:</strong> <br>
{{Start Time}} - {{End Time}}
<strong>Address:</strong> <br>
{{Address}}
<strong>Contact:</strong> <a href="mailto:{{Email}}">{{Email}}</a> <a href="tel:{{Phone}}">{{Phone}}</a>
<strong>Host Note:</strong> <br>
{{Notes}}

 

Configure Awesome Table view

Now we just need to configure our Awesome Table view to read these new changes.

Under Data Source update Range to include new column (A1:L)

Under Advanced parameters set Template range to “template!A1:2

Refresh your Awesome Table view and click on a map icon.

Conclusion

You’ve now got yourself a map that reads data submitted by a google form with pop ups and everything!

In this tutorial we learned:

  • How to create a Google Form that is attached to a spreadsheet.
  • How to configure our spreadsheet to display as a map using Awesome Tables.
  • How to create a template to display data from the spreadsheet as a pop up over map icons.

If you have any questions feel free to reach out to drew@goodgoodwork.io or leave a comment below.

Move on to Part 4: Sharing and embedding your Awesome Table.

Leave a Reply

Your email address will not be published. Required fields are marked *