从 google docs 电子表格中获取单元格的值到 javascript
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14885231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting value of a cell from google docs spreadsheet into javascript
提问by gordon613
I have a javascript function
我有一个 javascript 函数
function calculate() {
var interestRate=4.5;
...
}
I would like the interestRate to come from a cell in a google Docs spreadsheet. I created a google docs spreadsheet, and stored the interest rate in Cell B2
我希望利率来自谷歌文档电子表格中的一个单元格。我创建了一个 google docs 电子表格，并将利率存储在 Cell B2 中
I used the "Get a link to Published Data" feature in Google Docs to to get a link to cell B2. The link looks like this.
我使用 Google Docs 中的“获取已发布数据的链接”功能来获取指向单元格 B2 的链接。链接看起来像这样。
https://docs.google.com/spreadsheet/pub?key=....c&single=true&gid=0&range=b2&output=html
https://docs.google.com/spreadsheet/pub?key=..c&single=true&gid=0&range=b2&output=html
Is there anyway of getting the value from the link into my javascript function?
有没有办法从链接中获取值到我的 javascript 函数中？
Thanks
谢谢
回答by ultraklon
First of all, what i would recommend is to "Get a link to Published Data" as csv, as it is just 1 field so you don't have to parse it. If made this spreadsheet, and make a link with "Get a link to Published Data", this second link will get a csv with just one field in this case. You will be able to get this with the following js code (note i'm using jQuery)
首先，我建议将“获取已发布数据的链接”作为 csv，因为它只有 1 个字段，因此您不必解析它。如果制作了这个电子表格，并制作了一个带有“获取已发布数据的链接”的链接，在这种情况下，第二个链接将获得一个只有一个字段的 csv。您将能够使用以下 js 代码获得它（注意我使用的是 jQuery）
$.ajax("https://docs.google.com/spreadsheet/pub?key=0Auwt3KepmdoudE1iZFVFYmlQclcxbW85YzNZSTYyUHc&single=true&gid=0&range=b5&output=csv").done(function(result){
alert(result);
});
Regards
问候
EDIT:The full code
编辑：完整代码
<!doctype>
<html>
<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"> </script>
</head>
<body>
<script>
$.ajax("https://docs.google.com/spreadsheet/pub?key=0Auwt3KepmdoudE1iZFVFYmlQclcxbW85YzNZSTYyUHc&single=true&gid=0&range=b5&output=csv").done(function(result){
alert(result);
});
</script>
</body>
</html>
回答by Oliver
You may run into trouble with the Same origin policy. Usually, web browsers do not allow AJAX requests to a different domain as a security measure. I was a little suprised that ultraklon's solution works in firefox, so perhaps this has changed in newer browsers. It doesn't seem to work in IE8 though, so if you need to support that browser, read on.
您可能会遇到同源策略的问题。通常，作为安全措施，Web 浏览器不允许对不同域的 AJAX 请求。我有点惊讶 Ultraklon 的解决方案在 Firefox 中有效，所以这可能在较新的浏览器中发生了变化。不过它似乎在 IE8 中不起作用，所以如果您需要支持该浏览器，请继续阅读。
Usually, JSONPis used as a way around the same origin policy, but this is not an option in your case as google docs does not offer data in this format.
通常，JSONP用作绕过同源策略的一种方式，但在您的情况下这不是一个选项，因为 google docs 不提供这种格式的数据。
Perhaps the best solution would be to proxy the request to google docs via your own server, as suggested in this question. Create a method on you web server that takes a cell (or cell range) as a parameter. Have the server method request the cell data from google docs, then have it return this data to the caller in JSON format.
也许最好的解决方案是通过您自己的服务器将请求代理到 google docs，如本问题中所建议。在您的 Web 服务器上创建一个将单元格（或单元格范围）作为参数的方法。让服务器方法从 google docs 请求单元格数据，然后让它以 JSON 格式将此数据返回给调用者。
Once you have the request proxied, you can get it into your javascript code with a bit of ajax, as in ultraklon's answer:
代理请求后，您可以使用一些 ajax 将其放入您的 javascript 代码中，如ultaklon 的回答所示：
$.get('/google-docs-proxy?cell=B1', function(data) {
alert('data');
});
回答by EdF
This is possible, but it's a bit scruffy, despite the cross-site problem.
这是可能的，但它有点邋遢，尽管存在跨站点问题。
The steps are as follows:
步骤如下：
First you need to create a cell within a google spreadsheet that reads the value you want to use, for instance, if the value you want is in cell A1 of the spreadsheet you could put this as a formula in cell B1 as follows:
首先，您需要在谷歌电子表格中创建一个单元格来读取您要使用的值，例如，如果您想要的值在电子表格的单元格 A1 中，您可以将其作为公式放在单元格 B1 中，如下所示：
="interestrate="&text(A1,"0.0")&";"
What this does is to create a cell that contains the text(for illustration I've assumed that contained 5.7 as a number):
这样做是创建一个包含文本的单元格（为了说明，我假设包含 5.7 作为一个数字）：
interestrate="5.7";
Which is a valid statement in javascript.
这是 javascript 中的有效语句。
Then you put the following into the head of your html page (or wherever):
然后将以下内容放入 html 页面的头部（或任何地方）：
<script src="https://docs.google.com/spreadsheet/pub?key=0AgLSs-sWp9JUdDgxV3VXbUZhZ3BXUGQ3MEtyVWhYRnc&single=true&gid=0&output=txt&range=b1"></script>
(obviously putting the key value right for your spreadsheet which contains that new function).
（显然，将键值放在包含该新函数的电子表格中）。
Now, when the page loads the user's browser will fetch the single line of javascript from the google spreadsheet and execute it. Hence the variable, in this case "interestrate", is set to the value in the original cell (as a string so you'll need to parseFloat this to do calculations).
现在，当页面加载时，用户的浏览器将从谷歌电子表格中获取单行 javascript 并执行它。因此，在这种情况下，变量“利率”被设置为原始单元格中的值（作为字符串，因此您需要 parseFloat 来进行计算）。
This will take a little time so you need to make sure it's operated before your script tries to use the value.
这将需要一些时间，因此您需要在脚本尝试使用该值之前确保它已被操作。
Also, I suspect this will get VERY slow if you try it with lots of numbers.
另外，我怀疑如果您尝试使用大量数字，这会变得非常慢。
Good luck!
祝你好运！
