Use a macro ONLYOFFICE para obter e inserir informações de endereço detalhadas

Muitas vezes, trabalhar com dados mal inseridos pode ser um grande obstáculo. Mas e se houver uma maneira de automatizar essa tarefa? Neste artigo, vamos orientá-lo na criação de uma macro que recupera informações detalhadas de endereço com base nos dados básicos de endereço e as insere em uma planilha.

Sobre a API de geocodificação Geoapify

A API de geocodificação Geoapify é uma ferramenta poderosa que permite aos desenvolvedores integrar a funcionalidade de geocodificação em seus aplicativos e serviços. Geocodificação é o processo de conversão de endereços ou nomes de lugares em coordenadas geográficas.

Com a API Geoapify Geocoding, você pode recuperar dados detalhados sobre endereços, incluindo nomes de ruas, nomes de cidades, códigos postais, regiões administrativas e muito mais. E planejamos aproveitar esse recurso e integrá-lo à nossa macro.

Conceito de macro

• Leia o endereço da célula especificada.
• Envie uma solicitação para a Geoapify Geocoding API para buscar os detalhes do endereço.
• Processe a resposta e crie o objeto Address Details.
• Cole os detalhes do endereço na planilha.
• Leia o próximo endereço e repita o processo.

Construindo a macro

Primeiro, declaramos variáveis:

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

A API_KEY a variável contém sua chave de API Geoapify. O ENDPOINT A variável armazena o endpoint da API para o serviço de geocodificação. A variável oWorksheet é direcionada à planilha ativa. E a variável linha permite selecionar a linha desejada.

Em seguida adicionamos a função makeRequest que é responsável por enviar uma requisição para a 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)
    }

Ele usa a função $.ajax para fazer uma solicitação HTTP GET para o terminal, passando o endereço como parâmetro.

Em seguida, a função addressToRequest transforma o endereço em um formato compatível com a 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()
    }

Em seguida, a função createAddressDetailsObject processa a resposta. Se o endereço não for encontrado, uma mensagem de erro será retornada. Caso contrário, a função cria o objeto 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
    }

Para verificar os dados recebidos, usamos a função checkMissingData. Ele substitui os campos ausentes por um traço:

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

Em seguida, colamos os dados com a função pasteAddressDetails. A função usa o objeto oWorksheet para direcionar a planilha ativa e selecionar o intervalo apropriado:

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

Se houver uma mensagem de erro, ela será colada no intervalo:

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

Caso contrário, os detalhes do endereço são preenchidos no intervalo usando a função SetValue:

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

A função então incrementa a linha e chama recursivamente makeRequest para processar o próximo endereço:

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

Depois disso, invocamos a função recarregar. Ele aciona um recálculo da planilha após o processamento de cada endereço:

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

Todo o código da macro é o seguinte:

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

Vamos executar a macro e ver como funciona!

Agora, com esta macro, você pode automatizar sua rotina e recuperar informações detalhadas de endereço sem esforço. Esperamos que você economize tempo e minimize a entrada manual de dados. Este é apenas um dos muitos exemplos do que você pode fazer implementando nossos métodos de API.

Incentivamos você a experimentar e criar suas próprias macros. Não hesite em fazer perguntas ou compartilhar suas ideias conosco. Estamos abertos à discussão e cooperação. Boa sorte em seus esforços exploratórios!