Pular para o conteúdo
Node.js

Node.js: populando uma planilha no Google Sheets

Paloma Macetko
Um unicornio de crina colorida preenchendo com uma varinha as celulas de uma planilha flutuante, ao lado de uma coruja mensageira e de um castelo com velas

Olá meus Unicórnios! 🦄✨

Semana passada eu precisei de uma coisa que parecia bobinha: pegar uma lista de dados que já existia no meu sistema e jogar numa planilha do Google, para outras pessoas conseguirem olhar e mexer sem precisar entrar em lugar nenhum. Planilha todo mundo sabe usar. 📊

Achei que fosse meia hora. 😅 A parte de escrever na planilha é realmente rápida — são umas quinze linhas. O que me tomou a tarde foram três detalhes que não dão erro nenhum: o código roda liso, imprime "deu certo", e a planilha fica errada. Sem exceção, sem aviso, sem nada vermelho no terminal.

São esses três que eu quero te contar hoje, porque o resto você acha em qualquer documentação.

🔑 Primeiro: a conta de serviço

Para um programa escrever numa planilha sua, ele precisa de uma identidade própria. Isso se chama conta de serviço — pense nela como um robozinho que tem e-mail próprio e a quem você dá permissão, igualzinho a como você compartilharia com uma pessoa.

O caminho é esse, e é feito uma vez só:

  1. No Google Cloud Console, crie um projeto.
  2. Ative a Google Sheets API nesse projeto.
  3. Crie uma conta de serviço e gere uma chave no formato JSON. O navegador vai baixar um arquivinho.
  4. Abra esse JSON e procure o campo client_email. É um e-mail com cara estranha, terminando em .iam.gserviceaccount.com.
  5. Abra a sua planilha e compartilhe com esse e-mail, como Editor.

E o ID da planilha sai da própria URL, aquele monte de letras entre /d/ e /edit:

https://docs.google.com/spreadsheets/d/AQUI_ESTA_O_ID/edit

📦 A única dependência

Uma só, e é a oficial do Google:

npm install googleapis

O arquivo da chave JSON e o ID da planilha eu leio de variáveis de ambiente, nunca escritos dentro do código. Aquele JSON é uma credencial de verdade: quem tem ele escreve na sua planilha. Ele nunca vai para o Git.

const { existsSync } = require('fs');
const { google } = require('googleapis');

// As duas configurações vêm do ambiente, nunca escritas aqui dentro.
const ID_DA_PLANILHA = process.env.PLANILHA_ID;
const ARQUIVO_DA_CHAVE = process.env.PLANILHA_CHAVE;

// O nome da aba, exatamente como aparece na abinha de baixo do Sheets.
const ABA = 'Planilha1';

🔌 Conectando

A conexão são seis linhas. Você aponta para o arquivo da chave, diz o que quer poder fazer, e pede o cliente do Sheets:

// Conecta na conta de serviço e devolve o objeto que fala com o Sheets.
function conectar() {
    const autenticacao = new google.auth.GoogleAuth({
        keyFile: ARQUIVO_DA_CHAVE,
        // 'spreadsheets' dá leitura E escrita. Com o escopo '.readonly'
        // a autenticação passa e só a escrita falha, lá na frente.
        scopes: ['https://www.googleapis.com/auth/spreadsheets'],
    });
    return google.sheets({ version: 'v4', auth: autenticacao });
}

Repare no scopes. Existe um escopo parecidíssimo chamado spreadsheets.readonly, e ele é uma cilada linda: com ele a autenticação passa normalmente, o programa segue feliz, e só lá na frente, na hora de escrever, você toma o erro. Como o erro chega longe de onde a causa está, dá vontade de procurar bug no lugar errado. Se você vai escrever, é spreadsheets, sem sufixo.

🔗 A primeira armadilha: o link que come o texto

Essa foi a que mais me irritou, porque ela é invisível.

Quando você manda uma coluna com endereços de site, o Google Sheets olha aquele texto, reconhece que parece um link, e transforma em link clicável. Parece simpático. O problema aparece quando alguém exporta essa planilha, ou copia a coluna para outro lugar, ou tenta comparar com a lista original: o que está na célula deixou de ser exatamente o texto que você mandou.

A solução é uma aspa simples na frente. Ela funciona como um "isto aqui é texto puro, não interprete":

// O Sheets transforma texto que parece link num link clicável.
// A aspa simples na frente manda ele tratar como texto puro.
// A aspa não aparece na célula: é uma marca, não um caractere.
function comoTexto(valor) {
    if (!valor) return '';
    if (valor.startsWith('http://') || valor.startsWith('https://')) {
        return "'" + valor;
    }
    return valor;
}

O detalhe bonito é que a aspa não aparece na célula. Ela não é um caractere que você está adicionando ao valor — é uma marca que o Sheets consome ao gravar. Quem abrir a planilha vê o endereço limpo, igualzinho, só que como texto.

Para eu ter certeza de que isso funcionava mesmo, montei um Google Sheets falso aqui na minha máquina, que aplica a mesma regra do de verdade e me mostra o tipo de cada célula. Rodei o script sem a proteção do comoTexto:

C1   texto    Link
C2   LINK     https://exemplo.com/caneca
C3   LINK     https://exemplo.com/caderno
C4   LINK     https://exemplo.com/adesivo

Três células viraram LINK. Agora com a aspa de volta, mesmo script, mesmos dados:

C1   texto    Link
C2   texto    https://exemplo.com/caneca
C3   texto    https://exemplo.com/caderno
C4   texto    https://exemplo.com/adesivo

Mesmo endereço na tela, natureza completamente diferente por baixo. 🎯

👻 A segunda armadilha: as linhas fantasma

Essa é a que dá medo, porque ela não estraga a aparência — ela inventa dado.

O update do Sheets sobrescreve só as células que você mandar. Ele não faz ideia de que aquilo era para ser "a planilha inteira". Se ontem o seu programa escreveu 6 produtos e hoje escreve 2, as células das linhas 4 a 7 continuam exatamente como estavam.

E o resultado é o pior tipo de erro: uma planilha com 7 linhas, todas bem formatadas, onde as 4 primeiras são de hoje e as 3 últimas são de ontem. Ninguém olha para aquilo e desconfia.

Montei essa situação exata para ver o estrago. Primeiro, seis produtos:

Ontem, 6 produtos:
  linha 1: Produto                Estoque
  linha 2: Caneca                 12
  linha 3: Caderno                7
  linha 4: Adesivo                130
  linha 5: Chaveiro               4
  linha 6: Bloco                  20
  linha 7: Marcador               9

Agora o estoque acabou e só sobraram dois produtos. Escrevendo sem limpar antes:

Hoje, so 2 produtos — SEM clear:
  linha 1: Produto                Estoque
  linha 2: Caneca                 12
  linha 3: Caderno                7
  linha 4: Adesivo                130
  linha 5: Chaveiro               4
  linha 6: Bloco                  20
  linha 7: Marcador               9

Olha o tamanho do problema: a planilha continua com sete linhas. Quatro produtos que não existem mais estão ali, bonitinhos, com estoque e tudo. Se alguém tirar um relatório dessa planilha, o relatório vai estar errado — e vai parecer certo. 😳

A correção é uma chamada a mais, antes da escrita:

    // Limpar as colunas inteiras antes de escrever. O update só sobrescreve
    // as células que você manda: se ontem havia 50 linhas e hoje há 4,
    // as 46 antigas continuariam lá embaixo, com cara de dado atual.
    await sheets.spreadsheets.values.clear({
        spreadsheetId: ID_DA_PLANILHA,
        range: ABA + '!A:D',
    });

E aí sim:

Hoje, so 2 produtos — COM clear:
  linha 1: Produto                Estoque
  linha 2: Caneca                 12
  linha 3: Caderno                7

Repare que eu limpo A:D — as colunas inteiras, sem número de linha. É de propósito: eu não sei quantas linhas sobraram da última vez, então não adianta limpar um intervalo do tamanho do que estou escrevendo agora. Tem que limpar tudo o que aquelas colunas puderem ter.

📐 A terceira armadilha: RAW ou USER_ENTERED

Na hora de escrever, existe um campo chamado valueInputOption, e ele é obrigatório. Ele decide se o Google vai respeitar o que você mandou ou se vai tentar adivinhar o que você quis dizer.

  • RAW — grava exatamente o que você mandou. Texto é texto, número é número, ponto final.
  • USER_ENTERED — o Sheets interpreta como se uma pessoa tivesse digitado ali. Um =SOMA(A1:A2) vira fórmula de verdade; um 01/02 vira data; um 0800 perde o zero da frente e vira o número 800.

Para popular planilha com dado que veio de sistema, RAW é quase sempre o que você quer. O dado já está certo — deixa ele em paz.

    // O intervalo tem de acompanhar o tamanho real da matriz.
    const intervalo = ABA + '!A1:D' + linhas.length;

    await sheets.spreadsheets.values.update({
        spreadsheetId: ID_DA_PLANILHA,
        range: intervalo,
        // RAW grava exatamente o que você mandou.
        // USER_ENTERED faria o Sheets interpretar: '=SOMA(A1:A2)' viraria
        // fórmula e '01/02' viraria data.
        valueInputOption: 'RAW',
        requestBody: { values: linhas },
    });

E repare no intervalo, logo acima: ele é montado com linhas.length, o tamanho real da matriz. Chutar um número fixo ali é como deixar uma pegadinha armada para daqui a três meses, quando a lista crescer.

🚀 O script inteiro

São 112 linhas, uma dependência, e dá para ler de cima para baixo. Salve como planilha.js:

// Popula uma planilha do Google Sheets a partir de uma lista de dados.
// Rode com:  node planilha.js

const { existsSync } = require('fs');
const { google } = require('googleapis');

// As duas configurações vêm do ambiente, nunca escritas aqui dentro.
const ID_DA_PLANILHA = process.env.PLANILHA_ID;
const ARQUIVO_DA_CHAVE = process.env.PLANILHA_CHAVE;

// O nome da aba, exatamente como aparece na abinha de baixo do Sheets.
const ABA = 'Planilha1';

// Os dados que vamos escrever. Numa vida real viriam de um banco,
// de um CSV ou de uma API — aqui são fixos para o exemplo rodar sozinho.
const produtos = [
    { nome: 'Caneca de unicórnio', preco: 39.9, site: 'https://exemplo.com/caneca', estoque: 12 },
    { nome: 'Caderno arco-íris', preco: 24.5, site: 'https://exemplo.com/caderno', estoque: 7 },
    { nome: 'Adesivo brilhante', preco: 8, site: 'https://exemplo.com/adesivo', estoque: 130 },
];

// Conecta na conta de serviço e devolve o objeto que fala com o Sheets.
function conectar() {
    const autenticacao = new google.auth.GoogleAuth({
        keyFile: ARQUIVO_DA_CHAVE,
        // 'spreadsheets' dá leitura E escrita. Com o escopo '.readonly'
        // a autenticação passa e só a escrita falha, lá na frente.
        scopes: ['https://www.googleapis.com/auth/spreadsheets'],
    });
    return google.sheets({ version: 'v4', auth: autenticacao });
}

// O Sheets transforma texto que parece link num link clicável.
// A aspa simples na frente manda ele tratar como texto puro.
// A aspa não aparece na célula: é uma marca, não um caractere.
function comoTexto(valor) {
    if (!valor) return '';
    if (valor.startsWith('http://') || valor.startsWith('https://')) {
        return "'" + valor;
    }
    return valor;
}

// Monta a matriz de linhas. Cada linha é uma lista, na ordem das colunas.
function montarLinhas() {
    const linhas = [];

    // Primeira linha: o cabeçalho.
    linhas.push(['Produto', 'Preço', 'Link', 'Estoque']);

    for (const produto of produtos) {
        linhas.push([
            produto.nome,
            produto.preco,
            comoTexto(produto.site),
            produto.estoque,
        ]);
    }

    return linhas;
}

// Apaga o que estava lá e escreve as linhas novas.
async function popularPlanilha() {
    const sheets = conectar();
    const linhas = montarLinhas();

    // Limpar as colunas inteiras antes de escrever. O update só sobrescreve
    // as células que você manda: se ontem havia 50 linhas e hoje há 4,
    // as 46 antigas continuariam lá embaixo, com cara de dado atual.
    await sheets.spreadsheets.values.clear({
        spreadsheetId: ID_DA_PLANILHA,
        range: ABA + '!A:D',
    });

    // O intervalo tem de acompanhar o tamanho real da matriz.
    const intervalo = ABA + '!A1:D' + linhas.length;

    await sheets.spreadsheets.values.update({
        spreadsheetId: ID_DA_PLANILHA,
        range: intervalo,
        // RAW grava exatamente o que você mandou.
        // USER_ENTERED faria o Sheets interpretar: '=SOMA(A1:A2)' viraria
        // fórmula e '01/02' viraria data.
        valueInputOption: 'RAW',
        requestBody: { values: linhas },
    });

    return linhas.length - 1;
}

async function main() {
    if (!ID_DA_PLANILHA || !ARQUIVO_DA_CHAVE) {
        console.error('Defina PLANILHA_ID e PLANILHA_CHAVE antes de rodar.');
        process.exit(1);
    }

    if (!existsSync(ARQUIVO_DA_CHAVE)) {
        console.error('Não achei o arquivo da chave: ' + ARQUIVO_DA_CHAVE);
        process.exit(1);
    }

    try {
        const quantidade = await popularPlanilha();
        console.log('Planilha populada com ' + quantidade + ' produtos.');
    } catch (erro) {
        console.error('Não consegui escrever na planilha: ' + erro.message);
        process.exit(1);
    }
}

main();

Para rodar, as duas variáveis e pronto:

export PLANILHA_ID="o_id_que_esta_na_url"
export PLANILHA_CHAVE="./minha-chave.json"

node planilha.js

E a saída, quando dá tudo certo:

Planilha populada com 3 produtos.

🧯 Quando dá errado

O try/catch lá do fim existe por um motivo bem prático: os erros do Google chegam com mensagem longa e cheia de detalhe de rede, e quem está rodando o script quer saber é o que fazer. Eu prefiro uma linha em português e sair com código 1:

    try {
        const quantidade = await popularPlanilha();
        console.log('Planilha populada com ' + quantidade + ' produtos.');
    } catch (erro) {
        console.error('Não consegui escrever na planilha: ' + erro.message);
        process.exit(1);
    }

As duas conferências antes disso também são de gente que já apanhou. Sem elas, esquecer de definir a variável de ambiente dá um erro lá dentro da biblioteca do Google, num ponto que não te ajuda em nada:

Defina PLANILHA_ID e PLANILHA_CHAVE antes de rodar.
Não achei o arquivo da chave: ./nao-existe.json

Três palavras de aviso valem mais que uma pilha de stack trace. 🙏

💛 O que eu levo disso

As três armadilhas têm a mesma cara, e é isso que as torna perigosas: nenhuma delas dá erro. O programa roda, o terminal diz que deu certo, e o defeito fica na planilha esperando alguém confiar nela.

Por isso, quando eu mexo com Sheets, eu não confio no "rodou sem erro". Eu abro a planilha e olho: o endereço está clicável quando não devia? Sobrou linha de ontem lá embaixo? Aquele código que começa com zero perdeu o zero? São trinta segundos que evitam um relatório errado. ✨

Por hoje é só, meus unicórnios! 🦄✨

Que a magia do arco-íris continue brilhando em suas vidas! Até mais! 🌈🌟

Node.js JavaScript Google

Leia também