Geocoding with Google Apps Script: A Complete Working Example

Use a batch command instead of 1,000 independent formulas

A menu command has a clear beginning and end. It reads the active selection, constructs one output row per input row and writes the result block once. That makes counts, failures and user intent easier to reason about.

1. ReadCapture the active range and its display values once.
2. NormalizeJoin adjacent address components and skip blank rows.
3. GeocodeCall the built-in service and convert each response into a fixed row.
4. WritePlace latitude, longitude and status beside the selection in one operation.

Complete menu-driven example

The downloadable version contains comments and a reverse-geocoding companion. The central forward-geocoding function is shown below.

function geocodeSelectedRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  if (!range) throw new Error("Select address rows first.");

  const geocoder = Maps.newGeocoder();
  const output = range.getDisplayValues().map((row) => {
    const address = row.filter(Boolean).join(", ").trim();
    if (!address) return ["", "", "NO_ADDRESS"];
    try {
      const response = geocoder.geocode(address);
      if (response.status !== "OK" || !response.results.length) {
        return ["", "", "NO_MATCH"];
      }
      const point = response.results[0].geometry.location;
      return [point.lat, point.lng, "GEOCODED"];
    } catch (error) {
      return ["", "", "ERROR: " + error.message];
    }
  });
  sheet.getRange(range.getRow(), range.getLastColumn() + 1, output.length, 3)
    .setValues(output);
}

Install it in a safe test spreadsheet

1. Make a copy of your data.

   Test scripts on non-sensitive sample rows before using production records.

2. Open Extensions → Apps Script.

   Paste the complete download into the editor and save the project.

3. Reload the spreadsheet.

   The Geocoding menu appears after onOpen runs.

4. Select address rows.

   Select a combined address column or adjacent street, city, region, postal-code and country columns.

5. Run and authorize.

   Review Google's consent screen, then filter the status column when processing finishes.

Quotas are only one reliability boundary

Google lists daily Maps-service quotas, but a script must also fit within execution-time limits. A network or provider error can stop a synchronous run mid-selection. The example converts individual provider failures into status rows, but it does not persist a checkpoint after every request.

Before calling this a production system

• Use a document lock to prevent overlapping runs.
• Persist the next row when a job may exceed one execution.
• Make retries idempotent so a row is not charged twice.
• Add cancellation and an expiry for abandoned jobs.
• Throttle requests to the provider's documented rate.
• Separate service credentials from spreadsheet cells.
• Log enough context to diagnose failures without retaining sensitive data indefinitely.

Common questions