Just the other day a colleague of mine presented me with a dilemma. Well, she called it a dilemma, I called it a challenge. Here is the scenario:
She had a Google Sheet with a column of addresses, all of which she wanted to be linked to Google Maps. Now we all know that all you need to do is copy and paste the address into Google Maps and when the page loads, the URL at the top of the browser will be the link to that Maps location. This is fine unless you have a column of 50 plus addresses. Actually, anything over 2 would be annoying to an automated task obsessed maniac like me. After all, those that know me, know my rule of tech: It’s better to spend an hour learning how to automate a task than doing a 5-minute task that may need to be repeated over and over again. Particularly if you know you will be revisiting the said task again in the future.
So with that rule in mind, I started to think of a way to accomplish this task. Of course, the first thing we did was look for a Google Add-on… no luck…maybe an extension… no luck. So now what?
At this point, my inner geek started to boil. I would not be defeated by a measly column of addresses. So I started to dig in. I discovered that if you paste this URL (https://www.google.com/maps/search/?api=1&query=) followed by any address or even a building name like a famous landmark or a name of a school, you will get the Google Maps location for the address. Now it was just a matter of adding the address and turning the whole thing into a link. Wicked simple right? Well, at least the first part is. For this, I turned to one of my favorite text functions in Google Sheets. The CONCATENATE function. Yes “CONCATENATE” the act linking things, in this case, text, together. So that would do It, right?
This Video will explain the formula:
So here is the formula I used. You will have to fill in the italicized parts with your own information. =HYPERLINK(CONCATENATE(“https://www.google.com/maps/search/?api=1&query=”,Cell that has the address),”Text you want to show for the link“)
Exact example from the video:
It’s better with a script
Now this example above is OK for some but not for me. My inner geek quivered at the thought of having to make an additional column to make this work. It simply wasn’t efficient enough to be able to gloat about in a room full of fellow tech-heads. This called for a script.
But I Want to use this Script
Indeed you do so here is a link to a blank spreadsheet with the script attached: https://docs.google.com/spreadsheets/d/15Fid5o1TXRm_QssVagtIVFsfKCbjyw31qkAjw9Pe9eU/copy