GAS × RESASのAPIで市区町村から都道府県を出力する

皆さん初めまして。フロントエンドエンジニアのいわのすけです。

スプレッドシートで管理している顧客の住所の情報が都道府県なしの市区町村以降の住所からしか記載されていないことってありませんか？

▼下記画像参照
名前と電話番号はChatGPTで生成しており、架空のものになります。
住所は実在するものですが、市区町村までの記載にしてます。

もし後からE列に都道府県の情報も入力してほしいと言われると、量によっては途方もない時間がかかりますよね。さらに住所を特定できる郵便番号がないこともあるかと思います。

かなりニッチなケースですが、市区町村以降の住所の情報のみから、都道府県を割り出し出力するGASを作成したので、参考にしていただけると嬉しいです。

パフォーマンスの面で改善の余地があると思うので、意見やアドバイス等をいただけると嬉しいです。

初期の構想

APIから都道府県と市区町村の一覧データに引っ張ってくる
都道府県をキーにオブジェクトを作成する
市区町村以降の住所が入力されているセルから、情報を取得
取得した情報（市区町村以降の住所）の文字列に含まれるオブジェクト内でバリュー(市区町村)を特定する
合致率が最も高いものに変更する
キー名（都道府県）を出力する

改善した構想

セルやAPIから引っ張ってきた情報量が多すぎて、パフォーマンスがあまりにも悪いため、設計を改良

APIから都道府県と市区町村の一覧データに引っ張ってくる
都道府県をバリューにキーは市区町村でオブジェクトを作成する
※逆にすることで、都道府県に付随する大量のバリューの中でループを回す必要がなくなる
市区町村以降の住所が入力されているセルから、情報を取得
オブジェクト内で該当するバリューを特定する
合致率が最も高いものに変更する
バリューを出力する

使用するAPIについて

RESAS（地域経済分析システム）とは、経済産業省と内閣官房デジタル田園都市国家構想実現会議事務局が提供しているビッグデータを集約し、可視化を試みるシステム。

掲載しているデータをプログラムが取得できる機能をAPIで提供してくれています。
利用には会員登録とその後に発行されているAPIキーが必要になります。

実装

このセクションの最後に、コピペして使えるコードブロックを記述してますので、自由にご使用ください。

API-keyの設定
GASの設定＞スクリプトプロパティにプロパティと値を設定する
```
プロパティ：API_KEY
値：取得したAPIキー
```

変数定義
まずはスプレッドシートの情報を格納する変数定義をします。
住所が始まる行と列は入力するスプレッドシートに合わせて記入してください

const SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const STARTROW = 2; // 住所が始まる行
const ADDRESCOLUMN = 4; // 住所が含まれる列（例: D列）
const NUMROWS = SHEET.getLastRow() - STARTROW + 1;
const ADDRESRANGE = SHEET.getRange(STARTROW, ADDRESCOLUMN, NUMROWS);
const ADRESSES = ADDRESRANGE.getValues();

APIからのデータ取得と整形
次にAPIから都道府県と市区町村のデータを取得し、下記の形式でオブジェクトに格納します。
{
'広島市北区'：'広島県',
'広島市東区'：'広島県',
...}

var apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
var prefecturesUrl = 'https://opendata.resas-portal.go.jp/api/v1/prefectures';
var options = {
    'headers': {'X-API-KEY': apiKey},
    'muteHttpExceptions': true
};
// 都道府県データを取得
var response = UrlFetchApp.fetch(prefecturesUrl, options);
var prefecturesData = JSON.parse(response.getContentText());
var municipalityPrefectureMap = {};

// 都道府県データと市区町村データを取得し、マッピングを作成
if (prefecturesData && prefecturesData.result) {
    prefecturesData.result.forEach(function(prefecture) {
        var municipalitiesUrl = 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode=' + prefecture.prefCode;
        var cityResponse = UrlFetchApp.fetch(municipalitiesUrl, options);
        var municipalitiesData = JSON.parse(cityResponse.getContentText());

        if (municipalitiesData && municipalitiesData.result) {
            municipalitiesData.result.forEach(function(municipality) {
                // 市区町村名をキーとし、都道府県名をバリューとするマップを作成
                municipalityPrefectureMap[municipality.cityName] = prefecture.prefName;
            });
        }  
    });
}

入力住所の都道府県を出力する　
各住所をループし、オブジェクト内のキー名（市町村区名）の文字列が含まれるときにtrueを返し、真横のセルにバリューを返します。

// 各住所をループして市区町村名から都道府県を抽出
for (var i = 0; i < ADRESSES.length; i++) {
var address = ADRESSES[i][0]; // 住所は列の最初の要素
var foundPrefecture = null;
var longestMatchLength = 0; 

// 市区町村名をキーとしてループ
    for (var municipality in municipalityPrefectureMap) {
        if (address.includes(municipality) && municipality.length > longestMatchLength) {
            foundPrefecture = municipalityPrefectureMap[municipality]; // 対応する都道府県名を見つける
            longestMatchLength = municipality.length; // 最も長い合致の長さを更新
    }
}

// 該当する都道府県が見つかった場合、その名前をシートに出力
if (foundPrefecture !== null) {
    SHEET.getRange(STARTROW + i, ADDRESCOLUMN + 1).setValue(foundPrefecture);
    } else {
        // 該当する都道府県が見つからない場合
        SHEET.getRange(STARTROW + i, ADDRESCOLUMN + 1).setValue("都道府県が見つかりません");
    }
}

この処理で工夫した点は、合致する文字列がより多いものあれば処理を上書きするようにしている箇所です。
RESASのAPIから引っ張ってきたキー名（市町村区）の住所の中には、「北区」（※東京）「大阪市北区」、「福岡市北区」が存在します。

合致する文字列が多いものにする処理です。

上記の処理がなければ、北区が出てきた時点でオブジェクト内の中で順番が先の「北区」※東京が選ばれてしまいます。

完成コード

base.gs

    const SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const STARTROW = ⚪︎; // 住所が始まる行
    const ADDRESCOLUMN = ⚪︎; // 住所が含まれる列（例: D列）
    const NUMROWS = SHEET.getLastRow() - STARTROW + 1;
    const ADDRESRANGE = SHEET.getRange(STARTROW, ADDRESCOLUMN, NUMROWS);
    const ADRESSES = ADDRESRANGE.getValues();

main.gs

    function extractPrefectureToColumn() {
    
      var apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
      var prefecturesUrl = 'https://opendata.resas-portal.go.jp/api/v1/prefectures';
      var options = {
        'headers': {'X-API-KEY': apiKey},
        'muteHttpExceptions': true
      };
    
      // 都道府県データを取得
      var response = UrlFetchApp.fetch(prefecturesUrl, options);
      var prefecturesData = JSON.parse(response.getContentText());
      var municipalityPrefectureMap = {};
    
      // 都道府県データと市区町村データを取得し、マッピングを作成
      if (prefecturesData && prefecturesData.result) {
        prefecturesData.result.forEach(function(prefecture) {
          var municipalitiesUrl = 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode=' + prefecture.prefCode;
          var cityResponse = UrlFetchApp.fetch(municipalitiesUrl, options);
          var municipalitiesData = JSON.parse(cityResponse.getContentText());
    
          if (municipalitiesData && municipalitiesData.result) {
            municipalitiesData.result.forEach(function(municipality) {
              // 市区町村名をキーとし、都道府県名をバリューとするマップを作成
              municipalityPrefectureMap[municipality.cityName] = prefecture.prefName;
            });
          }
        });
      }
    
      // 各住所をループして市区町村名から都道府県を抽出
      for (var i = 0; i < ADRESSES.length; i++) {
        var address = ADRESSES[i][0]; // 住所は列の最初の要素
        var foundPrefecture = null;
        var longestMatchLength = 0; 
    
        // 市区町村名をキーとしてループ
        for (var municipality in municipalityPrefectureMap) {
            if (address.includes(municipality) && municipality.length > longestMatchLength) {
                foundPrefecture = municipalityPrefectureMap[municipality]; // 対応する都道府県名を見つける
                longestMatchLength = municipality.length; // 最も長い合致の長さを更新
            }
        }
    
        // 該当する都道府県が見つかった場合、その名前をシートに出力
        if (foundPrefecture !== null) {
          SHEET.getRange(STARTROW + i, ADDRESCOLUMN + 1).setValue(foundPrefecture);
        } else {
          // 該当する都道府県が見つからない場合
          SHEET.getRange(STARTROW + i, ADDRESCOLUMN + 1).setValue("都道府県が見つかりません");
        }
      }
    }

最後に

今回はAPIとGASを使用した業務効率化に取り組んでみましたが、ループ処理の中にループを書いていたり、処理が重たくなるlengthを条件式に加えており、改善の余地はあると思うので、引き続きベストプラクティスを探していきます。