google_sheet.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from typing import Dict

class SheetCRUDRepository:
    def __init__(self, worksheet):
        self.worksheet = worksheet
        self.titles = self.worksheet.row_values(1)  # Assuming titles are in the first row
        assert len(set(self.titles)) == len(self.titles), f"Failed to init {SheetCRUDRepository.__class__}, titles: {self.titles} contain duplicated values!"

    def create(self, data: Dict):
        values = [data.get(title, '') for title in self.titles]
        self.worksheet.append_row(values)

    def read(self, row_index: int) -> Dict:
        values = self.worksheet.row_values(row_index)
        return {title: value for title, value in zip(self.titles, values)}

    def update(self, row_index: int, data: Dict):
        values = [data.get(title, '') for title in self.titles]
        self.worksheet.update(f"A{row_index}:Z{row_index}", [values])

    def delete(self, row_index: int):
        self.worksheet.delete_row(row_index)

    def find(self, search_dict):
        for col_title, value in search_dict.items():
            if col_title in self.titles:
                col_index = self.titles.index(col_title) + 1  # Adding 1 to match gspread indexing
                cell = self.worksheet.find(value, in_column=col_index)
                if cell is None:
                    break
                row_number = cell.row
                return row_number, self.read(row_number)
        return None

def create_repositories():
    scope = [
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive'
    ]
    creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
    client = gspread.authorize(creds)
    sheet_url = "https://docs.google.com/spreadsheets/d/17OxKF0iP_aJJ0HCgJkwFsH762EUrtcEIYcPmyiiKnaM"
    sheet = client.open_by_url(sheet_url)
    worksheet = sheet.get_worksheet(0)
    account_repository = SheetCRUDRepository(worksheet)
    return account_repository


if __name__ == "__main__":
    a = create_repositories()
    print(a)