Access-Control-Allow-Origin header needed when pulling data from a Google Sheet with Javascript?

Question

everyone! First post here, although I've been lurking for quite a while. Basically, my question is about taking data from a published Google sheet and putting it on a website with Javascript. I have been able to get this done, using the following code between script tags, where anything in asterisks is replaced by the actual thing (note that this code is repeated multiple times for different cells):

$.ajax("https://docs.google.com/spreadsheets/d/*spreadsheetId*/pub?gid=0&range=*cellId*&single=true&output=csv").done(function(result){
    document.getElementById("*id*").innerHTML = result;
});

I got this code from here and changed it a slight amount: Getting value of a cell from google docs spreadsheet into javascript

Now, the problem with this is that, while it works locally on my own computer, it does not work when I upload it to my hosting service (if it helps, I'm using Hostinger and running the latest version of Chrome, although it doesn't work on IE either). Somebody on the post linked above said that this should work, despite cross domain issues, but for some reason, it doesn't seem to. When I inspect element and go under the console tab, there are many "XMLHttpRequest cannot load" errors (one for each time I tried to access the spreadsheet), and they each say the following:

https://docs.google.com/spreadsheets/d/*spreadsheetId*/pub?gid=0&range=*cellId*&single=true&output=csv. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://*myWebsite*' is therefore not allowed access.

Before I finish up my post, I'd like to add that, while I know enough HTML and CSS to make a website, my Javascript skills are currently very lacking. A lot of the Javascript I've used is adapted from what other people have said, and me blindly believing them. I tried adding some other script tags in the header, such as these:

<script type="text/javascript" src="jquery-3.0.0.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

And I really can't say for sure if these are helping me or just sitting there pointlessly. I did want to add that I have put these in my header though.

So, my main questions are:

1) Has anyone else experienced this problem currently or in the past?

2) Am I doing something painfully obviously wrong?

3) If not, is there a better way I should be pulling data from published Google spreadsheets?

4) If not, is there some code I could add in order to avoid the error in Chrome? I've noticed many people saying something can be done using a PHP header to avoid this, but I'd rather not use that unless it's really the only option. If there's a way to get around the error using Javascript, that would be ideal.

Anyway, thank you so much to anyone who sees this and takes the time to answer. I feel like such a noob asking this question, but I've been searching on and off over the past couple weeks and I can't seem to find a solution that works. Hopefully other people are experiencing the same thing and can learn from this too. Or maybe I'm just crazy.

Answer 1

Why this error?

With JavaScript running in browsers, certain restrictions are in place about what your code can access.

JavaScript in a browser can make requests to content from other sites via XMLHttpRequests, which is what $.ajax does in the background. However, certain restrictions are in place under the same-origin policy.

The reason for this policy, from the above link, is that allowing JavaScript to make arbitrary cross-domain requests would be a security concern:

Assume that a user is visiting a banking website and doesn't log out. Then he goes to any random other site and that site has some malicious JavaScript code running in the background that requests data from the banking site. Because the user is still logged in on the banking site, that malicious code could do anything on the banking site. For example, get a list of your last transactions, create a new transaction, etc. This is because the browser can send and receive session cookies to the banking website based on the domain of the banking website.

To avoid such a scenario, we have the same-origin policy, which prevents JavaScript from making requests to different websites, in short.

The main way to access data from a different site with Javascript is for the site to which you're making requests to add the Access-Control-Allow-Origin header when it returns the content. This tells your browser that the site you're requesting is giving explicit permission for another site to access its content.

If sheets does not allow this functionality, I suspect you'll need to find a different solution.

Things you could try

1. Looking at the question you linked, there's a key parameter in their request. I suspect that the key parameter is used to authenticate a request to Google's API, it's an API key. This is likely crucial to the request, so that Google can trace the request to you. Google may be refusing to serve you the content because you're missing this key.
2. Do some research into whether Google provides a formal CORS API for Sheets.
3. Try some of the methods of circumventing this policy, such as JSONP.

Basically, this error means that Google hasn't provided the header that gives your browser permission to access its content.

Answer 2

You need to create a script in your server in order to handle the connection to google docs. It could be done in php, python, javascript (node) or whatever server side language you use (in this example I will use php) and you will be able to add de CORS headers for your client response.

So this is my handler.php file:

<?php 
header("Access-Control-Allow-Origin: *");
$externalIp = file_get_contents('https://docs.google.com/spreadsheets/d/*spreadsheetId*/pub?gid=0&single=true&output=csv');
echo $externalIp;

And this is my client file:

<html>
<head>
    <title>My Title</title>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script>
        $.ajax("handler.php").done(function(result){
            document.getElementById("result").innerHTML = result;
        });         
    </script>
</head>
<body>
    <div id="result"></div>
</body>