7

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.

webminal.org
  • 44,948
  • 37
  • 94
  • 125
Itaer
  • 73
  • 1
  • 3

4 Answers4

16

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.

Adirmola
  • 783
  • 5
  • 15
2
   import pandas as pd
   import requests
   
   YOUR_SHEET_ID=''

   r = requests.get(f'https://docs.google.com/spreadsheet/ccc?key={YOUR_SHEET_ID}&output=csv')
   open('dataset.csv', 'wb').write(r.content)
   df = pd.read_csv('dataset.csv')
   df.head()

I tried @adirmola's solution but I had to tweak it a little. When he wrote "You need to find the correct URL request that download the file" he has a point. An easy solution is what I'm showing here. Adding "&output=csv" after your google sheet id.

Hope it helps!

santiago-giordano
  • 21
  • 3
0

I'm not exactly sure about your usage scenario, and Adirmola already provided a very exact answer to your question, but my immediate question is why you want to download the CSV in the first place.

Google Sheets has a python library so you can just get the data from the GSheet directly.

You may also be interested in this answer since you're interested in watching for changes in GSheets

Edo Akse
  • 4,051
  • 2
  • 10
  • 21
  • 1
    The authentication for applications for official google sheets APIs is a nightmare, lot of hoops to jump through for every user. If a sheet is accessible to anyone with a link, and anyone with access can download a TSV of a sheet, I would do that 95% of the time. – Chris L. Barnes Jun 29 '21 at 09:16
0

I would just like to say using the Oauth keys and google python API is not always an option. I found the above to be quite useful for my current application.

Ron Mann
  • 1