Jun
26
2011

Use Google Forms to Calculate Your Mileage

I am constantly looking for ways to simplify my work-flows. If it is not easy I try to find a way to make it easy. My district re-imburses me for mileage traveled between buildings as well as to conferences. I thought of a few different ways to accomplish this easily, I could use an app to track it, I could pull data from foursquare, or I could create a google form and have a link to it on my computer, my ipad, as well as my iphone.
What I did: Create a google form that has my buildings as well as an “other” box to type addresses. The spreadsheet will than take my choices, convert them to addresses and than get the mileage and directions from Google Maps.
 

First things first. Create your Google Form with your building locations. For this I used multiple choice with the other option to add conference locations. After you have completed the form we will now look into the actual calculations that occur in the Google spreadsheet.

 

We need to give our formula an actual address to search google maps and give us the mileage. For this we do a simple if statement. This is where you can enter your variables. If you have to add more copy and paste just a line and make sure you add a “)” at the end.

Code:
“= if(B2=”SHEL”;”712 Cleveland Rd. East. Huron, Ohio 44839″, if(B2=”HHS”;”710 Cleveland Rd. West. Huron, Ohio 44839″, if(B2=”BOE”;”712 Cleveland Rd. East. Huron, Ohio 44839″, if(B2=”WIS”;”1810 Maple Avenue Huron, OH 44839″, if(B2=”MCCO”;”325 Ohio Street Huron, OH 44839″, if(B2=”BUS”;”1802 Sawmill Parkway Huron, OH 44839″,B2))))))”

I place this formula in two new cells just to keep everything clean.

 

Now that you have converted your building locations to addresses, now we have to send the information to Google. If you have been using the same layout as me you should be able to copy this code into the F column.

Code:

“=INDEX(importXML(“http://maps.google.com/maps?saddr=”&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( D2 ;CHAR(13);” “);CHAR(10);” “)); ” “; “+”)&”&daddr=”&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( E2 ;CHAR(13);” “);CHAR(10);” “)); ” “; “+”)&”&ie=UTF8&hl=en&output=mobile&f=d&btnG=Get+Directions” ; “//span[1]” ) ; 1)”

 

Try it out!  When you enter things into the form you may need to drag the formula down to the new entry’s. Also when you hover over the mileage you will get a link that will take you to directions. So if you know what your conference address is before hand you can kill 2 birds with one stone. Enter your mileage and get directions!

If you have any issues or need some help please do not hesitate to Tweet or email me!

Thanks
TJ

About the Author: TJ Houston

TJ Houston is the Director of Technology at Huron City Schools. TJ leads professional development in all areas of technology and loves developing ways to make teachers, students, and staff's lives easier with the use of technology. TJ also loves photography and his portfolio can be found at http://www.tjshots.com. All of the views on this site are his own.

  • Chris

    TJ, this is a great site.  I tried creating a mileage form using this coding and it keeps giving me a parse error.  Is this the exact code that you used?  

    • http://www.tjhouston.com/ TJ Houston

      Sorry for the EXTREMELY delayed response. This is the new query that works. Much Cleaner 

      =index(query(importxml(“http://maps.google.com/maps?saddr=”&D2&”&daddr=”&E2,”//span[1]“);”select Col1 where Col1 contains ‘km’ or Col1 contains ‘mi’ “;0);1)

      Let me know
      Thanks
      TJ

      • http://twitter.com/daleehrhart daleehrhart

        As of 5/11/2013 here’s the working map url within the query.

        https://maps.google.com/maps?saddr=

        =index(query(importxml(“https://maps.google.com/maps?saddr=”&D3&”&daddr=”&E3,”//span[1]“);”select Col1 where Col1 contains ‘km’ or Col1 contains ‘mi’ “;0);1)

        • http://www.tjhouston.com/ TJ Houston

          Thanks for posting!

  • Chris

    OK, I can now get the addresses to show up, but the coding for column F is yielding “Error The xPath Query did not return any data”    

  • Davfreeman

    I work for a K-12 school district and travel between school frequently. We use a simple mileage grid with schools listed on both the x and y axis and the mileage between inside the grid. I would like to to create a form similar to the one described. I want to be able to choose each school visited from a drop down or checklist and have the form calculate the total mileage for the day. Any ideas?

    • http://www.tjhouston.com/ TJ Houston

      Do you want to use the google maps API? or static values?

      Thanks
      TJ

  • Guest with A Question

    Please help, and thanks for the page.  I tried this, but the result I get is “parse error.”  Any idea what that could mean?

    • http://www.tjhouston.com/ TJ Houston

      Could you post a screenshot?

      Thanks
      TJ

      • Guest

        Sure thing!  Here you can see what happens when I simply copy-paste your formula and change the cell numbers.  If I remove the beginning and ending quotes, then it will say “parse error”  Does this screen shot help you to determine what I might be doing wrong?  Thanks again!

        • http://www.tjhouston.com/ TJ Houston

          So the updated query is: =index(query(importxml(“http://maps.google.com/maps?saddr=”&D2&”&daddr=”&E2,”//span[1]“);”select Col1 where Col1 contains ‘km’ or Col1 contains ‘mi’ “;0);1)

          It wasn’t you at all it was a change to the Google Maps URL.

          Give that a try.
          Thanks
          TJ

  • Pingback: Merge info from Google Forms to PDF Document and Send via Email by @tjhouston | TeacherCast Blog

Find Me Online:

About Me:

My name is TJ Houston.
I am the Director of Technology at Huron City Schools. I love integrating technology into the classroom and helping teachers find the way on the information super highway. Things I love: My family, My awesome Girlfriend, Technology, Education, and Photography.

I run PD sessions twice a week and run community training the first Saturday of the month. I love teaching people new things.

Best School Admin Blog

Nominated for Best School Admin Blog

Facebook Friends

Sponsers

Classroom 2.0

Presentations

Presenter Badge
diigo education pioneer

Friends