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

入力が不十分なデータを扱うことは、しばしば大きな障害になるかもしれません。しかし、この作業を自動化する方法があるとしたらどうでしょう？今回は、基本的な住所データをもとに詳細な住所情報を取得し、スプレッドシートに挿入するマクロを作成する方法をご紹介します。
<img decoding="async" loading="lazy" class="aligncenter wp-image-61811" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros.png" alt="Use ONLYOFFICE macro to obtain and insert detailed address information" width="736" height="372" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros.png 1470w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros-300x151.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros-1024x517.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros-768x388.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros-634x320.png 634w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08135145/Geocoding-API-Macros-353x178.png 353w" sizes="(max-width: 736px) 100vw, 736px" />
<h2>Geoapify Geocoding API について</h2>
<a href="https://www.geoapify.com/geocoding-api?utm_source=blog&amp;utm_medium=article+&amp;utm_id=geocoding_spreadsheet_macro" target="_blank" rel="noopener">Geoapify Geocoding API</a>は、開発者がジオコーディング機能をアプリケーションやサービスに統合するための強力なツールです。ジオコーディングとは、住所や地名を地理的な座標に変換するプロセスです。
Geoapify Geocoding API を使用すると、通り名、都市名、郵便番号、行政地域など、住所に関する詳細なデータを取得することができます。そして、この機能を活用し、マクロに組み込むことを計画しています。
<h2>マクロのコンセプト</h2>
<ul>
<li>指定したセルから住所を読み取る。</li>
<li>Geoapify Geocoding API にリクエストを送り、住所の詳細をフェッチする。</li>
<li>レスポンスを処理し、Address Details オブジェクトを作成する。</li>
<li>住所の詳細をスプレッドシートに貼り付ける。</li>
<li>次の住所を読み取り、この処理を繰り返す。</li>
</ul>
<h2>マクロの構築</h2>
まず、変数を宣言します：
<pre class="theme:github font:monospace lang:js decode:true">   const API_KEY = 'your_API_key'
    const ENDPOINT = 'https://api.geoapify.com/v1/geocode/search'
    const oWorksheet = Api.GetActiveSheet()
    let row = 2
</pre>
API_KEY変数には、Geoapify APIキーが格納されます。ENDPOINT変数は、ジオコーディングサービスのAPIエンドポイントを格納する。oWorksheet 変数は、アクティブなスプレッドシートを対象とします。そして、row変数は、目的の行を選択することができます。
次に、APIへのリクエスト送信を担当するmakeRequest関数を追加します：
<pre class="theme:github font:monospace lang:js decode:true">  makeRequest(oWorksheet.GetRange(`A${row}`).GetText())
 
    // REQUEST
    function makeRequest(ADDRESS) {
        if (ADDRESS === '') return
        $.ajax({
            url: `${ENDPOINT}?text=${addressToRequest(ADDRESS)}&amp;apiKey=${API_KEY}`,
            dataType: 'json',
        }).done(successFunction)
    }
</pre>
これは、$.ajax関数を使用して、エンドポイントにHTTP GETリクエストを行い、パラメータとしてアドレスを渡します。
そして、addressToRequest関数がアドレスをURLと互換性のある形式に変換します：
<pre class="theme:github font:monospace lang:js decode:true ">function addressToRequest (address) {
        return address.replaceAll(' ', '%20').replaceAll(',', '%2C')
    }
</pre>
APIリクエストに成功した場合、successFunctionが呼び出されます。この関数では、APIからのレスポンスをパラメーターとして受け取ります：
<pre class="theme:github font:monospace lang:js decode:true ">  function successFunction(response) {
        const data = createAddressDetailsObject(response)
        pasteAddressDetails(data)
        reload()
    }
</pre>
その後、createAddressDetailsObject関数がその応答を処理する。アドレスが見つからなかった場合は、エラーメッセージが返されます。そうでない場合は、Address Details オブジェクトを作成します：
<pre class="theme:github font:monospace lang:js decode:true ">    // 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
    }
</pre>
受信したデータを確認するために、checkMissingData関数を使用します。これは、欠落したフィールドをダッシュに置き換えるものです：
<pre class="theme:github font:monospace lang:js decode:true "> function checkMissingData(data) {
        Object.keys(data).forEach(key =&gt; {
            if(data[key] === undefined) data[key] = '-'
        })
        return data
    }
</pre>
次に、pasteAddressDetails関数でデータを貼り付けます。この関数は、oWorksheetオブジェクトを使用してアクティブなシートをターゲットにし、適切な範囲を選択します：
<pre class="theme:github font:monospace lang:js decode:true ">function pasteAddressDetails(data) {
        const oRange = oWorksheet.GetRange(`B${row}:F${row}`)
</pre>
エラーメッセージがある場合は、その範囲に貼り付けられます：
<pre class="theme:github font:monospace lang:js decode:true "> if (data.error !== undefined) {
            oRange.SetValue([[data.error]])
        } 
</pre>
それ以外の場合は、SetValue 関数を使用して、アドレスの詳細を範囲内に入力します：
<pre class="theme:github font:monospace lang:js decode:true "> else {
            oRange.SetValue([
                [data.country],
                [data.county],
                [data.city],
                [data.post_code],
                [data.full_address_line]
            ])
        }</pre>
次に、この関数は行をインクリメントし、次のアドレスを処理するためにmakeRequestを再帰的に呼び出します：
<pre class="theme:github font:monospace lang:js decode:true "> // Execute recursively until "Address" value is empty
        row++
        makeRequest(oWorksheet.GetRange(`A${row}:A${row}`).GetText())
    }
</pre>
その後、reload関数を呼び出します。これは、各アドレスが処理された後、スプレッドシートの再計算をトリガーします：
<pre class="theme:github font:monospace lang:js decode:true"> function reload() {
        let reload = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);
        })
    }
</pre>
マクロのコード全体は以下の通りです：
<pre class="theme:github font:monospace lang:js decode:true ">(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)}&amp;apiKey=${API_KEY}`,
            dataType: 'json',
        }).done(successFunction)
    }
    // London, United Kingdom -&gt; 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 =&gt; {
            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);
        })
    }
})();
</pre>
マクロを実行し、その動作を確認してみましょう！
<div style="width: 640px;" class="wp-video">
<video class="wp-video-shortcode" id="video-61881-1" width="640" height="335" preload="metadata" controls="controls"><source type="video/webm" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08124014/Geocoding.webm?_=1" /><a href="https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08124014/Geocoding.webm">https://static-blog.onlyoffice.com/wp-content/uploads/2023/06/08124014/Geocoding.webm</a></video></div>
このマクロを使うことで、ルーチンを自動化し、詳細な住所情報を楽に取得することができます。このマクロを使うことで、手動でのデータ入力を最小限に抑え、時間を節約することができるようになります。これは、私たちのAPIメソッドを実装することでできることの多くの例の1つに過ぎません。
ぜひ、独自のマクロを構築してください。質問したり、アイデアを共有することを躊躇しないでください。私たちは、議論と協力に常にオープンです。
<div class="useful-links">
<h3>お役立ちリンク</h3>
<a href="https://www.geoapify.com/geocoding-api?utm_source=blog&amp;utm_medium=article+&amp;utm_id=geocoding_spreadsheet_macro" target="_blank" rel="noopener">Geoapify Geocoding API</a>
<a href="https://api.onlyoffice.com/plugin/macrosamples?utm_source=blog&amp;utm_medium=article+&amp;utm_id=geocoding_spreadsheet_macro" target="_blank" rel="noopener">ONLYOFFICE マクロサンプル</a>
<a href="https://api.onlyoffice.com/docbuilder/spreadsheetapi?utm_source=blog&amp;utm_medium=article+&amp;utm_id=geocoding_spreadsheet_macro" target="_blank" rel="noopener">ONLYOFFICE Spreadsheet API</a>
<a href="https://github.com/ONLYOFFICE" target="_blank" rel="noopener">ONLYOFFICEのGithubページ</a>
<a href="https://www.youtube.com/playlist?list=PLCF48HEKMOYNBygGjr9lSw3PLpVXQ6s_t&amp;utm_source=blog&amp;utm_medium=article+&amp;utm_id=geocoding_spreadsheet_macro" target="_blank" rel="noopener">ONLYOFFICEマクロのYouTubeチュートリアル</a>
<a href="https://www.onlyoffice.com/blog/2022/09/monitor-air-quality-data-with-onlyoffice-macro?utm_source=blog&amp;utm_medium=article+&amp;utm_id=geocoding_spreadsheet_macro" target="_blank" rel="noopener">ONLYOFFICEマクロで空気質指数データを監視する方法</a>
</div>

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

最新情報

ONLYOFFICEマクロを使ってスプレッドシートデータを分析する

Pflasterstube KaiserslauternがONLYOFFICEとMattermostを利用し、どのように人々を支援しているか

ONLYOFFICEと一緒に新学年を向かう：教育向け割引