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.

>> download_url = file_metadata.get('exportLinks')['text/csv'] + '&gid=' + gid << I would like to get those gids from API too, along with the tab names. — AMS, Sep 18 '15 at 10:36
No need really. It just reads better when you can't insert a newline in a comment :P — AMS, Sep 21 '15 at 08:37
Possible duplicate of [How to convert Google spreadsheet's worksheet string id to integer index (GID)?](http://stackoverflow.com/questions/11290337/how-to-convert-google-spreadsheets-worksheet-string-id-to-integer-index-gid) — Zig Mandel, Nov 10 '15 at 17:11

score 3 · Answer 1 · answered Jul 25 '22 at 08:02

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.

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

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

edited May 23 '17 at 12:24

Community

1
1

answered Nov 26 '15 at 11:34

sorin

161,544
178
535
806

This doesn't seem to work anymore (perhaps due to the new google spreadsheets). For example, a worksheet_id of omlf5j6 is giving me 53609033112 using this method, when the actual gid is 1366234904 – Chris Apr 06 '16 at 17:21

score 0 · Answer 3 · answered Dec 10 '15 at 08:01

You can do this using the Spreadsheet service in Google Apps Script. It's not as convenient as having a direct API call, but at least it is possible:

Call the SpreadsheetApp.openById(id) method to open the spreadsheet using the Drive file_id.
Call the Spreadsheet.getSheets() method to retrieve a list of Sheet objects.
For each sheet, call the Sheet.getSheetId() and Sheet.getSheetName() methods to get the name (which is what the spreadsheet user sees) and the sheetId (which is what needs to be provided in the gid= parameter when exporting).

You can use the recently-announced Apps Script Execution API to provide a REST API for your Apps Script project, which you can then call in a similar manner to the Drive API.

score 0 · Answer 4 · answered Aug 15 '22 at 16:02

I have a radical idea: given that you intend to automate things a bit and already know (at least some) python, why not export data from the tabs you need to CSV files, download them, and then import as pandas.DataFrames. After all, these data structures have been modeled after Google (and Excel) Sheets and are equally intuitive to use.

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

4 Answers4