GASを使ってスプレッドシート上の住所から緯度と経度を自動で取得する

2020年9月5日 13:02

スプレッドシート上で、膨大な量の住所から緯度と経度を自動で取得できるGAS（Google Apps Script）のプロジェクトを紹介します。
ぼくがそもそもプログラミングやコードに全く詳しくない（今回のプロジェクトも見よう見まねで作っただけです…）ので、はじめての人にも分かりやすいように書けたらと思います。

⇩今回は住所だけ並んだスプレッドシートで、

⇩自動で緯度と経度を取得できたらゴールです。画期的ですね。

実際のスクリプト

早速にも程がありますが、下記がこのプロジェクトを動かすスクリプトです。コピー＆ペーストしてもらって、スクリプト内の「??」を適宜埋めていただければ完成です。
スクリプトエディタは、スプレッドシートの「ツール」バーから開くことができます。

function geocoder() {
const START_ROW = ??;
const ADDRESS_COL = ??;
const LAT_COL = ??;
const LNG_COL = ??;
//上記４つの??には上から順に「読み取りを開始する行」「住所が入力されている列」「緯度を出力したい列」「経度を出力したい列」の番号を入れます。

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('??');
//??にシート名を入力します。例：'シート1'

var lastrow =spreadsheet.getLastRow();
//ここでは読み取りを行う最後の行を指定します。「spreadsheet.getLastRow()」はデータが入力されている一番最後の行まで、という意味です。
//もし最終行を行番号で指定したい場合は「spreadsheet.getLastRow()」の代わりに番号を入れます。
 
for(var i=START_ROW; i<=lastrow; i++){

  var address = spreadsheet.getRange(i,ADDRESS_COL).getValue();
 
  var geocoder = Maps.newGeocoder();
  geocoder.setLanguage('ja');
 
  var response = geocoder.geocode(address);
   
  if(response['results'][0] != null){
    spreadsheet.getRange(i,LAT_COL).setValue(response['results'][0]['geometry']['location']['lat']);
    spreadsheet.getRange(i,LNG_COL).setValue(response['results'][0]['geometry']['location']['lng']);
     
  }
}
}

実行上の注意

①どうやらこのプロジェクトは実行時間があまりに長いと途中で止まってしまうっぽいです。
取得するデータ量があまりにも多い場合は注意してください。自分がやってみた範囲では、実行時間が360秒を超えると自動的に止まってしまいます。データ量にして600～800行くらいだと思います。

②また、24時間以内のプロジェクト実行回数（＝読み取る行数）が多すぎる場合、次の実行まで一日空けなければなりません。エラーが出て、24時間後でなければ動かないようになってしまいます。

以上の2点から、
200～300行ずつ休み休み実行させるのがいいと思われます。
ぼくも一日に800行を一気に読み込んだらそ止まってしまいましたが、翌日200行ずつ分けて実行させたら計900行読み込むことができました。スプレッドシートも疲れちゃうわけですね。
よかったら参考にしてください。

参考にしたサイト

こちらの記事です。
【GAS】複数の施設名から住所と緯度経度を一括取得してGoogleスプレッドシートに反映する

この方は「施設名」から「住所・緯度経度」を自動で取得する方法を書かれていますが、これを少しアレンジしました。とても丁寧に解説されていて読みやすかったです。

プロジェクト作成に至った経緯

きっかけは「大学での研究に必要だったから」です。
建物の位置情報（緯度や経度）を知っていると、それを人口分布や交通網などのデータと一緒に地図上に落とすことができて、今まで見えなかった関係を空間的につかめるようになるわけですが、まさにそんなことを大学でやっています。
研究の途中で約2,000件の建物の緯度と経度を調べないといけないことになり、これは時間がかかりすぎるということで、いろいろと調べてこれを作りました。

本当は他にも自動で住所から緯度経度を取得する方法はたくさん公開されていて、住所をテキストで入力したり、エクセルファイルを読み込ませたり、CSVファイルを読み込ませたり…などなど。
ただどれもあまりに件数が多いと時間がかかったり精度が落ちたり一括で取得できなかったりと、うまく使いこなせませんでした。何も方法がなかったらどうしようかと心配しましたが、何とかうまくいってほっとしています。

同じような状況にいる方に、この記事を見てもらえたら幸いです。

おわりに：はじめてのnote投稿でした

ほんとうなら一記事目は自己紹介とかご挨拶とか（それこそ大学で何してるかとか）、そういったことを書くべきで、いきなりこんな堅苦しい内容の投稿から始まるなんて失礼極まりないという話なんですが、書きたいものが先にできてしまったので、書かずにはいられませんでした。
自己紹介等はまたあらためて、とはいえ急がずに、気ままに更新させていただけたらと思います。