How to convert from gid to od* (worksheet id) in new Google spreadsheets

Question

I am using Gdata api to update Google spreadsheet cell. Now New Google spreadsheet has been released and it seems the gid has over 8 digits.

The following is sample new sheets and the gid of sheet2 is 1794189920. https://docs.google.com/spreadsheets/d/1S6mvGGoOJa8JF6Qt6GGH5QT-X0HD4EjZhKNbihAwFuE/edit?usp=sharing

I tried to convert them with the following method, but doesn't work.

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

Thanks to anyone who can help shed light on this or suggest.

Cheers. Junya

Answer 1

All right, this is a ghetto answer because I'm not satisfied with it at all since I don't really understand what's going on, but maybe it will help people, so here goes..

I'm using the ruby google_drive gem, just trying to download all the worksheets in my spreadsheet as CSV. In order to do this, I have to convert from the encoded GID (I've seen it called 'WID') to the real GID. Before, my wid_to_gid method looked like this (documented from other answers here on stack overflow):

def wid_to_gid(wid)
    wid.to_i(36) ^ 31578
end

In order to handle the 7-digit WIDs, I changed it to this:

def wid_to_gid(wid)
    wid_val = wid.length > 3 ? wid[1..-1] : wid
    xorval = wid.length > 3 ? 474 : 31578
    wid_val.to_i(36) ^ xorval
end

So.. a couple things going on here.. I realized that (at least on my spreadsheet), for the 7-digit WIDs, I could get the GID by dropping the first character, converting to base 36 number and then XORing with 474 (no idea why this magic number.. sorry).

If the WID is only 3 digits, then the old transform works (as has been detailed in many other answers on stack overflow).

For the spreadsheet I was working with, it had 3 worksheets. Here are the WIDs and GIDs of each:

GID - WID
0          - od6
2019719111 - oxehkwt
1506531046 - oowy6v0

I really just wanted to get this done so I didn't spend more time investigating once I got it to work. Would appreciate it if others can verify/reject this code as working or not for them... and if it doesn't work, maybe it will at least give you some ideas!

Answer 2

I discovered a way to get the gid on the new sheets by looking into the WorksheetFeed class (from the .NET library Google.GData.Spreadsheets.WorksheetFeed). Basically setup your spreadsheet service, apply credentials, create a WorksheetQuery passing the feedUrl, call the query on the service and return the worksheet from the worksheetFeed.Entries. Then you can stream down the xml of the worksheet and from there search the xml for "?gid=" and parse the result. There is probably a more straightforward way of accomplishing this but I haven't found it and this works in our situation.

  Dim worksheetFeed As WorksheetFeed
  Dim query As WorksheetQuery
  Dim worksheet As WorksheetEntry
  Dim output As New MemoryStream
  Dim xml As String
  Dim gid As String = String.Empty

  Try
    _service = New Spreadsheets.SpreadsheetsService("ServiceName")
    _service.setUserCredentials(UserId, Password)
    query = New WorksheetQuery(feedUrl)
    worksheetFeed = _service.Query(query)
    worksheet = worksheetFeed.Entries(0)

    ' Save worksheet feed to memory stream so we can 
    ' get the xml returned from the feed url and look for
    ' the gid.  Gid allows us to download the specific worksheet tab
    Using output
      worksheet.SaveToXml(output)
    End Using

    xml = Encoding.ASCII.GetString(output.ToArray())

Answer 3

If you're using Python with gspread, here's what you do:

wid = worksheet.id
widval = wid[1:] if len(wid) > 3 else wid
xorval = 474 if len(wid) > 3 else 31578
gid = int(str(widval), 36) ^ xorval

I'll probably open a PR for this.