Using Google Maps APIv3 and Google Spreadsheets – Part II

In the last post, we focused on getting things into place so that we can start the process of collecting and displaying information in Google Maps. Here, we set up a Google Spreadsheet and Google Form to do the following:

  1. Collect Location data from our users (this will include address information & other tid-bits that we can feed to map users later – like a quote, a review, information about the location etc).
  2. Geocode addresses using Google’s API to obtain LatLng pairs that Google Maps plots on the embedded map.
  3. Set up spreadsheet formulas to perpetuate within the form, as well as a cell that feeds information into the infowindow.

Collecting Information from Users

To map information submitted by users, we need to provide a way for users to submit this data. I’m choosing Google Forms due to it’s native integration with Google Spreadsheets and it’s user friendliness. We will use the following example: we want users to submit information on local health walks and marathons in their communities. To do this, we will collect the following:

  1. Event Name
  2. Address, City, ZIP (this focuses mainly on California and giving a ZIP code should be sufficient enough to not require a state code. This has NOT been tested outside of the US, although the process should be similar.
  3. Contact Email Address (could be replaced with a website, a link etc).
  4. Date/Time of the Event
  5. A link to Get Directions

Create a new form in Google Drive and give it an identifiable name. I’ll name mine “Walk – Marathon Database.” You can enter a form description, however, I’ll focus on customizing the fields. I suggest you make all fields mandatory to collect all the information. The Event Name, Address, City, ZIP, Date and Time fields should all be standalone text boxes. Google Forms does not currently support calendar inputs. You could set up Time as a drop-down menu, but in the interest of time, we will assume that all users input time in the same format. Additionally, you could set up Date as 3 different fields (DAY, MONTH, YEAR). Again, in the interest of time, we will assume that all users enter date information in the same format. Once finished with the form, select the destination for your form responses. You will need to set it to create a new spreadsheet. Call it “Walk – Marathon Database (Responses).” Test your form by submitting TWO fictitious locations. It’s important to add two and not just one! The spreadsheet may give you errors when entering formulas otherwise. Make sure to give actual addresses! Last step here is to delete all extraneous/empty rows, and only leave your first two inputs and the column headers.

Set up Geocoding in Your New Google Response Spreadsheet

While Google Maps can plot markers based on address information alone, the process slows down significantly once you go past 10-15 addresses. To make the process faster, geocode and provide Google with LatLng (latitude and longitude) pairs. What we will be doing is defining two new functions in Google Spreadsheets, called getLatRange and getLongRange. You guessed it: one function gets the latitude, one gets the longitude. Then these get placed in a cell in the same row as the address information.

In the same google response spreadsheet, click on TOOLS, then SCRIPT EDITOR. Create a blank project. In the code window, erase what’s already there and paste the code below. Note: this code was adapted from another version available online by doing a simple Google search.

Save your new script with the name Geocoder (or whatever other name you think might work better in your case).

Setting up Cells and Formulas Within the Response Spreadsheet

Now that we have a way of collecting information and geocoding addresses, we have to set up our spreadsheet to feed this information to Google Maps. We will use the adjacent columns in the Google Spreadsheet to accomplish this goal. First, we want to concatenate the address information (address, city, zip) into one cell. This will help us make sure that users submitted content (in the case for non-required form fields). Column should be named MapConcatenation (do this in I1). Then, in cell I2, type in the following formula:

ARRAYFORMULA automatically copies whatever is in brackets across the entire column. We choose to combine cells E2, F2 and G2 across the columns E, F and G. This will ensure that column I will automatically be populated as rows get added.

Next we set up a logic test that says: if MapConcatenation is empty, return value 0, else, return value 1. This logic test is the second part in the process to validate a user’s submission. Call cell J1 “LogicTest.” Then, in cell J2, enter the following formula:

Any thoughtful database manager knows that it’s dangerous to mess with live data directly. For this reason, we will use columns K through Q to simply copy information from our live form-cells. While not necessary, it’s a safety feature I choose to implement. In the first cell of each column K-Q, enter the following column headers:

K: title / L: date / M: time / N: address / O: city / P: zip / Q: email

The column header name MATTERS as we will use these headers as hooks in our embedded Google Map.

In K2:Q2, use ARRAYFORMULA to copy the information in cells B2:H2 by using the following convention:

Of course, for cell L2 you would use ARRAYFORMULA(C2:C) and so forth. We’re now done with the need to use columns A-H so go ahead and hide those.

In columns R and S, we will use our Geocoder script to provide latitude and longitude information from the address entered by the user. Name cell R1 “latitude” and cell S1 “longitude.” Then in cells R2 and S2, enter the following formulas respectively:

Getting Latitude Information in cell R2

Getting Longitude Information in cell S2

Google might tell you that it’s “thinking” within the cell. Within a second or two, the cell should now feed you the latitude and longitude of the address. If you see it, PERFECT! If not, go back to the code and make sure everything was entered correctly.

The last step here is to set up the cell that feeds description information into the Google Maps infowindow. We’ll use column T for that. Name cell T1 “description.” The contents of T2 will depend on the format in which you want to display information in the infowindow. Google Maps parses the cell for content and displays information written in HTML and CSS so we can style our content according to our needs. I’m simplifying the process and will just display the information in a logical order:

Event Title
Event Date and Time
Event Location (Address, City, Zip)
Contact Information
Get Directions
 
To create this format in your infowindow, copy the following formula in your T2 cell:
This formula collects all of the previously entered information and puts it into a format that your Google Map can understand and display. Tinker with the formula, add CSS or more HTML elements and customize it more. This is a very basic example to get the point across. Your final spreadsheet should look something like this (reload the page if the spreadsheet does not load below):


That’s it! We’re done with the database setup. Next post, we’ll have some fun customizing our embedded Google Map and see our inputs get plotted on the map! Stay tuned for part III!

Leave a Reply

16 Comments

  1. Kathryn

    Thank you for these posts. I was searching for a way to have webinar attendees enter in their information and have it show up on a map. Your info definitely has me farther along than I expected.

    • Bogdan

      Thanks Kathryn! Wanted to give you a quick heads up…the main reason why there’s no part III right now is because I haven’t been able to find a good way to get Google Spreadsheets to continuously geocode the addresses people type in. The issue is that as soon as you close the spreadsheet, it becomes inactive and no longer geocodes. Then the map becomes useless.

      As soon as I figure out a way to get this to work dynamically, I’ll post a follow up part III. If you figure out a way, please post a reply in here!

      Cheers,

      Bogdan

  2. Pingback: Geographic coordinates from mapquest to Google Spreadsheet | Technology & Programming Answers

    • Bogdan

      Hi Will. See the comment I left for Kathryn below. There’s an issue with google spreadsheets where it stops geocoding dynamically (wondering if its an API usage limit on their end). As I find a solution, I’ll make sure to do a follow up part III.

  3. alice

    Got another problem, when running the getLatRange array, I get this: “error: TypeError: Cannot read property “status” from undefined. (line 18, file “Code”)”
    The code it is having issue with is ‘geoJSON.status’, meaning my geocoding isn’t working. Could this be because your code is designed to merge three fields to make a location whereas my form has users submit a single one? (too late to change this, my form already has responses).
    So instead of: =ARRAYFORMULA(getLatRange(N2:N&O2:O&P2:P))
    I use: =ARRAYFORMULA(getLatRange(I2:I))

    I am wondering if the way your fullAddress variable is created doesn’t work with a single column being used.
    Thanks!

    • Bogdan

      That looks like it’s the case. How is your data stored? Does one cell have address, city, zip? I can look around online for a solution. Depending on how your data is stored, you could split cells into multiples using =SPLIT(A2, “,”) which splits the cell at the comma delimiter.

      • alice

        It’s city, state (if applicable), country. I know that this data will geocode as a single entry (I tried using Fusion Tables before finding your blog, but it is a hassle to sync.
        I don’t script, is there a way to modify this script to accept a single field instead of a range?

      • alice

        I split my data as you recommended and get the same error.

      • Bogdan

        Looked online but couldn’t find a specific answer. HOWEVER, I did find that if you have two columns, one column with your data, and another empty column, and you specify =GETLATLONGRANGE(COL1&COL2), it does the trick. Of course there would be a more appropriate way around this but it should do the trick, no?

        Let me know if it works for you.

      • Bogdan

        and my apologies, I think I went through getlatrange and getlongrange, that way you get lat and long in two different columns.

  4. alice

    This is great! But I have one question: how do you set the new lat, long, and mapping cells you are creating to autopopulate for your incoming form responses?

    • alice

      Nevermind, brain wasn’t comprehending, I get it now!

      • Bogdan

        🙂 No problem. Glad you got it! Finishing up my exams this week so look forward to Part III soon 🙂

        Just as a heads up, the current method gives some javascript errors that I’ve yet to find a solution online so some stuff may change as I read up on the issue (Range Error: Maximum Call Stack Size Exceeded).

  5. Ryan

    Nice write up, really looking forward to part 3, and seeing your spreadsheet values populate a map =)

    • Bogdan

      Thanks Ryan! I’ll be posting that one up these next couple of days so stay tuned :).

Next ArticleSan Diego Stands United with Boston