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