Sincronização de Planilhas Usando AppScript

Introdução

Há algumas semanas, me perguntaram se era possível sincronizar planilhas no Google Sheets.

A ideia era que o usuário teria uma planilha principal e, ao alterar algo nela, as outras planilhas seriam automaticamente atualizadas para refletir essas mudanças.

Eu sabia que isso era viável, mas também estava ciente de que poderia causar problemas, dependendo de o que fosse alterado.

No AppsScript, é possível capturar eventos de alteração de dados. No entanto, imaginei que aplicar mudanças automaticamente sempre que algo fosse modificado poderia não ser a melhor abordagem, pois isso poderia causar conflitos em algum momento.

Por isso, comecei a planejar um fluxo de atualização mais organizado, aproveitando os recursos do AppsScript de maneira controlada e eficiente.

Todo o código desenvolvido está disponível nesse repositório: https://github.com/Matheus1714/spreadsheet-sync-with-appscript

Problemas de Eventos Simultâneos

Primeiro quero explicar o principal problema que pensei caso o evento fosse para cada alteração dos usuários:

DADO dois usuários
QUANDO estão alterando uma planilha ao mesmo tempo
ENTÃO sincronizar dados

Nesse cenário eu pensei em dois ou mais usuários alterando a planilha ao mesmo tempo. Nesse caso se faça a pergunta: "Qual deles eu devo devo escolher para atualizar as planilhas de destino?". Se eu escolho pelo primeiro que gerar o evento, eu vou descartar a resposta de outros, o que não é bom. Se eu armazenar os eventos que forem chegando em uma planilha intermiediária, eu estaria tornando o sistema complexo de mais para uma versão inicial.

Agora, se eu fizer de acordo com um temporizador fixo (ou intervalor do tempo real), eu estaria pegando o estado atual da planilha no instante e não me preocuparia com isso depois.

Lembrando que Tempo Real pode ter vários definições. Na Ciência da Computação ele é um intervalo que uma operação irá executar, podendo ser um tempo do relógio, a cada 1h, a cada 1 semana e assim por diante. Aqui uma referência caso queira ler mais sobre o assunto: Real-time operating system.

Explicação da Proposta

Minha proposta de solução está apresentada de acordo com o fluxo da figura abaixo. Nesse fluxo eu tenho uma planilha central com alguns grupos de colunas que quero mostrar, definidas como C1, C2 e C3. Caso ocorra algum evento de [clicar em um botão de update] ou [ter se passado 1h], eu atualizo os dados na planilha B e na planilha C com novas colunas C1' e C2'.

Coloquei o botão, pois achei que caso a pessoa quisesse ter os dados atualizados na hora isso já estaria disponível.

Aplicação da Proposta

Para aplicar o que eu estava propondo, eu fui no meu Google Drive e criei três planilhas, chamadas A, B e C organizadas da seguinte forma:

.
├── A
├── B
└── C

Dentro da planilha A, eu coloquei os seguintes campos:

C1			C2	C3
Nome	Email	Idade	Formação	Salário	Investido

Na planilhas B, coloquei:

C1'
Nome	Email	Idade

E na planilha C:

C2'
Nome	Formação

Agora, estando na planilha A, vá no cabeçalho e busque por:

Extensions > Apps Script

Ele vai abrir uma nova aba, no qual a esquerda estará um arquivo Code.gs.

Nesse arquivo eu primeiro configurei os dados principais:

const SHEET_ID_A = '...';
const SHEET_ID_B = '...';
const SHEET_ID_C = '...';
const TAB_NAME = "Dados"

O id de todos você obtem buscando na URL o valor depois do /d:

https://docs.google.com/spreadsheets/d/{id}/...

Depous eu criei essa variável colsMap, que mapeia as colunas dependendo da planilha. Fiz essa estrutura, pois ela é muito flexível para eu poder reutilizar operações ou futuras extensões do meu programa.

const colsMap = {
  A: ["name", "email", "age", "education", "salary", "invested"],
  B: ["name", "email", "age"],
  C: ["name", "education"],
}

A primeira função a ser implementada é a de obter os dados de uma planilha baseado em um id:

/**
 * @param {string} sheetId
 */
function getDataFromSheet(sheetId) {
  const spreadSheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadSheet.getSheetByName(TAB_NAME);

  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  const start = [3, 1];
  const end = [lastRow - 2, lastColumn];

  if(end[0] === 0 || end[1] === 0) return null;
  
  const range = sheet.getRange(start[0], start[1], end[0], end[1]);
  const data = range.getValues();

  return data;
}

Perceba que posso usar ela para qualquer planilha se eu quiser, mas eu só uso ela para a planilha A.

Em seguida eu filtrei os dados baseado no tipo que defini com colsMap:

/**
 * @param {string[][] | null} data
 * @param {'A' | 'B' | 'C'} from;
 * @param {'A' | 'B' | 'C'} to;
 */
function filterData(data, from, to) {
  if(!data || !data?.length || !data[0]?.length) return null;

  const fromCols = colsMap[from];
  const toCols = colsMap[to];

  if(data[0].length !== fromCols.length) {
    throw Error('data must be the same length the colsMap');
  }

  return data.map((item) => {
    return toCols.map((col) => {
      const index = fromCols.indexOf(col);
      if(index === -1) {
        return null;
      }
      return item[index];
    })
  }).filter((item) => item.length);
}

A função anterior reduz as colunas para associa as colunas de origem e destino de forma correta.

Depois, eu faço o clear dos dados anteriores com essa função:

/**
 * @param {string} sheetId
 */
function clear(sheetId) {
  const spreadSheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadSheet.getSheetByName(TAB_NAME);

  const lastColumn = sheet.getLastColumn();
  const lastRow = sheet.getLastRow();

  const start = [3, 1];
  const end = [lastRow - 2, lastColumn];

  sheet.getRange(start[0], start[1], end[0], end[1]).clearContent();
}

Por fim eu tenho a função de sincronizar os dados:

/**
 * @param {string} sheetId
 * @param {{ [key: string]: string }[]} data;
 * @param {'A' | 'B' | 'C'} type
 */
function syncDataIn(sheetId, data, type) {
  const spreadSheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadSheet.getSheetByName(TAB_NAME);

  const lastColumn = colsMap[type].length;
  const lastRow = data.length;

  const start = [3, 1];
  const end = [lastRow, lastColumn];

  sheet.getRange(start[0], start[1], end[0], end[1]).setValues(data);
}

Com todas as funções implementadas, agora é só usar cada uma delas na regra de negócio que quero. Eu criei uma função main para agregar tudo isso.

function main() {
  const sourceData = getDataFromSheet(SHEET_ID_A);

  const targets = [
    { from: 'A', to: 'B', sheetId: SHEET_ID_B },
    { from: 'A', to: 'C', sheetId: SHEET_ID_C },
  ]
  
  targets.forEach((target) => {
    const { to, from, sheetId } = target;
    const data = filterData(sourceData, from, to);
    if(data) {
      clear(sheetId);
      syncDataIn(sheetId, data, to);
    }
  });
}

Tendo tudo isso, vá no topo da janela e configure para execuar a partir da função main. Depois aperte "Run".

O resultado final é a atualização de B e C a partir de A.

Para criar o botão de executar a função main, vá na planilha e execute os passos do gif:

Para criar o acionador para atualizar a cada 1h, vá na janela dos scripts e busque do lado esquerdo Acionadores ou Triggers. Depois aperte em adicionar acionador ou trigger e configure de acordo com o que fiz na imagem:

Com isso você terá planilhas que sincronizam independente de você estar ou não alterando elas.

Conclusão

Com a construção desse caso foi possível realizar a sincronização usando o AppScript, como comentado no início desse post. Além disso, acredito que usar o AppScript para montar provas de conceito são úteis, pois gera resultados rápidos. Claro que o ideial é depois passar tudo isso para um software que gerencie tudo.