- c - 在位数组中找到第一个零
- linux - Unix 显示有关匹配两种模式之一的文件的信息
- 正则表达式替换多个文件
- linux - 隐藏来自 xtrace 的命令
我想确定一种方法来获取 Google 电子表格工作簿中每个工作表的 URL 中的工作表 ID。例如,“sheet2”的工作表 ID this workbook是 '1244369280' ,因为它的 url 是 https://docs.google.com/spreadsheets/d/1yd8qTYjRns4_OT8PbsZzH0zajvzguKS79dq6j--hnTs/edit#gid=1244369280
我发现的一种方法是提取 Google 电子表格的 XML,因为根据 this question ,获取工作表 ID 的唯一方法是向下流式传输工作表的 XML,但该示例是使用 Javascript 编写的,我需要在 Python 中执行此操作
这是我想在 Python 中执行的 Javascript 代码:
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())
似乎从 Google 电子表格获取 XML 的最佳方法是使用 Gdata,所以我下载了 GData 并尝试了 the Google Spreadsheet example用我的证书。
见下文
#!/usr/bin/python
#
# Copyright (C) 2007 Google Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
__author__ = 'api.laurabeth@gmail.com (Laura Beth Lincoln)'
try:
from xml.etree import ElementTree
except ImportError:
from elementtree import ElementTree
import gdata.spreadsheet.service
import gdata.service
import atom.service
import gdata.spreadsheet
import atom
import getopt
import sys
import string
class SimpleCRUD:
def __init__(self, email, password):
self.gd_client = gdata.spreadsheet.service.SpreadsheetsService()
self.gd_client.email = 'chris@curalate.com'
self.gd_client.password = 'jkjkdioerzumawya'
self.gd_client.source = 'Spreadsheets GData Sample'
self.gd_client.ProgrammaticLogin()
self.curr_key = ''
self.curr_wksht_id = ''
self.list_feed = None
def _PromptForSpreadsheet(self):
# Get the list of spreadsheets
feed = self.gd_client.GetSpreadsheetsFeed()
self._PrintFeed(feed)
input = raw_input('\nSelection: ')
id_parts = feed.entry[string.atoi(input)].id.text.split('/')
self.curr_key = id_parts[len(id_parts) - 1]
def _PromptForWorksheet(self):
# Get the list of worksheets
feed = self.gd_client.GetWorksheetsFeed(self.curr_key)
self._PrintFeed(feed)
input = raw_input('\nSelection: ')
id_parts = feed.entry[string.atoi(input)].id.text.split('/')
self.curr_wksht_id = id_parts[len(id_parts) - 1]
def _PromptForCellsAction(self):
print ('dump\n'
'update {row} {col} {input_value}\n'
'\n')
input = raw_input('Command: ')
command = input.split(' ', 1)
if command[0] == 'dump':
self._CellsGetAction()
elif command[0] == 'update':
parsed = command[1].split(' ', 2)
if len(parsed) == 3:
self._CellsUpdateAction(parsed[0], parsed[1], parsed[2])
else:
self._CellsUpdateAction(parsed[0], parsed[1], '')
else:
self._InvalidCommandError(input)
def _PromptForListAction(self):
print ('dump\n'
'insert {row_data} (example: insert label=content)\n'
'update {row_index} {row_data}\n'
'delete {row_index}\n'
'Note: No uppercase letters in column names!\n'
'\n')
input = raw_input('Command: ')
command = input.split(' ' , 1)
if command[0] == 'dump':
self._ListGetAction()
elif command[0] == 'insert':
self._ListInsertAction(command[1])
elif command[0] == 'update':
parsed = command[1].split(' ', 1)
self._ListUpdateAction(parsed[0], parsed[1])
elif command[0] == 'delete':
self._ListDeleteAction(command[1])
else:
self._InvalidCommandError(input)
def _CellsGetAction(self):
# Get the feed of cells
feed = self.gd_client.GetCellsFeed(self.curr_key, self.curr_wksht_id)
self._PrintFeed(feed)
def _CellsUpdateAction(self, row, col, inputValue):
entry = self.gd_client.UpdateCell(row=row, col=col, inputValue=inputValue,
key=self.curr_key, wksht_id=self.curr_wksht_id)
if isinstance(entry, gdata.spreadsheet.SpreadsheetsCell):
print 'Updated!'
def _ListGetAction(self):
# Get the list feed
self.list_feed = self.gd_client.GetListFeed(self.curr_key, self.curr_wksht_id)
self._PrintFeed(self.list_feed)
def _ListInsertAction(self, row_data):
entry = self.gd_client.InsertRow(self._StringToDictionary(row_data),
self.curr_key, self.curr_wksht_id)
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print 'Inserted!'
def _ListUpdateAction(self, index, row_data):
self.list_feed = self.gd_client.GetListFeed(self.curr_key, self.curr_wksht_id)
entry = self.gd_client.UpdateRow(
self.list_feed.entry[string.atoi(index)],
self._StringToDictionary(row_data))
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print 'Updated!'
def _ListDeleteAction(self, index):
self.list_feed = self.gd_client.GetListFeed(self.curr_key, self.curr_wksht_id)
self.gd_client.DeleteRow(self.list_feed.entry[string.atoi(index)])
print 'Deleted!'
def _StringToDictionary(self, row_data):
dict = {}
for param in row_data.split():
temp = param.split('=')
dict[temp[0]] = temp[1]
return dict
def _PrintFeed(self, feed):
for i, entry in enumerate(feed.entry):
if isinstance(feed, gdata.spreadsheet.SpreadsheetsCellsFeed):
print '%s %s\n' % (entry.title.text, entry.content.text)
elif isinstance(feed, gdata.spreadsheet.SpreadsheetsListFeed):
print '%s %s %s' % (i, entry.title.text, entry.content.text)
# Print this row's value for each column (the custom dictionary is
# built using the gsx: elements in the entry.)
print 'Contents:'
for key in entry.custom:
print ' %s: %s' % (key, entry.custom[key].text)
print '\n',
else:
print '%s %s\n' % (i, entry.title.text)
def _InvalidCommandError(self, input):
print 'Invalid input: %s\n' % (input)
def Run(self):
self._PromptForSpreadsheet()
self._PromptForWorksheet()
input = raw_input('cells or list? ')
if input == 'cells':
while True:
self._PromptForCellsAction()
elif input == 'list':
while True:
self._PromptForListAction()
def main():
# parse command line options
try:
opts, args = getopt.getopt(sys.argv[1:], "", ["user=", "pw="])
except getopt.error, msg:
print 'python spreadsheetExample.py --user [username] --pw [password] '
sys.exit(2)
user = 'fake@gmail.com'
pw = 'fakepassword'
key = ''
# Process options
for o, a in opts:
if o == "--user":
user = a
elif o == "--pw":
pw = a
if user == '' or pw == '':
print 'python spreadsheetExample.py --user [username] --pw [password] '
sys.exit(2)
sample = SimpleCRUD(user, pw)
sample.Run()
if __name__ == '__main__':
main()
但是这会返回以下错误:
Traceback (most recent call last):
File "/Users/Chris/Desktop/gdata_test.py", line 200, in <module>
main()
File "/Users/Chris/Desktop/gdata_test.py", line 196, in main
sample.Run()
File "/Users/Chris/Desktop/gdata_test.py", line 162, in Run
self._PromptForSpreadsheet()
File "/Users/Chris/Desktop/gdata_test.py", line 49, in _PromptForSpreadsheet
feed = self.gd_client.GetSpreadsheetsFeed()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/gdata/spreadsheet/service.py", line 99, in GetSpreadsheetsFeed
converter=gdata.spreadsheet.SpreadsheetsSpreadsheetsFeedFromString)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/gdata/service.py", line 1074, in Get
return converter(result_body)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/gdata/spreadsheet/__init__.py", line 395, in SpreadsheetsSpreadsheetsFeedFromString
xml_string)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/atom/__init__.py", line 93, in optional_warn_function
return f(*args, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/atom/__init__.py", line 127, in CreateClassFromXMLString
tree = ElementTree.fromstring(xml_string)
File "<string>", line 125, in XML
cElementTree.ParseError: no element found: line 1, column 0
[Finished in 0.3s with exit code 1]
[shell_cmd: python -u "/Users/Chris/Desktop/gdata_test.py"]
[dir: /Users/Chris/Desktop]
[path: /usr/bin:/bin:/usr/sbin:/sbin]
我还应该提到,我一直在使用 Gspread 作为与 Google 电子表格交互的方法,但是当我运行下面的代码时,我得到了 gid,但我需要有工作表 ID。
gc = gspread.authorize(credentials)
sh = gc.open_by_url('google_spreadsheet_url')
sh.get_id_fields()
>> {'spreadsheet_id': '1BgCEn-3Nor7UxOEPwD-qv8qXe7CaveJBrn9_Lcpo4W4','worksheet_id': 'oqitk0d'}
最佳答案
查看 self.gd_client.ProgrammaticLogin()
调用 - 这是导致主要问题的原因,因为它使用了“ClientLogin”授权方法,该方法首先被弃用,后来被弃用 removed on April 20, 2015 .
我实际上会研究更新鲜和积极开发的 gspread
模块代替。
这是一个有点疯狂的示例,演示了如何为给定的电子表格和工作表名称提取实际的“gid”值。请注意,您首先需要 generate the JSON file with the OAuth credentials (我假设您已经这样做了)。
代码(添加注释,希望有助于理解它):
import urlparse
import xml.etree.ElementTree as ET
import gspread
from oauth2client.service_account import ServiceAccountCredentials
SPREADSHEET_NAME = 'My Test Spreadsheet'
WORKSHEET_NAME = "Sheet2"
PATH_TO_JSON_KEYFILE = '/path/to/json/key/file.json'
NAMESPACES = {'ns0': 'http://www.w3.org/2005/Atom'}
SCOPES = ['https://spreadsheets.google.com/feeds']
# log in
credentials = ServiceAccountCredentials.from_json_keyfile_name(PATH_TO_JSON_KEYFILE, SCOPES)
gss_client = gspread.authorize(credentials)
# open spreadsheet
gss = gss_client.open(SPREADSHEET_NAME)
# extract the full feed url
root = gss._feed_entry
full_feed_url = next(elm.attrib["href"] for elm in root.findall("ns0:link", namespaces=NAMESPACES) if "full" in elm.attrib["href"])
# get the feed and extract the gid value for a given sheet name
response = gss_client.session.get(full_feed_url)
root = ET.fromstring(response.content)
sheet_entry = next(elm for elm in root.findall("ns0:entry", namespaces=NAMESPACES)
if elm.find("ns0:title", namespaces=NAMESPACES).text == WORKSHEET_NAME)
link = next(elm.attrib["href"] for elm in sheet_entry.findall("ns0:link", namespaces=NAMESPACES)
if "gid=" in elm.attrib["href"])
# extract "gid" from URL
gid = urlparse.parse_qs(urlparse.urlparse(link).query)["gid"][0]
print(gid)
看起来还有一种方法可以将工作表 ID 转换为 gid 值,请参阅:
关于python - 如何使用 python 从 Google 电子表格获取工作表 ID?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36523723/
出现在 python 2.7.8 中。 3.4.1 不会发生这种情况。 示例: >>> id(id) 140117478913736 >>> id(id) 140117478913736 >>> id
好吧,我对动态创建的控件的 ID 很困惑。 Public Class TestClass Inherits Panel Implements INamingContainer
我收到下面的错误,说有堆栈溢出。发生这种情况是因为带有 IN (id, id, id...id) 的 SQL 语句有大量参数。有没有什么办法解决这一问题?这是在我使用 Eclipse 的本地环境中发生
为什么 CPython(不知道其他 Python 实现)有以下行为? tuple1 = () tuple2 = ()
为什么 CPython(对其他 Python 实现一无所知)有以下行为? tuple1 = () tuple2 = ()
非常简单的问题:当我有一个持久对象时,它通常有一个名为 ID 的属性(对于抽象类)。 那么..命名约定是ID还是Id? 例如。 public int ID { get; set; } 或 public
知道为什么我会收到此错误,我已经尝试了所有命名约定(小写/大写) 我正在使用 Vaadin,这是我的代码片段: public class Usercontainer extends BeanI
为什么 CPython(不知道其他 Python 实现)有以下行为? tuple1 = () tuple2 = ()
我需要改变表的所有主键 UPDATE TODO SET id = id + 1 但我做不到(Demo 来自 Ahmad Al-Mutawa 的回答)描述了原因。主键不能这样改。 我也不能根据这是 sq
我正在尝试列出与用户相关的讨论列表。 想象一下,如果你愿意的话: posts -------------------------------------------------------------
我有一个表,其中包含一些具有自己的 ID 和共享 SKU key 的文章。我尝试使用左连接进行查询,并使用组结果获取从查询返回的所有 id。 我的数据结构是这样的: id - name -
在下表People中: id name 1 James 2 Yun 3 Ethan 如果我想找到最大 ID,我可以运行此查询 select max(id) id from People; 结果是
我正在产品页面上创建评论模块,其中显示垃圾评论选项,并显示 onclick 显示和隐藏弹出窗口。现在它在单个评论中工作正常但是当评论是两个时它同时打开两个因为类是相同的。现在这就是为什么我想要获取父
根据 REST 哲学,PUT操作应该(取自维基百科): PUT http://example.com/resources/142 Update the address member of the co
我想知道如何在使用 PHP 或 JavaScript 进行身份验证后从 Google Analytics 获取 Property Id、View Id 和 Account Id?因为我希望能够将它们存
我想使用所选按钮的 ID 进行删除。但我不知道如何从中获取/获取 id。我尝试了 this.id 但不起作用。 这是我创建按钮的地方: var deleteEmployer= document.cre
我有一个具有以下结构的表“表” ID LinkedWith 12 13 13 12 14 13 15 14 16
请不要在未阅读问题的情况下将问题标记为重复。我确实发布了一个类似的问题,但 STACKOVERFLOW 社区成员要求我单独重新发布修改后的问题,因为考虑到一个小而微妙的修改,解决方案要复杂得多。 假设
在 Android Studio 中,我创建了一个 Person.java 类。我使用Generate 创建了getter 和setter 以及构造函数。 这是我的 Person.java 类: pu
如何在 jQuery 中制作这样的东西: //这是显示的主体 ID //当我悬停 #hover-id 时,我希望 #principal-id 消失并更改 。但是当我将光标放在 #this-id 上时
我是一名优秀的程序员,十分优秀!