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

avatar
(Edited)

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

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:
Open Projects Page.png

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:

Nuevo-proyecto.png

Le vas a colocar un nombre a tu proyecto e informar si está relacionado a alguna organización o ninguna.

Give-it-a-name.png

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:

Enable_API.gif

API_Panel.png

A continuación, buscarás que APIs o productos de Google quieres utilizar para tu proyecto, en este caso la API de Google Sheets:

Search_library API.png

Luego tendrás que darle clic al botón de azul para habilitar el API:

Habilitar_API.png

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.

Create_Service_account.png

También puedes ver el botón de crear credenciales y tu cuenta de servicio al habilitar la API:

Create_Credentials.png

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.

Credenciales 1.png

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.
Credenciales 2.png

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.

Functions.png

Optional.png

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:
account_details.png

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.

Create_Keys.png

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.

Carpeta_entorno_Virtual.png

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:

Empty_Spreadsheet.png

Las credenciales las tienes que arrastrar a la carpeta de tu proyecto, la de tu entorno virtual:

Credentials in root directory.png

Cada hoja tiene una URL y un ID único, el cual utilizarás para acceder a ella, para leer, escribir y actualizar datos. Ese ID debe mantenerse en un lugar secreto, aquí se muestra porque es un ejemplo, ya que el que tiene acceso a él puede leer y modificar los datos.

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/[email protected]_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:

Spreadsheet_filled.png

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 y espero verlos pronto!


0
0
0.000
6 comments
avatar

Congratulations @luis96xd! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You distributed more than 500 upvotes.
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 STOP

To support your work, I also upvoted your post!

Check out the last post from @hivebuzz:

Hive Power Up Month - Quick feedback
Feedback from the September 1st Hive Power Up Day
0
0
0.000
avatar

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

0
0
0.000
avatar

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!

0
0
0.000