【実録】非エンジニアの私が、コードを1行も書かずに22万件の住所→郵便番号を自動取得した話

function matchZipCodes() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var targetSheet = ss.getSheetByName(“施設一覧”);

  var masterSheet = ss.getSheetByName(“マスタ”);

  if (targetSheet === null || masterSheet === null) {

    SpreadsheetApp.getUi().alert(“エラー：シート名が正しくありません。「施設一覧」と「マスタ」というシートが存在するか確認してください。”);

    return;

  }

  // — アラビア数字を漢数字に変換（100以上は無視） —

  function toKanji(str) {

    var num = parseInt(str, 10);

    if (isNaN(num)) return str;

    if (num >= 100) return str;

    var kanji = [“”, “一”, “二”, “三”, “四”, “五”, “六”, “七”, “八”, “九”];

    if (num === 10) return “十”;

    if (num < 10) return kanji[num];

    var tens = Math.floor(num / 10);

    var ones = num % 10;

    var res = “”;

    if (tens === 1) res += “十”;

    else if (tens > 1) res += kanji[tens] + “十”;

    res += kanji[ones];

    return res;

  }

  // — 究極の正規化：入力ミスや異体字をすべて吸収 —

  function normalizeText(addr) {

    if (!addr) return “”;

    addr = String(addr).normalize(“NFKC”);

    // 先頭・末尾の不要な記号、先頭の謎の数字を削除

    addr = addr.replace(/^[-ー－_]+/g, “”);

    addr = addr.replace(/[-ー－_]+$/g, “”);

    addr = addr.replace(/^\d+(都道府県|北海道|(青森|岩手|宮城|秋田|山形|福島|茨城|栃木|群馬|埼玉|千葉|東京|神奈川|新潟|富山|石川|福井|山梨|長野|岐阜|静岡|愛知|三重|滋賀|京都|大阪|兵庫|奈良|和歌山|鳥取|島根|岡山|広島|山口|徳島|香川|愛媛|高知|福岡|佐賀|長崎|熊本|大分|宮崎|鹿児島|沖縄)県)/, “$1”);

    // 【修正】異体字やよくある漢字の間違いを統一（蘂、檜を追加）

    var weirdChars = {

      “⽟”:”玉”, “⼤”:”大”, “⾼”:”高”, “⿐”:”鼻”, “⽬”:”目”,

      “⼭”:”山”, “田”:”田”, “⽔”:”水”, “⽊”:”木”, “⾦”:”金”,

      “⼟”:”土”, “⽉”:”月”, “⽇”:”日”, “⽯”:”石”, “⽴”:”立”,

      “⼝”:”口”, “⼿”:”手”, “⼼”:”心”, “⾞”:”車”, “⾨”:”門”, “⻑”:”長”,

      “冨”:”富”, “礪”:”砺”, “諌”:”諫”, “﨑”:”崎”, “烏”:”鳥”, “靜”:”静”,

      “蘂”:”蕊”, “檜”:”桧” // ← 新規追加

    };

    addr = addr.replace(/[⽟⼤⾼⿐⽬⼭⽥⽔⽊⾦⼟⽉⽇⽯⽴⼝⼿⼼⾞⾨⻑冨礪諌﨑烏靜蘂檜]/g, function(m) {

      return weirdChars[m] || m;

    });

    addr = addr.replace(/甚平衛/g, “甚兵衛”);

    addr = addr.replace(/群馬県足利市/g, “栃木県足利市”);

    addr = addr.replace(/[\s ]+/g, “”);

    addr = addr.replace(/^知県/, “愛知県”);

    addr = addr.replace(/県[a-zA-Z]/g, “県”);

    // 【修正】表記揺れの統一（「が」を追加、「条通」を「条」に）

    addr = addr.replace(/の/g, “ノ”);

    addr = addr.replace(/[ヶヵが]/g, function(m) { return m === “ヵ” ? “カ” : “ケ”; }); // ← 「が」も「ケ」に強制統一

    addr = addr.replace(/条通/g, “条”); // ← 「条通」の「通」を無視する

    addr = addr.replace(/大字/g, “”).replace(/字/g, “”);

    addr = addr.replace(/第(\d+)/g, function(match, p1) {

      return “第” + toKanji(p1);

    });

    addr = addr.replace(/([0-9]+)(-|ー|‐|－)/, function(match, p1) {

      return toKanji(p1) + “丁目”;

    });

    addr = addr.replace(/(\d+)(条|丁目|線|番町|割)/g, function(match, p1, p2) {

      return toKanji(p1) + p2;

    });

    return addr;

  }

  // — マスタデータのカッコ書き（丁目）を自動展開 —

  function expandTowns(town) {

    var match = town.match(/^(.*?)（(.*?)）$/);

    if (!match) return [town];

    var base = match[1];

    var content = match[2];

    var results = [base];

    if (content.match(/次|一円|その他|地階/)) return results;

    content = String(content).normalize(“NFKC”).replace(/丁目/g, “”);

    var parts = content.split(/、|,/);

    for (var i = 0; i < parts.length; i++) {

      var part = parts[i];

      if (part.indexOf(“〜”) !== -1 || part.indexOf(“-“) !== -1 || part.indexOf(“～”) !== -1) {

        var range = part.split(/〜|-|～/);

        if (range.length === 2) {

          var start = parseInt(range[0], 10);

          var end = parseInt(range[1], 10);

          if (!isNaN(start) && !isNaN(end) && start <= end) {

            for (var j = start; j <= end; j++) results.push(base + toKanji(j.toString()) + “丁目”);

          }

        }

      } else {

        var num = parseInt(part, 10);

        if (!isNaN(num)) results.push(base + toKanji(num.toString()) + “丁目”);

      }

    }

    return results;

  }

  // 1. 辞書の作成

  var masterData = masterSheet.getDataRange().getValues();

  var zipMap = {};

  for (var i = 0; i < masterData.length; i++) {

    var zip = String(masterData[i][2]);

    var pref = String(masterData[i][6]);

    var city = String(masterData[i][7]);

    var rawTown = String(masterData[i][8]);

    var town = rawTown;

    if (town === “以下に掲載がない場合”) town = “”;

    var expandedTowns = expandTowns(town);

    var cityPrefixes = [pref + city, city];

    if (city.indexOf(“郡”) !== -1) {

      var cityNoGun = city.split(“郡”)[1];

      cityPrefixes.push(pref + cityNoGun, cityNoGun);

    }

    if (city.indexOf(“市”) !== -1 && city.indexOf(“区”) !== -1) {

      var cityNoCity = city.split(“市”)[1];

      cityPrefixes.push(pref + cityNoCity, cityNoCity);

    }

    var mapVal = [zip, pref, city, rawTown];

    for (var t = 0; t < expandedTowns.length; t++) {

      var eTown = expandedTowns[t];

      for (var p = 0; p < cityPrefixes.length; p++) {

        var key = normalizeText(cityPrefixes[p] + eTown);

        if (key && !zipMap[key]) zipMap[key] = mapVal;

        if (eTown.slice(-1) === “町”) {

          var keyMachi = normalizeText(cityPrefixes[p] + eTown.slice(0, -1));

          if (keyMachi && !zipMap[keyMachi]) zipMap[keyMachi] = mapVal;

        }

      }

      var keyTownOnly = normalizeText(eTown);

      if (keyTownOnly && !zipMap[keyTownOnly]) zipMap[keyTownOnly] = mapVal;

      if (eTown.slice(-1) === “町”) {

        var keyTownMachiOnly = normalizeText(eTown.slice(0, -1));

        if (keyTownMachiOnly && !zipMap[keyTownMachiOnly]) zipMap[keyTownMachiOnly] = mapVal;

      }

    }

    var cityKey1 = normalizeText(pref + city);

    var cityKey2 = normalizeText(city);

    var cityKey3 = “”;

    if (city.indexOf(“区”) !== -1) {

      var parts = city.split(“市”);

      if(parts.length > 1) cityKey3 = normalizeText(parts[1]);

    } else if (city.indexOf(“郡”) !== -1) {

      var parts = city.split(“郡”);

      if(parts.length > 1) cityKey3 = normalizeText(parts[1]);

    }

    if (!zipMap[cityKey1]) zipMap[cityKey1] = mapVal;

    if (!zipMap[cityKey2]) zipMap[cityKey2] = mapVal;

    if (cityKey3 && !zipMap[cityKey3]) zipMap[cityKey3] = mapVal;

  }

  // 2. 検索と書き出し

  var lastRow = targetSheet.getLastRow();

  if (lastRow < 2) return;

  var targetData = targetSheet.getRange(2, 4, lastRow – 1, 6).getValues();

  var outputValues = [];

  for (var j = 0; j < targetData.length; j++) {

    var rawPref = String(targetData[j][0] || “”);

    var rawCity = String(targetData[j][1] || “”);

    var rawAddress = String(targetData[j][5] || “”);

    if (rawAddress === “”) {

      outputValues.push([“”, “未合致: (元データが空欄)”, “”, “”, “”]);

      continue;

    }

    var normalizedAddress = normalizeText(rawAddress);

    var combinedAddress = normalizeText(rawPref + rawCity + rawAddress);

    var foundZip = “”;

    var usedAddress = “”;

    var outPref = “”;

    var outCity = “”;

    var outTown = “”;

    function searchZip(searchTarget) {

      for (var len = searchTarget.length; len > 0; len–) {

        var searchStr = searchTarget.substring(0, len);

        if (zipMap[searchStr]) {

          var val = zipMap[searchStr];

          var cleanZip = val[0].replace(/-/g, “”);

          while (cleanZip.length < 7) {

            cleanZip = “0” + cleanZip;

          }

          var formattedZip = cleanZip.substring(0, 3) + “-” + cleanZip.substring(3);

          return { zip: formattedZip, pref: val[1], city: val[2], town: val[3], matched: searchStr };

        }

      }

      return null;

    }

    var result = searchZip(normalizedAddress);

    if (result) {

      foundZip = result.zip;

      usedAddress = result.matched;

      outPref = result.pref;

      outCity = result.city;

      outTown = result.town;

    } else {

      var combinedResult = searchZip(combinedAddress);

      if (combinedResult) {

        foundZip = combinedResult.zip;

        usedAddress = combinedResult.matched;

        outPref = combinedResult.pref;

        outCity = combinedResult.city;

        outTown = combinedResult.town;

      }

    }

    if (foundZip === “”) {

      usedAddress = “未合致: ” + normalizedAddress;

    }

    outputValues.push([foundZip, usedAddress, outPref, outCity, outTown]);

  }

  targetSheet.getRange(2, 15, outputValues.length, 5).setValues(outputValues);

  SpreadsheetApp.getUi().alert(“処理が完了しました！O列からの出力を確認してください。”);

}