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
– Juan, Commented Aug 3, 2019 at 16:56
@Juan Do you mean the Authorized JavaScript origins at 3:02? I do have my domain (people.fsv.cvut.cz) set up here and my JavaScript file is located at (people.fsv.cvut.cz/www/holanjak/test). — JamesBear
– JamesBear, Commented Aug 3, 2019 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
– Juan, Commented Aug 3, 2019 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
– JamesBear, Commented Aug 3, 2019 at 17:44
In this answer 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
– JamesBear, Commented Aug 3, 2019 at 17:51

Jakub Holan · Accepted Answer · 2019-08-03 18:14:23Z

1

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

answered Aug 3, 2019 at 18:14

Jakub Holan

4512 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write to Sheet using API and JavaScript - CORS block

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related