Getting value of a cell from google docs spreadsheet into javascript

23,660

Solution 1

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>

Solution 2

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

Solution 3

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!

23,660

Author by

gordon613

This user likes to keep an air of mystery about them

Updated on July 28, 2022

Comments

gordon613 almost 2 years
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
Oliver over 11 years

Have you tested this? I suspect it will not work due to cross domain issues.
ultraklon over 11 years

Works for me Oliver, i have the code, i'll edit the answer so you can copy paste it if you want to try it
Oliver over 11 years

Weird, you're quite right: it works fine in firefox. I'm not sure if it is failing in IE8 due to the cross domain issue or if this is just IE being bad - I don't understand why XSS sometimes works and sometimes doesn't.
MarutiB over 10 years

Cross domain issues wont happen as Google docs server sends CORS headers more here :- developer.mozilla.org/en/docs/HTTP/Access_control_CORS
Kokodoko over 8 years

But... they do happen! Testing in safari and getting: origin (my domain) is not allowed by Access-Control-Allow-Origin.