fbpx

Subscribe on YouTube!

Quick tutorial showing you how to set up a Google Spreadsheet to automatically calculate and keep track of your business mileage, ready for your year-end expense claim. This quick tip was part of our 10 New Year’s Resolutions for Businesses, which you can watch at https://goo.gl/EnrT4R

https://goo.gl/XZdg2C – G Suite and Gmail for Business makes your email look more professional by using your website’s domain name as well as giving you access to collaborative tools like Google Docs, Google Sheets, Google Calendar and Google Drive. You can get a 14-day free trial and if you message us privately we can also give you a 20% discount code for your first year. Click: https://goo.gl/XZdg2C

The formula you’ll need for calculating the route distance in metres between your two points is:

=importxml("https://maps.googleapis.com/maps/api/directions/xml?origin=" & C2 & "&destination=" & D2 & "&sensor=false&alternatives=false&key=___YOUR_API_KEY___","//leg/distance/value")

Replace “___YOUR_API_KEY___” in the formula above with an API key from https://console.cloud.google.com/ – create a project, add a billing account ($200 free credit per month), activate Directions API and then generate credentials.

TIP: Make sure the double-quote marks are simple ones – if necessary delete and re-type!

Facebook – https://www.facebook.com/SwitchedOnNetwork/
Twitter – https://twitter.com/SwitchedOnNet
Instagram – https://www.instagram.com/switchedonnetwork/
Google+ – https://plus.google.com/112924836454795429890
YouTube Channel – https://www.youtube.com/SwitchedOnNetwork?sub_confirmation=1

Paul Freeman-Powell

Paul (@paulfp) is the main presenter of the award-winning Switched On Network YouTube Channel, which covers a variety of interesting topics usually relating to his love of technology and all things geeky. He also founded and runs Innobella Media, where he leads in all aspects of video production, video editing, sound & lighting. A father of 3 children including twins, his hobbies used to include photography, playing the drums and cycling. With a degree in Modern European Languages, Paul speaks French, Spanish and a little bit of Italian, and holds dual British & Irish citizenship.

View all posts

10 comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    • Have you set up your billing account? They’ve changed how it works since I made this video. You now have a limit of 1 lookup per day unless you set up a billing account, which gives you $200 credit per month – so you shouldn’t pay anything.

  • I set up a billing account and followed your instruction but. I keep getting an error there is a problem with the formula. I cannot figure out what I am missing.

    • Try the formula in another cell but strip out the xml function call and second argument, so you’re just printing the complete parsed URL to the cell. The paste this into a web browser and you should either see a bunch of XML with each step in the directions (if it’s worked) or an error message which will point you in the right direction.

  • Came across your youtube video and then came here per the directions.

    I was trying to make a simple driving expense report that would automatically calculate the number of miles after I input the to and from destinations. Still got an error after creating an account, starting a free trial, getting the API key. Seemed a little too complicated.

    For those who are trying to do the same as me; I suggest checking out this site for easy instructions on how to insert a custom script that will do this for you:

    https://developers.google.com/apps-script/quickstart/custom-functions#try_it_out

    • I was hoping this would work for my simple mileage sheet. There is no such formula =drivingdistance that I can find. No menu that follows the steps given either. Odd.

  • I keep getting the #ERROR as well. Has anything changed? I have followed all the steps to getting an API key.

Subscribe to our YouTube Channel!

%d bloggers like this: