Como analisar dados no Google Sheets com Python: passo a passo

3. Configure uma conexão

Dentro do workbook, clique em View > Databases e depois no ícone +. Você verá uma visão geral de todas as tecnologias de banco de dados às quais o DataLab permite conectar:

Selecione Google Sheets. Agora será solicitado que você conceda permissão ao DataCamp para acessar arquivos no seu Google Drive.

Clique em “Conceder permissão ao Google Drive”. Por fim, um seletor de arquivos será exibido:

Selecione a planilha da qual você deseja ler dados. Pode ser a cópia da planilha de exemplo que fornecemos ou a sua própria planilha.

4. Consulte o arquivo do Google Sheets

Agora você pode consultar a planilha que acabou de conectar! Você fará isso com uma célula SQL, um bloco do DataLab para consultar bases com SQL. Se você criou um workbook a partir do template fornecido, essa célula SQL já estará pronta. Se começou do zero, adicione-a abrindo o launcher e clicando em “SQL”:

Na própria célula SQL, clique no menu Source no canto superior esquerdo.

Selecione a planilha que você quer analisar (no exemplo, é "Unicorn Companies"). Em seguida, escreva a seguinte consulta SQL:

SELECT * FROM unicorn_companies

Ao clicar em Run, essa consulta vai buscar todas as linhas da aba unicorn_companies dentro da planilha Unicorn Companies e armazenar o resultado em um novo DataFrame do pandas chamado df:

Um DataFrame do pandas é uma estrutura de dados em formato de tabela que você pode usar no Python para armazenar e manipular dados. Vamos adicionar uma célula Python e executar o código a seguir, que calcula a mediana de investimentos das empresas unicórnio do conjunto de dados por país:

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

Na célula SQL acima, usamos uma consulta simples para pegar todos os dados, mas você também pode usar uma instrução SQL mais elaborada para, por exemplo, buscar as 10 empresas unicórnio mais valiosas dos Estados Unidos:

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10

É isso! Para mais dicas de como ajustar a consulta SQL — por exemplo, para focar em um recorte da aba — confira o artigo correspondente na documentação do DataLab.

Como mencionado, essa conexão integrada com o Google Sheets só pode ser usada para ler dados. Se você quiser ler e também escrever resultados de volta no Google Sheets, veja a próxima seção, que explica como usar a API do Google Sheets para ter o máximo de flexibilidade.

Usando a API do Google Sheets

Escolha esta opção se quiser tanto ler dados de uma planilha do Google quanto escrever o resultado dos seus cálculos em Python na mesma ou em outra planilha.

Como dito na introdução, o ganho de flexibilidade vem com uma configuração inicial mais trabalhosa. Especificamente, vamos seguir estes passos:

• Configurar uma conta de serviço do Google (Google Service Account)
• Criar uma planilha do Google com dados
• Criar um novo workbook
• Armazenar as credenciais da conta de serviço no DataLab
• Ler dados do Google Sheets
• Analisar os dados em Python
• Escrever os resultados no Google Sheets

Vamos ver cada um deles separadamente.

1. Configure uma Google Service Account

Para acessar dados do Google Sheets programaticamente, você precisa criar uma conta de serviço do Google, um tipo especial de conta usada por programas para acessar recursos do Google, como uma planilha. Você usará essa conta de serviço para conectar o DataLab ao Google Sheets.

Você só precisa configurar essa conta de serviço uma vez para cada conta Google em que quiser acessar planilhas; pode pular esta etapa nas próximas vezes em que trabalhar com dados de planilhas na mesma conta.

Siga os passos abaixo para criar a conta de serviço e gerar as credenciais necessárias.

• Garanta que você está conectado à sua conta Google.
• Acesse a Google API Library
• Crie um novo projeto clicando no dropdown na barra superior.
• Pesquise por “Google Sheets API” e ative-a. Isso pode levar até 10 segundos.
• Na barra lateral “APIs and services”, vá até a aba “Credentials”.
• Clique em “+ CREATE CREDENTIALS” e selecione “Service Account”.
• No primeiro passo (service account details), informe um nome para a conta de serviço, por exemplo, “gsheet-operator”, e clique em “Create and continue”.
• No segundo passo, selecione o papel “Owner” e clique em “Continue”.
• No terceiro passo, não altere nada e clique em “Done”.
• De volta à página de Credenciais, clique na conta de serviço que você acabou de criar.
• Vá até a aba Keys, clique em “Add Key > Create new key”.
• Escolha “JSON” e clique em “Create”. O arquivo JSON com as credenciais da sua conta de serviço será baixado automaticamente no seu computador.

Pronto: você tem uma conta de serviço e um arquivo JSON de credenciais! Vá até sua pasta de Downloads (ou onde o JSON foi salvo), abra-o e confira. Ele deve ser parecido com isto:

{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "gsheets-operator@steam-verve-386214.iam.gserviceaccount.com",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}

Há um campo “client_email” ali: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Copie esse e-mail — você vai precisar dele no próximo passo.

2. Crie uma planilha do Google com dados

Antes de analisar dados em planilhas, garanta que você tem uma planilha com dados. Se não tiver um conjunto de dados à mão, comece por uma planilha que preparamos para este tutorial: abra a planilha de exemplo e, já no Google Sheet, clique em “Arquivo > Fazer uma cópia”, dê um nome e clique em “Fazer uma cópia”. Se você já tem uma planilha com os dados que deseja analisar, basta abri-la.

Esteja você trabalhando com uma duplicata da planilha de exemplo ou com a sua própria, é preciso dar acesso à conta de serviço do Google criada no primeiro passo:

• Clique em “Compartilhar”.
• Adicione o e-mail da conta de serviço que você copiou anteriormente como editor da planilha (ex.: gsheets-operator@<google-project-name>.iam.gserviceaccount.com).
• Clique em “Enviar”.

Tudo certo: conta de serviço ok, planilha com acesso correto ok. Hora de escrever um pouco de Python!

3. Crie um novo workbook

O próximo passo é criar um novo projeto de dados no DataLab, também chamado de workbook. Você pode criar um workbook vazio do zero, útil se quiser escrever todo o código, ou criar um workbook que já vem com todo o código incluído.

4. Armazene as credenciais da sua conta de serviço no DataLab

Para usar o JSON de credenciais da conta de serviço no seu novo workbook, você precisa armazená-lo no DataLab.

Você até poderia copiar e colar o conteúdo do JSON dentro de uma célula de código do seu workbook recém-criado:

Essa não é uma abordagem segura e não é recomendada. Se você compartilhar seu notebook com alguém, a pessoa verá as credenciais em texto puro. Ela poderia se passar por você e acessar recursos aos quais essa conta de serviço tem acesso.

Essa abordagem também exigiria repetir esse código em todo workbook que você quiser usar para acessar dados de planilhas. Se precisar girar (rotacionar) as credenciais, terá que editar cada workbook.

Uma abordagem mais segura e escalável é armazenar o JSON em uma variável de ambiente. Ao conectar essa variável, ela ficará disponível na sua sessão Python. No seu workbook:

• Clique na aba “Environment” no lado esquerdo
• Clique no ícone de mais ao lado de “Environment Variables”
• No modal “Add Environment Variables”:
  • Defina “Name” como GOOGLE_JSON
  • Defina “Value” como o conteúdo completo do arquivo JSON da conta de serviço que foi baixado. Abra o arquivo, selecione tudo, copie e cole no campo Value.
  • Defina “Environment Variables Set Name” como “Google Service Account” (pode ser qualquer nome)

Depois de preencher todos os campos, clique em “Create”, “Next” e, por fim, “Connect”. Sua sessão do DataLab será reiniciada e a GOOGLE_JSON ficará disponível como variável de ambiente no seu workbook. Você pode verificar criando uma célula Python com o código abaixo e executando:

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)

Se quiser reutilizar as mesmas credenciais da conta de serviço em outro workbook, não precisa configurar a variável de ambiente de novo: é só reutilizá-la nos outros workbooks.

5. Leia dados do Google Sheets

Agora vem a parte divertida: escrever código Python para conectar aos dados do Google Sheets.

Instale e importe os pacotes certos

Comece instalando e importando todos os pacotes necessários para configurar a conexão com sua conta da Google API. Vamos usar o google-auth para autenticar na API do Google, o gspread para interagir facilmente com planilhas do Google e o pandas para manipular dados tabulares em Python.

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import os

Configure um cliente com credenciais com escopo

Carregue a variável de ambiente GOOGLE_JSON e armazene-a em uma variável:

import os
google_json = os.environ["GOOGLE_JSON"]

Crie um objeto de credenciais a partir do JSON da conta de serviço:

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)

Atribua o ‘escopo da API’ correto às credenciais para garantir as permissões necessárias para interagir com a planilha do Google:

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)

Crie um novo cliente com essas credenciais com escopo:

client = gspread.authorize(creds_with_scope)

Leia dados da planilha em um DataFrame

Com o cliente pronto, podemos ler os dados da planilha criada anteriormente.

Primeiro, crie uma instância da planilha a partir da URL:

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')

Depois, foque na worksheet (a aba da planilha do Google) da qual você quer obter os dados. Vamos pegar a primeira aba usando o índice 0:

worksheet = spreadsheet.get_worksheet(0)

Primeiro, obtenha todos os registros em formato JSON:

records_data = worksheet.get_all_records()

Em seguida, converta os registros JSON em um DataFrame do pandas:

records_df = pd.DataFrame.from_dict(records_data)
records_df

Sucesso!

6. Analise os dados da planilha em Python

Agora você pode usar Python para analisar os dados lidos da planilha do Google. O código mínimo abaixo assume que você está usando a planilha de exemplo. Se estiver trabalhando com outro conjunto de dados, esses passos podem não funcionar. Confira o curso Data Manipulation with pandas da DataCamp para se aprofundar!

Vamos calcular a mediana de investimentos por país:

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

7. Escreva os resultados no Google Sheets

Se você quer que os usuários da planilha com quem colabora vejam o resultado do seu trabalho diretamente lá, pode escrever os resultados da sua análise em Python em uma nova worksheet (isto é, aba) dentro da planilha original.

Neste exemplo, criamos uma nova worksheet chamada median_country, caso ela ainda não exista, e depois inserimos o conteúdo do DataFrame median_funding_df, incluindo o cabeçalho com os nomes das colunas. Esse código foi gerado com a ajuda do AI Assistant!

# Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new worksheet named 'median_by_country' in the Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Write the medians_list to the new worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

Ao visitar sua planilha novamente, você verá uma nova aba com os dados do DataFrame! Essa escrita de resultados só é possível usando a conexão via API do Google Sheets; não funciona com o conector nativo do Google Sheets do DataCamp apresentado no início deste tutorial.

Conclusão

Dependendo da sua necessidade, analisar dados do Google Sheets com Python pode ser simples ou exigir uma configuração inicial mais robusta. Se você só quer ler dados de uma aba, use o conector nativo e comece em menos de cinco cliques. Se precisa ler e também escrever dados no Google Sheets, vale ativar a API do Google Sheets e configurar uma conta de serviço.

Nos dois casos, depois que tudo está configurado, acessar dados do Google Sheets fica quase tão fácil quanto consultar um arquivo CSV. A grande vantagem é encontrar seus stakeholders onde eles estão: é bem provável que parte dos seus colegas seja fã de planilhas.

Chega de exportações trabalhosas de CSV e viva os fluxos automáticos e poderosos para trabalhar com dados do Google Sheets sem sair do DataLab.