CanalQb - Google Apps Script - Preenchendo automaticamente formulas no Google Sheet
Hoje vamos explicar um script útil e prático que pode ser usado para automatizar o preenchimento de células em uma planilha do Google Sheets. Esse script foi desenvolvido para funcionar em uma planilha específica chamada "aliexpress", mas também forneceremos informações sobre como adaptá-lo para outras planilhas.
Antes de mergulharmos nos detalhes do script, vamos entender brevemente o que ele faz:
1. Desativar/Ativar o Cálculo Automático
O script possui duas funções: desativarCalculoAutomatico() e ativarCalculoAutomatico(). A primeira desativa o cálculo automático de fórmulas na planilha, o que é útil quando estamos realizando um preenchimento em massa, pois evita cálculos desnecessários e melhora a performance do script. A segunda função, por sua vez, ativa o cálculo automático após o preenchimento.
2. Função getNomeColuna(numeroColuna)
Essa função simples recebe um número de coluna como entrada e retorna o nome da coluna correspondente. Por exemplo, se passarmos o número 3, a função retornará "C".
3. Função preencherCelulasVazias()
Esta é a função principal do script, que automatiza o preenchimento de células vazias na planilha "aliexpress". O preenchimento é feito com base em algumas regras específicas para cada coluna. Vamos entender o que acontece dentro dessa função:
Primeiro, a função desativa o cálculo automático para evitar cálculos desnecessários enquanto preenche as células.
Em seguida, a função obtém a referência à planilha "aliexpress" usando SpreadsheetApp.getActiveSpreadsheet().getSheetByName("aliexpress").
É obtido o número da última linha preenchida na planilha através de planilha.getLastRow(), e definimos o número da última coluna como 15, que é o número da coluna "O" na planilha.
Em seguida, definimos uma faixa de células a serem preenchidas. Essa faixa abrange da coluna "I" à coluna "O" e começa na primeira linha (linha 1) e vai até a última linha preenchida. Isso é feito usando planilha.getRange(1, 9, ultimaLinha, ultimaColuna - 8).
Os valores presentes na faixa de células são obtidos usando range.getValues() e armazenados na matriz valores.
Agora, percorremos cada célula da faixa usando dois loops for. Se o valor da célula for vazio, significa que precisa ser preenchido de acordo com a regra específica da coluna.
Para cada coluna, temos uma regra específica de preenchimento. Por exemplo, para a coluna "I", a fórmula é definida para verificar as células das colunas "H" e "G" e, com base nisso, preencher o conteúdo da célula atual.
As fórmulas para cada coluna são construídas usando a referência da linha atual (linha + 1) e os valores das células em outras colunas (como "H" e "K").
Depois que as fórmulas são criadas, elas são armazenadas na matriz valores na posição da célula correspondente.
Após o término do preenchimento, os valores atualizados são definidos novamente na planilha usando range.setValues(valores).
Finalmente, a função ativa o cálculo automático novamente usando ativarCalculoAutomatico().
4. Função onOpen()
Essa função é um "gatilho" que é executado automaticamente sempre que a planilha é aberta. Ela adiciona um menu personalizado "CanalQb" na parte superior da planilha. Esse menu possui uma única opção "Gera Formulas", que ao ser selecionada, chama a função preencherCelulasVazias().
Como usar esse script em outras planilhas?
Para usar esse script em outras planilhas, você pode seguir os seguintes passos:
Copie todo o código do script.
Abra a planilha para a qual deseja adicionar o script.
Na barra superior, clique em "Ferramentas" e selecione "Editor de Scripts".
Cole o código na janela do Editor de Scripts.
Se necessário, altere o nome da planilha na linha var planilha = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("aliexpress"); para o nome da sua planilha.
Salve o projeto clicando no ícone de disquete.
Agora, quando você abrir sua planilha, verá o menu personalizado "CanalQb" na parte superior. Ao clicar nele, você encontrará a opção "Gera Formulas". Essa opção executará a função preencherCelulasVazias(), preenchendo automaticamente as células vazias de acordo com as regras definidas no script.
Espero que este post tenha sido útil para entender o funcionamento desse script e como adaptá-lo para outras planilhas. Automatizar tarefas repetitivas em planilhas pode economizar muito tempo e esforço, permitindo que você se concentre em outras atividades importantes. Experimente o script em sua própria planilha e aproveite os benefícios da automação! Se tiver alguma dúvida ou precisar de ajuda, fique à vontade para perguntar. Boa planilha!
Link para o Script, está salvo em TXT, basta copiar e colar no Google APPS Script: https://cb.run/hfDz
Vamos para alguns prints, para deixar mais visual:
Acima está um trecho da minha planilha.
Da coluna A até a coluna G, um script python que eu desenvolvi cria um produtor através dos produtos da Aliexpress.
A Coluna H, é a coluna que faz a validação se o vídeo ou UPADO no Youtube.
E por fim, da coluna I até a coluna O, existe uma formulá que eu preciso incluir, na I apenas para falar se upei no Youtube, e as demais para anunciar no Facebook.
Mas toda vez que uma linha é inserida, somos obrigado a entrar na planilha e repetir a formula arrastando ela, para preencher os valores.
Então em busca do Script para resolver isso, achei diversos modelos na internet, e até mesmo no chatopenai, mas infelizmente todos consomem muita memoria quando passamos de uma determinada quantidade de consultas.
Para você que nunca criou um Script para um documento do Google, aqui está onde fazer
Dica: Nunca saia colando Script da internet, o Risco é seu!
Na primeira vez que você executar, o Google vai pedir permissão, e você precisa confirmar, e se o caso clicar em avançado, e depois em executar Script não seguro
Para resolver isso basicamente eu fiz o seguinte via Script.
Desativei o calculo de formulas do Google Sheet, para dar mais memoria.
Pedi para dentro de uma Range (I2:O+Ultimalinha), procurar celulas vazias.
assim que ele acha a celula, ele preenche com as formulas que eu preciso, através das condições, como por exemplo.
assim que ele acha a celula, ele preenche com as formulas que eu preciso, através das condições, como por exemplo.
if (nomeColuna === "J") {
textoPreencher = '=IF(K' + (linha + 1) + '<>"";"in stock";"0")';
}
Nesta situação, se o nomeColuna = J, então ele valida e insere a formula que eu preciso, e a linha +1 retorna a posição da linha real que ele está, tendo isso em mente, ele consegue alimentar formulas em todas as celulas.
Terminado todo o preenchimento das vazias, então ele ativa o calculo de formulas do Google Shet.
Isso faz com que a memoria consumida, da sua parte do Google, seja muito menor.
Como adpatar para seu mundo, tentei deixar o mais simples possivel.
var planilha = SpreadsheetApp.getActiveSpreadsheet().getSheetByName( "aliexpress");var ultimaLinha = planilha.getLastRow();var ultimaColuna = 15; // Número da coluna "O" (I é a coluna 9, J é a 10 e assim por diante)var range = planilha.getRange(1, 9, ultimaLinha, ultimaColuna - 8);var valores = range. getValues ();
Neste trecho do Script, onde está aliexpress, este é o nome da sua sheet, que normalemente está como sheet1 ou mesmo plan1
- A variavel ultimacoluna = 15, ou seja de A até O são 15 colunas
var range = planilha.getRange(1, 9, ultimaLinha, ultimaColuna - 8);
- O numero 1 representa qual é a linha que irá começar.
- O numero 9, representa qual coluna irá começar 9 = a Coluna I
- A variavel ultimaLinha = a ultima linha que irá montar a matriz
- A variavel "ultimaColuna - 8" serve para que a range entre especificamente em cada coluna, com o For que está por vir, logo mais pra frente do script "for (var coluna = 0; coluna < ultimaColuna - 8; coluna++) {"
Acredito que entendo essa parte do Script, você seja capaz de montar tudo que precisa, com base nas suas necessidades.
Assim que você concluir, vai querer testar, então é só clicar no icone de salvar e executar
Basta clicar no icone do cronometro, e por fim em adicionar acionador
Eu uso a seguinte configuração, eu falo para o Google Sheets, que a cada 12 horas, ele execute a função do script com o nome "preencherCelulasVazias"
E fim, está pronto, 2 vezes por dia o proprio google executa para mim a inclusão das formulas nas novas linhas.
Um abraço, e se possivel siga o canal no Youtube, e deixe um comentário aqui no Blog, me incentive a crescer!
0 Comentários