# 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

#### TJ Houston

Hey everyone, My name is TJ and I am here to help you! I am a lifelong learner that strives to help others. Work: By day I work for Panasonic as the Sales Support Rep for the Midwest Region. Play: I love all things photography, videography, and tech! Home: I have a loving wife (Ashley) and an adorable German shepherd (Jynx) You can find me on twitter @tjhouston on instagram @tjshotsphotography or if you want to see some of my professional photography work check out tjshots.com. All of the views on this site are my own and do not reflect my employers.

• 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?

• 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

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

=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)

• Thanks for posting!

• James Kenney

Is there an updated working map url/query? I am receiving a formula parse error. Thank you in advance, TJ!

• 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?

• 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?

• 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!

• 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

• James Kenney

I know I’m digging up old dirt with this question, but am hoping you can take a look at these screen shots for obvious errors. Thank you.

• Pedometer

I enjoy reading your work, your scripting! – well done! I have a challenge and I am wondering if you would be willing to try taking it on. Our school district has a pedometer challenge. I would like to track our progress using 1) google form to have walkers submit walking steps, 2) send the google form data to a google map 3) accounting for the newly submitted walking steps, the google map has a marker (showing the current ‘virtual’ location) move around the geographical perimeter of our district. So, my goal is to allow users to submit their walking steps via google form and as a result they get to see the real-time movement/progress on a Google map and the location change their steps made to our current location– all without a GPS since this is all virtual.