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
This code shows an error even after securing our own API key. Are you sure it’s correct?
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.
Still not working for this noob.
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
Oh thanks, I’ll check that out!
The API key is a recent requirement (since I made the video) – it used to be dead straightforward but I agree it’s a bit of a faff now.
i found the same error but change ” for ” and its started working
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.
I have received a “Error Data Loading” message. It seems to not be reading the google maps api. I have set this up and have been using it for months but not it just stopped working. Any tips?
The most common cause for that I’ve seen is folk coming to the end of their 12m free trial with Google, so having to set up a billing account – is that the case for you perhaps?
I have one set up already and have had no billing issuesa
The best thing to do then is debug in a web browser. Take the full URL that Sheets generates (you may have to partly construct it yourself in Notepad) and paste it into address bar. If all is well you’ll see an XML document of driving directions, if not you should see an error pointing you in the right direction.