Querying and pushing data from Python to Google Sheets

main.py

import arrow
import gspread
from oauth2client.service_account import ServiceAccountCredentials

import settings


def get_client():
    """Return a Google client API object

    Returns:
        client_class: instance of the client_class
    """
    scope = ["https://www.googleapis.com/auth/drive"]
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        settings.GOOGLE_CLIENT_SECRET_PATH, scope
    )
    client = gspread.authorize(credentials)
    return client


def get_supervisions(client, worksheetName):
    """Returns the list of supervisions contained in the worksheet.

    Args:
        client (client_class): The client object currently authenticated
        worksheetName (str): The name of the worksheet as it appears on Google Drive

    Returns:
        List of lists: The list of supervisions
    """
    sheet = client.open(worksheetName).sheet1
    records = sheet.get_all_values()
    return records


def process_supervisions(supervisions):
    """Process all the supervisions

    Args:
        supervisions ([]): the list of supervision list you want to process

    Returns:
        []: the processed list of supervision lists
    """
    fields = supervisions[0]
    values = supervisions[1:]

    actionColIndex = fields.index("Action")
    studentIdColIndex = fields.index("student id")
    supervisorIdColIndex = fields.index("supervisor id")
    statusColIndex = fields.index("Synchronization status")
    timestampeColIndex = fields.index("Synchronized at")

    for value in values:

        # only perform a change for values that did not change already
        if value[timestampeColIndex] == "":
            value[statusColIndex] = ""

            result = process_supervision(
                action=value[actionColIndex],
                studentSciper=value[studentIdColIndex],
                supervisorSciper=value[supervisorIdColIndex],
            )

            value[statusColIndex] = result
            value[timestampeColIndex] = arrow.now("Europe/Zurich").format(
                "YYYY-MM-DD HH:mm:ss"
            )

    returnValue = [fields] + values

    return returnValue


def process_supervision(studentId, supervisorId, action="add"):
    return "OK"


def update_supervisions(supervisions, client, worksheetName):
    """saves the list of supervisions to GSheet

    Args:
        supervisions (List): The list of supervisions
        client (client_class): The Google authenticated client object
        worksheetName (str): The name of GSheet
    """
    sheet = client.open(worksheetName).sheet1

    sheet.update(supervisions, value_input_option="USER_ENTERED")


if __name__ == "__main__":
    client = get_client()
    supervisions = get_supervisions(client, settings.WORKSHEET_NAME)
    supervisions = process_supervisions(supervisions)
    update_supervisions(supervisions, client, settings.WORKSHEET_NAME)

requirements.txt

gspread
python-dotenv
oauth2client
arrow

settings.py

import os
from pathlib import Path

from dotenv import load_dotenv

dotenv_path = os.getenv("ENV_FILE_PATH", Path(".") / ".env")
load_dotenv(dotenv_path=dotenv_path)

GOOGLE_CLIENT_SECRET_PATH = os.getenv("GOOGLE_CLIENT_SECRET_PATH", "client_secret.json")
WORKSHEET_NAME = os.getenv("WORKSHEET_NAME", "worksheetName")