ONLYOFFICEマクロで詳細な住所情報を取得し、スプレッドシートに挿入する方法
入力が不十分なデータを扱うことは、しばしば大きな障害になるかもしれません。しかし、この作業を自動化する方法があるとしたらどうでしょう？今回は、基本的な住所データをもとに詳細な住所情報を取得し、スプレッドシートに挿入するマクロを作成する方法をご紹介します。
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つに過ぎません。
ぜひ、独自のマクロを構築してください。質問したり、アイデアを共有することを躊躇しないでください。私たちは、議論と協力に常にオープンです。
