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

#post_title | ONLYOFFICE 官方博客

处理输入质量不佳的数据常常可能会是一大拦路虎。但是，如果有一种方法可以将这项任务自动化处理呢？在本文中，我们会引导您创建一个宏，根据基本地址数据检索详细的地址信息，并将其插入到电子表格中。
<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 地理编码 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</a> 地理编码 API 是一款强大的工具，开发人员可以使用它将地理编码功能集成到他们的应用和服务中。地理编码就是将地址或地名转换为地理坐标的过程。
您可以使用 Geoapify 地理编码 API，检索地址相关的详细数据，包括街道名、城市名、邮政编码、行政区域等。我们要利用此功能，将其集成到我们的宏中。
<h2>宏的概念</h2>
<ul>
<li>从指定的单元格中读取地址。</li>
<li>向“Geoapify 地理编码 API”发送一条请求，获取地址详细信息。</li>
<li>处理响应，创建地址详细信息对象。</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”变量会以活动电子表格为目标。“行”变量支持用户选择所需的行。
然后，我们添加负责向 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-61895-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 方法执行诸多操作，这只是其中的一个示例。
我们诚挚建议您做尝试、构建您自己的宏。欢迎提出问题或分享您的想法，欢迎您与我们讨论、合作。祝好运！
<div class="useful-links">
<h3>相关链接</h3>
<a href="https://www.geoapify.com/geocoding-api" target="_blank" rel="noopener">Geoapify 地理编码 API</a>
<a href="https://api.onlyoffice.com/plugin/macrosamples" target="_blank" rel="noopener">ONLYOFFICE 宏示例</a>
<a href="https://api.onlyoffice.com/docbuilder/spreadsheetapi" target="_blank" rel="noopener">ONLYOFFICE 电子表格 API</a>
<a href="https://github.com/ONLYOFFICE" target="_blank" rel="noopener">Github 上的 ONLYOFFICE</a><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"> 
</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/zh-hans/2022/11/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宏分析电子表格数据

2023一起重返校园：ONLYOFFICE 教育优惠

ONLYOFFICE 2023 年夏季活动总结