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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2655664/06a31c7b-a1c0-ae0f-ab60-74dd836e62ba.png)

## Geoapify Geocoding API について

[Geoapify Geocoding API](https://www.geoapify.com/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つに過ぎません。

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

## お役立ちリンク

* [Geoapify Geocoding API](https://www.geoapify.com/geocoding-api)
* [ONLYOFFICE マクロサンプル](https://api.onlyoffice.com/plugin/macrosamples?utm_source=qiita&utm_medium=article+&utm_id=geocoding_spreadsheet_macro&_ga=2.99224858.568922795.1688981205-2010401340.1681826884)
* [ONLYOFFICE Spreadsheet API](https://api.onlyoffice.com/docbuilder/spreadsheetapi?utm_source=qiita&utm_medium=article+&utm_id=geocoding_spreadsheet_macro&_ga=2.132803210.568922795.1688981205-2010401340.1681826884)
* [ONLYOFFICEのGithubページ](https://github.com/ONLYOFFICE)
* [ONLYOFFICEマクロのYouTubeチュートリアル](https://www.youtube.com/playlist?list=PLCF48HEKMOYNBygGjr9lSw3PLpVXQ6s_t&utm_source=qiita&utm_medium=article+&utm_id=geocoding_spreadsheet_macro)
* [ONLYOFFICEマクロで空気質指数データを監視する方法](https://www.onlyoffice.com/blog/2022/09/monitor-air-quality-data-with-onlyoffice-macro?utm_source=qiita&utm_medium=article+&utm_id=geocoding_spreadsheet_macro)