CORS preflight fails when writing to Google Spreadsheet API

Question

I'm working on a JS application that uses Google spreadsheets. I access them via the REST interface using OAuth authorization, and all is well and good when I stick to GET requests used for reading.

I'd like to add a new worksheet using the API shown in the docs. This requires a POST request with the rather strange Content-type: application/atom+xml, which I do like this (JQuery):

$.ajax("https://spreadsheets.google.com/feeds/worksheets/{{key}}/private/full", {
  type: "POST",
  contentType: "application/atom+xml",
  headers: { Authorization: "Bearer" + token },
  data: data
});

This makes Chrome issue a preflight request due to CORS requirements. The preflight OPTIONS request fails - Google does not include Access-Control-Allow-Origin headers in the response, and Chrome refuses to go forward:

OPTIONS https://spreadsheets.google.com/feeds/.../private/full 
No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost' is therefore not allowed access.

However, if I make a straight GET to the same URL (this is actually how you read the spreadsheet), I do get the Access-Control-* headers, which means that CORS support was intended. The same with POST requests with standard content types (like text/plain) which don't trigger a preflight OPTIONS - I get the CORS headers (even though the request fails do to the bad content type).

Does anybody have an idea how to work around this problem, or the "correct" way to do it from a browser? Alternatively, a pointer towards any piece of working code that is able to do 'write' operations to Google spreadsheets from in-browser JS would also be great.

I would like to keep this application client-side only if possible - I understand that with a server-side component handling Google API interaction, this thing would be easier.

Answer 1

Never mind what I wrote above. That only solves it part of the time. It seems Google Sheets API simply doesn't support CORS consistently. I wrote a server-side proxy which only passes requests through to google.com, that being the only way forward.

I thought I'd share my js code, because I wrote a nice little thing that can be used just like $.ajax. Also glad to share the server-side code too, but you could use something like this to interface with your own server-side proxy. It's not pretty but it is working. Oh, um, LGPL. Here's the js:

//  ####    ####   #####    ####     ##    ##   ##    ##   ##  ##
// ##  ##  ##  ##  ##  ##  ##       ####   ##   ##   ####  ##  ##
// ##      ##  ##  #####    ####   ##  ##  ## # ##  ##  ##  ####
// ##  ##  ##  ##  ## ##       ##  ######  #######  ######   ##
//  ####    ####   ##  ##   ####   ##  ##   ## ##   ##  ##   ##

function CorsAway(serverSideUrl) {
    // Server-side proxy handling of cross-domain AJAX requests.
    this.serverSideUrl = serverSideUrl;

    // This hash contains information as to whether each $.ajax parameter should be submitted to $.ajax directly, or passed to the CorsAway server.
    // true means that the parameter should be passed to the CorsAway server
    this.parameterIsForRemoteServer = {
//      accepts:        // not supported
//      async:          // not supported
        beforeSend:     false,              // submit to $.ajax
//      cache:          // not supported, see $.ajax documentation for how to implement
        complete:       false,              // submit to $.ajax
        contents:       false,              // submit to $.ajax
        contentType:    true,               // submit to remote server
        context:        false,              // submit to $.ajax
        converters:     false,              // submit to $.ajax
//      crossDomain:    // not supported
        data:           true,               // submit to remote server
        dataFilter:     false,              // submit to $.ajax
        dataType:       false,              // submit to $.ajax
        error:          false,              // submit to $.ajax
//      global:         // not supported
        headers:        true,               // submit to remote server
//      ifModified:     // not supported
//      isLocal:        // not supported
//      jsonp:          // not supported
//      jsonpCallback:  // not supported
        method:         true,               // submit to remote server
///     mimeType:       true,               // submit to remote server
///     password:       true,               // submit to remote server
//      processData:    // REQUIRES SPECIAL HANDLING: SEE COMMENTS IN CODE BELOW
//      scriptCharset:  // not supported
        statusCode:     false,              // submit to $.ajax
        success:        false,              // submit to $.ajax
        timeout:        false,              // submit to $.ajax
//      traditional:    // not supported
//      type:           // not supported
///     url:            true,               // submit to remote server
///     username:       true                // submit to remote server
//      xhr:            // not supported
//      xhrFields:      // not supported
    }

    // Use it just like $.ajax
    this.ajax = function (url, jqAjaxInfo) {
        //Redirect all requests to a call to the server

        // Sort jqAjaxInfo into parameters for $.ajax and for the remote server
        var localAjaxParams = {};
        var remoteHttpRequestParams = {};
        for(var k in jqAjaxInfo) {
            if(this.parameterIsForRemoteServer[k]) {
                // Submit it to the remote server
                remoteHttpRequestParams[k] = jqAjaxInfo[k];
            } else {        // some parameters are not supported; their behavior is undefined and doesn't matter
                // Submit it to $.ajax
                localAjaxParams[k] = jqAjaxInfo[k];
            }
        }

        // Prepare specially encapsulated data parameter for local $.ajax to submit to server-side CorsAway
        localAjaxParams.data = {
            dataToSubmit:               localAjaxParams.data,
            remoteHttpRequestParams:    remoteHttpRequestParams,
            remoteUrl:                  url
        };
        localAjaxParams.method = 'PUT'; // Always make request to CorsAway by PUT

        // Make call to $.ajax and pass info to server-side CorsAway service
        $.ajax(this.serverSideUrl, localAjaxParams);
    }
}

// Instantiate global object with URL of server-side CorsAway service
window.corsAway = new CorsAway('/local/url/of/corsaway.php');

So now instead of $.ajax I use window.corsAway.ajax with exactly the same results. The server-side proxy is designed to return the data from the remote server, or to pass through any HTML error it receives back to ajax.

Something seems very wrong about writing a utility called CorsAway, but hey. The server-side proxy checks the domain and only passes things through to approved domains (right now only Google) so what could go wrong, right? Somebody tell me if something could go wrong. :-)

Answer 2

Updated

I know this is not a answer for your question, but I just found answer for my issue my own. I have updated my ajax call from

$.ajax({
    url: 'https://spreadsheets.google.com/feeds/worksheets/{0}/private/full',
    headers: {
        'GData-Version': '3.0',
        'Authorization': 'Bearer ' + authToken
    }
});

to

$.ajax({
    url: 'https://spreadsheets.google.com/feeds/worksheets/{0}/private/full',
    headers: {
        'Authorization': 'Bearer ' + authToken
    }
});

Removed GData-Version header.

Initial

I am having the same issue. I think this is a issue introduced in last couple of days because for me this use to work last week.

Answer 3

I haven't tried writing to Sheets on my project yet, but I've been bedeviled by exactly the same error on reading from the worksheets feed. I solved it by adding a ?callback parameter to the url. This is totally weird and I don't understand why it worked, b ut it seems to be a particularity of Chrome.

I saw another solution that recommended having a callback function callbackFunction defined in global scope that just returns true. I played around a bit and found that ?callback= calls a function, must be in global (window) scope but doesn't have to be defined all the way up top, it's just fine to assign it to window.callbackFunc right before the ajax call, and it passes the text response to the callback. So:

window.callbackContinue = function (response) {
     console.log(response);
}

$.ajax('https://my.url?callback=callbackContinue', ajaxOptions);

totally works. My CORS problems went away when I added that callback parameter to the URL. So now I'm using window.callbackInGlobalScopeWithAVeryLongNameBecauseIMakeALotOfDifferentAjaxCallsInMyApplication instead of the $.ajax({success: ...}) option, but that may just be life in the ajax lane.

I don't understand why it works and I don't know if it will work with write operations or if it is specific to the worksheets feed. I did NOT get this error reading from the spreadsheets feed, so there's something going on fishy. What I'm working on is kind of a side project that I do instead of knitting, so one day in the future I'll try it with a write operation and we'll see what happens. Just wanted to offer this admittedly bizarre idea for now.