Use ONLYOFFICE macro to obtain and insert detailed address 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.

 

About Geoapify Geocoding API

Geoapify 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.

Macro concept

• Read the address from the specified cell.
• Send a request to the Geoapify Geocoding API to fetch the address details.
• Process the response and create the Address Details object.
• Paste the address details into the spreadsheet.
• Read the next address and repeat the process.

Building the macro

First, we declare variables:

    const API_KEY = 'your_API_key'
    const ENDPOINT = 'https://api.geoapify.com/v1/geocode/search'
    const oWorksheet = Api.GetActiveSheet()
    let row = 2

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:

  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)
    }

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:

function addressToRequest (address) {
        return address.replaceAll(' ', '%20').replaceAll(',', '%2C')
    }

If the API request is successful, the successFunction is called. It receives the response from the API as a parameter:

  function successFunction(response) {
        const data = createAddressDetailsObject(response)
        pasteAddressDetails(data)
        reload()
    }

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:

    // 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
    }

To check the received data, we use the checkMissingData function. It replaces the missing fields with a dash:

 function checkMissingData(data) {
        Object.keys(data).forEach(key => {
            if(data[key] === undefined) data[key] = '-'
        })
        return data
    }

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:

function pasteAddressDetails(data) {
        const oRange = oWorksheet.GetRange(`B${row}:F${row}`)

If there is an error message, it is pasted into the range:

 if (data.error !== undefined) {
            oRange.SetValue([[data.error]])
        } 

Otherwise, the address details are populated in the range using the SetValue function:

  } else {
            oRange.SetValue([
                [
                    data.country,
                    data.county,
                    data.city,
                    data.post_code,
                    data.full_address_line
                ]
            ]);
        }

The function then increments the row and recursively calls makeRequest to process the next address:

 // Execute recursively until "Address" value is empty
        row++
        makeRequest(oWorksheet.GetRange(`A${row}:A${row}`).GetText())
    }

After that, we invoke the reload function. It triggers a recalculation of the spreadsheet after each address is processed:

 function reload() {
        let reload = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);
        })
    }

The entire macro code is the following:

(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' }
        }
        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 => {
            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);
        })
    }
})();

Let’s run the macro and see how it works!

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!