Read a google sheet using Python and output to file

read-google-sheet.py

from os import path, getenv
from csv import DictWriter

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

from dotenv import load_dotenv
load_dotenv()

def authenticate(scopes:list[str]):
  creds = None
  # The file token.json stores the user's access and refresh tokens, and is
  # created automatically when the authorization flow completes for the first
  # time.
  if path.exists("token.json"):
    creds = Credentials.from_authorized_user_file("token.json", scopes)
  # If there are no (valid) credentials available, let the user log in.
  if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
      creds.refresh(Request())
    else:
      flow = InstalledAppFlow.from_client_secrets_file(
          "credentials.json", scopes
      )
      creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open("token.json", "w") as token:
      token.write(creds.to_json())
  return creds

def read_sheet(creds, spreadsheet_id, sheet_range):
  service = build("sheets", "v4", credentials=creds)
  sheet = service.spreadsheets()
  result = (
      sheet.values()
      .get(spreadsheetId=spreadsheet_id, range=sheet_range)
      .execute()
  )
  return result.get("values", [])

def build_objects(headers:list[str], values) -> dict[str, str]:
  data = []
  for row in values:
    datum = {}
    for i, header in enumerate(headers):
      datum[header] = row[i]
    data.append(datum)
  return headers, data

def main(scopes:list[str], spreadsheet_id:str, sheet_range:str, headers: list[str], output_filename:str) -> None:
  creds = authenticate(scopes)
  try:
    (headers, data) = build_objects(headers, read_sheet(creds, spreadsheet_id, sheet_range))
    
    with open(output_filename, 'w', newline='') as file:
      writer = DictWriter(file, fieldnames=headers)
      writer.writeheader()
      writer.writerows(data)
    print(f"'{output_filename}' has been created successfully.")
  except HttpError as err:
    print(err)

if __name__ == "__main__":
  # 1. pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib dot-env
  # 2. create .env file with data below
  # 3. python ./read-google-sheet.py
  main(["https://www.googleapis.com/auth/spreadsheets.readonly"],
    getenv("SPREADSHEET_ID"),
    getenv("SHEET_RANGE"),
    getenv("HEADERS").split(','),
    getenv("OUTPUT_FILENAME")
  )