How to export a csv from Google Sheet API?

Question

I can't find any reference to an API that enables Rest API clients to export an existing Google Sheet to a csv file.

https://developers.google.com/sheets/

I believe there should be a way to export them.

Answer 1

The following URL gives you the CSV of a Google spreadsheet per sheet. The sheet must be accessible by the public, by anyone with the link (unlisted).

The parameters you need to provide are:

• sheet ID (that is simply the ID in the URL of a Google Spreadsheet https://docs.google.com/spreadsheets/d/{{ID}}/edit)
• sheet name (that is simply the name of the sheet as given by the user)

https://docs.google.com/spreadsheets/d/{{ID}}/gviz/tq?tqx=out:csv&sheet={{sheet_name}}

With that URL you can run a GET-request to fetch the CSV. Or paste it in your browser address bar.

Answer 2

You can use the Drive API to do this today -- see https://developers.google.com/drive/v3/web/manage-downloads#downloading_google_documents, however that will limit you to the first sheet of the document. The Sheets API doesn't expose exporting as CSV today, but may offer it in the future.

Answer 3

Nobody's mentioned gspread yet, so here's how I did it:

#open sheet
sheet = gc.open_by_key(sheet_id)

#select worksheet
worksheet = sheet.get_worksheet(0)

#download values into a dataframe
df = pd.DataFrame(worksheet.get_all_records())

#save dataframe as a csv, using the spreadsheet name
filename = sheet.title + '.csv'
df.to_csv(filename, index=False)