1import os, pathlib, io
 2
 3from dotenv import load_dotenv
 4from google.auth.transport.requests import Request
 5from google.oauth2.credentials import Credentials
 6from google_auth_oauthlib.flow import InstalledAppFlow
 7from googleapiclient.discovery import build
 8from googleapiclient.errors import HttpError
 9from googleapiclient.http import MediaIoBaseDownload
10
11def get_google_credentials():
12    SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly', 'https://www.googleapis.com/auth/drive.readonly']
13    creds = None
14    if os.path.exists('token.json'):
15        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
16    if not creds or not creds.valid:
17        if creds and creds.expired and creds.refresh_token:
18            creds.refresh(Request())
19        else:
20            flow = InstalledAppFlow.from_client_secrets_file(
21                'credentials.json', SCOPES)
22            creds = flow.run_local_server(port=1111)
23        with open('token.json', 'w') as token:
24            token.write(creds.to_json())
25    return creds
26
27def get_rows():
28    try:
29        sheet = build('sheets', 'v4', credentials=creds).spreadsheets()
30        result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE).execute()
31        return result.get('values', [])
32    except HttpError as err:
33        print(err)
34        return []
35
36def get_file(file_id, cache_path):
37    drive = build('drive', 'v3', credentials=creds).files()
38    try:
39        filename = drive.get(fileId=file_id).execute()['name']
40    except HttpError as err:
41        print(err)
42        return ""
43    extension = pathlib.Path(filename).suffix
44
45    file_name = file_id + extension
46    file_path = os.path.join(cache_path, file_name)
47
48    fh = io.BytesIO()
49    request = drive.get_media(fileId=file_id)
50    downloader = MediaIoBaseDownload(fh, request)
51    done = False
52    while done is False:
53        status, done = downloader.next_chunk()
54    fh.seek(0)
55    fh_content = fh.read()
56    with open(file_path, "wb") as fp:
57        fp.write(fh_content)
58    return file_name
59
60load_dotenv()
61SPREADSHEET_ID = os.getenv("SPREADSHEET_ID")
62RANGE = os.getenv("RANGE")
63creds = get_google_credentials()