Planilha para Rastreio de Muitas Encomendas - Importa Brasil
Planilha para Rastreio de Muitas Encomendas
15/01/2020, 14:28
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, de forma a divulgar um material útil caso alguém queira fazer algum programa com essa função.
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...7y4/edit#gid=0 (Versão corrigida pelo usuário dimitri 10/02/2020, muito obrigado por identificar e corrigir o bug dimitri )
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.
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 começam a ser liberadas para a entrega podem seguir uma certa ordem, onde as que chegam no Brasil no dia 10 são liberadas na segunda, as do dia 11 são liberadas na terça e por aí vai... Esse é um padrão que pelo menos até o fim de 2019 eu via acontecer (já tem um tempo que não faço importações).
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.
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 consegue pegar mais coisas dos sites, porém é mais chata de mexer, enquanto que a IMPORTHTML é mais simples de mexer mas nem sempre pega o que você precisa, aí você recorre a IMPORTXML. Em ambas você faz um trabalho de tentativa e erro, vendo se é ou não possível retirar a informação que você precisa e também como tratar essa informação para ser melhor exibida na planilha. 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 apresentaria no Chrome ou outro navegador. Um exemplo disso são sites muito dinâmicos, geralmente você consegue pegar informações de menu, cabeçalho e rodapé mas não consegue pegar conteúdo, como notícias, dados de produtos em uma loja virtual etc.
Ainda bem que nessa aplicação foi simples, se não tinha escrito esse textão pra nada kkk. 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.
Last edited by FernandoJSM; 05/04/2020, 13:34.
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.
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; 10/02/2020, 23:07.
03/04/2020, 23:14
Nossa, muito obrigado por identificar e corrigir esse bug! Desde que uso a planilha não peguei um rastreio que começasse com zero, então ia demorar um tempo pra achar ele rs. Ah, e me desculpa a demora para responder aqui Dimitri, nesses últimos meses não acessei o ImportaBR.
kykomizereh
Estava procurando exatamente isso, muito obrigado Fernando. Vai ser bastante útil para meu controle.