[Python] First Attempt at Downloading a Google Sheet using Python Selenium, Getting a 401...

I need to programmatically download a Google Sheet as a csv file and I'm trying to use Python Selenium to accomplish this. Unfortunately I'm receiving a 401 error on the authentication.

Here's my code:

import os
import logging
import requests
import sys
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

#local defs
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
SPREADSHEET_ID = "<ID>"
OUT_DIR = 'tmp/'
OUT_FILENAME = "locationOfServices.csv"

#Process to download the google sheet
def getGoogleSeet(spreadsheet_id, outDir, outFile):
url = f'https://docs.google.com/spreadsheets/d/<ID>/export?format=csv'
response = requests.get(url)
if response.status_code == 200:
filepath = os.path.join(outDir, outFile)
with open(filepath, 'wb') as f:
f.write(response.content)
print('CSV file saved to: {}'.format(filepath))
else:
print(f'Error downloading Google Sheet: {response.status_code}')
sys.exit()

#Main function to loc ingo Google Sheet
def main():
credentials = None
if os.path.exists("token.json"):
credentials = Credentials.from_authorized_user_file("token.json", SCOPES)
if not credentials or not credentials.valid:
if credentials and credentials.expired and credentials.refresh_token:
credentials.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
credentials = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(credentials.to_json())
try:
service = build("sheets", "v4", credentials=credentials)
sheets = service.spreadsheets()

os.makedirs(OUT_DIR, exist_ok = True)
filepath = getGoogleSeet(SPREADSHEET_ID, OUT_DIR, OUT_FILENAME)

sys.exit(0); ## success
except HttpError as error:
print(error)

if __name__ == "__main__":
main()


On my initial run it asked me which account I wanted to use (which is a service account), had my approve some information, and successfully created a token file. But it also gave me the 401 Error. After that I've tried several times to tweak stuff but continue to get the 401.

I have verified that the service account has Edit permissions to access the Google Sheet.

I've created a project in Google Cloud to create the OAuth2 connection. In the project I enabled Google Sheets API and created an OAuth Client ID:



I downloaded the JSON file and renamed it credentials.json and stored it in my working directory.



The other parts of the Google service that I set up are below:







Can anybody spot what it is I'm doing incorrectly?

Continue reading...