以 JSON 形式访问（新式的、公开的）Google 工作表

Question

提问by jochen

How can I access the contents of a (new-style) Google sheet a JSON? My aim is to access the values from JavaScript, so I need to be able to download the JSON via HTTP.

如何通过 JSON 访问（新式）Google 表格的内容？我的目标是从 JavaScript 访问值，因此我需要能够通过 HTTP 下载 JSON。

Example: how can I download the data from this sheetas JSON?

示例：如何从该工作表中以 JSON格式下载数据？

I tried to find the answer via a web search, but ultimately failed:

我试图通过网络搜索找到答案，但最终失败了：

Many tutorials on the web start with the instruction to find the key=...value in the url. The URL I got when I exported the sheet is https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/pubhtml?gid=1822753188&single=trueand has no key=...in it.
The answer to "Accessing A Public Google Sheet"seems to indicate that I should try https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/export?format=csv&id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI&gid=1822753188to get a CSV version, but this does not work for me: I get a sign-in page instead of the data.
I found approaches using Google Apps Scripts, but these seem to require some user action in the browser instead of giving a download link.

网络上的许多教程都是从key=...在 url 中查找值的说明开始的。我导出工作表时得到的 URL 是https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/pubhtml?gid=1822753188&single=true没有key=...的。
“访问公共 Google 表格”的答案似乎表明我应该尝试https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/export?format=csv&id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI&gid=1822753188获取 CSV 版本，但这对我不起作用：我得到的是登录页面而不是数据。
我找到了使用 Google Apps Scripts 的方法，但这些方法似乎需要用户在浏览器中进行一些操作，而不是提供下载链接。

Answer 1

回答by Senseful

If you want to use the latest API (v4), you'll need to do the following:

如果您想使用最新的 API (v4)，您需要执行以下操作：

Generate a spreadsheets API key (see instructions below).
Make your sheet publicly accessible.

Use a request of the form:

https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY

生成电子表格 API 密钥（请参阅下面的说明）。
使您的工作表可公开访问。

使用以下形式的请求：

https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY

You'll then get a clean JSON response back:

然后你会得到一个干净的 JSON 响应：

{
  "range": "Sheet1!A1:D5",
  "majorDimension": "ROWS",
  "values": [
    ["Item", "Cost", "Stocked", "Ship Date"],
    ["Wheel", ".50", "4", "3/1/2016"],
    ["Door", "", "2", "3/15/2016"],
    ["Engine", "0", "1", "30/20/2016"],
    ["Totals", "5.5", "7", "3/20/2016"]
  ],
}

Note that if you want to specify the entire contents of a page, an identifier such as Sheet1is perfectly valid.

请注意，如果您想指定页面的全部内容，则使用诸如之类的标识符Sheet1是完全有效的。

See Basic Readingfor more information.

有关更多信息，请参阅基本阅读。

As of v4 API, all requests must be accompaniedby an identifier (e.g. API key):

从 v4 API 开始，所有请求都必须附带一个标识符（例如 API 密钥）：

Requests to the Google Sheets API for public data must be accompanied by an identifier, which can be an API key or an access token.

向 Google Sheets API 请求公共数据必须附带一个标识符，该标识符可以是 API 密钥或访问令牌。

Follow the steps in the linked document to create an API key on the credentials page.

按照链接文档中的步骤在凭据页面上创建 API 密钥。

Make sure to:

确保：

Create a new app on Google Cloud Platform.
Create a new API key.
Add the Google Sheets API. (API Manager > Dashboard > Enable API)

在 Google Cloud Platform 上创建一个新应用。
创建一个新的 API 密钥。
添加 Google 表格 API。（API 管理器 > 仪表板 > 启用 API）

Note that you can still access public datawithout forcing the user to log in:

请注意，您仍然可以在不强制用户登录的情况下访问公共数据：

In the new Sheets API v4, there is no explicit declaration of visibility. API calls are made using spreadsheet IDs. If the application does not have permission to access specified spreadsheet, an error is returned. Otherwise the call proceeds.

在新的 Sheets API v4 中，没有明确的可见性声明。API 调用是使用电子表格 ID 进行的。如果应用程序无权访问指定的电子表格，则返回错误。否则呼叫继续。

Note that you do notneed to publish the sheet to the web. All you need to do is make sure anyone with the linkcan access the sheet.

请注意，您并不需要将表发布到网上。您需要做的就是确保知道链接的任何人都可以访问该工作表。

(I.e. when you click Create credentialson the Google Sheets API, choose Other non-UI, User data, and it says "User data cannot be accessed from a platform without a UI because it requires user interaction for sign-in." you can safely ignore that message. The API Key is all you really need, since this is public data.)

（即，当您在 Google Sheets API 上单击创建凭据时，选择其他非 UI、用户数据，并显示“无法从没有 UI 的平台访问用户数据，因为它需要用户交互才能登录。”您可以安全地忽略该消息。API 密钥就是您真正需要的，因为这是公共数据。）

Common error messages:

常见错误信息：

The request is missing a valid API key.

请求缺少有效的 API 密钥。

You didn't include the key=param in your call.

您没有key=在通话中包含参数。

API key not valid. Please pass a valid API key. Google developers console

API 密钥无效。请传递有效的 API 密钥。谷歌开发者控制台

You supplied an incorrect API key. Make sure that you typed in your key correctly. If you don't have a key yet, go to the Google developers consoleand create one.

您提供的 API 密钥不正确。确保您正确输入了您的密钥。如果您还没有密钥，请转到Google 开发者控制台并创建一个。

API Key not found. Please pass a valid API key.
Google developer console API key

未找到 API 密钥。请传递有效的 API 密钥。
Google 开发者控制台 API 密钥

Your API Key is probably correct, but you most likely didn't add the Google Sheets permission. Go to the Google developer console API key page and add the sheets permission.

您的 API 密钥可能是正确的，但您很可能没有添加 Google 表格权限。转到 Google 开发者控制台 API 密钥页面并添加工作表权限。

The caller does not have permission

调用者没有权限

Your sheet isn't set to be publicly accessible.

您的工作表未设置为可公开访问。

Answer 2

回答by jochen

I have finally (kind of) solved my problem. Just for future reference, and in case somebody else runs into the same troubles, here the solution I came up with:

我终于（有点）解决了我的问题。仅供将来参考，以防其他人遇到同样的麻烦，这里是我想出的解决方案：

To make the worksheet publicly accessible, one needs to make the worksheet publicly accessible. This is done in the Google Sheets web interface, using the menu entries File > Publish to the web ... > link > publish. It is possible to either publish the whole spreadsheet or individual worksheets.
An API to access data from Google Sheets programmatically is described on the Google Sheets API web pages. This API uses URLS of the form https://spreadsheets.google.com/feeds/.../key/worksheetId/.... Slightly oddly, the meaning of keyand worksheetIdseems not to be explained in the API documentation.
My experiments show that the keyvalue can be found by taking part of the URLs used to access the sheet via the web interface (see also here). The key is everything after the /d/, until the next slash. For the spreadsheet in the question, the key is thus 1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI. The worksheetIdseems to be an integer, giving the position of the worksheet in the spreadsheet. For the example in the question one has to know that the sheet shown is the second worksheet, the worksheetIdin this case is 2.
The API defined publicand privaterequests. To access an exported resource without authentication, publicrequests must be used.
The API calls to get data from the spreadsheet are explained in the section "Retrieving a list-based feed"(click on the "Protocol" tab in the examples). The URL required extract the data from the spreadsheet in the question is
https://spreadsheets.google.com/feeds/list/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/2/public/full
A HTTP GET request to this URL returns that data as XML. (I have not found a way to get the data as JSON.)
The usual protections agains cross-site requests make it difficult to access the data via JavaScript XML RPC calls in a web app. One way around this problem is to proxy the API calls through the web server (e.g.using nginx's proxy_pass directive).

要使工作表可公开访问，需要使工作表可公开访问。这是在 Google Sheets 网络界面中使用菜单条目完成的File > Publish to the web ... > link > publish。可以发布整个电子表格或单个工作表。
Google Sheets API 网页上描述了以编程方式从 Google Sheets 访问数据的API。此 API 使用表单https://spreadsheets.google.com/feeds/.../键/worksheetId 的URL /...。有点奇怪的是，API 文档中似乎没有解释key和worksheetId的含义。
我的实验表明，可以通过获取用于通过 Web 界面访问工作表的部分 URL 来找到键值（另请参见此处）。关键是 , 之后的所有内容/d/，直到下一个斜线。对于问题中的电子表格，关键是1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI。该worksheetId似乎是一个整数，给在电子表格中的工作表中的位置。对于问题中的示例，必须知道显示的工作表是第二个工作表，在这种情况下，工作表 ID是2。
API 定义public和private请求。要在没有身份验证的情况下访问导出的资源，public必须使用请求。
从电子表格中获取数据的 API 调用在“检索基于列表的提要”部分（单击示例中的“协议”选项卡）中进行了说明。从问题中的电子表格中提取数据所需的 URL 是
https://spreadsheets.google.com/feeds/list/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/2/public/full
对此 URL 的 HTTP GET 请求将该数据作为 XML 返回。（我还没有找到将数据作为 JSON 获取的方法。）
针对跨站点请求的常见保护措施使得在 Web 应用程序中通过 JavaScript XML RPC 调用访问数据变得困难。解决此问题的一种方法是通过 Web 服务器代理 API 调用（例如使用 nginx 的proxy_pass 指令）。

The above steps are at least a partial solution to the problem in the question. The only difficulty is that the data is returned as XML rather than as JSON. Since the API documentation does not mention JSON, maybe it is not possible any more to extract the data in this format?

以上步骤至少是题中问题的部分解决方案。唯一的困难是数据以 XML 而不是 JSON 形式返回。由于 API 文档没有提到 JSON，所以可能无法再以这种格式提取数据了？

Answer 3

回答by bomberHymanets

Here's how to get the JSON using those same URL parameters:

以下是使用相同 URL 参数获取 JSON 的方法：

"https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/od6/public/values?alt=json";

Creds to @jochen on the answer with the path all the way up to XML "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/od6/public/" + sheetID;

对@jochen 的信任提供了一直到 XML 的路径 "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/od6/public/" + sheetID;

As @jochen's answer explains, this sheetIDis based on the order of the sheets in the spreadsheet.

正如@jochen 的回答所解释的，这sheetID是基于电子表格中工作表的顺序。

Answer 4

回答by owyongsk

A faster solution here is to use this https://gist.github.com/ronaldsmartin/47f5239ab1834c47088eto wrap around your existing spreadsheet.

这里一个更快的解决方案是使用这个https://gist.github.com/ronaldsmartin/47f5239ab1834c47088e来环绕您现有的电子表格。

You first need to change your sheet access to Anyone with link can View

您首先需要将您的工作表访问权限更改为 Anyone with link can View

Add the idand sheethtml param to the URL below.

将id和sheethtml 参数添加到下面的 URL。

https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec

Eg: your idis your sheet id which is

例如：您id的工作表 ID 是

1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI

and your sheet which is

和你的工作表

Sheet2

In your case you can actually see your data here as json at

在您的情况下，您实际上可以在此处将数据视为 json

https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI&sheet=Sheet2

https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFOOSheuqIpI

To be safe, you should deploy the code sheetAsJson.gsin the github gist above as your own in your Google Drive.

为安全起见，您应该将sheetAsJson.gs上面 github gist 中的代码作为您自己的代码部署在您的 Google Drive 中。

以 JSON 形式访问（新式的、公开的）Google 工作表

提问by jochen

回答by Senseful

Common error messages:

常见错误信息：

回答by jochen

回答by bomberHymanets

回答by owyongsk

相关推荐

最近更新

标签

以 JSON 形式访问（新式的、公开的）Google 工作表

提问by jochen

回答by Senseful

Common error messages:

常见错误信息：

回答by jochen

回答by bomberHymanets

回答by owyongsk

相关推荐

快速创建 JSON

如何在jackson json中将空字符串序列化为空字符串

对于 Restful API，GET 方法可以使用 json 数据吗？

为什么 jqXHR.responseText 返回字符串而不是 JSON 对象？

相关推荐

最近更新

标签