pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

# Import standard libraries
import pandas as pd
import numpy as np
import pickle
import os

# Import Google API libraries
from googleapiclient import discovery
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

# Function to generate user token
def gsheet_api_check(SCOPES, TOKEN, CREDENTIALS):
    creds = None
    if os.path.exists(TOKEN):
        with open(TOKEN, 'rb') as token:
            creds = pickle.load(token)
    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, SCOPES)
            creds = flow.run_console()
        with open(TOKEN, 'wb') as token:
            pickle.dump(creds, token)
    return creds

# Connect to Google Sheets API v4
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']   # Allow read and write to Google Sheets
TOKEN = 'data/gsheet_token.pickle'
CREDENTIALS = 'data/gsheet_credentials.json'
CREDS = gsheet_api_check(SCOPES, TOKEN, CREDENTIALS)

# Connect to the service
service = discovery.build('sheets', 'v4', credentials=CREDS)
SPREADSHEET_ID = '1c0HS6Wc09bri6vvQQds3zVMGvXNbYJ3gKY5wtyaAYxI'   # Found in the Google Sheet URL
RANGE = 'Sheet1!A:Z'   # Tab and cells range to be read and/or written

# Read from a Google Sheet
request = service.spreadsheets().values().get(
    spreadsheetId=SPREADSHEET_ID, 
    range=RANGE
)
response = request.execute()

# Convert data to a pandas DataFrame
pd.DataFrame(response.get('values', []))

# Optional: clear data on a Sheet range
request = service.spreadsheets().values().clear(
    spreadsheetId=SPREADSHEET_ID, 
    range=RANGE
)
response = request.execute()

# Create DataFrame to be written to the Google Sheet
df = pd.DataFrame(np.random.random(size=(5, 4)), columns=list('ABCD')).round(3)

# Convert DataFrame to a list of rows, with the columns names as first row
value_range_body = {
  "values": [list(df.columns)] + df.values.tolist()
}

# Write data to the Google Sheet
request = service.spreadsheets().values().update(
    spreadsheetId=SPREADSHEET_ID, 
    range=RANGE, 
    valueInputOption='RAW', 
    body=value_range_body)
response = request.execute()