How can I get a list of sheets (name and "gid") in a Google spreadsheet using the Drive API?

Question

I am trying to use Google Drive API (Python) to download some tabs of a spreadsheet file. Is the gids information in the file's metadata? What I am trying to do is (this may not be correct, please suggest) :

file_metadata = self.service.files().get(file_id=file_id).execute()
# in the following line, how can I get a download url with a gid (a tab in a spreadsheet file)
download_url = file_metadata.get('exportLinks')['text/csv']
# download the file.

Answer 1

You can use an old visualization API URL

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

to download a sheet by its name. I just posted this here together with code for the Google API Python library. This is also mentioned in another answer which links to the Charts docs where it is shown how to do this with a gid. Alternatively, you can download all sheets by exporting the spreadsheet as an e.g. Excel file,

class MimeTypes:
    EXCEL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

drive_api.files().export_media(fileId=file_id, mimeType=MimeTypes.EXCEL)

If you still want to go with getting sheets by gid, the gid is the sheetId in the Google Sheets API (see here).

And you should be able to use the Sheets API to get all the sheets - and their corresponding sheetIds.

Answer 2

Indeed this seems to be a duplicate of How to convert Google spreadsheet's worksheet string id to integer index (GID)?

Here is the quick answer:

def to_gid(worksheet_id):
    return int(worksheet_id, 36) ^ 31578