I have a use-case where I want to fetch a Google Sheet and turn it into a dict where the first row of the sheet is used for the keys. I am using requests to grab the CSV export data from the sheet. So basically my goal is to convert this kind of response:
b'the_id,the_name,another_thing
1234,mooo,a1
324234,blah,b2
98765,blub,c3
324,,xx
68584,,yy
7463,,zz
2553,,'
to this kind of dict:
{'another_thing': ['a1', 'b2', 'c3', 'xx', 'yy', 'zz', ''],
'the_id': ['1234', '324234', '98765', '324', '68584', '7463', '2553'],
'the_name': ['mooo', 'blah', 'blub', '', '', '', '']}
This is the code, using some list and dict comprehensions, I came up with to get this done:
import pprint
import requests
def from_google_sheet(sheet_id: str) -> dict:
"""
Convert Google Sheet to dict where the first row becomes the keys.
"""
url_template = f'https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv&id={sheet_id}&gid=0'
response = requests.get(url=url_template)
if response.status_code==200:
my_data = [x.split(',') for x in response.content.decode('utf-8').splitlines()]
return {key: [my_data[y][x] for y in range(1, len(my_data))] for x, key in enumerate(my_data[0])}
return {}
pprint.pprint(from_google_sheet('1nZ-Pjbr4NW0z_g_VZ4W1ZM7QV6TSXRt_yjVWa7jZLow'))
My question is: is there a nicer/cleaner/more readable way to get the same result without using some third party packages? Have I re-invented the proverbial wheel?
Edit: to clarify: the question is about converting the response to a dict where the first row is used as keys, it is not about how to fetch the data.