Geek asks and answers

How to save a Google Sheets file as CSV from Python 3 (or 2)?

Am I looking for an easy way to save a csv file from a published Google Sheets document? Since it is published, it is available through a direct link (modified specifically in the example below).

All my browsers will tell me to save the csv file as soon as I run the link.

None:

DOC_URL = 'https://docs.google.com/spreadsheet/ccc?key=0AoOWveO-dNo5dFNrWThhYmdYW9UT1lQQkE&output=csv'    

f = urllib.request.urlopen(DOC_URL)
cont = f.read(SIZE)
f.close()
cont = str(cont, 'utf-8')
print(cont)

and:

req = urllib.request.Request(DOC_URL)
req.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.13 (KHTML, like Gecko) Chrome/24.0.1284.0 Safari/537.13')
f = urllib.request.urlopen(req)
print(f.read().decode('utf-8'))

print anything other than html content.

(Tried the second version after reading this other post: Download the google docs public table to csv using python .)

, ? Google, , , . , API Google Python 3, "" , , .

"User-Agent", , , , , , , (b/c ), , .

+5
2

Google 302 cookie. , .

, User-Agent, , urllib.request.urlopen cookie, HTTP 302.

, , DOC_URL:

>>> from http.cookiejar import CookieJar
>>> from urllib.request import build_opener, HTTPCookieProcessor
>>> opener = build_opener(HTTPCookieProcessor(CookieJar()))
>>> resp = opener.open(DOC_URL)
>>> # should really parse resp.getheader('content-type') for encoding.
>>> csv_content = resp.read().decode('utf-8')

, , , Right Way ™ - . .

, csv_content, , requests, :

>>> import requests
>>> csv_content = requests.get(DOC_URL).text

. . - , - , requests.

+4

requests HTTP- Python, ( ) , , , .. , . Google CSV API Google .

, API- Drive? - Sheets API ? , API- "" - , , , , .., API- Drive - , /, , ..

cmd-line. ( Python, , API Google.) , , inventory ( ), DRIVE - API:

FILENAME = 'inventory'
SRC_MIMETYPE = 'application/vnd.google-apps.spreadsheet'
DST_MIMETYPE = 'text/csv'

# query for latest file named FILENAME
files = DRIVE.files().list(
    q='name="%s" and mimeType="%s"' % (FILENAME, SRC_MIMETYPE),
    orderBy='modifiedTime desc,name').execute().get('files', [])

# if found, export Sheets file as CSV
if files:
    fn = '%s.csv' % os.path.splitext(files[0]['name'].replace(' ', '_'))[0]
    print('Exporting "%s" as "%s"... ' % (files[0]['name'], fn), end='')
    data = DRIVE.files().export(fileId=files[0]['id'], mimeType=DST_MIMETYPE).execute()

    # if non-empty file
    if data:
        with open(fn, 'wb') as f:
            f.write(data)
        print('DONE')

, , , - . , . API Google, ( , ) . ( 2 , , , .)

+3

More articles:


All Articles