¡Hola a todos! Bienvenidos a este nuevo post. ¿Sabías que puedes pedirle a un programa que realice tus tareas repetitivas en Excel o que podrías usar unas Hojas de cálculo de Google cómo si fueran una base de datos?
A continuación aprenderás a activar los servicios en la nube de Google, a crear tus credenciales de autenticación y a leer, añadir, eliminar y actualizar datos en una hoja de cálculo utilizando la API Google Sheets, la cual puedes utilizar de forma 100% gratuita.
Como requisitos para realizar simplemente tienes que tener:
- Una cuenta de Google.
- Python 2.6 o superior.
- La herramienta pip (Package installer for Python) para descargar e instalar los módulos necesarios.
- Crear un proyecto nuevo en tu cuenta de Google Cloud Console
- Obtener tus credenciales de Autorización para crear aplicaciones.
Cómo iniciar
Puedes descargar la última versión de Python en su página oficial: Python 3.9.7.
Cuando realizas la instalación de Python viene con pip por defecto.
Para crear un proyecto tienes que ir a: https://console.cloud.google.com
Vas a iniciar sesión con tu cuenta de Google y verás tu menú principal o Dashboard y lo primero que tendrás que hacer es darle clic a la lista de proyectos de tu cuenta, como se muestra en la imagen:
Aquí vas a crear un nuevo proyecto, Google te permite crear 25 proyectos de forma gratuita, usando sus servicios, así que elige bien qué proyectos quieres hospedar o resolver con Google Cloud:
Le vas a colocar un nombre a tu proyecto e informar si está relacionado a alguna organización o ninguna.
Paso 1: Activación de la API de Google Sheets
Para trabajar con las APIs de Google necesitas unas credenciales, las cuales serán tus claves privadas, vinculadas a tu cuenta de Google Cloud. Para ello, tendrás que darle clic al botón de Habilitar API y servicios:
A continuación, buscarás que APIs o productos de Google quieres utilizar para tu proyecto, en este caso la API de Google Sheets:
Luego tendrás que darle clic al botón de azul para habilitar el API:
Para realizar la autenticación en el sistema tendrás que crear una de una cuenta de servicio (Service Account) o un ID de OAuth2, el segundo es más complejo y se usa cuando tu aplicación sale al público y necesita acceder a datos de otros usuarios.
Paso 2: Crear una cuenta de servicio
Una cuenta de servicio otorga los privilegios para todo el dominio Le permite a tu código acceder a tus recursos y datos de tu aplicación.
También puedes ver el botón de crear credenciales y tu cuenta de servicio al habilitar la API:
Luego de presionar el botón de crear credenciales, tendrás que indicar que API estás utilizando (Google Sheets), a qué datos tendrá acceso, ya sea datos de usuario (email, nombres, edad, entre otros.) o datos de aplicación (solo datos que pertenecen a la aplicación, es decir, servicios interactuando con otros servicios o aplicaciones, bases de datos, etc.). En nuestro caso, solo accederemos a datos de la aplicación y seleccionaremos la segunda opción.
Luego indicarás si tu proyecto está relacionado con otros servicios en la nube de Google (Compute Engine, Kubernetes, Engine, App Engine o Cloud Functions), los cuales no utilizaremos así que elegiremos la segunda opción.
A continuación, agregarás detalles a tu cuenta de servicio, un nombre, un ID único, y una descripción.
El siguiente paso es otorgar permisos, tanto para que la cuenta de servicio acceda al proyecto, la cuál diremos que sí y si deseamos que otros usuarios tengan acceso, que en estos momentos colocaremos que no. Finalmente presionamos el botón de "Listo", para finalizar y crear la credenciales.
Paso 3: Crear tus credenciales de autenticación
Las credenciales son el mecanismo de autenticación para que solo tú como desarrollador o tu aplicación puedan acceder a los datos relacionados con el proyecto. El archivo JSON, que puedes cambiarle el nombre a credentials.json, te permitirá acceder a tus recursos de nube. Guarda la clave en un lugar seguro.Para crear una clave, tienes que darle clic al correo electrónico de tu cunta de servicio para ver los detalles:
Luego irás a la pestaña de Claves, seleccionarás el tipo de clave en formato JSON (Javascript Object Notation) y se guardará el archivo en tu computadora, al cual deberás cambiarle el nombre a credentials.json para que coincida con el del código.
Paso 4: Instalar las bibliotecas o módulos necesarios
En tu consola o terminal escribirás el siguiente comandopip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
Aquí actualizarás e instalarás:
google-api-python-client: el Cliente de Python para conectar con la API.
google-auth-httplib2: biblioteca que permite las solicitudes http para la autenticación con los servidores de Google
google-auth-oauthlib Biblioteca para la autenticación con O-Auth. Útil para que tu aplicación puede interactuar con otras aplicaciones y con los servicios de Google.
Ahora sí, el código. Vas a crear un entorno virtual de Python por si deseas mantener todo tu proyecto aislado del resto de otros proyectos.
Para ello, escribe los siguientes comandos en tu terminal para crear un entorno virtual:
python -m venv name_of_your_enviroment
Luego tendras que activarlo con el siguiente comando:
Then you will need to activate it:
source name_of_your_enviroment/Scripts/activate
Ahora, cada módulo se descargará y se insalará solo en esta carpeta, aislada de otros proyectos y versiones, listo para subir a plataformas como Github.
Now, every module will be only installed in this folder.
Ahora, tienes que cambiar de directorio utilizando el siguiente comando.
Then, you need to change directory command to swap to the actual folder of the project:
cd name_of_your_enviroment
Luego tendrás que crear tu hoja de cálculo, ve a:
Crea tu propia hoja y asígnale un nombre y presiona guardar. Obtendrías un resultado como el siguiente:
Las credenciales las tienes que arrastrar a la carpeta de tu proyecto, la de tu entorno virtual:
Este es el código base para realizar la operación más básica con las hojas de cálculo de Google, la petición GET. Vas a crear un archivo y le puedes poner por nombre: sheets.py y colocarás lo siguiente (todo va en el mismo archivo):
Importa las bibliotecas o módulos necesarios:
from __future__ import print_function
import os.path
from googleapiclient.discovery import build
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google.oauth2 import service_account
Define los alcances del código o de la aplicación, en este caso esta acceder a las hojas de cálculo de Google con el permiso de solo lectura, es una lista de Python:
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
Si deseas un acceso completo, lectura, escritura, manipulación de archivos, puedes colocar los siguientes alcances(scopes):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive','https://www.googleapis.com/auth/drive.file']
Escribe el ID de tu hoja de cálculo que quiere leer o editar, la primera opción es poco o nada recomendable porque lo deja expuesto al público.
# Define cual es la URL de tu hoja de cálculo
SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
La segunda opción es utilizando variables de entorno, definidas en tu sistema operativo. Utiliza una de ellas. Aquí SS_ID es tu identificador real y debe estar guardada dentro de tu sistema. Elige una de las dos opciones, si tu aplicación es solo para tí no hay riesgo.
# Define el ID utilizando una variable de entorno.
spreadsheet_ID = os.environ.get('SS_ID')
Define que rango de valores quieres leer o escribir con el siguiente formato: El nombre de la hoja, signo de exclamación hacia abajo (!), Columna 1 Fila 1, dos puntos (:) Columna 2 Fila 2.
#Define que rango de valores leeás.
RANGE_NAME = 'Hoja!A2:E'
Esto crearía un "rectángulo" de selección donde A2 es la esquina superior izquierda, y E es la esquina inferior derecha. Si no se coloca la fila como se muestra arriba, indica que es toda la fila.
Creamos una función para autenticarnos y acceder a los servicios
def auth():
creds = None
# Si no existen credenciales válidas, permitir al usuario que inicie sesión.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
#Accedemos al archivo que descargaste "credentials.json":
creds = service_account.Credentials.from_service_account_file(
SERVICE_ACCOUNT_FILE, scopes=SCOPES)
#Instanciamos o llamamos a servicio de Google Sheets
service = build('sheets', 'v4', credentials=creds)
return service
Ahora crearemos una función para leer u obtener valores de la hoja, se le pasa como parámetros el ID de la hoja y el rango de valores o las celdas a leer:
def get():
"""Read all the rows from a spreadsheet .
Prints values from a spreadsheet.
"""
#Autenticamos
service = auth()
# Call the Sheets API
sheet = service.spreadsheets()
#Realizamos la llamada a la API para obtener datos
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
range=RANGE_NAME).execute()
#Los resultados se almacenarán en la variable values (una lista o array)
values = result.get('values', [])
if not values:
print('No data found.')
else:
print('Title, Content')
# Crea un ciclo for e itera por cada fila en la hoja de cálculo.
for row in values:
if len(row)>0:
#Imprime la primera y la última columna, con índices 0 y -1
print('%s, %s' % (row[0], row[-1]))
return result
A continuación se creará una función para escribir dentro de la hoja de cálculo:
def write(new_row):
"""Add a new row.
Append new values to a sample spreadsheet.
"""
#El contenido a escribir es una lista o array y se almacena en body["values"]
values = [new_row]
body = {
'values': values
}
#Autenticamos
service = auth()
range_name = f"{SHEET_NAME}!A2:G"
#Realizamos la llmamada para agregar un nuevo valor o una fila de valores
result = service.spreadsheets().values().append(
spreadsheetId=SPREADSHEET_ID, range=range_name,
valueInputOption='USER_ENTERED', body=body).execute()
if(result.get("tableRange") != None):
print('1 row added at ' + result.get("tableRange"))
Existirá un momento donde deseemos actualizar datos existentes, así que crearemos una función para actualizar, y se le pasa como parámetro una lista y un ID, que es el número de fila en la hoja de cálculo:
def update(id, content):
#To avoid changing Headers row
if(id > 1):
#Autenticamos
service = auth()
#Search row by its ID
range_name = f'{SHEET_NAME}!A{id}:G{id}'
values = [content]
body = {
'values': values
}
#Send the update call to the API
result = service.spreadsheets().values().update(
spreadsheetId=SPREADSHEET_ID, range=range_name,
valueInputOption='USER_ENTERED', body=body).execute()
#Print a message to the user
print('{0} cells updated.'.format(result.get('updatedCells')))
Finalmente, crearemos una función para eliminar datos, que recibe un id cómo parámetro y representa el número de fila, para ello utilizaremos la función clear de Google Sheets:
def delete(id):
if(id > 1):
service = auth()
sheet = service.spreadsheets()
range_name = f'{SHEET_NAME}!A{id}:G{id}'
result = sheet.values().clear(spreadsheetId=SPREADSHEET_ID,
range=range_name).execute()
values = result.get('values', [])
for row in values:
print(row)
print('1 cell deleted at '+ result.get("clearedRange"))
not_null = list(filter(lambda x: len(x) > 0, get().get('values')[0:]))
body = {
'values': not_null
}
result = sheet.values().clear(spreadsheetId=SPREADSHEET_ID,
range=f"{SHEET_NAME}!A2:G").execute()
result = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=f"{SHEET_NAME}!A2:G",
valueInputOption='USER_ENTERED', body=body).execute()
else:
print("Índice incorrecto")
El proceso para oborrar es el siguiente:
- Borramos una fila que posee cierto índice (ID)
- Hacemos una petición GET para ver que filas no tienen espacios vacíos.
- Las que no son nulas se guardaran en una variable not_null
- Se realizará una llamada update para actualizar la hoja con todos los datos no nulos.
De esta manera, borraremos una fila y no quedará el espacio vacío sino que se actualizará toda la hoja.
Esta es una solción bastante útil, ya que Google Sheets tiene una interfaz muy intuitiva y es gratuita. Por otra parte, es importante que tengas en cuenta los límites de uso, permite:
- 500 peticiones por cada 100 segundos por proyecto.
- 100 peticiones por cada 100 segundos por usuario.
Esto es solo el inicio, leer, escribir, actualizar y borrar, pero esto tiene un gran potencial, puedes utilizar las hojas de cálculo junto a un bot, almacenar datos scrapeados de la web, automatizar listas, calificaciones, reportes, asientos contables, etc.
Te doy un pequeño adelanto, te mostraré como podemos obtener el precio de apertura, el precio de cierre, el precio mínimo y el precio máximo de cierta criptomoneda, utilizando websockets.
En la consola o terminal del editor escribimos:
pip install websockets
Luego, tienes que crear un archivo que puedes nombrar index.py y escribirás lo siguiente:
import websocket
import json
#El nombre del archivo que creaste para las hojas de cálculo
import sheets
SOCKET = "wss://stream.binance.com:9443/ws/cakeusdt@kline_1m"
#Start values
maximos = []
minimos = []
min_status = "-"
max_status = "-"
#Websockets events
def on_open(ws):
print("Opened")
def on_message(ws, message):
global maximos, minimos, min_status, max_status
##Convert the received message in JSON format into a Python Object
message = json.loads(message)
#Get the candlestick (k) and verify if it is closed
candle = message['k']
timestamp = candle['t']
candle_is_closed = candle['x']
#if candle is closed ['x'] get the close price: candle['c']
if candle_is_closed:
open_price = float(candle['o'])
close_price = float(candle['c'])
minimos.append(float(candle['l']))
maximos.append(float(candle['h']))
if len(minimos) > 1:
new_min = float(minimos[-1])
last_min = float(minimos[-2])
print(last_min, new_min)
if new_min < last_min:
min_status = "Mínimo más bajo"
print("Mínimo más bajo")
elif new_min > last_min:
min_status = "Mínimo más alto"
print("Mínimo más alto")
else:
min_status = "Sin cambio"
print("Igual")
if len(maximos) > 1:
last_max = float(maximos[-2])
new_max = float(maximos[-1])
print(last_max, new_max)
if new_max < last_max:
max_status = "Máximo más bajo"
print("Máximo más bajo")
elif new_max > last_max:
max_status = "Máximo más alto"
print("Máximo más alto")
else:
max_status = "Sin cambio"
print("Igual")
print("Minimos", minimos)
print("maximos", maximos)
lista = [open_price, close_price, minimos[-1], maximos[-1], min_status, max_status]
#Llamar a la función escribir que definimos previamente
sheets.write(lista)
print("Written to the spreadsheet")
def on_close(ws):
print("Closed connection")
ws = websocket.WebSocketApp(SOCKET, on_open=on_open, on_close=on_close, on_message=on_message)
ws.run_forever()
Nos conectamos al websocket de Binance y obtenemos datos de las velas japonesas de un par de criptos, con una temporalidad de 1 minuto, si la vela cerró, se almacena el precio mínimo ['l'], el máximo ['h'], el precio de apertura ['o'] y el de cierre ['c'], con estos datos podemos hacer un análisis técnico y nos puede ayudar a tomar mejores decisiones de inversión.
Por último, ejecutamos nuestro código, abrimos una terminal o consola de comandos y escribimos lo siguiente:
python index.py
Esta línea llamará al código principal, se reciben datos de Binance y cada minuto se llama a la función write que se encuentra en el otro archivo sheets.py.
Una de las cosas que se puede hacer es es comparar los dos últimos mínimos y máximos y verificar si es más bajo o más alto que el anterior. Y finalmente escribimos en la hoja de cálculo con la función write que definimos previamente y se le pasa como parámetro una lista de 6 valores.
Obtenemos el siguiente resultado, todo automático:
Pero el funcionamiento a detalle de este código lo veremos en otro post ya que este se hizo demasiado largo.
¿Qué te ha parecido este post? ¿Se te ocurre una gran idea? ¿Has encontrado algún error? Déjamelo saber en los comentarios
Disclaimer:Todas las imágenes son capturas de pantalla en la plataforma de Google Cloud y Google Sheets, así como de mi editor de código Visual Studio Code para reflejar el proceso.
Saludos @luis96xd verdaderamente impresionante todo este trabajo de programación que has realizado, y el plasmar todo ese esfuerzo en un post para compartirlo con todos los hiverlectores demuestra lo que significa agregarle valor a esta comunidad. Gracias por compartir tu trabajo con nosotros. En lo particular me inspira a cada día esforzarme por realizar contenidos de valor. Excelente @luis96xd
Muchas gracias Sr. Ysrael por sus palabras alentadoras, me alegra mucho que haya pasado por aquí y le haya gustado el contenido, espero que le haya sido de mucha ayuda
¡Saludos!
The rewards earned on this comment will go directly to the person sharing the post on Twitter as long as they are registered with @poshtoken. Sign up at https://hiveposh.com.
Congratulations @luis96xd! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :
Your next target is to reach 600 upvotes.
You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOPTo support your work, I also upvoted your post!
Check out the last post from @hivebuzz: