Convert Google Sheets to CSV with URL using Python

Question

I am trying to figure how to make this program (found online) take a URL or Google Sheets ID instead of a name to find the document and convert to CSV. Currently it takes a file name and functions properly. This is my first time using APIs and I haven't worked with python in a while so any help is appreciated!

from __future__ import print_function
import os


from apiclient import discovery
from httplib2 import Http
from oauth2client import file, client, tools



SCOPES = 'https://www.googleapis.com/auth/drive.readonly'
store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
    creds = tools.run_flow(flow, store)
DRIVE = discovery.build('drive', 'v3', http=creds.authorize(Http()))

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

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

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 data:
        with open(fn, 'wb') as f:
            f.write(data)
        print('DONE')
    else:
        print('ERROR (could not download file)')
else:
    print('!!! ERROR: File not found')

edit: to add clarity

In your question, you say I am trying to figure how to make this program (found online) take a URL or Google Sheets ID instead of a name to find the document.. But in your title, you say Convert Google Sheets to CSV with URL using Python. By this, I cannot understand about your goal. I apologize for my poor English skill. Can I ask you about the detail of your goal? — Tanaike
– Tanaike, Commented May 20, 2021 at 0:26
Currently the program converts a google sheet to a CSV, but it requires the name of the document. I want to be able to use the URL/Google Sheet ID instead of a name to locate the document. Does that make sense? — katherinekelly2012
– katherinekelly2012, Commented May 20, 2021 at 13:45
Thank you for replying. From your replying, I proposed an answer. Could you please confirm it? If that was not the direction you expect, I apologize. — Tanaike
– Tanaike, Commented May 20, 2021 at 23:34

Tanaike · Accepted Answer · 2021-05-20 23:34:52Z

I believe your goal and your current situation as follows.

You want to export a Google Spreadsheet as a CSV file using a Spreadsheet ID instead of Spreadsheet name.
You want to achieve this using googleapis for python.
You have already been able to get values from Google Drive using Drive API.

Modification points:

In your script, in order to retrieve the Spreadsheet ID, the method of "Files: list" is used using the Spreadsheet name, and the Spreadsheet is exported as a CSV using the method of "Files: export.
In order to achieve your goal, you can directly use the Spreadsheet ID for the method of "Files: export.
And, in your script, the file name is retrieved from the Spreadsheet name. In this case, the method of "Files: get" is used for retrieving the Spreadsheet name from the Spreadsheet ID.

When above points are reflected to your script, it becomes as follows. Please modify your script below DRIVE = discovery.build('drive', 'v3', http=creds.authorize(Http())) as follows.

Modified script:

DRIVE = discovery.build('drive', 'v3', http=creds.authorize(Http()))

spreadsheetId = '###' # Please set the Spreadsheet ID.
DST_MIMETYPE = 'text/csv'

# Retrieve Spreadsheet name from Spreadsheet ID.
res1 = DRIVE.files().get(fileId=spreadsheetId).execute()

# Export Spreadsheet as CSV.
fn = '%s.csv' % os.path.splitext(res1['name'].replace(' ', '_'))[0]
print('Exporting "%s" as "%s"... ' % (res1['name'], fn), end='')
data = DRIVE.files().export(
    fileId=spreadsheetId, mimeType=DST_MIMETYPE).execute()
if data:
    with open(fn, 'wb') as f:
        f.write(data)
    print('DONE')
else:
    print('ERROR (could not download file)')

Collectives™ on Stack Overflow

Convert Google Sheets to CSV with URL using Python

1 Answer 1

Modification points:

Modified script:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related