住所一覧から緯度経度を求める(その2)

前回は、100件ずつ緯度経度を追加するGASコードを作りました。

表のデータを上から順に処理していましたが、よく考えたらすべてのデータを順に処理する必要はありません。自分に必要なデータだけで充分です。
データにフィルタをかけ、表示されているものだけを対象に処理することにしました。

フィルタをかける
GASスクリプト失敗
GASスクリプト人力補完で実行

フィルタをかける

Googleスプレッドシートにある表の任意のセルを選択した状態で、データ＞フィルタを作成　を選択します。

自動的に表の範囲が選択され、項目の行にフィルタを表すマーク（逆三角形型三本線）が追加されました。

例えば東京都だけを選択したい場合、[prefecture]セルの右端のマークを押下し、一覧に表示された都道府県名から [東京都] だけにチェックマークを入れます。

46都道府県のチェックマークを手作業で外すのは大変なので、検索窓の上にある [クリア] を選択することで一旦すべてのチェックを外し、該当する都道府県をチェックし直すと良いでしょう。

フィルタがかかると、ウィンドウの右下に [全件数/フィルタ後のデータ数] が表示されます。

24,057ではまだ多すぎるので、この中から更に渋谷区だけでフィルタをかけます。
[city]セルの右端のマークを押下し、検索窓に[渋谷区]と入れて条件を設定します。

2,122件まで減らせました。

まだ多いので、[category] で主に見たいと思う飲食店だけを抽出します。
[category] は、元のページのソースから、以下のように設定されていることが分かっています。
飲食店は2～6であることが分かります。

[category]セルの右端のマークを押下し、一覧に表示された数字から2～6を選択しても良いですし、せっかく数値で入力されているので、[条件でフィルタ]＞[次の間にある]として [2および6] の設定をしても良いです。

656件まで減らせました。
これくらいなら許容範囲です。

GASスクリプト失敗

ここで、GASスクリプトを使います。
前回作成したスクリプトでは getDataRange().getValues() を使ってデータを取得していましたが、フィルタで表示されている行だけを対象に処理したいので、getFilter() と isRowHiddenByFilter() を使って処理してみたのですが、
Exceeded maximum execution time
エラーが出てしまいました。
処理に時間がかかりすぎて、最大実行時間（通常は6分）以内に終わらなかったことが原因です。

GASスクリプト人力補完で実行

そこで、
1）フィルタで表示されているデータだけを手動でコピー
2）処理用シート tmp を作成（なければ新規）しペースト
3）tmp 上で緯度・経度をジオコーディングで補完
4) 補完後の緯度・経度を元のシートに反映
という、人力作業を使うことにしました。

VBAなら見た目通りの範囲選択・コピー・貼り付けができるのですが、GASではそれができないので、そこは手作業で我慢します。

新しいシートとして [tmp] の名前で作成します。

元のシートに戻り、フィルタをかけた状態の表の上で、 [ctrl + A] ＞ [ctrl + C] （全選択＞コピー）します。

[tmp]シートに貼り付け、[拡張機能]＞[Apps Script] として以下のコードを実行します。
LIMITは500にしてありますが、適宜調整してください。
一回で終わらなければ、複数回実行して最後まで緯度経度を求めます。

function processGeocodeInTmpSheet() {
  const TMP_SHEET_NAME = "tmp";
  const LIMIT = 500;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(TMP_SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  const addressCol = headers.indexOf("address");
  const latCol = headers.indexOf("Latitude");
  const lngCol = headers.indexOf("Longitude");

  if (addressCol === -1 || latCol === -1 || lngCol === -1) {
  Logger.log("必要な列が見つかりません。");
  return;
  }

  let updatedCount = 0;

  for (let i = 1; i < data.length && updatedCount < LIMIT; i++) {
  const row = data[i];
  const address = row[addressCol];
  const lat = row[latCol];
  const lng = row[lngCol];

  if (address && lat === "" && lng === "") {
    const geo = Maps.newGeocoder().geocode(address);
    if (geo.status === "OK" && geo.results.length > 0) {
      const loc = geo.results[0].geometry.location;
      sheet.getRange(i + 1, latCol + 1).setValue(loc.lat);
      sheet.getRange(i + 1, lngCol + 1).setValue(loc.lng);
      updatedCount++;
      Utilities.sleep(150);
    } else {
      Logger.log(`ジオコーディング失敗: ${address}`);
    }
  }

  }

  Logger.log(${updatedCount} 件の住所を処理しました。);
}

[tmp]シートのF列, G列に緯度, 経度が入力されました。
緯度と経度のデータを選択してコピーし、元の一覧シートに貼り付けておくと後から見返せます。