Getting data from Google docs into Javascript

Question

I try to get data from google spreadsheets and everything is allright when I'm testing html page locally. But when I load my html file and javascript file into server nothing works.

Here is the code of html file "page.htm":

<html>
<head>
<title>
</title>
<script type="text/javascript" src="teams.js" >
</script>
</head>
<body
onload= "Data();">
<table>             
    <form name="Team">
    <tr>                
        <td>
        <input  size="19"  name="tName" readonly >
        </td>               
    </tr>
    </form>
    </table>
</body>
</html>

And js file "teams.js":

function Data() {
var url="https://docs.google.com/spreadsheets/d/18WEeF3d9pJWYK1sheNHgc0KOi845cjyZgJ8x6TVisFM/pub?&gid=0&range=A1&output=csv";

xmlhttp=new XMLHttpRequest();
  xmlhttp.onreadystatechange = function() {
    if(xmlhttp.readyState == 4 && xmlhttp.status==200){
      document.Team.tName.value = xmlhttp.responseText;
    }
  };
  xmlhttp.open("GET",url,true);
  xmlhttp.send(null);
}

Google doc

What does your browser's console say? Any errors? (I'm voting for CORS error) — jehna1, Oct 29 '15 at 17:32
It says nothing - on browser I can see just an empty field "Team.tName" where should be a word "Team1" — Rm1, Oct 29 '15 at 17:39
Actually I did it like here: http://stackoverflow.com/questions/16485255/how-do-you-import-data-from-a-google-spreadsheet-to-javascript — Rm1, Oct 29 '15 at 17:42
Would it be possible to get the link to the actual, uploaded files? — jehna1, Oct 29 '15 at 17:56
unfortunately no - I used not mine server. But I cheked if it's problem with connection between html file and js-file: I had put into js-file at function "Data()" a string "document.Team.tName.value = Team1" - and it started to work. PS Browsers in which I checked this - Chrome, Opera, IE — Rm1, Oct 29 '15 at 18:01
Tried on my own server and figured why it didn't work. I got it working though, see my answer below. — jehna1, Oct 29 '15 at 18:25

jehna1 · Accepted Answer · 2015-10-29T18:31:40.490

Tried this on my own server - got a following CORS error on the browser's console:

This means that you cannot directly access the url with your browser, because the Google's server is not sending back a required header field that would allow this.

A way around this is to use an alternative API, that can provide us with JSONP format output for the Google Spreadsheet:

So consider this JavaScript:

function Data(response) {
  document.Team.tName.value = response.feed.entry[0].gs$cell.$t;
}

And the following HTML:

<html>
<head>
<title>
</title>
<script type="text/javascript" src="teams.js" >
</script>
</head>
<body>
<table>             
    <form name="Team">
    <tr>                
        <td>
        <input  size="19"  name="tName" readonly >
        </td>               
    </tr>
    </form>
    </table>
<script src="https://spreadsheets.google.com/feeds/cells/18WEeF3d9pJWYK1sheNHgc0KOi845cjyZgJ8x6TVisFM/1/public/values?alt=json-in-script&callback=Data&range=A1"></script>
</body>
</html>

And it should work perfectly.

This works as, rather than your won code, the Google's own server calls the Data function with the proper data - a method called JSONP that allows cross-domain data requests. Requesting data from another domain is blocked by default in the browsers. The only exception is the file:// protocol, which allows some requests to any domains, as there is no origin domain to match the rules. This explains why your code worked on the local, but not after it had been uploaded to the server.

score 0 · Answer 2 · edited May 23 '17 at 12:13

When I run the code it works, fills the input with the csv file value. When I try to run the link on How do you Import data from a Google Spreadsheet to Javascript? got cross origin block from my browser.

If you can't run the script below you should try allowing CORS on your browser or perhaps try with ajax.load to get the file.

<html>

<head>
  <title>
  </title>
  <script type="text/javascript">
    function Data() {
      var url = "https://docs.google.com/spreadsheets/d/18WEeF3d9pJWYK1sheNHgc0KOi845cjyZgJ8x6TVisFM/pub?&gid=0&range=A1&output=csv";

      xmlhttp = new XMLHttpRequest();
      xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
          document.Team.tName.value = xmlhttp.responseText;
        }
      };
      xmlhttp.open("GET", url, true);
      xmlhttp.send(null);
    }
  </script>
</head>

<body onload="Data();">
  <table>
    <form name="Team">
      <tr>
        <td>
          <input size="19" name="tName" readonly>
        </td>
      </tr>
    </form>
  </table>
</body>

You should see it something like:

Getting data from Google docs into Javascript

2 Answers2

Linked