使用 ONLYOFFICE 宏获取和插入详细的地址信息

2023年06月09日作者：Alina

处理输入质量不佳的数据常常可能会是一大拦路虎。但是，如果有一种方法可以将这项任务自动化处理呢？在本文中，我们会引导您创建一个宏，根据基本地址数据检索详细的地址信息，并将其插入到电子表格中。

Use ONLYOFFICE macro to obtain and insert detailed address information

关于 Geoapify 地理编码 API

Geoapify 地理编码 API 是一款强大的工具，开发人员可以使用它将地理编码功能集成到他们的应用和服务中。地理编码就是将地址或地名转换为地理坐标的过程。

您可以使用 Geoapify 地理编码 API，检索地址相关的详细数据，包括街道名、城市名、邮政编码、行政区域等。我们要利用此功能，将其集成到我们的宏中。

宏的概念

从指定的单元格中读取地址。
向“Geoapify 地理编码 API”发送一条请求，获取地址详细信息。
处理响应，创建地址详细信息对象。
将地址详细信息粘贴到电子表格中。
读取下一个地址并重复该过程。

构建宏

首先，我们要声明变量：

    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”变量会以活动电子表格为目标。“行”变量支持用户选择所需的行。

然后，我们添加负责向 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);
        })
    }
})();