【Googleスプレッドシート】Googleマップとの連携!住所から緯度経度を取得する関数

多くの方が、Googleスプレッドシートで住所リストから緯度経度を取得したいと考えたことがあるでしょう。手動で一つずつ検索するのは時間がかかり、ミスも発生しやすいです。この記事では、Googleマップと連携して住所から緯度経度を自動取得する方法を2つ紹介します。Apps Scriptを使う方法と、IMPORTDATA関数だけを使う方法です。どちらもスプレッドシートの関数として利用でき、作業を大幅に効率化できます。

住所から緯度経度を取得する仕組みと前提条件

Googleスプレッドシートで住所から緯度経度を自動取得するには、外部のジオコーディングサービスを利用します。代表的な方法は、Googleマップが提供するGeocoding APIを使うことです。このAPIに住所を送信すると、緯度と経度のデータがJSON形式で返ってきます。スプレッドシートからこのAPIを呼び出す方法として、Apps Scriptでカスタム関数を作成する方法と、IMPORTDATA関数を使って直接APIレスポンスを取得する方法があります。前者はスクリプトの知識が必要ですが柔軟性が高く、後者は関数のみで設定できます。どちらの方法も、APIキーの取得が必要です。まずはGoogle Cloud Platformでプロジェクトを作成し、Geocoding APIを有効にしてAPIキーを発行してください。APIキーには無料枠があり、月額200ドル相当のクエリまで無料で利用できます。

方法1:Apps Scriptでカスタム関数を作成する手順

Apps Scriptを使うと、標準の関数と同じようにセルで使えるカスタム関数を作成できます。ここでは、住所から緯度経度を返す関数GEOCODEを作成する手順を説明します。

手順1:Geocoding APIキーを取得する

1. Google Cloud Consoleを開く
   ブラウザでGoogle Cloud Consoleにアクセスし、プロジェクトを作成します。既存のプロジェクトがあればそれを選択してください。
2. Geocoding APIを有効にする
   「APIとサービス」から「ライブラリ」を選択し、Geocoding APIを検索して有効にします。
3. APIキーを発行する
   「認証情報」で「認証情報を作成」からAPIキーを発行します。必要に応じてHTTPリファラー制限をスプレッドシートのドメインに設定してください。

手順2:Apps Scriptエディタを開く

1. スプレッドシートを開く
   緯度経度を取得したいスプレッドシートを開きます。
2. 拡張機能メニューからスクリプトエディタを開く
   「拡張機能」をクリックし、「Apps Script」を選択します。新しいタブでスクリプトエディタが開きます。

手順3:スクリプトコードを記述する

スクリプトエディタに以下のコードを貼り付けます。APIキーは取得したものに置き換えてください。

/**
 * 住所から緯度経度を取得するカスタム関数
 * @param {string} address 住所
 * @param {string} apiKey Google Geocoding APIキー
 * @return {string} 緯度,経度
 * @customfunction
 */
function GEOCODE(address, apiKey) {
  var url = 'https://maps.googleapis.com/maps/api/geocode/json?address=' + encodeURIComponent(address) + '&key=' + apiKey;
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  if (json.status === 'OK') {
    var lat = json.results[0].geometry.location.lat;
    var lng = json.results[0].geometry.location.lng;
    return lat + ',' + lng;
  } else {
    return 'エラー: ' + json.status;
  }
}

この関数は、住所とAPIキーを引数に取り、緯度と経度をカンマ区切りで返します。エラーの場合はステータスコードを返します。APIキーをスクリプト内に直接埋め込みたい場合は、関数内で定数として定義し、引数を住所だけにすることもできます。

手順4:スクリプトを保存して関数を利用する

1. スクリプトを保存する
   エディタの保存アイコンをクリックするか、Ctrl+Sで保存します。プロジェクト名は任意です。
2. スプレッドシートに戻る
   タブを閉じてスプレッドシートに戻ります。数秒待つと、カスタム関数が認識されます。
3. セルに関数を入力する
   例えば、A2に住所、B1にAPIキーが入力されている場合、セルに「=GEOCODE(A2, $B$1)」と入力します。緯度経度が表示されます。
4. 緯度と経度を分割する
   結果を緯度と経度に分けたい場合は、SPLIT関数を使います。例えば「=SPLIT(GEOCODE(A2, $B$1), “,”)」と入力すると、隣接する2つのセルに緯度と経度が別々に表示されます。

注意:カスタム関数は最初の読み込みに少し時間がかかることがあります。大量の住所を処理する場合は、Geocoding APIの1日あたりの無料利用枠に注意してください。また、APIキーをスクリプト内に直接書く場合は、スクリプトを公開しないように注意しましょう。

方法2:IMPORTDATAとQUERY関数を使う手順

スクリプトを使いたくない場合は、IMPORTDATA関数でGeocoding APIのレスポンスを直接取得し、QUERY関数で緯度経度を抽出する方法もあります。ただし、この方法ではJSONのパースが複雑なため、QUERY関数では完全に解析できない場合があります。ここでは、より実用的な方法として、IMPORTDATAとSPLIT、REGEXEXTRACTを組み合わせた手順を紹介します。

手順1:APIキーを準備する

方法1と同じく、Google Cloud ConsoleでGeocoding APIキーを取得します。HTTPリファラー制限をスプレッドシートのドメインに設定することをおすすめします。

手順2:住所を含むURLを作成する

A列に住所が入力されているとします。B列にAPIキーを入力しておきます。C2セルに次の数式を入力してAPIリクエストURLを生成します。
="https://maps.googleapis.com/maps/api/geocode/json?address="&ENCODEURL(A2)&"&key="&$B$1

手順3:IMPORTDATAでJSONデータを取得する

D2セルにIMPORTDATA関数を入力しますが、IMPORTDATAは複数行のデータをセル範囲に展開するため、1セルに収めるには工夫が必要です。ここでは、JOIN関数を使って全行を連結する方法を紹介します。
=JOIN("", IMPORTDATA(C2))

これで、JSONテキストが1つのセルに結合されます。ただし、IMPORTDATAの実行には時間がかかることと、1つのセルに収まる文字数に制限があることに注意してください。

手順4:緯度経度を抽出する

結合されたJSONから緯度経度を抜き出すには、REGEXEXTRACT関数で正規表現を利用します。以下の数式で緯度を抽出します。
=REGEXEXTRACT(D2, "\"lat\" : ([0-9.\-]+),\"lng\" : ([0-9.\-]+)")

この正規表現は、latとlngの値をキャプチャします。結果は2つのグループとして返されます。緯度と経度を別々のセルに表示したい場合は、SPLIT関数と組み合わせることもできます。

しかしながら、この方法はデータ量が多いとエラーが発生しやすく、またGoogleマップのAPIレスポンスが変更された場合に正規表現を修正する必要があります。そのため、大量データや堅牢性が求められる場合は、方法1のApps Scriptをおすすめします。

注意点とトラブルシューティング

APIキーの有効期限と使用制限

Geocoding APIには無料枠がありますが、1か月に使用できるクエリ数に制限があります。超えると課金が発生するため、事前に利用料金を確認してください。また、APIキーを第三者に公開しないよう注意し、スプレッドシートの共有設定にも気を付けましょう。

IMPORTXMLを使った無料の方法

APIキーを使わずに緯度経度を取得する方法として、IMPORTXML関数でGoogleマップの検索結果から座標を抽出する手法があります。しかし、この方法はGoogleマップのページ構造に依存しており、頻繁に変更されるため安定しません。おすすめできません。

緯度経度の精度について

Geocoding APIが返す緯度経度は、住所の精度に依存します。番地まで一致すればピンポイントの位置ですが、市区町村レベルではおおよその中心点となります。精度を上げるには、できるだけ詳細な住所を使用してください。

各方法の比較

項目	Apps Script	IMPORTDATA+関数
精度	高い	高い
安定性	安定	やや不安定
難易度	中	易
無料枠	あり	あり
大量データ処理	向く	向かない

まとめ

この記事では、Googleスプレッドシートで住所から緯度経度を自動取得する2つの方法を解説しました。Apps Scriptを使ったカスタム関数は、大量データでも安定して動作し、カスタマイズ性も高いです。IMPORTDATAを使う方法はスクリプト不要で手軽に始められますが、データ量が多い場合や安定性が重要な場合はApps Scriptを選びましょう。まずはAPIキーを取得して、自分の用途に合った方法を試してみてください。緯度経度を取得した後は、Googleマップにプロットしたり、距離計算に活用したりと、さまざまな応用が可能です。ぜひスプレッドシートの自動化を進めてみてください。