Explore

Converting GPS coordinates to street address with Google Maps API

Setting up Google Maps API

Use GOOGLEMAPS custom function, which utilizes the Google Maps Geocoding API. First, you need to get an API key. Follow these steps to get one:

Go to the

Google Cloud Platform Console⁠

Create a new project or select an existing one.

Use the search bar to search for "Geocoding API" and enable it.

Go to the "Credentials" tab in the APIs & Services dashboard and click "Create credentials."

Choose "API key" and copy the generated key.

Adding Google Maps API to Google Sheet through Apps Script

Now that you have an API key, you can create a custom function in Google Sheets:

Open your Google Sheets document.

Click on "Extensions" in the menu, then click on "Apps Script."

Replace the existing code with the following code snippet:

function GOOGLEMAPS(lat, long) {

var apiKey = "YOUR_API_KEY";

var response = UrlFetchApp.fetch("https://maps.googleapis.com/maps/api/geocode/json?latlng=" + lat + "," + long + "&key=" + apiKey);

var json = JSON.parse(response.getContentText());

if (json.status == "OK") {

return json.results[0].formatted_address;

} else {

return "Error: " + json.status;

}

This is how it will look

⁠

⁠

Replace YOUR_API_KEY with the API key you obtained earlier.

Save the script by clicking the floppy disk icon or pressing Ctrl + S (Cmd + S on Mac).

Close the Apps Script window

Now you can use the custom function GOOGLEMAPS in your Google Sheets document to convert GPS coordinates into street addresses. For example, if you have the latitude in cell A2 and longitude in cell B2, you can use the formula:

scssCopy code

=GOOGLEMAPS(B2, C2)

In the Google Sheet:

In the Google Sheet:

Location

Coordinate A

Coordinate B

CALC: Street address

-1.0556957785454,37.110269614869

-1.055695779

37.11026961

=GOOGLEMAPS(B2, C2)

There are no rows in this table

⁠

Split the coordinate into a column for lattitude and a column for longitude: Data → Split text to columns (see screenshot)

Add GOOGLEMAPS formula in column D

⁠

⁠

Possible Issues with setting up Maps API

API Key Restrictions: Make sure you don't have any restrictions on your API key that might be preventing its use with the Geocoding API. To check this, follow these steps:

Go to the

Google Cloud Platform Console⁠

Select your project.

Click on "Navigation Menu" (three horizontal lines) in the top left corner.

Click on "APIs & Services" > "Credentials."

Find your API key in the list and click on the edit button (pencil icon).

Under "Key restrictions," ensure that there are no restrictions that would prevent the use of the key with the Geocoding API. If there are any, update the settings and save.

Billing: Make sure you have a valid billing account linked to your Google Cloud Platform project. To check this, follow these steps:

Go to the

Google Cloud Platform Console⁠

Select your project.

Click on "Navigation Menu" (three horizontal lines) in the top left corner.

Click on "Billing."

Ensure that your project is linked to an active billing account.

API Enabled: Double-check that the Geocoding API is enabled for your project. To verify this, follow these steps:

Go to the

Google Cloud Platform Console⁠

Select your project.

Click on "Navigation Menu" (three horizontal lines) in the top left corner.

Click on "APIs & Services" > "Library."

Search for "Geocoding API" and click on it.

Ensure that the API is enabled for your project. If not, enable it.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.