Google Sheets API:从公开表中读取单个单元格的值 [英] Google Sheets API: Read single cell value from public sheet
问题描述
我正在尝试使用PHP/JS为WordPress写一个简单的插件.该插件只有一个目标，即创建一个小部件.对于那些不熟悉WordPress插件实现的人来说，这并不是太复杂-我可以像往常一样使用JS/PHP.因此，出于这个问题的目的，单个PHP/HTML文件可能会提供我正在寻找的答案.
I am trying to write a simple plugin for WordPress using PHP/JS. The plugin has only one objective, to create a widget. For those of you unfamiliar with the WordPress plugin implementation, it's not too complicated - I can use JS/PHP as normal. For the purpose of this question therefore, a single PHP/HTML file may give the answer I'm looking for.
我有一个具有以下共享设置的可公开访问的工作表:
I have a publicly-accessible Sheet with the following sharing settings:
我现在要做的就是检索单个工作表上单个单元格的值.该工作表称为实时摘要，单元格为 E20 .
All I want to do now is retrieve the value of a single cell on a single worksheet. The worksheet is called Live Summary and the cell is E20.
我已经使用oAuth 2.0身份验证进行了这项工作.但是，我不希望用户必须进行身份验证才能看到此信息.
I have gotten this working using oAuth 2.0 authentication. However, I don't want the user to have to authenticate to see this information.
我希望我的网站始终显示此单元格的值，就像它是网站的功能一样，或者好像我是从安装了WordPress的MySQL数据库中提取它一样.
I want my website to display the value of this cell at all times, as if it were a feature of the website or as if I were pulling it from the MySQL database that WordPress is installed on.
我已经从Google Sheets API中了解了有关使用某些GET端点的信息，但是对于我来说，如果我不使用oAuth令牌，我什至不知道从哪里开始.
I've read about using certain GET endpoints from the Google Sheets API but for the life of me I don't even know where to start if I'm not using oAuth tokens.
有人可以请a)告诉我是否可行，如果可以的话b)为我指明正确的方向开始使用
Could someone please a) tell me if this possible and, if it is, b) point me in the right direction to get started?
最好使用JavaScript-但我也可以使用PHP.
Preferably using JavaScript - but I could cope with PHP too.
推荐答案
在这里，您可以在没有OAuth的情况下使它工作.
Here's how you can make it work without OAuth.
1)将您的工作表公开给网络上的所有人.(就像您已经拥有的一样.)
1) Make your sheet public to everyone on the web. (As you already have.)
2)发布:在Google表格用户界面中，导航至文件>在网络上发布...
2) Publish it: In the Google Sheets UI, navigate to File > Publish on the web...
3)选择链接，整个文档，网页类型，然后单击发布.
3) Choose type Link, Entire Document, Web Page and click Publish.
之后，您可以(仍然)使用 https://developers中记录的旧版API.google.com/sheets/api/v3/worksheets 访问您的数据.
After that you can (still) use the legacy API documented in https://developers.google.com/sheets/api/v3/worksheets to access your data.
用于访问数据的URL的格式为:
The URL to access the data is of the form:
https://spreadsheets.google.com/feeds/<WHAT_KIND_OF_DATA>/<YOUR_SHEET_KEY>//<YOUR_SPREADSHEET_SHEET:)>/public/full?alt = json
例如，可以在以下位置找到我已发布的工作表的数据(在E20处仅包含文本"I'm E20"):
So, for example, data for a sheet I have published (containing only the text "I'm E20" at E20) can be found at:
还有一些仅发布数据子集的选项，可能更适合您.但我希望这能使您前进.
There are also options to only publish a subset of the data, which might be better suited for you. But i hope this gets you forward.
下一步:
下一个问题可能是CORS.您可以使用例如JSON-P.这是一个可以正常工作的JSON-P解决方案，用于将E20单元格的内容加载到我的工作表中:
The next problem might be CORS. You can bypass it with e.g. JSON-P. Here's a fully working JSON-P solution to loading the content of the E20-cell on my sheet:
<html>
<body>
<pre id='data'></pre>
</body>
<script>
const onDataLoaded = (data) => {
const e20Content = data.feed.entry.find((entry) => entry.title.$t == 'E20').content.$t
document.getElementById('data').innerHTML = e20Content
}
</script>
<script src="https://spreadsheets.google.com/feeds/cells/1TbMrtJl01i-Q5YudlhdAB_E1LTYkkLswnql5LHyiIuk/1/public/basic?alt=json-in-script&callback=onDataLoaded"></script>
</html>
这篇关于Google Sheets API:从公开表中读取单个单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
