0

I have a public google sheet with 2 sheets https://docs.google.com/spreadsheets/d/14hFn00O9632n96Z2xGWvfrcY-K4kHiOGR02Rx7dsj54/edit#gid=447738801 I know how to wget this as a csv (if it had only 1 sheet), but is there a simple way of getting sheet 1 and sheet 2 as a dictionary or as a csv file (each sheet as 1 csv file) and I will parse it. gid of both sheets are different

In the end I will have header as key and values below the header as values

pythonRcpp
  • 2,042
  • 6
  • 26
  • 48
  • You can't have multiple values per key, unless the values are in a list. – glhr Apr 09 '19 at 10:39
  • ohh, yes then I dont know how we can keep them as a list :( header being the key and other column as value of the header which is a list) – pythonRcpp Apr 09 '19 at 10:47
  • using pandas for the additional things to be done after file is downloaded, thanks for the help – pythonRcpp Apr 09 '19 at 11:06

2 Answers2

1

Use the requests library to download each sheet, and write the response content to a file.

Working implementation:

import requests

sheets = {
    'sheet1': 'https://docs.google.com/spreadsheets/d/14hFn00O9632n96Z2xGWvfrcY-K4kHiOGR02Rx7dsj54/export?format=csv&id=14hFn00O9632n96Z2xGWvfrcY-K4kHiOGR02Rx7dsj54&gid=0',
    'sheet2': 'https://docs.google.com/spreadsheets/d/14hFn00O9632n96Z2xGWvfrcY-K4kHiOGR02Rx7dsj54/export?format=csv&id=14hFn00O9632n96Z2xGWvfrcY-K4kHiOGR02Rx7dsj54&gid=447738801'
} 

for sheet in list(sheets.keys()):
    response = requests.get(sheets[sheet])
    with open(f'{sheet}.csv', 'wb') as csvfile:
        csvfile.write(response.content)

This will save each sheet in a file (sheet1.csv and sheet2.csv in this case). Note that I got the link for each sheet just by downloading it as CSV from a browser and copying the download link.

You can then convert it to a dictionary using the CSV library. See this post.

glhr
  • 4,439
  • 1
  • 15
  • 26
  • 1
    Thanks @glhr it mostly solves my purpose, Do we have a way to keep the response.content in memory only (and not write file to disk and then read it again to create a dict). TIA – pythonRcpp Apr 09 '19 at 10:08
  • It's possible, put please explain in your post how you would like the data to be formatted in a dictionary exactly (eg. do you want a dictionary for each row?). – glhr Apr 09 '19 at 10:34
  • added to the post, each cloumn header is a key and values below it are its values, was trying `flike = io.StringIO(response.text)` then `for row in csv.reader(flike):` – pythonRcpp Apr 09 '19 at 10:38
0

Just use pandas to load CSV. Then you can convert into Dict or anything else later

import pandas as pd 
# Read data from file 'filename.csv' 
data = pd.read_csv("filename.csv") 

data.to_dict('series')