Python + gspread small demo

gspread-demo.py

"""
gspread demo

gspread==4.0.1
requests==2.26.0
"""

import string
from pprint import pprint

import gspread
import requests
from gspread import Spreadsheet, Client, Worksheet, Cell
from gspread.utils import rowcol_to_a1

# paste your url to the spreadsheet (don't forget to grant access!):
# https://docs.gspread.org/en/latest/oauth2.html
SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/123"


def show_available_worksheets(sh):
    available_worksheets = sh.worksheets()

    for ws in available_worksheets:
        print("Worksheet with name", ws.title, "and id", ws.id)


def show_main_ws(sh):
    ws = sh.sheet1
    print("working on ws", ws)


def create_fill_and_delete_another_ws(sh):
    another_worksheet = sh.add_worksheet(title="Another worksheet", rows=25, cols=10)
    print(another_worksheet)
    input("enter to insert")
    another_worksheet.insert_row(list(range(10)))
    input("enter to delete")
    sh.del_worksheet(another_worksheet)


def insert_some_data(ws):
    ws.insert_rows([
        list(range(1, 40)),
        list(string.ascii_lowercase),
        list(string.ascii_uppercase),
        list(string.punctuation),
    ])


def update_table_with_empty_cells(ws: Worksheet):
    ws.update_cell(1, 4, "Hello there!")
    new_values = [[""] * 3] * 4
    print("update with values", new_values)
    ws.update("A5:C8", new_values)


def show_all_in_table(ws):
    list_of_lists = ws.get_all_values()
    print(list_of_lists)
    print("=" * 20)
    for row in list_of_lists:
        print(row)


def create_and_fill_comments_worksheet(sh):
    comments_data = requests.get("https://jsonplaceholder.typicode.com/comments").json()
    header_row = ["id", "postId", "name", "email", "body"]
    comments_ws = sh.add_worksheet("comments", rows=len(comments_data), cols=len(header_row))
    rows = [header_row]
    for comment in comments_data:  # type: dict
        rows.append([
            comment.get(row_key, "")
            for row_key in header_row
        ])

    comments_ws.insert_rows(rows)

    return comments_ws


def show_comments_worksheet(ws):
    list_of_dicts = ws.get_all_records()
    pprint(list_of_dicts)


def find_comment_by_author(ws: Worksheet):
    cell: Cell = ws.find("Haven_Barrows@brant.org")

    print("Found something at Row %s Cell %s" % (cell.row, cell.col))

    # row = ws.get
    row = ws.row_values(cell.row)
    print(row)


def do_batch_update(ws: Worksheet):
    batches = []
    for i in range(1, 20, 2):
        items_count = i + 1
        addr_from = rowcol_to_a1(i, 1)
        addr_to = rowcol_to_a1(i, items_count)
        data_range = f"{addr_from}:{addr_to}"
        print("range:", data_range)
        batch = {
            "range": data_range,
            "values": [[i] * items_count]
        }
        batches.append(batch)
    ws.batch_update(batches)


def apply_formatting(ws):
    ws.format("A2:B2", {
        "backgroundColor": {
            "red": 0.5,
            "green": 1.0,
            "blue": 0.3
        },
        "horizontalAlignment": "CENTER",
        "textFormat": {
            "foregroundColor": {
                "red": 0.2,
                "green": 0.7,
                "blue": 1.0
            },
            "fontSize": 12,
            "bold": True
        }
    })


def main():
    gc: Client = gspread.service_account(filename="./service_account.json")
    sh: Spreadsheet = gc.open_by_url(SPREADSHEET_URL)
    print("working on the Spreadsheet", sh.title, "with id =", sh.id)
    show_available_worksheets(sh)
    show_main_ws(sh)
    create_fill_and_delete_another_ws(sh)
    ws = sh.sheet1
    insert_some_data(ws)
    update_table_with_empty_cells(ws)
    show_all_in_table(ws)
    comments_ws = create_and_fill_comments_worksheet(sh)
    # comments_ws = sh.worksheet("comments")
    show_comments_worksheet(comments_ws)
    find_comment_by_author(comments_ws)
    do_batch_update(ws)
    apply_formatting(ws)


if __name__ == '__main__':
    main()