使用 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);
        })
    }
})();

现在让我们来运行一下宏,看看效果如何!

现在,您可以使用这个宏,让各种例行事务自动更新,轻松检索详细地址信息。我们希望它能帮您节约些时间,最大程度减少手动数据输入。您可以通过实施我们的 API 方法执行诸多操作,这只是其中的一个示例。

我们诚挚建议您做尝试、构建您自己的宏。欢迎提出问题或分享您的想法,欢迎您与我们讨论、合作。祝好运!

创建免费的 ONLYOFFICE 账户

在线查看并协作编辑文本文档、电子表格、幻灯片、表单和 PDF 文件。