Getting value of a cell from google docs spreadsheet into javascript

6
votes

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

3
javascriptgoogle-sheetsgoogle-docs

3 Answers

5
votes

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

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');
});
1
votes

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:

="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):

interestrate="5.7";

Which is a valid statement in javascript.

Then you put the following into the head of your html page (or wherever):

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

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!