如何将 Google 电子表格的工作表字符串 id 转换为整数索引 (GID)? [英] How to convert Google spreadsheet's worksheet string id to integer index (GID)?

问题描述

要将 Google 电子表格的单个工作表导出为 CSV，需要传递整数工作表索引 (GID).

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

但是，这些信息在哪里?使用 gdata.spreadsheets.client，我可以为工作表找到一些字符串 id，例如oc6、ocv、odf".

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)

它返回低于原子 XML.(部分)

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

我猜应该有什么神奇的功能，但找不到.如何将它们转换为整数 ID?或者我可以导出带有字符串 id 的工作表吗?

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?

我刚刚用 python 制作了转换表.脏但工作:-(

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
}

推荐答案

我发现您的问题正在寻找解决同一问题的方法，但很惊讶这些工作表 ID 实际上与 gids - 我最初假设它们是独立分配的，而不是混淆的练习.

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.

通过对他们用来从您的表格生成工作表 ID 的公式进行逆向工程，我找到了一个稍微更简洁的解决方案:

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

因此，一些 Python 将工作表 ID 转换为 gid:

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

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

这仍然很脏，但可以用于高于 99 的 GID，而不需要大表.至少只要他们不更改生成逻辑(他们可能不会更改，因为这会破坏人们已经使用的现有 ID).

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).

这篇关于如何将 Google 电子表格的工作表字符串 id 转换为整数索引 (GID)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！