Função do Cloud para automatizar a importação de dados CSV para o app Planilhas Google

1. Introdução

O objetivo deste codelab é que você entenda como escrever uma função do Cloud para reagir ao upload de um arquivo CSV para o Cloud Storage, ler o conteúdo dele e usá-lo para atualizar uma planilha Google usando a API Sheets.

Isso pode ser visto como a automação de uma etapa manual de "importar como CSV". Isso garante que você possa analisar os dados (talvez produzidos por outra equipe) em uma planilha assim que eles estiverem disponíveis.

Esta é a aparência da implementação :

2. Configuração e requisitos

Configuração de ambiente autoguiada

1. Faça login no Console do Cloud e crie um novo projeto ou reutilize um existente. Crie uma se você ainda não tiver uma conta do Gmail ou do G Suite.

Observação: para acessar facilmente o Console do Cloud, basta memorizar o URL, que é console.cloud.google.com.

Lembre-se do código do projeto, um nome exclusivo em todos os projetos do Google Cloud. O nome acima já foi escolhido e não servirá para você. Faremos referência a ele mais adiante neste codelab como PROJECT_ID.

Observação: se você estiver usando uma conta do Gmail, deixe o local padrão definido como Sem organização. Se você estiver usando uma conta do G Suite, escolha um local adequado à sua organização.

2. Em seguida, será necessário ativar o faturamento no Console do Cloud para usar os recursos do Google Cloud.

A execução deste codelab não será muito cara, se for o caso. Siga todas as instruções na seção "Limpeza", que orienta você sobre como encerrar recursos para não incorrer em cobranças além deste tutorial. Novos usuários do Google Cloud estão qualificados para o programa de US$ 300 de avaliação sem custos.

3. Criar e configurar uma planilha Google e ativar a API

Primeiro, vamos criar um documento das Planilhas Google (essa planilha pode ser de qualquer usuário). Depois de criado, lembre-se do identificador. Ele será usado como uma variável de ambiente para a função que vamos escrever :

No console do GCP, ative a API Google Sheets no projeto recém-criado. Para isso, acesse a seção "APIs e serviços" e depois "Biblioteca de APIs":

Na seção "IAM e administrador", acesse "Contas de serviço" e anote o e-mail da conta de serviço padrão do App Engine. Ele precisa estar no formato [e-mail]. É claro que você também pode criar uma conta de serviço dedicada a essa ação.

Por fim, conceda a essa conta de serviço privilégios de edição à sua planilha usando o botão "Compartilhar":

Com essa configuração, podemos escrever nossa função do Cloud e configurá-la para usar essa conta de serviço. Ele poderá gravar no documento de planilha que acabamos de criar.

4. Criar um bucket de armazenamento

Vamos criar o bucket que nossa função do Cloud vai monitorar em busca de novos arquivos CSV.

No console, use o menu à esquerda para navegar até "Storage"... :

... e crie um bucket chamado csv2sheet-POSTFIX (substitua o POSTFIX por algo exclusivo) com todas as outras configurações definidas como valores padrão :

Observação:escolha o local, o tipo de local e a classe de armazenamento que quiser. Isso não deve afetar o restante do codelab. Em caso de dúvida, mantenha os valores padrão.

5. Criar a função do Cloud

Agora podemos criar uma função do Cloud chamada csv2sheet, que é acionada quando arquivos são enviados para um bucket específico do Cloud Storage. O código será escrito em Node.js 8 com funções assíncronas usando o editor in-line diretamente no console do Cloud :

Defina o acionador como "Cloud Storage" e ajuste o nome do bucket para o que você criou na etapa anterior.

Atualize também o ponto de entrada da função que vamos escrever para csv2sheet :

Agora mude o corpo da função para :

1. usar as APIs Cloud Storage e Sheets
2. marque a função csv2sheet como async
3. Pegue o fileName dos metadados do evento do Cloud Storage e derive um nome para a nova planilha que vamos criar :

const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

O uso de async aqui é necessário para usar await, como veremos em breve.

Algumas opções importantes ao criar essa função incluem (clique no link "Mais" na parte de baixo da tela) :

• Use o menu suspenso para selecionar a conta de serviço discutida acima.
• Defina uma variável de ambiente chamada SPREADSHEET_ID que corresponda ao documento de planilha criado anteriormente :

Como última etapa de configuração, aqui está o conteúdo de package.json com as APIs do Cloud Storage e do Google Sheets como as duas dependências que vamos usar. Use a guia PACKAGE.JSON do editor inline do console:

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

Depois de configurar tudo conforme descrito, clique em "Criar". Depois de um minuto, a função será criada e implantada.

Observação:todo o código futuro discutido neste laboratório precisa ser adicionado à função do Cloud que acabamos de criar.

6. Configurar a autenticação e a API Sheets

Antes de escrever mais código na função do Cloud usando o editor inline, precisamos bloquear a criação de uma API de cliente do Google com os escopos adequados do Storage e do Sheets. Lembre-se de que isso faz parte de uma função async.

No editor de funções do console, clique em "EDITAR" e adicione o seguinte código ao corpo da função csv2sheet :

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

Em seguida, podemos criar um cliente da API Sheets :

const sheetsAPI = google.sheets({version: 'v4', auth});

7. Usar a API Sheets para criar uma planilha vazia

Com um cliente da API Google Sheets, podemos criar uma nova planilha simples no documento. Mas antes de continuarmos, aqui está uma observação rápida sobre o vocabulário:

• uma planilha é o documento real e é referenciada pelo identificador (discutido acima e visível no URL do documento).
• Uma página é uma das guias do documento e pode ser referenciada pelo nome (nome da guia) ou por um identificador gerado na criação da página.

Com isso em mente, aqui está uma função que usa o cliente da API Google Sheets para criar uma planilha vazia na posição 2 (normalmente depois da "Planilha1" padrão), com 26 colunas, 2.000 linhas e a primeira linha congelada. Adicione-a à sua função usando o editor inline:

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

Em vez de codificar a referência à planilha, usamos a variável de ambiente SPREADSHEET_ID criada anteriormente.

Precisamos lembrar o sheetId para outras solicitações feitas a essa planilha específica. Além disso, o nome da planilha precisa ser exclusivo. A criação vai falhar se já houver uma planilha chamada sheetName.

A função batchUpdate na API Google Sheets é uma maneira comum de interagir com documentos e está descrita aqui.

8. Ler dados de um arquivo CSV de armazenamento

Agora que temos um lugar para despejar nossos dados, vamos desenvolver ainda mais nossa função do Cloud no editor inline e usar a API Storage para buscar os dados reais do arquivo que acabou de ser enviado e armazená-los em uma string:

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. Preencher a planilha recém-criada

Agora é hora de preencher a planilha que criamos usando a mesma API cliente do Google Sheets e os dados que acabamos de coletar. Vamos aproveitar a oportunidade para adicionar um pouco de estilo às colunas da planilha (mudando o tamanho da fonte da linha superior e colocando em negrito) :

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

Esse código precisa ser adicionado à nossa função do Cloud, que agora está 99% concluída.

Observe como os dados e o estilo são combinados como vários requests em uma única chamada da API Sheets batchUpdate. Isso resulta em uma atualização mais eficiente e atômica.

Também definimos um intervalo de edição que corresponde ao tamanho da planilha criada. Isso significa que o conteúdo que exceder 26 colunas (o valor columnCount usado ao criar a planilha) vai falhar com esse código específico.

Se tudo der certo, você poderá:

1. salvar a função atualizada
2. arrastar e soltar um arquivo CSV no bucket
3. os dados correspondentes aparecem na sua planilha.

10. Juntando tudo e testando o fluxo

As chamadas para as funções que acabamos de discutir podem ser feitas como chamadas de bloqueio sucessivas na função csv2sheet original:

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

Se você precisar do código-fonte completo da função, ele está disponível aqui (provavelmente é mais fácil ter tudo em um conjunto).

Depois que tudo estiver pronto, basta fazer upload de um arquivo CSV para o bucket certo e ver sua planilha ser atualizada com uma nova página com o conteúdo do arquivo. Confira um exemplo de arquivo CSV se você não tiver um.

Faça o upload de vários arquivos para o bucket e veja o que acontece.

11. Pronto! Hora de desativar a infraestrutura

Brincadeira, não há infraestrutura para desmontar. Tudo foi feito sem servidor.

Se quiser, você pode excluir a função do Cloud e o bucket que criou ou até mesmo todo o projeto.

12. A seguir

Este codelab conclui as etapas para ouvir uploads em um bucket do Cloud Storage em uma função do Cloud para atualizar uma planilha Google usando a API apropriada.

Confira o que fazer a seguir:

• Confira os guias de instruções do Cloud Functions, que incluem algumas práticas recomendadas.
• Siga um dos tutoriais do Cloud Functions.
• Saiba mais sobre a API Google Sheets

Se você teve problemas com este codelab, use o link no canto inferior esquerdo para informar qualquer problema.

Seu feedback é muito importante!