入力が不十分なデータを扱うことは、しばしば大きな障害になるかもしれません。しかし、この作業を自動化する方法があるとしたらどうでしょう？今回は、基本的な住所データをもとに詳細な住所情報を取得し、スプレッドシートに挿入するマクロを作成する方法をご紹介します。

Geoapify Geocoding API について

Geoapify Geocoding APIは、開発者がジオコーディング機能をアプリケーションやサービスに統合するための強力なツールです。ジオコーディングとは、住所や地名を地理的な座標に変換するプロセスです。

Geoapify Geocoding API を使用すると、通り名、都市名、郵便番号、行政地域など、住所に関する詳細なデータを取得することができます。そして、この機能を活用し、マクロに組み込むことを計画しています。

マクロのコンセプト

指定したセルから住所を読み取る。
Geoapify Geocoding API にリクエストを送り、住所の詳細をフェッチする。
レスポンスを処理し、Address Details オブジェクトを作成する。
住所の詳細をスプレッドシートに貼り付ける。
次の住所を読み取り、この処理を繰り返す。

マクロの構築

まず、変数を宣言します：

 const API_KEY = 'your_API_key'
    const ENDPOINT = 'https://api.geoapify.com/v1/geocode/search'
    const oWorksheet = Api.GetActiveSheet()
    let row = 2

API_KEY変数には、Geoapify APIキーが格納されます。ENDPOINT変数は、ジオコーディングサービスのAPIエンドポイントを格納する。oWorksheet 変数は、アクティブなスプレッドシートを対象とします。そして、row変数は、目的の行を選択することができます。

次に、APIへのリクエスト送信を担当するmakeRequest関数を追加します：

makeRequest(oWorksheet.GetRange(`A${row}`).GetText())
 
    // REQUEST
    function makeRequest(ADDRESS) {
        if (ADDRESS === '') return
        $.ajax({
            url: `${ENDPOINT}?text=${addressToRequest(ADDRESS)}&apiKey=${API_KEY}`,
            dataType: 'json',
        }).done(successFunction)
    }

これは、$.ajax関数を使用して、エンドポイントにHTTP GETリクエストを行い、パラメータとしてアドレスを渡します。

そして、addressToRequest関数がアドレスをURLと互換性のある形式に変換します：

function addressToRequest (address) {
        return address.replaceAll(' ', '%20').replaceAll(',', '%2C')
    }

APIリクエストに成功した場合、successFunctionが呼び出されます。この関数では、APIからのレスポンスをパラメーターとして受け取ります：

 function successFunction(response) {
        const data = createAddressDetailsObject(response)
        pasteAddressDetails(data)
        reload()
    }

その後、createAddressDetailsObject関数がその応答を処理する。アドレスが見つからなかった場合は、エラーメッセージが返されます。そうでない場合は、Address Details オブジェクトを作成します：

    // Create Address Details object if address is found
    function createAddressDetailsObject(response) {
        if (response.features.length === 0) {
            return { error: 'Address not found' }
        }
        let data = {
            country: response.features[0].properties.country,
            county: response.features[0].properties.county,
            city: response.features[0].properties.city,
            post_code: response.features[0].properties.postcode,
            full_address_line: response.features[0].properties.formatted
        }
        data = checkMissingData(data)
        return data
    }

受信したデータを確認するために、checkMissingData関数を使用します。これは、欠落したフィールドをダッシュに置き換えるものです：

 function checkMissingData(data) {
        Object.keys(data).forEach(key => {
            if(data[key] === undefined) data[key] = '-'
        })
        return data
    }

次に、pasteAddressDetails関数でデータを貼り付けます。この関数は、oWorksheetオブジェクトを使用してアクティブなシートをターゲットにし、適切な範囲を選択します：

function pasteAddressDetails(data) {
        const oRange = oWorksheet.GetRange(`B${row}:F${row}`)
エラーメッセージがある場合は、その範囲に貼り付けられます：

 if (data.error !== undefined) {
            oRange.SetValue([[data.error]])
        }

それ以外の場合は、SetValue 関数を使用して、アドレスの詳細を範囲内に入力します：

 else {
            oRange.SetValue([
                [data.country],
                [data.county],
                [data.city],
                [data.post_code],
                [data.full_address_line]
            ])
        }

次に、この関数は行をインクリメントし、次のアドレスを処理するためにmakeRequestを再帰的に呼び出します：

 // Execute recursively until "Address" value is empty
        row++
        makeRequest(oWorksheet.GetRange(`A${row}:A${row}`).GetText())
    }

その後、reload関数を呼び出します。これは、各アドレスが処理された後、スプレッドシートの再計算をトリガーします：

 function reload() {
        let reload = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);
        })
    }

マクロのコード全体は以下の通りです：

(function()
{
    const API_KEY = 'your_API_key'
    const ENDPOINT = 'https://api.geoapify.com/v1/geocode/search'
    const oWorksheet = Api.GetActiveSheet()
    let row = 2
    makeRequest(oWorksheet.GetRange(`A${row}`).GetText())
    
    
    // REQUEST
    function makeRequest(ADDRESS) {
        if (ADDRESS === '') return
        $.ajax({
            url: `${ENDPOINT}?text=${addressToRequest(ADDRESS)}&apiKey=${API_KEY}`,
            dataType: 'json',
        }).done(successFunction)
    }
    // London, United Kingdom -> London%2C%20United%20Kingdom
    function addressToRequest (address) {
        return address.replaceAll(' ', '%20').replaceAll(',', '%2C')
    }
    
    
    // RESPONSE
    function successFunction(response) {
        const data = createAddressDetailsObject(response)
        pasteAddressDetails(data)
        reload()
    }
    // Create Address Details object if address is found
    function createAddressDetailsObject(response) {
        if (response.features.length === 0) {
            return { error: 'Address not found' }
        }
        let data = {
            country: response.features[0].properties.country,
            county: response.features[0].properties.county,
            city: response.features[0].properties.city,
            post_code: response.features[0].properties.postcode,
            full_address_line: response.features[0].properties.formatted
        }
        data = checkMissingData(data)
        return data
    }
    // Replace missing fields with '-'
    function checkMissingData(data) {
        Object.keys(data).forEach(key => {
            if(data[key] === undefined) data[key] = '-'
        })
        return data
    }
    
    
    // PASTE
    function pasteAddressDetails(data) {
        const oRange = oWorksheet.GetRange(`B${row}:F${row}`)
        if (data.error !== undefined) {
            oRange.SetValue([[data.error]])
        } else {
            oRange.SetValue([
                [data.country],
                [data.county],
                [data.city],
                [data.post_code],
                [data.full_address_line]
            ])
        }
        // Execute recursively until "Address" value is empty
        row++
        makeRequest(oWorksheet.GetRange(`A${row}:A${row}`).GetText())
    }
    
    // Sheet has to be reloaded on changes
    function reload() {
        let reload = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);
        })
    }
})();

このマクロを使うことで、ルーチンを自動化し、詳細な住所情報を楽に取得することができます。このマクロを使うことで、手動でのデータ入力を最小限に抑え、時間を節約することができるようになります。これは、私たちのAPIメソッドを実装することでできることの多くの例の1つに過ぎません。

ぜひ、独自のマクロを構築してください。質問したり、アイデアを共有することを躊躇しないでください。私たちは、議論と協力に常にオープンです。

お役立ちリンク