How to Automatically Convert Addresses to Latitude and Longitude in Google Sheets Using Google Maps API

In the previous article, we introduced how to obtain an API key for using the Google Maps API.

In this article, we will show you how to use the Google Maps API to automatically output latitude and longitude from addresses in Google Sheets.

By following the steps, you’ll be able to set it up easily.

For details on how to obtain an API key, please refer to [this article].

あわせて読みたい

How to Get a Google Maps API Key: Retrieve Latitude and Longitude from Addresses in Google Sheets Have you ever wanted to automatically retrieve latitude and longitude from addresses to enhance data analysis or improve workflow efficiency? With the Google…

Example Output

When you input an address in column A and run the script, the latitude and longitude are automatically output to columns B and C, respectively.

Steps

Preparing the Spreadsheet

• Input addresses in column A.
• Use the first row as the header row.

The sheet should be named “GeoData”, as the script will reference this name during execution.

If you want to use a different sheet name, you will need to modify the script accordingly.

Executing Google Apps Script

STEP

Open Apps Script Editor

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.

STEP

Create the Script

Since the default function myFunction() {} is present, delete it and replace it with the script below.

function getLatLng() {
  // Specify the fixed sheet name
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('GeoData');

  // Retrieve addresses from column A (starting from row 2 to the last row)
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1);
  const addresses = range.getValues();
  
  // Get the API key from script properties
  const apiKey = PropertiesService.getScriptProperties().getProperty("Maps_API_KEY");
  if (!apiKey) {
    throw new Error("API key is not set. Please add 'Maps_API_KEY' to the script properties.");
  }

  // Fetch latitude and longitude for each address
  for (let i = 0; i < addresses.length; i++) {
    const address = addresses[i][0]; // Get the address
    if (address) {
      const response = UrlFetchApp.fetch(
        `https://maps.googleapis.com/maps/api/geocode/json?address=${encodeURIComponent(address)}&key=${apiKey}`
      );
      const data = JSON.parse(response.getContentText());

      if (data.status === "OK") {
        const location = data.results[0].geometry.location;
        sheet.getRange(i + 2, 2).setValue(location.lat); // Write latitude to column B
        sheet.getRange(i + 2, 3).setValue(location.lng); // Write longitude to column C
      } else {
        sheet.getRange(i + 2, 2).setValue("Error"); // Handle errors
        sheet.getRange(i + 2, 3).setValue("Error");
      }
    }
  }
}

Script Description

Retrieving the Sheet

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('GeoData');

• Specify the sheet name to access the desired sheet.
• If you want to change the sheet name, replace 'GeoData' with your actual sheet name (e.g., 'AddressList').

Retrieving Addresses

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1);
  const addresses = range.getValues();

• Access the range of column A (where the addresses are entered) from the second row to the last row.
• The data is retrieved in an array format. It is assumed that the first row is a header row, and the data starts from the second row onward.

Retrieving the API Key

  const apiKey = PropertiesService.getScriptProperties().getProperty("Maps_API_KEY");
  if (!apiKey) {
    throw new Error("API key is not set. Please add 'Maps_API_KEY' to the script properties.");
  }

• The script retrieves the API key named Maps_API_KEY from the script properties.
• If the API key is not set, an error will be thrown. Ensure the API key is registered in the script properties beforehand.

Geocoding Each Address (Converting to Latitude and Longitude)

  for (let i = 0; i < addresses.length; i++) {
    const address = addresses[i][0]; // Get the address
    if (address) {
      const response = UrlFetchApp.fetch(
        `https://maps.googleapis.com/maps/api/geocode/json?address=${encodeURIComponent(address)}&key=${apiKey}`
      );
      const data = JSON.parse(response.getContentText());

• For each address, a geocoding request is sent to the Google Maps API.
• The address is encoded using the encodeURIComponent function to safely include it in the URL.
• The API response is parsed in JSON format for further processing.

Writing the Results

      if (data.status === "OK") {
        const location = data.results[0].geometry.location;
        sheet.getRange(i + 2, 2).setValue(location.lat); // Write latitude to column B
        sheet.getRange(i + 2, 3).setValue(location.lng); // Write longitude to column C
      } else {
        sheet.getRange(i + 2, 2).setValue("Error"); // Handle errors
        sheet.getRange(i + 2, 3).setValue("Error");
      }

• If the response is successful (status: "OK"), the latitude and longitude are written to columns B and C, respectively.
• If an error occurs, “Error” is output in both columns B and C.

STEP

Save the Script

After writing the script, save it with a name.

(Example: “LatLng”)

STEP

Run the Script

Run the script to calculate the latitude and longitude from the addresses in column A.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

STEP

Latitude and Longitude Output

Column B will display the latitude, and column C will display the longitude.

To verify, input the latitude and longitude into Google Maps, and the corresponding location will be displayed.

For example:
Address: 1600 Amphitheatre Parkway, Mountain View, CA
Latitude: 37.4221534
Longitude: -122.0842385

Notes

Address Accuracy:

If the input address is vague or inaccurate, the script may not return correct results. Ensure the address is as precise as possible.

Free Usage Tier:

Google Maps API offers a free usage tier of $200 per month, which typically covers regular usage without incurring charges.

Advanced: Creating a Custom Function

Get Latitude:=INDEX(GEOCODE(A2), 1)
Get Longitude:=INDEX(GEOCODE(A2), 2)

You can use Google Apps Script to create a custom function that can be called directly in your spreadsheet.

Add the following code to your Apps Script editor:

function GEOCODE(address) {
  if (!address) return ["", ""]; // Return empty values if no address is provided

  // Retrieve the API key from script properties
  const apiKey = PropertiesService.getScriptProperties().getProperty("Maps_API_KEY");
  
  // Construct the Google Maps Geocoding API URL
  const url = `https://maps.googleapis.com/maps/api/geocode/json?address=${encodeURIComponent(address)}&key=${apiKey}`;
  
  // Fetch the API response
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());
  
  // Check if the response status is OK and return latitude and longitude
  if (data.status === "OK") {
    const location = data.results[0].geometry.location;
    return [location.lat, location.lng];
  }

  // Return an error message if the response status is not OK
  return ["Error", "Error"];
}

Click the “▶ Run” button in the top menu to execute the script once.

Then, follow the same steps as before to grant the necessary permissions.

How to Use the Custom Function

Enter the following formulas into the cells:

To get the latitude (Column B):

=INDEX(GEOCODE(A2), 1)

To get the longitude (Column C):

=INDEX(GEOCODE(A2), 2)

Conclusion

By using Google Apps Script, you can easily retrieve latitude and longitude from addresses directly within Google Sheets.

This method is ideal for batch processing address data or automating location-based tasks. Give it a try to enhance your workflow!

あわせて読みたい

Google Sheets + Google Maps: Automatically Generate Map Links from Addresses Google Maps links are useful, but does it take a lot of time to display each one if there are many places you want to check? In particular, if you have addre…

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here