Cómo Automatizar el llenado de hojas de Cálculo con Google Sheets API - [SPA][ENG]

¡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:

1. Una cuenta de Google.
2. Python 2.6 o superior.
3. La herramienta pip (Package installer for Python) para descargar e instalar los módulos necesarios.
4. Crear un proyecto nuevo en tu cuenta de Google Cloud Console
5. Obtener tus credenciales de Autorización para crear aplicaciones.

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:

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.

Paso 4: Instalar las bibliotecas o módulos necesarios

En tu consola o terminal escribirás el siguiente comando

pip 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:

• https://docs.google.com/spreadsheets/

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:

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')))

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")

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.