LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ONLYOFFICE

ONLYOFFICEマクロで詳細な住所情報を取得し、スプレッドシートに挿入する方法

Posted at

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

image.png

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つに過ぎません。

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

お役立ちリンク

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?