Use Google Forms to Calculate Your Mileage
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.
“= 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.
“=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)”
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!
Find Me Online:
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.