Olá, Internet. Tudo certo?

Neste artigo, vou explicar na prática o uso do Google Sheets e sua API para leitura e envio das informações contidas em uma planilha do Google. Esse recurso é muito interessante, pois ele permite que você acesse as informações que estão em nuvem e possibilita pegar outras informações que estão armazenadas localmente e colocar em nuvem.

Uma outra ideia que pode ser feita (caso não sejam muitas informações), é a utilização de gráficos em uma outra sheet, consumindo a sheet que está sendo enviada às informações. Dessa forma, você pode acompanhar da onde estiver com atualizações em tempo real (graças a eficiência do Google Docs):

Em minha aplicação Python, eu realizo a atualização das linhas somente para o Sheet1, enquanto o Sheet2 tem o gráfico que consome os dados Sheet1 e é atualizado em tempo real (sem refresh de página).

Temos um “ferramenta de B.I” sem custo na nuvem

Vamos à prática!

Primeiramente, para fazer a prática deste artigo, você precisa ter os seguintes itens:

1. Uma conta no Gmail para utilização do Google Docs.
2. Conhecimentos básicos de programação, não será necessário saber Python a fundo.
3. Ter instalado a versão do Python 2.7 em seu computador.
4. Utilizar algum editor de texto, ou de preferência uma IDE Python. Eu utilizo o PyCharm.
5. Fazer o download da biblioteca gspread para utilização da API do Google Sheets.
6. Fazer o download da biblioteca pyodbc para conexão com o SQL Server.

Em relação às bibliotecas, caso não saiba instalar, não se preocupe, vou mostrar como fazer o mesmo.

Criando o projeto e habilitando a API no Google Developers.

Primeiramente, acesse o link QuickStart para utilizar o Wizard da Google, realizar a criação do seu projeto e habilitar a API do Google Sheets de forma rápida. Será apresentada uma tela como esta:

Clique no link da pagina “this wizard”, e selecione a opção criar projeto:

Clique em continuar, depois será mostrado que a API está ativada:

Clique em Acessar credenciais e vamos adicionar as credenciais ao nosso projeto. Na tela seguinte, selecione as seguintes opções:

Estamos adicionando a credencial referente ao Google Sheets API. Vamos realizar a chamada de uma “aplicação web” e queremos acessar os dados que pertencem ao nosso próprio aplicativo (Dados do aplicativo).

Após isso, clique no botão “Preciso de quais credenciais?”. Será aberto a opção para criar uma conta de serviço. Esta conta de serviço é a responsável por manipular nossas planilhas Google (como se fosse uma outra conta da Google):

1. Nome da conta de serviço: pode colocar qualquer nome.
2. ID da conta de serviço: coloque algo parecido com o nome da conta de serviço, desde que não contenha caracteres especiais e nem espaços.
3. Papel: selecione todos os papeis marcando as opções de administrador.
4. Tipo de chave: selecione a JSON, que será o tipo que utilizaremos para acessar a Chave de autenticação.

Clique em continuar e será feito o download automaticamente da chave de autenticação em json:

Após feito o download, coloque o arquivo Json de preferência em uma pasta onde vamos criar nossas aplicações .py:

Não se preocupe com os demais arquivos, apenas crie uma pasta e coloque seu Json nesta pasta ou outro diretório que preferir. Feito isso, vamos agora ao PyCharm para começar a utilizar o Google Sheets.

Voltando à pagina da Google, após o download do Json, selecione a opção Gerenciar contas de serviço:

Será mostrado o seu projeto. Selecione a opção permissões:

Depois, adicione o seu account service que foi criado anteriormente (Conta de serviço) como membro do projeto, e atribua as permissões conforme a imagem abaixo:

Após adicionar como membro, selecione no menu esquerdo a opção Biblioteca. Você será redirecionado para página abaixo:

Selecione a opção Drive API em APIs do G Suite, e depois clique em Ativar. Será mostrado a tela abaixo com um gráfico das requisições da API:

Hora do Pycharm

Após realizar a instalação do PyCharm community, crie um projeto, clique com botão direito e selecione a opção: New->Directory, depois clique com botão direito novamente nessa pasta criada dentro do seu projeto e selecione a opção Show in Explorer. Será aberta a pasta que foi criada do seu projeto. Copie e cole seu arquivo Json criado anteriormente para dentro desta pasta.

Instalando as bibliotecas – Gspread e Pyodbc.

Na terminal no Pycharm, podemos fazer comandos Pip para instalação das bibliotecas:

Para realizarmos a instalação das bibliotecas necessárias, digite:

pip install gspread

Após instalar o Gspread, vamos instalar agora o Pyodbc:

pip install pyodbc

Feita a instalação, as bibliotecas estarão dentro do External Libraries->lib->site-packages.

Codificando

Vamos criar três arquivos python para esse artigo. Um será responsável pela conexão com o banco de dados SQL Server e demais funcionalidades do Pyodbc. Outro será responsável pela autenticação oauth2 com nosso serviço do google sheets através do arquivo json criado e um arquivo principal, onde vamos utilizar as duas classes criadas (pyodbc e gspread), e para começar a manipular nossa planilha Google, a estrutura do projeto fica da seguinte forma:

Vamos primeiramente criar o database.py que ira utilizar os recursos do Pyodbc. Inicialmente vamos codificar apenas a conexão:

Importando a classe pyodbc e criando uma classe chamada dbsql com um método construtor que cria um objeto chamado Conn, recebendo o resultado da conexão conforme os parâmetros. Os parâmetros são basicamente:

Driver – Driver de conexão com o banco (SQL Server,Access,ORACLE etc).
SERVER – Nome do computador que se encontra instalado o SQL Server.
DATABASE – Nome do banco de dados.
UID – Nome do login de conexão da instância.
PWD – Senha referente ao login de conexão da instância.

Criado o database.py, salve, e vamos para a próxima classe:

Na linha 1 estamos importando a classe gspread.

Na linha 2, importando a classe ServiceAccountCredentials do pacote oauth2cliente – classe já padrão na versão do Python 2.7+ , contudo, por via das dúvidas é interessante realizar um upgrade para verificar novas atualizações. Basta ir no console e digitar o comando: pip install –upgrade oauth2client

Linha 4, criando a classe conn_sheet com o método construtor __init__ e tendo três atributos: scope, credentials e gc.

O atributo scope recebe o papel de autenticação referente a API, onde podemos restringir entre acesso total, leitura e escrita.

https://www.googleapis.com/oauth/drive.file

https://www.googleapis.com/auth/drive.readonly

https://www.googleapis.com/auth/drive.modify

Em nosso caso, vamos utilizar o scope drive.file para permissão total. No atributo credentials, estamos obtendo a chave de credenciamento juntamente com o nosso scopo do arquivo json obtido na criação do serviço no Google Developer. E por último, no atributo gc, temos o resultado da autorização pelo gspread passando como atributo o credentials (o credenciamento).

Agora vamos para o arquivo main.py que será responsável pelos nossos testes com o envio de informações do SQL Server para o Google Sheets, bem como a leitura do que foi enviado para o Python.

Vamos entender o que esta sendo feito nesta primeira prática.

1. Os dois primeiros from são referentes às classes criadas para conexão com o banco de dados e conexão com o Google Sheets através do gspread.
2. Instanciamos a classe conn_sheet e atribuímos a uma variável sheet que terá todos os atributos da classe conn_sheet.
3. Atribuímos à variável conn, a propriedade gc da classe conn_sheet, pois o gc contém a autenticação.
4. Através do objeto conn, executamos o método create para criar uma planilha na conta de serviço criada anteriormente e colocamos o retorno na variável sh.
5. Após a criação, utilizamos o método share para que a planilha seja compartilha da conta de serviço criada para nosso e-mail pessoal, onde poderemos acompanhar, em tempo real, a atualização das linhas do Google Sheets.
6. Depois de compartilhado, realizamos a abertura dessa planilha na aba 1, ou seja, sheet1.
7. Eu comentei o método clear(), pois como estamos criando uma planilha nova, não é necessário limpar todas as células, mas fica a titulo de curiosidade. Esta função limpa toda a planilha atual (aba selecionada sheet1, no caso).
8. Realizamos a conexão com nosso SQL Server utilizando a classe criada dbSql() e já passando o atributo conn que contém toda a string de conexão.
9. Depois criamos um cursor com nosso objeto sqlserver para verificarmos linha a linha o resultado do nosso select.
10. Utilizando o objeto cursor, executamos o método execute, passando como parâmetro nosso T-SQL.
11. Crio uma variável x que será utilizada como ponteiro de linha da nossa planilha.
12. Utilizando objeto sh que contém a planilha.sheet1, realizamos um update nas primeiras linhas com o cabeçalho da nossa “tabela”.
13. Depois, realizo um for passando a variável row. O resultado que contém cada coluna da linha atual retornado pelo nosso select.
14. Realizo uma atualização de célula novamente para a segunda linha em diante com o resultado do meu select, para suas respectivas colunas.
15. Atribuo a próxima linha da minha planilha a variável x.

 

Ao fim da execução do código, toda sua planilha vai estar com os dados obtidos do seu SQL Server. A função Share() envia para seu e-mail pessoal, a planilha criada:

Ao abrir, será possível acompanhar em tempo real enquanto seu código Python realiza as atualizações. Isso porque o share foi executado antes dele começar a atualizar as linhas:

A estrutura da tabela de vendas que criei no meu SQL Server ficou da seguinte forma –  foi criada especialmente para esse artigo apenas para conhecimento:

Lembrando que podemos criar uma sheet2 e criar um gráfico apontando os dados para a sheet1 (que é atualizada pelo main.py) e acompanhar em gráfico os dados.

Espero que eu tenha compartilhado conhecimento. Obrigado!

Referências:

• http://gspread.readthedocs.io/en/latest/
• https://developers.google.com/sheets/api/quickstart/python
• https://github.com/burnash/gspread