以 JSON 形式访问(新式的、公共的)Google 工作表 [英] Accessing a (new-style, public) Google sheet as JSON

问题描述

如何通过 JSON 访问(新式)Google 表格的内容?我的目标是从 JavaScript 访问值，因此我需要能够通过 HTTP 下载 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.

示例:如何从 这张表作为JSON?

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

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

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

• 网络上的许多教程都是从在 url 中查找 key=... 值的说明开始的.我导出工作表时得到的 URL 是 https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/pubhtml?gid=1822753188&single=true 并且没有 key=... 在里面.

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

访问公共 Google 表格" 的答案似乎是表明我应该尝试 https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/export?format=csv&id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI&gid=1822753188 以得到一个CSV版本，但这对我不起作用:我得到的是登录页面而不是数据.

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.

我找到了使用 Google Apps Scripts 的方法，但这些方法似乎需要用户在浏览器中进行一些操作，而不是提供下载链接.

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

推荐答案

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

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

1. 生成电子表格 API 密钥(请参阅下面的说明).
2. 使您的工作表可公开访问.

3. 使用以下形式的请求:

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

3. Use a request of the form:

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

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

You'll then get a clean JSON response back:

{
  "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"]
  ],
}

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

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

有关详细信息，请参阅基础阅读.

See Basic Reading for more information.

从 v4 API 开始，所有请求都必须伴随一个标识符(例如 API 密钥):

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

向 Google Sheets 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.

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

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

确保:

1. 在 Google Cloud Platform 上创建一个新应用.
2. 创建新的 API 密钥.
3. 添加 Google Sheets API.(API 管理器 > 仪表板 > 启用 API)

<小时>

请注意，您可以仍然访问公共数据而无需强制用户登录在:

---

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

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

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.

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

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.

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

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

请求缺少有效的 API 密钥.

The request is missing a valid API key.

您没有在调用中包含 key= 参数.

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

API 密钥无效.请传递有效的 API 密钥.Google 开发者控制台

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

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

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 密钥.
Google 开发者控制台 API 密钥

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

您的 API 密钥可能是正确的，但您很可能没有添加 Google 表格权限.转到 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.

调用方没有权限

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

Your sheet isn't set to be publicly accessible.

这篇关于以 JSON 形式访问(新式的、公共的)Google 工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！