Write to Sheet using API and JavaScript - CORS block

Question

I am using Google API and javascript to write data into Google Sheet. Appending data into sheet works as intended. However, basic writing data into specific range gives a "404" and "Access to XMLHttpRequest has been blocked by CORS policy" errors.

Hello, first time contributor and beginner in coding here. I would like to ask you for your help with the following problem. I have tried to post the question following the recommendations and I apologize if I've missed something and my question is not formulated correctly.

Following a youtube tutorial, I have made a working code which appends data into a google sheet.

function submit_form() {
    var sheetid = '...';
    var clientid = '...';
    var clientsecret = '...';
    var refreshtoken = '...';
    var accesstoken = false;
    var sheeturl = 'https://sheets.googleapis.com/v4/spreadsheets/'+sheetid+'/values/B6:append?valueInputOption=USER_ENTERED';
    var data = '{"range": "B6", "majorDimension":"ROWS", "values":[["postedText"]]}';
    var xhr = new XMLHttpRequest();
    xhr.open('POST', 'https://www.googleapis.com/oauth2/v4/token?client_id='+clientid+'&client_secret='+clientsecret+'&refresh_token='+refreshtoken+'&grant_type=refresh_token');
    xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
    xhr.onload = function() {
        var response = JSON.parse(xhr.responseText);
        var accesstoken = response.access_token;
        if(accesstoken) {
            var xxhr = new XMLHttpRequest();
            xxhr.open('POST', sheeturl);
            xxhr.setRequestHeader('Content-type', 'application/json');
            xxhr.setRequestHeader('Authorization', 'OAuth ' + accesstoken );
            xxhr.onload = function() {
              if(xxhr.status == 200) {
                $('#message').html('<p>Success</p>');
              } else {
                $('#message').html('<p>Fail</p>');
              }
            };
            xxhr.send(data);
        }
    };
    xhr.send();
}

However, my goal is to write data into specific range.

I have followed the API documentation, but when I modify my code to write (not append) to specific range

var sheeturl = 'https://sheets.googleapis.com/v4/spreadsheets/'+sheetid+'/values/B6?valueInputOption=USER_ENTERED';

I get two errors -

POST https://sheets.googleapis.com/... 404

and

Access to XMLHttpRequest at 'sheets.googleapis.com/...' from origin 'http://...' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

I have reviewed the tutorial, API setting/credetials, and my code, but I cannot find any reason for why the APPEND works but basic write does not.

I have found two questions (1st, 2nd) regarding Sheets API and CORS but none have helped me.

Does anyone have any idead why the append works but basic write does not? Thank you.

EDIT: I have also tried using the APIs Explorer and both append and update, i.e. basic write, work as intended when executed through the APIs Explorer.

EDIT 2: The following are browser responses when executing the code - Append and Write.

More or less by the minute 3:25 of the video it talks about the origns of the javascript you will be using so you enable them. Yoy must have missed that part. — Juan, Aug 03 '19 at 16:56
@Juan Do you mean the Authorized JavaScript origins at 3:02? I do have my domain (http://people.fsv.cvut.cz) set up here and my JavaScript file is located at (http://people.fsv.cvut.cz/www/holanjak/test/). — JamesBear, Aug 03 '19 at 17:10
Yes, I meant that part. Check the browser when you make the call. In the inspect part in the network tab, you shuld see an xhr options and/or post. When you click on it, you will be able to see the headers. Between the headers, in the request, you should have an origin header. Check if it is the same you have setup in the api. — Juan, Aug 03 '19 at 17:17
@Juan Yes, in both the append and write code, the Origin Request Header is the one I have set up in the API (people.fsv.cvut.cz) - see the screenshots I have added to my question. In the case of "append code" I also get Response headers. In the case of "write code", I do not get the Response headers. — JamesBear, Aug 03 '19 at 17:44
In [this answer](https://stackoverflow.com/a/38871487/11876173) I have found that PUT request should be used when writing data, whereas i have used the POST request. **I have changed the POST to PUT and it works now**. As for the code with append, I have no idea why it worked with POST request. — JamesBear, Aug 03 '19 at 17:51
@Juan yes, thank you very much for your time. Thanks to you, now I know about the inspect part in the network tab where I can see the xhr options and post/put. — JamesBear, Aug 03 '19 at 18:02

score 1 · Accepted Answer · answered Aug 03 '19 at 18:14

As already said in the comments to the question, the issue was with the POST vs PUT method.

As stated in the API documentation and as presented in the JavaScript & XMLHttpRequest example posted as an answer to a similar question, the PUT method should be used when writing to a single range. The first code, used for the appending of data, uses a POST method which is used to append data.

The solution to this question is, therefore, to simply change POST to PUT in the following line of code.

xxhr.open('PUT', sheeturl);

Write to Sheet using API and JavaScript - CORS block

1 Answers1