In this article, we will show you how to create a macro that takes the basic address data from the spreadsheet and completes it with the detailed information.

Quite often, working with poorly entered data might be a huge hindrance. But what if there’s a way to automate this task? In this article we will guide you through creating a macro that retrieves detailed address information based on the basic address data and inserts it into a spreadsheet.
<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" />
&nbsp;
<h2>About 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</a> Geocoding API is a powerful tool that enables developers to integrate geocoding functionality into their applications and services. Geocoding is the process of converting addresses or place names into geographical coordinates.
With the Geoapify Geocoding API, you can retrieve detailed data about addresses, including street names, city names, postal codes, administrative regions, and more. And we plan to take advantage of this feature and integrate it into our macro.
<h2>Macro concept</h2>
<ul>
<li>Read the address from the specified cell.</li>
<li>Send a request to the Geoapify Geocoding API to fetch the address details.</li>
<li>Process the response and create the Address Details object.</li>
<li>Paste the address details into the spreadsheet.</li>
<li>Read the next address and repeat the process.</li>
</ul>
<h2>Building the macro</h2>
First, we declare variables:
<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>
The API_KEY variable holds your Geoapify API key. The ENDPOINT variable stores the API endpoint for the geocoding service. The oWorksheet variable targets the active spreadsheet. And the row variable allows selecting the desired row.
Then we add the makeRequest function that is responsible for sending a request to the API:
<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>
It uses the $.ajax function to make an HTTP GET request to the endpoint, passing the address as a parameter.
Then the addressToRequest function transforms the address into a format compatible with the URL:
<pre class="theme:github font:monospace lang:js decode:true ">function addressToRequest (address) {
        return address.replaceAll(' ', '%20').replaceAll(',', '%2C')
    }
</pre>
If the API request is successful, the successFunction is called. It receives the response from the API as a parameter:
<pre class="theme:github font:monospace lang:js decode:true">  function successFunction(response) {
        const data = createAddressDetailsObject(response)
        pasteAddressDetails(data)
        reload()
    }
</pre>
Then the createAddressDetailsObject function processes the response. If the address is not found, an error message is returned. Otherwise, the function creates the Address Details object:
<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>
To check the received data, we use the checkMissingData function. It replaces the missing fields with a dash:
<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>
Then we paste the data with the pasteAddressDetails function. The function uses the oWorksheet object to target the active sheet and select the appropriate range:
<pre class="theme:github font:monospace lang:js decode:true ">function pasteAddressDetails(data) {
        const oRange = oWorksheet.GetRange(`B${row}:F${row}`)
</pre>
If there is an error message, it is pasted into the range:
<pre class="theme:github font:monospace lang:js decode:true"> if (data.error !== undefined) {
            oRange.SetValue([[data.error]])
        } 
</pre>
Otherwise, the address details are populated in the range using the SetValue function:
<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>
The function then increments the row and recursively calls makeRequest to process the next address:
<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>
After that, we invoke the reload function. It triggers a recalculation of the spreadsheet after each address is processed:
<pre class="theme:github font:monospace lang:js decode:true "> function reload() {
        let reload = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);
        })
    }
</pre>
The entire macro code is the following:
<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' }
 }
 console.log(response)
 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>
Let’s run the macro and see how it works!
<div style="width: 640px;" class="wp-video">
<video class="wp-video-shortcode" id="video-61789-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>
Now, with this macro, you can automate your routine and retrieve detailed address information effortlessly. We hope it will save you time and minimize manual data entry. This is just one of many examples of what you can do by implementing our API methods.
We kindly encourage you to experiment and build your own macros. Don’t hesitate to ask questions or share your ideas with us. We are open to discussion and cooperation. Best of luck in your exploratory endeavors!
<div class="useful-links">
<h3>Useful links</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 Macro samples</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 on 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">YouTube tutorial on ONLYOFFICE macros</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">Monitor air qualify data with ONLYOFFICE macro</a>
</div>

Use ONLYOFFICE macro to obtain and insert detailed address information

Recent posts

Use ONLYOFFICE macro to analyze spreadsheet data

Back to school 2023 with ONLYOFFICE: discounts for education

Best AI writers 2023