Menu
I have a javascript function
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
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.
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?
Thanks
417
Get selected cell value in your Google Sheet Script First, let us add a menu item to your Google sheet so that it is easy for us to try the functions. Now we have to add getCurrentCellValue() function. This function will get the value of the currently selected cell and then show it in a message box.
Get data from other sheets in your spreadsheet Select a cell. Type = followed by the sheet name, an exclamation point, and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'!
Type = followed by the sheet name, an exclamation mark and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'! B4 .
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)
$.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>
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.
Usually, JSONP is 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.
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.
Once you have the request proxied, you can get it into your javascript code with a bit of ajax, as in ultraklon's answer:
$.get('/google-docs-proxy?cell=B1', function(data) {
alert('data');
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
