2

I am trying to download specific sheet from a spread-sheet (on Google Drive) but unable to find a method to do so. I am using Python Client API library (v3) and passing file_id and mimeType in export_media() function as shown below:

request = service.files().export_media(fileId=file_id,mimeType='text/csv')
media_request = http.MediaIoBaseDownload(local_fd, request)

This code always export the sheet which is present at first place. Can you please describe a method through which I can download specific sheet/sheets by providing gid or any other parameter.

Husnain Taseer
  • 93
  • 1
  • 8

2 Answers2

1

I don't think the Drive API has a feature to specify a sheet name.

Two workarounds spring to mind...

  1. You could use the Sheets API (https://developers.google.com/sheets/api/reference/rest/) and write your own csv formatter. It sounds more complex than it is. It's probably 10 lines of code, especially if you go for Tab Separated instead of Comma Separated.

  2. Use the Google Spreadsheet File/Publish to the Web feature to publish a csv of any given sheet. Note that the content will be public, so anybody with the link (which is pretty obtuse) would be able to read the data.

pinoyyid
  • 21,499
  • 14
  • 64
  • 115
  • Thanks for providing this API. I have quickly overviewed it and found that to meet my requirement I have to first use the copyTo method to copy the specific sheet to a separate spreadsheet and from the received spreadsheet_id of newly created sheet I can download it. I will try this method. – Husnain Taseer Apr 20 '17 at 18:42
0

You can use an old visualization API URL (see other answer)

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

To make this request using the Google API Python library, you can use the credentials you already have and create an HTTP client instance yourself:

http_client = googleapiclient.discovery._auth.authorized_http(creds)
response, content = http_client.request(url)

Check response.status before you proceed.

Note that this API behaves a bit differently than your regular CSV export. Specifically there are some things I saw it does with headers - it will make them disappear if they are not set to Plain Text on a numeric column (see here), or merge multiple text rows appearing in the top of your sheet as a single header row.

Yuval
  • 3,207
  • 32
  • 45