Accessing the contents of a public Google Sheet as CSV using Requests (or other library)

Question

I wrote a small python program that works with data from a CSV file. I am tracking some numbers in a google sheet and I created the CSV file by downloading the google sheet. I am trying to find a way to have python read in the CSV file directly from google sheets, so that I do not have to download a new CSV when I update the spreadsheet.

I see that the requests library may be able to handle this, but I'm having a hard time figuring it out. I've chosen not to try the google APIs because this way seems simpler as long as I don't mind making the sheet public to those with the link, which is fine.

I've tried working with the requests documentation but I'm a novice programmer and I can't get it to read in as a CSV.

This is how the data is currently taken into python:

file = open('data1.csv', newline='')
reader = csv.reader(file)

I would like the file = open() to ideally be replaced by the requests library and pull directly from the spreadsheet.

Answer 1

You need to find the correct URL request that download the file.

Sample URL:

csv_url='https://docs.google.com/spreadsheets/d/169AMdEzYzH7NDY20RCcyf-JpxPSUaO0nC5JRUb8wwvc/export?format=csv&id=169AMdEzYzH7NDY20RCcyf-JpxPSUaO0nC5JRUb8wwvc&gid=0'

The way to doing it is by manually download your file while inspecting the requests URL at the Network tab in the Developer Tools in your browser.

Then the following is enough:

import requests as rs
csv_url=YOUR_CSV_DOWNLOAD_URL
res=rs.get(url=csv_url)
open('google.csv', 'wb').write(res.content)

It will save CSV file with the name 'google.csv' in the folder of you python script file.