以JSON形式访问(新样式、公共)谷歌表

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.

如何访问(新样式)谷歌表a JSON的内容?我的目标是从JavaScript访问值，因此我需要能够通过HTTP下载JSON。

Example: how can I download the data from this sheet as 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=true and has no key=... in it.

  web上的许多教程都是从查找密钥的说明开始的。在url的值。我导出表格时得到的URL是https://docs.google.com/spreadsheets/d/1mhv1lpzuftruzhzry - ms0cidvkyifjlwcmpi4oqvi/pubhtml?gid=1822753188&single=true，无键=…在里面。

• 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=1822753188 to get a CSV version, but this does not work for me: I get a sign-in page instead of the data.

  “访问公共谷歌表”的答案似乎表明，我应该尝试https://docs.google.com/spreadsheets/d/1mhv1lpzuftruzhz - ms0cidvkyifjlwcmpi4oqvi/export?要得到一个CSV版本，但这对我不起作用:我得到了一个登录页面，而不是数据。

• I found approaches using Google Apps Scripts, but these seem to require some user action in the browser instead of giving a download link.

  我发现了使用谷歌应用程序脚本的方法，但这些方法似乎需要浏览器中的一些用户操作，而不是提供下载链接。

4 个解决方案

#1

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:

我终于解决了我的问题。仅供以后参考，万一有人遇到同样的麻烦，我的解决方案如下:

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

   为了使工作表可以公开访问，需要使工作表可以公开访问。这是在谷歌表web界面中完成的，使用菜单项文件>发布到web…> >发布的链接。可以发布整个电子表格或单独的工作表。

2. 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 key and worksheetId seems not to be explained in the API documentation.

   在谷歌Sheets API web页面上描述了以编程方式从谷歌表访问数据的API。此API使用表单https://spreadsheets.google.com/feeds/./key/worksheetid/…的url。有点奇怪的是，key和worksheetId的含义似乎没有在API文档中解释。

   My experiments show that the key value 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 worksheetId seems 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 worksheetId in this case is 2.

   我的实验表明，可以通过获取通过web界面访问表的url(请参见这里)找到键值。键在/d/之后，直到下一个斜杠。对于问题中的电子表格，关键是1mhv1lpzuftruzhzry - ms0cidvkyifjlwcmpi4oqvi。工作表似乎是一个整数，给出了工作表在电子表格中的位置。对于问题中的示例，必须知道所显示的工作表是第二个工作表，本例中的工作表是2。

   The API defined public and private requests. To access an exported resource without authentication, public requests must be used.

   API定义了公共和私有请求。要访问一个没有身份验证的导出资源，必须使用公共请求。

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

   API调用从电子表格获取数据，在“检索基于列表的提要”一节中解释(在示例中单击“Protocol”选项卡)。所需的URL从问题中的电子表格中提取数据

   https://spreadsheets.google.com/feeds/list/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/2/public/full

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

   对该URL的HTTP GET请求将该数据作为XML返回。(我还没有找到以JSON格式获取数据的方法。)

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

   对跨站点请求的常见保护使得通过web应用程序的JavaScript XML RPC调用很难访问数据。

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，所以可能不可能再以这种格式提取数据了?

#2

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

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

1. Generate a spreadsheets API key (see instructions below).
2. 生成一个电子表格API键(参见下面的说明)。
3. Make your sheet publicly accessible.
4. 让你的信息公开。

5. Use a request of the form:

   使用表格的要求:

   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", "$20.50", "4", "3/1/2016"],
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "30/20/2016"],
    ["Totals", "$135.5", "7", "3/20/2016"]
  ],
}

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

注意，如果要指定页面的全部内容，那么Sheet1这样的标识符是完全有效的。

See Basic Reading for more information.

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

---

As of v4 API, all requests must be accompanied by 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.

对公共数据的谷歌Sheets API的请求必须附带一个标识符，该标识符可以是API键或访问令牌。

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

按照链接文档中的步骤在凭证页面上创建一个API键。

Make sure to:

确保:

1. Create a new app on Google Cloud Platform.
2. 在谷歌云平台上创建一个新的应用程序。
3. Create a new API key.
4. 创建一个新的API密钥。
5. Add the Google Sheets API. (API Manager > Dashboard > Enable API)
6. 添加谷歌表API。(API管理器>仪表板>启用API)

---

Note that you can still access public data without 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 not need to publish the sheet to the web. All you need to do is make sure anyone with the link can access the sheet.

请注意，您不需要将该表发布到web上。您所需要做的就是确保具有链接的任何人都可以访问这个表。

(I.e. when you click Create credentials on 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.)

(也就是说，当您单击谷歌Sheets API上的Create credentials时，选择其他非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.

你没有把钥匙=参数包括在你的电话里。

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 console and create one.

您提供了错误的API密钥。确保你输入的钥匙是正确的。如果您还没有密钥，请转到谷歌开发人员控制台并创建一个密钥。

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

API密匙不见了。请传递有效的API密钥。谷歌开发人员控制台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键可能是正确的，但是您很可能没有添加谷歌表权限。进入谷歌开发者控制台API关键页面并添加表单权限。

The caller does not have permission

打电话的人没有权限

Your sheet isn't set to be publicly accessible.

你的表格不能被公开访问。

#3

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 sheetID is based on the order of the sheets in the spreadsheet.

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

#4

A faster solution here is to use this https://gist.github.com/ronaldsmartin/47f5239ab1834c47088e to 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

您首先需要将您的表访问权限更改为任何具有链接的人都可以查看

Add the id and sheet html param to the URL below.

将id和表单html参数添加到下面的URL。

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

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

Eg: your id is your sheet id which is

你的身份证就是你的身份证

1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI

1 mhv1lpzuftruzhzry-ms0cidvkyifjlwcmpi4oouqvi

and your sheet which is

还有你的床单

Sheet2

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-ms0ciDVKYiFJLWCMpi4OOuqvI&sheet=Sheet2

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

为了安全起见，应该部署代码sheetAsJson。在你的谷歌驱动器上作为你自己的github的主旨。