Anúncio

Collapse
No announcement yet.

Planilha para Rastreio de Muitas Encomendas

Collapse
X
 
  • Filter
  • Hora
  • Show
Clear All
new posts

  • Planilha para Rastreio de Muitas Encomendas

    Já tem um tempo que fiz uma planilha no Google Docs inspirada no programa criado pelo usuário Roknauta em 2018, que foi originada de uma discussão no antigo tópico "Postem aqui seus números de rastreio". A primeira versão era utilizando macros (que era bem simples) mas hoje eu decidi fazer uma versão mais simples, compartilhar com vocês e também explicar como ela funciona, não só para vocês saberem como ela funciona mas também para ter um material útil caso alguém queira fazer algo parecido.

    Aqui está o link da planilha compartilhada, este link está como "apenas visualização" a fim de preservar a versão compartilhada, logo para ter sua planilha você vai ter que abrir o link e depois ir em Arquivo -> Fazer uma cópia (Deve estar logado com uma conta do Google)

    https://docs.google.com/spreadsheets...it?usp=sharing

    ---------------------------------------------------
    Como utilizar:
    ---------------------------------------------------
    Para usar, é só colar o seu código de rastreio XXNNNNNNNNNXX padrão UPU na célula D1, recomendo colar sem formatação (ctrl+shift+v) para não bagunçar a planilha;

    A planilha automaticamente atualiza os códigos da tabela para os próximos, mostrando a data/local do último status e o último status da encomenda.

    Como só é possível pegar o último status, todos os códigos aparecem com o formato de link para uma página onde dá pra ver todo o rastreamento completo.

    Uma boa ideia é manter essa planilha principal com várias páginas. Para isso é só duplicar a página principal e ir renomeando para o produto da encomenda.

    ---------------------------------------------------
    FAQ (Perguntas Mais Frequentes):
    ---------------------------------------------------

    P: Pra que serve isso?
    R: É uma forma de verificar como os pacotes com código de rastreio emitidos "próximos" ao seu estão sendo movimentados. Basicamente, a partir do código de rastreio de entrada, são analisados "N" códigos anteriores e posteriores a ele (mais detalhes na explicação de como a planilha funciona), e automaticamente o serviço de rastreamento é executado para cada um desses códigos. Uma coisa interessante é que as vezes dá pra identificar um padrão, exemplo: Encomendas que chegam

    P: Todos os códigos gerados tem rastreio?
    R: Não, alguns códigos gerados não aparecem como sendo encomendas (na planilha eles aparecem como #N/A, ou seja, o serviço de rastreamento não conseguiu encontrar uma encomenda com o código gerado). Não sei falar o critério que eles usam, mas uma coisa interessante é que se eu coloco um código de entrada destinado ao Brasil, os códigos gerados são destinados ao Brasil também.

    P: Por que fez isso em uma planilha do Google Docs?
    R: É acessível, de graça e você consegue usar no app!

    P: Por que demora para atualizar?
    R: Pelo que identifiquei até agora: Internet lenta, serviço do Google demorando para responder (algumas funções da planilha são executadas remotamente, não no seu computador), mais de uma pessoa acessando sua planilha (ex: Acesso pelo computador e pelo smartphone ao mesmo tempo). Também, o valor de códigos gerados está fixado em 50 (25 anteriores e 25 posteriores ao código de entrada), e para cada um a planilha acessa o serviço de rastreio para pegar os dados, o que pode demorar. Se demorar demais, recomendo tentar mais tarde.

    ---------------------------------------------------
    Como funciona:
    ---------------------------------------------------

    Esta sessão é justamente para manter a continuidade dessa ferramenta bastante útil e também dar suporte para quem quiser modificar a planilha e/ou saber como ela funciona.

    ------------------------
    1. Geração dos códigos próximos
    ------------------------

    Essa é a base do algoritmo. Os códigos de rastreio do padrão UPU são compostos da seguinte forma:

    2 letras indicando o serviço + 8 dígitos do número de série + 1 dígito verificador do número de série + 2 letras indicando o país de onde a encomenda foi postada.

    Basicamente a ideia é manter as letras (para pegar os pacotes do mesmo tipo de serviço e mesma origem), alterar o número de série (simplesmente somar e subtrair do número de série original) e gerar um novo dígito verificador.

    1.1 Extraindo o número serial
    É o mais simples, aqui na planilha usei a função MID para tirar o número de série do código de entrada.

    MID($D$1;3;8)

    1.2 Alterando o número de série
    Na planilha tem uma coluna chamada de "Posição Relativa", que é o valor que vai ser somado ao número de série para criar o novo código. Para isso, usei a função MID.

    MID($D$1;3;8)+A7

    1.3 Gerando o novo dígito verificador
    Essa é a parte chata. Na primeira versão eu fiz esta planilha utilizando macros, o que deixa tudo bem mais simples. Implementar direto na planilha dá mais velocidade na hora de gerar os códigos, mas a fórmula fica enorme.
    O código segue o padrão do link da Wikipédia que citei anteriormente, mas caso ele vá pro espaço, aqui tá uma explicação em português:

    - Para cada um dos 8 dígitos do número serial, são atribuídos 8 pesos (8,6,4,2,3,5,9,7) da direita para a esquerda.
    - Primeiro calcula-se S, a soma de cada dígito multiplicado pelo seu peso.
    Por exemplo, o código LB 47312482 9 CN:
    S = 4*8 + 7*6 + 3*4 + 1*2 + 2*3 + 4*5 + 8*9 + 2*7 = 200
    - Depois calcula-se o dígito de verificação C, da equação C = 11 - (S mod 11)
    * mod aqui significa que você quer o resto da divisão de S por 11

    Se C = 10, mude C para 0
    Se C = 11, mude C para 5

    Da nossa continha da soma, C = 11 - (200 mod 11) = 11 - 2 = 9.

    Na planilha, para calcular a soma eu usei a função MID em cima do número de série gerado anteriormente:

    S = MOD(MID(MID($D$1;3;8)+A7;1;1)*8 + MID(MID($D$1;3;8)+A7;2;1)*6 + MID(MID($D$1;3;8)+A7;3;1)*4 + MID(MID($D$1;3;8)+A7;4;1)*2 + MID(MID($D$1;3;8)+A7;5;1)*3 + MID(MID($D$1;3;8)+A7;6;1)*5 + MID(MID($D$1;3;8)+A7;7;1)*9 + MID(MID($D$1;3;8)+A7;8;1)*7

    Para calcular C eu usei a função MOD

    C 11 - MOD(S;11)

    Para checar o valor de C, usei funções IF aninhadas

    IF(C=10;0;IF(C=11;5;C))

    Para juntar o código de novo, usei as funções LEFT e RIGHT para pegar os caracteres do código de entrada e CONCATENATE para juntar tudo como um único código.

    CONCATENATE(left($D$1;2);MID($D$1;3;8)+A7;C;right($D$1;2))

    Para transformar em link, usei a função HYPERLINK.

    Eu resumi bem essas funções, se você for olhar lá vai ver a zona que ficou! (Provavelmente tinha uma maneira mais fácil, mas de todo jeito esse foi o jeito mais rápido que eu consegui fazer).

    ------------------------
    2. Puxando os dados de rastreio para a planilha do Google
    ------------------------

    Agora é que as coisas ficam interessantes. Fazendo um curso de Python, descobri a técnica de "Web Scraping" basicamente é você ter um programa que vai acessar um site e tirar informações de lá, por exemplo, acessar todos os dias um site de uma loja e pegar o preço de um produto pra ver se ele está sendo alterado, monitorar quantos seguidores alguém tem no twitter etc. Nisso, pensei na possibilidade se dava pra fazer o mesmo no Excel e por fim nas planilhas do Google, e dependendo do site é possível!.

    Então, o melhor site que achei foi o Rastreamento Correios WEBSRO, um site particular em que é possível colocar o código de rastreio no próprio link para obter os dados de rastreamento.

    2.1 Gerando o link
    O próprio site dá um exemplo, é necessário que o link tenha o seguinte formato: http://www.websro.com.br/rastreament...=XXXXXXXXXXXXX onde XXXXXXXXXXXXX é o código de rastreamento. Para isso usei a função CONCATENATE, com o começo do link e o código gerado.

    CONCATENATE("https://www.websro.com.br/rastreamento-correios.php?P_COD_UNI=";D1)

    2.2 Pegando os dados do site
    As planilhas do Google tem 2 funções que puxam os dados de um link: IMPORTXML e IMPORTHTML. A IMPORTXML é mais geral e mais chata de mexer, enquanto que a IMPORTHTML é mais simples de mexer mas nem sempre pega tudo. A primeira coisa a saber é que é um trabalho de tentativa e erro. Nem sempre dá pra pegar os dados que te interessam de um link, pois o site não é acessado da mesma forma que um navegador, então nem sempre vai apresentar para a planilha a mesma página que apresenta para você.

    Aqui, foi simples. Quando você acessa o link do websro com o código de rastreio, é gerada página com uma tabela com os dados de rastreio. Então depois de tentativa e erro, consegui puxar os dados da data e status com a função IMPORTHTML.
    Aqui eu puxo a tabela completa
    IMPORTHTML(CONCAT("https://www.websro.com.br/rastreamento-correios.php?P_COD_UNI=";B7);"table";1)
    Usando a função INDEX eu consigo pegar a célula que eu quero da tabela
    INDEX(IMPORTHTML(CONCAT("https://www.websro.com.br/rastreamento-correios.php?P_COD_UNI=";B7);"table";1);2;2)

    ------------------------
    3. Gerando a planilha
    ------------------------

    O mais fácil de tudo. Basta botar quase todas as referências de células na forma relativa e copiar pela planilha abaixo.

  • #2
    Muito bom!! Valeu!! Percebi que as vezes demora um pouquinho para carregar os status, mas de fato é muito conveniente.

    Comentário


    • #3
      Postado originalmente por dimitri Ver Post
      Muito bom!! Valeu!! Percebi que as vezes demora um pouquinho para carregar os status, mas de fato é muito conveniente.
      Sim, a maior desvantagem é a demora que acontece às vezes. Uma solução é apagar as linhas que tem os códigos gerados (por exemplo, você pode deixar uns 15 antes e uns 15 depois). Eu gosto de deixar umas 50 porque é só pra tirar a dúvida mesmo de como está o meu pacote, então de certa forma vale a pena esperar.

      Comentário


      • #4
        Fernando, acho que encontrei um bug com a planilha. Alguns códigos parecem causar um erro no cálculo do check digit. Um exemplo é o código PW029901023BR, que aparece como PW29901025BR (código inválido) na posição relativa zero. Todos os outros códigos também aparecem com check digit errado, resultando numa planilha sem dado algum.
        Edit: acho que pode estar relacionado com o fato do código começar com zero, mas é só um palpite no escuro.

        EDIT2: Problema resolvido! Dei uma fuçada e consegui corrigir o erro! Basicamente, alguma das funções estava omitindo os zeros à esquerda, gerando esse erro. Consegui corrigir por meio da função TEXT, fazendo com que os números sejam tratados como texto, preservando, assim, os zeros à esquerda. Quem quiser conferir, vai o link da planilha corrigida: https://docs.google.com/spreadsheets...it?usp=sharing
        Last edited by dimitri; 11/02/2020, 00:07.

        Comentário

        Working...
        X