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!