如何仅使用 Javascript 访问 Google Sheets 电子表格?
我想仅使用 JavaScript(无 .NET、C#、Java 等)访问 Google 电子表格
我来了
请告诉我如何使用 JavaScript 或其任何框架(如 jQuery)访问(创建/编辑/删除)Google Sheets。
I want to access Google Spreadsheets using JavaScript only (no .NET, C#, Java, etc.)
I came here and was shocked to know that there is NO API for JavaScript to access Google Sheets.
Please tell me how to access (CREATE/EDIT/DELETE) Google Sheets using JavaScript or any of its frameworks like jQuery.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(12)
要点如下。
您可以使用 Google Sheets API。目前无法使用 API 删除电子表格(请阅读文档)。将 Google Docs API 视为创建和查找文档的途径。
您可以使用基于工作表的 Feed 在电子表格中添加/删除工作表。
可以通过基于列表的 Feed 更新电子表格或基于单元格的 Feed。
可以通过上述 Google Spreadsheets API 读取电子表格,或者仅针对已发布的工作表,使用 Google Visualization API 查询语言来查询数据(它可以返回 CSV、JSON 或 HTML 表格格式的结果)。
忘记 jQuery。只有当您遍历 DOM 时,jQuery 才真正有价值。由于 GAS(Google Apps 脚本)不使用 DOM,jQuery 不会为您的代码添加任何价值。坚持香草。
我真的很惊讶没有人在答案中提供这些信息。不仅可以做到这一点,而且使用普通 JS 相对容易做到。唯一的例外是 Google Visualization API,它相对较新(截至 2011 年)。可视化 API 还专门通过 HTTP 查询字符串 URI 工作。
Here's the Gist.
You can create a spreadsheet using the Google Sheets API. There is currently no way to delete a spreadsheet using the API (read the documentation). Think of Google Docs API as the route to create and look-up documents.
You can add/remove worksheets within the spreadsheet using the worksheet based feeds.
Updating a spreadsheet is done through either list based feeds or cell based feeds.
Reading the spreadsheet can be done through either the Google Spreadsheets APIs mentioned above or, for published sheets only, by using the Google Visualization API Query Language to query the data (which can return results in CSV, JSON, or HTML table format).
Forget jQuery. jQuery is only really valuable if you're traversing the DOM. Since GAS (Google Apps Scripting) doesn't use the DOM jQuery will add no value to your code. Stick to vanilla.
I'm really surprised that nobody has provided this information in an answer yet. Not only can it be done, but it's relatively easy to do using vanilla JS. The only exception being the Google Visualization API which is relatively new (as of 2011). The Visualization API also works exclusively through a HTTP query string URI.
有一种解决方案不需要发布电子表格。但是,该工作表确实需要“共享”。更具体地说,需要以一种任何知道链接的人都可以访问电子表格的方式共享工作表。完成此操作后,即可使用 Google Sheets HTTP API。
首先,您需要一个 Google API 密钥。前往这里:
https://developers.google.com/places/web-service/get -api-key
注意。请注意向公众提供 API 密钥的安全后果:https://support .google.com/googleapi/answer/6310037
获取电子表格的所有数据 - 警告,这可能会包含大量数据。
获取工作表元数据
获取一系列单元格
现在有了这些信息,就可以使用 AJAX 检索数据,然后在 JavaScript 中对其进行操作。我建议使用 axios。
There's a solution that does not require one to publish the spreadsheet. However, the sheet does need to be 'Shared'. More specifically, one needs to share the sheet in a manner where anyone with the link can access the spreadsheet. Once this is done, one can use the Google Sheets HTTP API.
First up, you need an Google API key. Head here:
https://developers.google.com/places/web-service/get-api-key
NB. Please be aware of the security ramifications of having an API key made available to the public: https://support.google.com/googleapi/answer/6310037
Get all data for a spreadsheet - warning, this can be a lot of data.
Get sheet metadata
Get a range of cells
Now armed with this information, one can use AJAX to retrieve data and then manipulate it in JavaScript. I would recommend using axios.
2016 更新:最简单的方法是使用 Google Apps Script API,特别是 电子表格服务。这适用于私人工作表,与需要发布电子表格的其他答案不同。
这将允许您将 JavaScript 代码绑定到 Google 表格,并在打开表格或选择菜单项(您可以定义)时执行它。
这是快速入门/演示。代码如下所示:
您还可以将此类脚本发布为网络应用。
2016 update: The easiest way is to use the Google Apps Script API, in particular the SpreadSheet Service. This works for private sheets, unlike the other answers that require the spreadsheet to be published.
This will let you bind JavaScript code to a Google Sheet, and execute it when the sheet is opened, or when a menu item (that you can define) is selected.
Here's a Quickstart/Demo. The code looks like this:
You can also publish such scripts as web apps.
看起来好像可以,但使用起来很痛苦。它涉及使用 Google 数据 API。
http://gdatatips.blogspot.com/ 2008/12/using-javascript-client-library-w-non.html
“JavaScript 客户端库具有用于日历、通讯录、Blogger 和 Google Finance 的辅助方法。但是,您可以将它与几乎任何Google Data API 用于访问经过身份验证的/私人提要。此示例使用 DocList API。”
以及编写与电子表格交互的小工具的示例:http://code.google.com /apis/电子表格/小工具/
It looks lke you can, but it's a pain to use. It involves using the Google data API.
http://gdatatips.blogspot.com/2008/12/using-javascript-client-library-w-non.html
"The JavaScript client library has helper methods for Calendar, Contacts, Blogger, and Google Finance. However, you can use it with just about any Google Data API to access authenticated/private feeds. This example uses the DocList API."
and an example of writing a gadget that interfaces with spreadsheets: http://code.google.com/apis/spreadsheets/gadgets/
“JavaScript 访问 Google 文档”实施起来会很乏味,而且 Google 文档也不是那么容易获取的。我有一些很好的链接可以分享,通过它们你可以实现 js 对 gdoc 的访问:
也许这些会有所帮助你出去..
'JavaScript accessing Google Docs' would be tedious to implement and moreover Google documentation is also not that simple to get it. I have some good links to share by which you can achieve js access to gdoc:
May be these would help you out..
抱歉,这是一个糟糕的答案。显然,这个问题已经存在近两年了,所以问题不要屏住呼吸。
以下是官方请求,您可以" star"
也许您能想到的最接近的方法就是使用 Google App Engine 推出您自己的服务/Python 并使用您自己的 JS 库公开您需要的任何子集。尽管我很想自己有更好的解决方案。
Sorry, this is a lousy answer. Apparently this has been an issue for almost two years so don't hold your breath.
Here is the official request that you can "star"
Probably the closest you can come is rolling your own service with Google App Engine/Python and exposing whatever subset you need with your own JS library. Though I'd love to have a better solution myself.
在这个快速变化的世界中,大多数链接都已经过时了。
现在您可以使用 Google 云端硬盘 Web API:
In this fast changing world most of these link are obsolet.
Now you can use Google Drive Web APIs:
您可以使用 Sheetsee.js 和 tabletop.js
you can do it by using Sheetsee.js and tabletop.js
您可以使用 RGraph 工作表连接器以 JavaScript 读取 Google Sheets 电子表格数据:
https://www.rgraph.net/canvas/docs/import-data-from-google-sheets.html
最初(几年前)这依赖于一些 RGraph 函数来发挥其魔力- 但现在它可以独立工作(即不需要RGraph公共库)。
一些示例代码(此示例制作一个 RGraph 图表):
You can read Google Sheets spreadsheets data in JavaScript by using the RGraph sheets connector:
https://www.rgraph.net/canvas/docs/import-data-from-google-sheets.html
Initially (a few years ago) this relied on some RGraph functions to work its magic - but now it can work standalone (ie not requiring the RGraph common library).
Some example code (this example makes an RGraph chart):
对于此类事情,您应该使用 Google Fusion Tables。 API 就是为此目的而设计的。
For this type of thing you should use Google Fusion Tables. The API is designed for that purpose.
我创建了一个简单的 javascript 库,它通过 JSON api 检索 google 电子表格数据(如果已发布):
https://github.com/mikeymckay/google-spreadsheet-javascript
您可以在此处查看其实际效果:
http://mikeymckay.github.com/google-spreadsheet-javascript/sample.html
I have created a simple javascript library that retrieves google spreadsheet data (if they are published) via the JSON api:
https://github.com/mikeymckay/google-spreadsheet-javascript
You can see it in action here:
http://mikeymckay.github.com/google-spreadsheet-javascript/sample.html
2018 年 1 月更新:当我去年回答这个问题时,我忽略了使用 JavaScript 访问 Google API 的第三种方式,那就是使用 Node.js 应用它的客户端库,所以我在下面添加了它。
现在是 2017 年 3 月,这里的大多数答案都已过时 - 接受的答案现在指的是使用较旧 API 版本的库。最新答案:您只需使用 JavaScript 即可访问大多数 Google API 。 Google 今天提供了 2 种(更正,3 种)方法来执行此操作:
使用REST API时,您需要管理&存储您的源代码并通过滚动您自己的授权代码来执行授权(请参阅上面的示例)。 Apps 脚本代表您处理此问题,管理数据(减少 Ape-inago 在他们的答案中提到的“痛苦” ),并且您的代码存储在 Google 的服务器上。但是您的功能仅限于 App Script 提供的服务,而 REST API 为开发人员提供了更广泛的 API 访问权限。但是嘿,有选择是件好事,对吧?总之,为了回答OP原来的问题,开发人员有三种方式使用JavaScript访问Google表格,而不是零。
Jan 2018 UPDATE: When I answered this question last year, I neglected to mention a third way to access Google APIs with JavaScript, and that would be from Node.js apps using its client library, so I added it below.
It's Mar 2017, and most of the answers here are outdated -- the accepted answer now refers to a library that uses an older API version. A more current answer: you can access most Google APIs with JavaScript only. Google provides 2 (correction, 3) ways to do this today:
When using the REST API, you need to manage & store your source code as well as perform authorization by rolling your own auth code (see samples above). Apps Script handles this on your behalf, managing the data (reducing the "pain" as mentioned by Ape-inago in their answer), and your code is stored on Google's servers. But your functionality is restricted to what services App Script provides whereas the REST API gives developers much broader access to the API. But hey, it's good to have choices, right? In summary, to answer the OP original question, instead of zero, developers have three ways of accessing Google Sheets using JavaScript.