post
Código para Sincronizar Dados de Uma Página Horizontal em Página Vertical Dentro do Google Sheets (Usando o Apps Script)
Publicado em: 04/07/2024 / Atualizado em: 04/07/2024
Imagina que você tenha uma planilha criada, com dados de um cliente que estão organizados com uma linha do tempo na horizontal. Isso quer dizer que, cada coluna é uma data (dia 01, dia 02, dia 03, assim por diante) e cada linha representa um cliente ou canal.
Porem, agora você precisa sincronizar esses dados em um painel externo, mas precisa que eles estejam organizados na vertical. Isso quer dizer que, seus dados, para serem consumidos ou melhor visualizados, eles precisam estar em linhas, não em colunas.
Para isso é possível criar um código via Apps Script do Google Sheets, onde ele pode pegar os dados de uma das páginas dessa planilha, e enviar para outra página, facilmente.
Você vai precisar criar 2 arquivos (Códigos.gs e outro Index.html), como mostra abaixo:
Agora, adicione o código do arquivo Index.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!-- Import Google Icon Font -->
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<!-- Import Materialize CSS -->
<link href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css" rel="stylesheet">
<!-- Import Materialize JavaScript -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
</head>
<body>
<div class="container" style="max-width: 88% !important">
<form id="syncForm" class="col s12">
<div class="row">
<div class="input-field col s12">
<input style="text-transform: uppercase" id="sourceColumn" type="text" class="validate" required>
<label for="sourceColumn">Coluna de Origem (ex: CZ ou CZ:DD)</label>
</div>
</div>
<div class="row">
<div class="input-field col s12">
<input id="targetRow" type="number" class="validate" required>
<label for="targetRow">Linha de Destino (ex: 147)</label>
</div>
</div>
<div class="row">
<div class="col s12 center-align">
<button class="btn waves-effect waves-light" type="button" onclick="syncData()">Sincronizar Agora
<i class="material-icons right">send</i>
</button>
</div>
</div>
</form>
</div>
<script>
// Função para capturar e preencher a linha selecionada na planilha "dashboard"
function getSelectedRow() {
google.script.run.withSuccessHandler(function(row) {
document.getElementById('targetRow').value = row;
}).getSelectedRow();
}
// Chamada inicial para preencher automaticamente o campo "targetRow"
document.addEventListener('DOMContentLoaded', function() {
getSelectedRow();
});
// Função para sincronizar os dados
function syncData() {
google.script.run.copyData(
document.getElementById('sourceColumn').value,
document.getElementById('targetRow').value
);
google.script.host.close();
}
</script>
</body>
</html>E também o código do arquivo Código.gs:
// version: 1.2.0
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Sincronizar Dashboard')
.addItem('Abrir formulário', 'openDialog')
.addToUi();
}
function openDialog() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var activeCell = sheet.getActiveCell();
var targetRow = activeCell.getRow();
var htmlOutput = HtmlService.createHtmlOutputFromFile('Index')
.setWidth(400)
.setHeight(300);
// Passa a linha selecionada como parâmetro para preencher automaticamente o campo
htmlOutput.append(`<script>document.getElementById('targetRow').value = ${targetRow};</script>`);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Sincronizar Dashboard');
}
function copyData(sourceColumns, targetRow) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName('Dados na Horizontal');
var targetSheet = ss.getSheetByName('Dados na Vertical');
// Linhas específicas na folha de origem
var sourceRows = [
// Data da coluna
4,
// Dados Gerais
7, 8, 9, 10, 11, 12
];
// Determinar se é uma única coluna ou um intervalo de colunas
var columns = getColumnsFromRange(sourceColumns);
// Copiar dados de cada coluna do intervalo de colunas
columns.forEach(function(column, index) {
var data = sourceRows.map(function(row) {
var cellValue = sourceSheet.getRange(column + row).getValue();
return (cellValue === '#DIV/0!' ? 0 : cellValue);
});
// Colocando os dados nas linhas específicas da coluna de destino
for (var i = 0; i < data.length; i++) {
targetSheet.getRange(Number(targetRow) + index, i + 1).setValue(data[i]);
}
});
}
// Função para obter todas as colunas entre um intervalo de colunas
function getColumnsFromRange(columnRange) {
if (columnRange.indexOf(':') === -1) {
return [columnRange]; // apenas uma coluna
} else {
var columns = [];
var range = columnRange.split(':');
var startCol = range[0].toUpperCase();
var endCol = range[1].toUpperCase();
var startColIndex = getColumnIndex(startCol);
var endColIndex = getColumnIndex(endCol);
for (var i = startColIndex; i <= endColIndex; i++) {
columns.push(getColumnName(i));
}
return columns;
}
}
// Função para obter o índice da coluna a partir do nome da coluna
function getColumnIndex(columnName) {
var column = 0, length = columnName.length;
for (var i = 0; i < length; i++) {
column += (columnName.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column;
}
// Função para obter o nome da coluna a partir do índice da coluna
function getColumnName(columnIndex) {
var columnName = '';
while (columnIndex > 0) {
var remainder = (columnIndex - 1) % 26;
columnName = String.fromCharCode(65 + remainder) + columnName;
columnIndex = Math.floor((columnIndex - 1) / 26);
}
return columnName;
}
// Função para retornar a linha selecionada na planilha "dashboard"
function getSelectedRow() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var activeCell = sheet.getActiveCell();
var targetRow = activeCell.getRow();
return targetRow;
}OBS: Você vai precisar alterar algumas informações do código para que funcione na sua planilha. As linhas para alterar são:
Altere para o nome da planilha de origem:
var sourceSheet = ss.getSheetByName('Dados na Horizontal')Altere para o nome da planilha de destino:
var sourceSheet = ss.getSheetByName('Dados na Vertical')Altere para as linhas que precisa obter da planilha de origem:
var sourceRows = [
// Data da coluna
4,
// Dados Gerais
7, 8, 9, 10, 11, 12
];Colocando o código para rodar:
Depois disso, atualize a página da planilha, e logo acima, no menu do google sheets, verá que tem um item adicionado chamado “Sincronizar Dashboard“:
Ao clicar nele, e abrir o formulário no primeiro acesso, ele vai pedir sua verificação para rodar o script, de a permissão, e clique em “continuar para não seguro” para que ele siga em frente.
Isso de “não seguro” acontece pois o google realmente não verificou esse App que acabamos de criar, pois não é um app enviado para a galeria de extensões do google, é apenas um App local que estamos desenvolvendo. Então, ele mostra essa mensagem, sempre que for adicionar código customizado em suas planilhas.
Assim que autorizar, clique novamente no botão para abrir o formulário, e você verá que ele vai abrir esse formulário, depois de executar:
Assim, você pode pegar qualquer dado de uma página que esteja na horizontal para esta página na vertical.
ATENÇÃO: Lembre-se sempre de alterar o código com as linhas que precisa, e também com o nome das páginas.
Publicações recomendadas:
Inscreva-se em Nossa News:
Seja notificando sempre que tiver conteúdo novo disponível no meu canal do Youtube ou artigo no meu Blog.
Link Curto para Compartilhamento
Compartilhe esse conteudo nas redes sociais ou por mensagem usando o link curto abaixo. Basta clicar em cima do link para copiar.
bruno.art.br/pb/5543
ID de Referência: 5543
Sugira uma publicação
Envie uma mensagem e sugira um publicação sobre um assunto que tenha dificuldades de resolver.