spreadsheet-delete.py

import requests
import os.path
import pathlib
import sys
import re

from enum import Enum

from typing import List, Dict, Any, TypedDict


from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
import datetime

DRY_RUN = True

SCOPES = [
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/spreadsheets',
]
class DateType(Enum):
    ctime = 1
    mtime = 2
    vtime = 3

# i need this to be define this 
class Sheet:
    def __init__(self, id: str,name: str, deletable: bool, owner: str, cTime: str, mTime: str, vTime: str):
        self.id = id
        self.name = name
        self.d = deletable
        self.owner = owner
        # this wasn't needed anymore since owner will absolutely have a times modified and viewed 
        self.ctime = datetime.datetime.fromisoformat(cTime)

        if not mTime == "":
            self.mtime = datetime.datetime.fromisoformat(mTime)
        else:
            print("MTIME",mTime)
            self.mtime = ""
        if not vTime == "":
            self.vtime = datetime.datetime.fromisoformat(vTime)
        else:
            print("VTIME", vTime)
            self.vtime = ""
    
    def p(self) -> str:
        # print()
        return f"{self.name:50} {self.id:35} {self.owner:10} {self.d:10} {self.ctime} {self.mtime} {self.vtime}"

    def compare(self, date: datetime.datetime, datetype: DateType = DateType.vtime) -> bool:
        if datetype == DateType.ctime:
           selected_date = self.ctime
        elif datetype == DateType.mtime:
            selected_date = self.mtime
        else:
            selected_date = self.vtime

        return date > selected_date


FILES: Dict[str, Sheet] = {}

def auth(cred_path_str: str = "creds.json") -> Credentials:
    creds: Credentials | None
    token_path = pathlib.Path("token.json")
    cred_path = pathlib.Path(cred_path_str)
    print(cred_path)
    creds = None
    if token_path.exists():
        creds = Credentials.from_authorized_user_file(str(token_path), SCOPES)
    
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            if not cred_path.exists():
                print("Missing oauth credentials!")
                sys.exit(1)
            flow = InstalledAppFlow.from_client_secrets_file(str(cred_path), SCOPES)
            creds = flow.run_local_server(port=0)
            with token_path.open("w") as f:
                f.write(creds.to_json())
    return creds

def list_sheets(creds: Credentials) -> List[Dict[str, Any]]:
    srvc = build("drive", "v3", credentials=creds)

    query = "mimeType='application/vnd.google-apps.spreadsheet'" 
    fields = "files(id, name, owners, permissions, mimeType, createdTime, modifiedTime, viewedByMeTime)" 

    res = srvc.files().list(q=query, fields=fields).execute()
    files = res.get("files",[])


    return files

def download_sheet(file_id: str,fname: str,  creds: Credentials) -> bool:

    mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    folder = pathlib.Path("./dload/")
    folder.mkdir(parents=True, exist_ok=True)

    try:
        srvc = build("drive", "v3", credentials=creds)
        request = srvc.files().export_media(fileId=file_id, mimeType=mime_type)
        dat = request.execute()


        with open( (folder + fname), "wb") as f:
            f.write(dat)
    except HttpError as e:
        print(f"Download Failed: file: {file_id} {e}")
        return False

    return True
    


def process_sheets(files: List[Dict[str, Any]], 
                  reg_str: str = "", 
                  date: datetime.datetime | None = None,
                  datetype: DateType | None = None) -> None:
    name = 40
    id = 35
    role = 10
    if not files:
        print("No spreadsheets found.")
        return

    print(f"{'Sheet Name':50} {'File ID':40} {'User Role':10} {'deletable':10}")
    print("-" * 120)

    for f in files:
        name = f.get("name", "Unnamed")
        file_id = f.get("id", "N/A")

        permissions = f.get("permissions", [])
        # print(f)
        cdate = f.get("createdTime", "")
        mtime = f.get("modifiedTime","")
        vtime = f.get("viewedByMeTime", "")

        role = "unknown"

        for p in permissions:
            if p.get("emailAddress"):  # could refine to match current user
                role = p.get("role")

        # only get sheets that are owned by the user for now  
        sheet = Sheet(file_id, name, role == "owner", role, cdate, mtime, vtime)

        if not reg_str == "" and datetype != None and date != None:
            if sheet.compare(date, datetype) and filter_sheet(reg_str):
                FILES[sheet.name] = sheet
        elif date != None:
            if datetype == None:
                if sheet.compare(date):
                    FILES[sheet.name] = sheet
            else:
                if sheet.compare(date, datetype):
                    FILES[sheet.name] = sheet 
        else:
            if filter_sheet(sheet, reg_str):
                FILES[sheet.name] = sheet

        if FILES[sheet.name]:
            sheet.p()
            

def filter_sheet(sheet: Sheet, reg_str: str) -> bool:
    matches = re.search(reg_str, sheet.name)
    return matches is not None
    

   
def delete_sheet(sheet: Sheet, creds: Credentials) -> bool:
    try:
        service = build("drive", "v3", credentials=creds)
        service.files().delete(fileId=sheet.id).execute()
    except HttpError as e:
        print(f"Failed to delete spreedsheet: {sheet.name} with ID: {sheet.id}: {e}")
        return False

    print(f"Deleted spreadsheet: {sheet.name} with ID: {sheet.id}")
    return True

def main() -> None:
    # regex string
    reg_str = ""
    # date to use
    date = datetime.datetime.fromisoformat("2024-01-01")
    # MAKE SURE IT WORKS BEFORE DISABLING THIS
    dry_run = True
    # download test
    d_test = True

    # hardcoded for now?
    creds = auth("./client_secret.json")
    sheets = list_sheets(creds)

    process_sheets(sheets, reg_str=reg_str, date=date)

    for k in FILES.keys():
        sheet = FILES[k]
        # TODO: make sure that the last replace we want to do, since this is
        # changing spaces for underscores
        fname = k.replace('\n', '_').replace('\r', '').replace(' ', '_')

        if not dry_run or d_test:
            download_sheet(sheet.id, fname, creds)

        if not dry_run:
            delete_sheet(sheet.id, cred=creds)
    

if __name__ == "__main__":
    main()