python 以编程方式更新 google docs 上托管的电子表格

Programatically updating a spreadsheet hosted on google docs

提问by chollida

I've got a pre-existing spreadsheet hosted on google docs. Each month I update this document. I've got a template workseet in the spreadseet that I'd like to clone and then update.

我有一个预先存在的电子表格托管在 google docs 上。每个月我都会更新这份文件。我在电子表格中有一个模板工作表，我想克隆然后更新。

I'd prefer to clone the worksheet rather than create it from scratch as it has some pretty complex formulas.

我更喜欢克隆工作表而不是从头开始创建它，因为它有一些非常复杂的公式。

I'm using the Python api for the google docs here:

我在这里使用 Python api 作为 google 文档：

http://code.google.com/apis/spreadsheets/data/1.0/developers_guide_python.html

http://code.google.com/apis/spreadsheets/data/1.0/developers_guide_python.html

Does anyone know how to clone and copy a worksheet in a pre-existing document?

有谁知道如何在预先存在的文档中克隆和复制工作表？

Edit

编辑

I seemed to have confused one reader. I don't have an excel spreadsheet. I only have a Google Docs spreadsheet that has a template worksheet.

我似乎让一位读者感到困惑。我没有excel电子表格。我只有一个带有模板工作表的 Google Docs 电子表格。

I'd like to clone this worksheet, rename it and then edit it programatically.

我想克隆这个工作表，重命名它，然后以编程方式编辑它。

采纳答案by DoctorRuss

1. Clone your template worksheet using the instructions on Copying Documents
2. Access the list of worksheetswithin the cloned document and iterate through to the required spreadsheet.
3. Use the cell feedto get the appropriate cell in your spreadsheet, then updatethe values.

1. 使用复制文档中的说明克隆您的模板工作表
2. 访问克隆文档中的工作表列表并迭代到所需的电子表格。
3. 使用单元格提要在电子表格中获取适当的单元格，然后更新值。

回答by Evan Plaice

Whoa!!! Back the truck up. There's a much simpler approach

哇！！！把卡车倒回去。有一个更简单的方法

I have been researching this a bit over the past few weeks because I'm planning to do the same thing for my monthly reports. I don't have the actual code fleshed out yet but I'll add it as I make progress.

在过去的几周里，我一直在研究这个，因为我计划为我的月度报告做同样的事情。我还没有充实实际的代码，但我会在取得进展时添加它。

In Google Docs there are so many API's and similar terms related to working with docs that things can get a little confusing. If you don't already know, establish in your head the fact that GAS (Google Apps Scripting) and GAE (Google App Engine) are two completely different things. Even though they sound the same thing they're about as similar as Java is to JavaScript.

在 Google Docs 中有很多 API 和与使用文档相关的类似术语，以至于事情可能会变得有点混乱。如果您还不知道，请在脑海中确定 GAS（Google Apps Scripting）和 GAE（Google App Engine）是两个完全不同的东西。尽管它们听起来相同，但它们与 Java 与 JavaScript 的相似之处。

GAS are the scrips embedded in Google Docs (which will hopefully be importable as stand alone modules in the future) that drive things like validation and dynamic documents but they're a lot more powerful than most suspect (they can do things like modify/update external documents and auto-email responses). Keep in mind that these need to be lightweight because they're run on google's servers. If your script takes to long to finish up it's execution will get cut off prematurely (google around to find the limits). That means you should use vanilla JS only (no frameworks like jQuery) and performance tweaks wherever possible.

GAS 是嵌入在 Google Docs 中的脚本（希望将来可以作为独立模块导入），它驱动诸如验证和动态文档之类的事情，但它们比大多数人怀疑的要强大得多（它们可以做诸如修改/更新之类的事情）外部文档和自动电子邮件回复）。请记住，这些需要是轻量级的，因为它们在 google 的服务器上运行。如果您的脚本需要很长时间才能完成，则它的执行将过早地中断（谷歌四处寻找限制）。这意味着你应该只使用 vanilla JS（没有像 jQuery 这样的框架）并尽可能地调整性能。

GAE, on the other hand, is like a web server (with an available database layer) that lives somewhere in the cloud. It exists as a convenient (and already deployed) middleware layer for businesses/interests to create custom apps to do more heavy lifting. Unfortunately, the external Spreadsheet's API is too limited to accomplish what we're working on by itself so it's a non-option.

另一方面，GAE 就像位于云中某处的 Web 服务器（具有可用的数据库层）。它作为一个方便（并且已经部署）的中间件层存在，供企业/利益相关者创建自定义应用程序来完成更多繁重的工作。不幸的是，外部电子表格的 API 太有限，无法单独完成我们正在处理的工作，因此它是不可选择的。

Automation using Google Apps Scripting and time-based triggers

使用 Google Apps 脚本和基于时间的触发器的自动化

This approach shouldwork but requires a slightly hackish approach.

这种方法应该可行，但需要稍微有点hackish 的方法。

Open up the workbook containing your report sheets. Click on [Tools] -> [Script editor...]. Once there goto [Triggers] -> [Current script's triggers...].

打开包含您的报告表的工作簿。单击 [工具] -> [脚本编辑器...]。一旦到达 [触发器] -> [当前脚本的触发器...]。

If you don't have any triggers present, add one. Then, under the 'Events' dropdown menu select 'Time-driven'.

如果您没有任何触发器，请添加一个。然后，在“事件”下拉菜单下选择“时间驱动”。

Welcome to the world of server-side event handlers. One of the neat features that you get with cloud-based documents is the ability to trigger cron jobs directly within your document. No external middleware necessary.

欢迎来到服务器端事件处理程序的世界。您从基于云的文档中获得的一项简洁功能是能够直接在您的文档中触发 cron 作业。不需要外部中间件。

If you haven't noticed by now there's no trigger for 'Month timer'. This is where it gets hacky. To work around the lack of this feature it's going to require that we fire the trigger on a daily basis and use some JavaScript to match the current date with the previous day's date.

如果您现在还没有注意到“月计时器”没有触发器。这就是它变得笨拙的地方。为了解决缺少此功能的问题，我们需要每天触发触发器并使用一些 JavaScript 将当前日期与前一天的日期相匹配。

[code will go here]

[代码将在这里]

First, comes the function that gets attached to the time trigger event handler. This block of code just simply parses the date, compares it to the previous date, and stores the value in a hidden sheet (that we use as out persistence layer) for the next day's comparison. If the new-month condition is met then the next block of code runs.

首先是附加到时间触发事件处理程序的函数。这段代码只是简单地解析日期，将它与前一个日期进行比较，并将该值存储在一个隐藏表（我们用作外部持久层）中以供第二天的比较。如果满足新月条件，则运行下一个代码块。

[code will go here]

[代码将在这里]

Yours will obviously differ from mine a bit but the basic concept is:

您的显然与我的略有不同，但基本概念是：

• Load the SpreadSheet object (not to be confused with a Sheet object)
• Locate the template Sheet object
• Clone the template Sheet giving it an appropriate date-range-based name

• 加载 SpreadSheet 对象（不要与 Sheet 对象混淆）
• 找到模板 Sheet 对象
• 克隆模板 Sheet 给它一个合适的基于日期范围的名称

In mine, my next step will be to extract data from the month to generate a stacked line graph to report the current status to my higher-ups.

在我的项目中，我的下一步将是从月份中提取数据以生成堆叠折线图，以向我的上级报告当前状态。

Note: Because of the multi-user collaboration nature of docs, events have to be fired server-side. This creates a big problem for us. Because the event code runs elsewhere if the code errors, we don't get any feedback from our browser. The only solution to this is to setup a notification on the trigger to immediately email you when the script faults.

注意：由于文档的多用户协作性质，必须在服务器端触发事件。这给我们带来了很大的问题。因为如果代码出错，事件代码会在别处运行，所以我们不会从浏览器获得任何反馈。唯一的解决方案是在触发器上设置通知，以便在脚本出现故障时立即通过电子邮件发送给您。

Update: While researching this, I found another cool technique. If I can manage to get this working without any bugs I might try to invoke the trigger using a date marked on Google Calendar.

更新：在研究这个时，我发现了另一种很酷的技术。如果我能设法让它在没有任何错误的情况下工作，我可能会尝试使用 Google 日历上标记的日期来调用触发器。

回答by Nathan

This is really complicated. I understand that you can edit your spreadsheets with Python using their API, Google tends to offer that ability on many of their web services and it's all done by sending HTTP post requests made of XML somehow, I hope you know that part, I don't.

这真的很复杂。我知道您可以使用 Python 使用他们的 API 编辑电子表格，Google 倾向于在他们的许多网络服务上提供这种能力，这一切都是通过以某种方式发送由 XML 组成的 HTTP 发布请求来完成的，我希望你知道那部分，我不知道吨。

According to thisyou can at least add worksheets, read rows from other worksheets and write rows to worksheets. if you must, you could copy it one row at a time, however sending an additional POST request for each row seems like a horrible idea.

根据这个你至少可以将工作表添加，阅读其他工作表和写行的工作表行。如果必须，您可以一次复制一行，但是为每一行发送一个额外的 POST 请求似乎是一个可怕的想法。

Edit:

编辑：

I'm learning more and more about this, but still a long way off from solving your original problem. This overview of REST principlesgoes over the basic style of interaction that goes on between programs on the web. Google seems to be following it religiously.

我对这方面的了解越来越多，但离解决您原来的问题还有很长的路要走。这的REST原则概述越过相互作用的基本风格，继续在网络上的程序之间。谷歌似乎虔诚地追随它。

It all takes place within the HTTP protocol, something I knew nothing about before today. In this HTTP specificationthe basic game is spelled out. Its not as dry as it looks, and maybe I'm just a huge geek, but I find it an inspiring read. Not unlike The Constitution of The United States.

这一切都发生在 HTTP 协议中，在今天之前我对此一无所知。在此HTTP 规范中详细说明了基本游戏。它不像看起来那么枯燥，也许我只是一个巨大的极客，但我发现它是一个鼓舞人心的读物。与美国宪法没有什么不同。

So since you want to "clone" a document, your going to be using a GET request for a particular worksheet, and then sending that worksheet back as the payload of POST.

因此，由于您想“克隆”一个文档，您将对特定工作表使用 GET 请求，然后将该工作表作为 POST 的有效负载发回。

Getting closer :)

越来越近 ：）

回答by Charles

First of all, I've never worked with Python before - but I'll tell you how I did this in C++.

首先，我以前从未使用过 Python - 但我会告诉你我是如何在 C++ 中做到这一点的。

I've used cURL to make a GET request to the google documents API. The binary data of the file was returned and I wrote that to a file. Now I had the XLS file and then I used a C/C++ library that could read XLS files to manipulate the downloaded file. The API that I used supported a variety of options; you could do anything that you could do in Excel. After modification I uploaded it again to Google Docs.

我使用 cURL 向google 文档 API发出 GET 请求。文件的二进制数据被返回，我将其写入文件。现在我有了 XLS 文件，然后我使用了一个 C/C++ 库，它可以读取 XLS 文件来操作下载的文件。我使用的 API 支持多种选项；你可以做任何你可以在 Excel 中做的事情。修改后我再次上传到谷歌文档。

回答by torbiak

Couldn't you could export your spreadsheet as a xls and then upload it as a new doc with a (slightly) different name, specifying the new name in the XML metadata?

难道您不能将电子表格导出为 xls，然后将其上传为名称（略有不同）的新文档，并在 XML 元数据中指定新名称？

The Download and Create/upload Document sections at http://code.google.com/apis/documents/overview.htmlshould be beneficial.

http://code.google.com/apis/documents/overview.html上的下载和创建/上传文档部分应该很有用。

I can't immediately see any import/export functionality in the Python API docs, but sending a few http requests isn't so bad.

我无法立即在 Python API 文档中看到任何导入/导出功能，但发送一些 http 请求并没有那么糟糕。

回答by wescpy

(Feb 2017)Rephrasing question with current terminology: How do you copy a Google Sheet template, then modify it (the copy) programmatically?Short answer: it's much easier with current Google APIs, specifically the Google Drive v3 APIand the Google Sheets v4 API, and you can do it with any language supported by the Google APIs Client Libraries.

（2017 年 2 月）用当前术语重新表述问题：如何复制 Google 表格模板，然后以编程方式修改它（副本）？简短回答：使用当前的 Google API，特别是Google Drive v3 API和Google Sheets v4 API容易得多，并且您可以使用Google API 客户端库支持的任何语言来实现。

The latest Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (UI), i.e., create frozen rows, cell formatting, resize rows/columns, add pivot tables, cell validation, create charts, etc.

最新的 Sheets API 提供了旧版本中不可用的功能，即让开发人员能够像使用用户界面 (UI) 一样以编程方式访问 Sheet，即创建冻结行、单元格格式、调整行/列大小、添加数据透视表、单元格验证、创建图表等。

As you can guess, the Sheets API is primarily for programmatically accessing spreadsheet operations & functionality as described above, but to perform file-level accesssuch as copying a template Sheet, use the Google Drive APIinstead.

如您所料，Sheets API 主要用于以编程方式访问上述电子表格操作和功能，但要执行文件级访问（例如复制模板 Sheet），请改用Google Drive API。

Pseudocode (Python) to copy a file (Sheet) using the Drive API (assuming we first search for the most recently modified file with the template name, hence the orderByand selection of the first result [0]below):

使用Drive API复制文件（Sheet）的伪代码（Python）（假设我们首先使用模板名称搜索最近修改的文件，因此orderBy选择[0]下面的第一个结果）：

TMPLFILE = 'my Sheets template'
tmpl = DRIVE.files().list(q="name='%s'" % TMPLFILE).execute().get('files')[0]
NEW_SHEET = {'name': 'Sheets data, Feb 2017'}
SHEET_ID = DRIVE.files().copy(body=NEW_SHEET, fileId=tmpl['id']).execute().get('id')

Pseudocode to read values from a SQL database (SQLite) and write them to the new Sheet created above (starting from cell 'A1' as "upper-left") as if a user entered the values from the UI (so formulae can be applied, etc.):

从 SQL 数据库 (SQLite) 读取值并将它们写入上面创建的新工作表的伪代码（从单元格“A1”开始作为“左上角”），就像用户从 UI 输入值一样（因此可以应用公式） ， 等等。）：

cxn = sqlite3.connect('db.sqlite')
cur = cxn.cursor()
rows = cur.execute('SELECT * FROM data').fetchall()
cxn.close()
DATA = {'values': rows}
SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
    range='A1', body=DATA, valueInputOption='USER_ENTERED').execute()

If you're relatively new to modern Google APIs, I have a (somewhat dated but) user-friendly intro videofor you. There are 2 videos after that maybe useful too, including one that demonstrates using the Drive API. Those are videos 2, 3, and 4 in this playlist. Videos 23 & 25 are another pair featuring the Drive and Sheets APIs.

如果您对现代 Google API 比较陌生，我为您准备了一个（有些过时但）用户友好的介绍视频。之后还有 2 个视频可能也很有用，包括一个演示如何使用 Drive API。这些是此播放列表中的视频 2、3 和 4 。视频 23 和 25 是另一对以 Drive 和 Sheets API 为特色的视频。

All newer videos can be found in this playlistwhere you'll find another pair of videos featuring the Sheets API plus a reprise of the "template copying" code above but copying a Slides template which is then modified with the Slides API) instead (video 2).

所有较新的视频都可以在此播放列表中找到，您可以在其中找到另一对视频，其中包含 Sheets API 以及上面“模板复制”代码的重演，但复制的是 Slides 模板，然后使用Slides API修改）（视频2）。

As mentioned in another answer, you can also use Google Apps Scriptto do something similar if you prefer that environment vs. using REST APIs, although Apps Script currently uses older APIs. Also there are few outstanding bugs that may make it a bit more challenging (specifically this oneand this one).

正如另一个答案中提到的，如果您更喜欢该环境而不是使用 REST API ，您也可以使用Google Apps Script来做类似的事情，尽管 Apps Script 目前使用旧的 API。还有一些突出的错误可能会使它更具挑战性（特别是这个和这个）。