How to convert Google spreadsheet's worksheet string id to integer index (GID)?

Question

To export google spreadsheet's single worksheet to CSV, integer worksheet index(GID) is required to be passed.

https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=%s&gid=%d&exportFormat=csv

But, where are those informations? With gdata.spreadsheets.client, I could find some string id for worksheet like "oc6, ocv, odf".

client = gdata.spreadsheets.client.SpreadsheetsClient()
feed = client.GetWorksheets(spreadsheet, auth_token=auth_token)

And it returns below atom XML. (part of it)

<entry gd:etag="&quot;URJFCB1NQSt7ImBoXhU.&quot;">
    <id>https://spreadsheets.google.com/feeds/worksheets/0AvhN_YU3r5e9dGpTWGx3UVU3MTczaXJuNEFKQjMwN2c/ocw</id>
    <updated>2012-06-21T08:19:46.587Z</updated>
    <app:edited xmlns:app="http://www.w3.org/2007/app">2012-06-21T08:19:46.587Z</app:edited>
    <category scheme="http://schemas.google.com/spreadsheets/2006" term="http://schemas.google.com/spreadsheets/2006#worksheet"/>
    <title>AchievementType</title>
    <content type="application/atom+xml;type=feed" src="https://spreadsheets.google.com/feeds/list/0AvhN_YU3r5e9dGpTWGx3UVU3MTczaXJuNEFKQjMwN2c/ocw/private/full"/>
    <link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/cells/0AvhN_YU3r5e9dGpTWGx3UVU3MTczaXJuNEFKQjMwN2c/ocw/private/full"/>
    <link rel="http://schemas.google.com/visualization/2008#visualizationApi" type="application/atom+xml" href="https://spreadsheets.google.com/tq?key=0AvhN_YU3r5e9dGpTWGx3UVU3MTczaXJuNEFKQjMwN2c&amp;sheet=ocw"/>
    <link rel="self" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/worksheets/0AvhN_YU3r5e9dGpTWGx3UVU3MTczaXJuNEFKQjMwN2c/private/full/ocw"/>
    <link rel="edit" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/worksheets/0AvhN_YU3r5e9dGpTWGx3UVU3MTczaXJuNEFKQjMwN2c/private/full/ocw"/>
    <gs:rowCount>280</gs:rowCount>
    <gs:colCount>28</gs:colCount>
</entry>

Also I tried with sheet parameter but failed with "Invalid Sheet" error.

https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=%s&sheet=XXX&exportFormat=csv

I guess there should be some magic function but could not find it. How can I convert them to integer id?? Or Can I export worksheet with string id?

EDIT: I just made convert table with python. DIRTY but working :-(

GID_TABLE = {
    'od6': 0, 
    'od7': 1, 
    'od4': 2, 
    'od5': 3, 
    'oda': 4, 
    'odb': 5, 
    'od8': 6, 
    'od9': 7, 
    'ocy': 8, 
    'ocz': 9, 
    'ocw': 10, 
    'ocx': 11, 
    'od2': 12, 
    'od3': 13, 
    'od0': 14, 
    'od1': 15, 
    'ocq': 16, 
    'ocr': 17, 
    'oco': 18, 
    'ocp': 19, 
    'ocu': 20, 
    'ocv': 21, 
    'ocs': 22, 
    'oct': 23, 
    'oci': 24, 
    'ocj': 25, 
    'ocg': 26, 
    'och': 27, 
    'ocm': 28, 
    'ocn': 29, 
    'ock': 30, 
    'ocl': 31, 
    'oe2': 32, 
    'oe3': 33, 
    'oe0': 34, 
    'oe1': 35, 
    'oe6': 36, 
    'oe7': 37, 
    'oe4': 38, 
    'oe5': 39, 
    'odu': 40, 
    'odv': 41, 
    'ods': 42, 
    'odt': 43, 
    'ody': 44, 
    'odz': 45, 
    'odw': 46, 
    'odx': 47, 
    'odm': 48, 
    'odn': 49, 
    'odk': 50, 
    'odl': 51, 
    'odq': 52, 
    'odr': 53, 
    'odo': 54, 
    'odp': 55, 
    'ode': 56, 
    'odf': 57, 
    'odc': 58, 
    'odd': 59, 
    'odi': 60, 
    'odj': 61, 
    'odg': 62, 
    'odh': 63, 
    'obe': 64, 
    'obf': 65, 
    'obc': 66, 
    'obd': 67, 
    'obi': 68, 
    'obj': 69, 
    'obg': 70, 
    'obh': 71, 
    'ob6': 72, 
    'ob7': 73, 
    'ob4': 74, 
    'ob5': 75, 
    'oba': 76, 
    'obb': 77, 
    'ob8': 78, 
    'ob9': 79, 
    'oay': 80, 
    'oaz': 81, 
    'oaw': 82, 
    'oax': 83, 
    'ob2': 84, 
    'ob3': 85, 
    'ob0': 86, 
    'ob1': 87, 
    'oaq': 88, 
    'oar': 89, 
    'oao': 90, 
    'oap': 91, 
    'oau': 92, 
    'oav': 93, 
    'oas': 94, 
    'oat': 95, 
    'oca': 96, 
    'ocb': 97, 
    'oc8': 98, 
    'oc9': 99
}

Answer 1

This code works with the new Google Sheets.

// Conversion of Worksheet Ids to GIDs and vice versa
// od4 > 2
function wid_to_gid(wid) {
    var widval = wid.length > 3 ? wid.substring(1) : wid;
    var xorval = wid.length > 3 ? 474 : 31578;
    return parseInt(String(widval), 36) ^ xorval;
}
// 2 > od4
function gid_to_wid(gid) {
    var xorval = gid > 31578 ? 474 : 31578;
    var letter = gid > 31578 ? 'o' : '';
    return letter + parseInt((gid ^ xorval)).toString(36);
}

Answer 2

I found your question looking for a solution to the same problem, and was surprised that those worksheet IDs actually correspond 1:1 to gids - I originally assumed they were assigned independently, instead of being an exercise in obfuscation.

I was able to find a slightly cleaner solution by reverse-engineering the formula they use to generate worksheet IDs from your table:

worksheetID = (gid xor 31578) encoded in base 36

So, some Python to go from a worksheet ID to gid:

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

This is still dirty, but will work for GIDs higher than 99 without requiring giant tables. At least as long as they don't change the generation logic (which they probably won't, as it would break existing IDs that people already use).

Answer 3

I cannot add a comment to Wasilewski's post because apparently I lack reputation so here are the two conversion functions in Javascript based on Wasilewski's answer:

// Conversion of Worksheet Ids to GIDs and vice versa
// od4 > 2
function wid_to_gid(wid) {
    return parseInt(String(wid),36)^31578
}
// 2> 0d4
function gid_to_wid(gid) {
    // (gid xor 31578) encoded in base 36
    return parseInt((gid^31578)).toString(36);
}