As a lot of other people have pointed out, my original answer is somewhat outdated. So here is my answer updated for v4 of the Google Spreadsheets API. Now there's a way to get the gids, but we can't use the the drive files.export API because it only exports first worksheet in the spreadsheet (even if you specify the gid).
To export all of the worksheets as CSV files, you need to get the gids for the worksheets you want to export using the spreadsheets.get API. That API call returns a bunch of information about the spreadsheet including each of the worksheets. You can get the gid from the properties.sheetId property for each worksheet.
Once you have that, you can just build the same URL that the Sheets uses when you select File->Download As->CSV. You can take the data.spreadsheetUrl value from spreadsheets.get and replace /edit
with /export
and then add the gid as the parameter. You will also need to include Authorization Bearer <auth token>
in the HTTP header in the request.
Here's a python script based on their quickstart example that downloads all of the sheets for the spreadsheet with a specified ID. You need to replace <spreadsheet id>
with the ID for a spreadsheet you have access to:
import apiclient.discovery
import httplib2
import oauth2client.file
import oauth2client.tools
import re
import requests
import shutil
import urllib.parse
SCOPES = 'https://www.googleapis.com/auth/drive.readonly'
SPREADSHEET_ID = '<spreadsheet id>'
store = oauth2client.file.Storage('credentials.json')
creds = store.get()
if not creds or creds.invalid:
flow = oauth2client.client.flow_from_clientsecrets('client_secret.json', SCOPES)
creds = oauth2client.tools.run_flow(flow, store)
service = apiclient.discovery.build('sheets', 'v4', http=creds.authorize(httplib2.Http()))
result = service.spreadsheets().get(spreadsheetId = SPREADSHEET_ID).execute()
urlParts = urllib.parse.urlparse(result['spreadsheetUrl'])
path = re.sub("\/edit$", '/export', urlParts.path)
urlParts = urlParts._replace(path=path)
headers = {
'Authorization': 'Bearer ' + creds.access_token,
}
for sheet in result['sheets']:
params = {
'id': SPREADSHEET_ID,
'format': 'csv',
'gid': sheet['properties']['sheetId'],
}
queryParams = urllib.parse.urlencode(params)
urlParts = urlParts._replace(query=queryParams)
url = urllib.parse.urlunparse(urlParts)
response = requests.get(url, headers = headers)
filePath = '/tmp/foo-%s.csv' % (+ params['gid'])
with open(filePath, 'wb') as csvFile:
csvFile.write(response.content)