Send Request body to a google sheet using sheets API and XMLHttpRequest

Question

For context, I'm doing a small application in Qt QML that requires reading/writing data into a google sheet. The reading part is working fine, however I'm having trouble using the "sheets.spreadsheets.values.append" function from google sheets API V4 (documentation can be found here : https://developers.google.com/sheets/api/reference/rest/)

The Javascript function below takes this URL as parameter: https://sheets.googleapis.com/v4/spreadsheets/{SHEET-ID}/values/A2:ZZ:append?valueInputOption=RAW&key={API-KEY}

The request body passed as parameter is this one:

{
 "majorDimension": "ROWS",
 "values": [
  [
   "15:41 02/08/2019",
   "Steven",
   "20",
   "Male",
   "test@mail.com",
   "FooBar"
  ]
 ]
}

The function that is called to do the POST:

function postReq(url, callback, request = null) {
    var xhr = new XMLHttpRequest();
    xhr.open("POST", url);
    xhr.onload = function (e) {
        if (e) console.log(e);
        if (xhr.readyState === 4) {
            if (xhr.status === 200) {
                console.log(xhr.responseText);
                callback(xhr.responseText.toString());
            } else {
                callback(null);
                console.log(xhr.status);
            }
        } else {
            console.log(xhr.status);
        };
    };
    xhr.send(request);
};

This always returns a 401 response code which persists even if I publish the sheet to the web and the API key shouldn't be the issue since it works fine when reading data (I made sure the sheet is editable too).

score 1 · Accepted Answer · answered Aug 02 '19 at 23:43

You want to append rows using the method of values.append of Sheets API.
You are using API key for this.
Sheets API has already been enabled at API console.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

Unfortunately, API key cannot be used for POST method. API key can be used for only GET method. So please use the access token retrieved by OAuth2 and Service account.
For your script, please send the request body as application/json of the content type.

Modified script:

As the script for using the access token, I modified your script as follows. Before you use this, please set your access token.

function postReq(url, callback, request = null) {
    const accessToken = "###"; // <--- Please set your access token here.

    // Sample request body?
    var request = {
     "majorDimension": "ROWS",
     "values": [
      [
       "15:41 02/08/2019",
       "Steven",
       "20",
       "Male",
       "test@mail.com",
       "FooBar"
      ]
     ]
    };

    var xhr = new XMLHttpRequest();
    xhr.open("POST", url);
    xhr.setRequestHeader('Authorization', 'Bearer ' + accessToken); // Added
    xhr.setRequestHeader('Content-Type', 'application/json'); // Added
    xhr.onload = function (e) {
        if (e) console.log(e);
        if (xhr.readyState === 4) {
            if (xhr.status === 200) {
                console.log(xhr.responseText);
                callback(xhr.responseText.toString());
            } else {
                callback(null);
                console.log(xhr.status);
            }
        } else {
            console.log(xhr.status);
        };
    };
    xhr.send(JSON.stringify(request)); // Modified
};

Note:

Above modified script, in my environment, I could confirm that it worked.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

You understood my question very well and thanks a lot for the time put into this response. Unfortunately I'm a bit lost when it comes to using OAuth2 as I've never used it before, I was hoping the API key would work since you its so much simpler. Either way I'm very thankful for your response, you were extremely helpful — Real, Aug 03 '19 at 00:37

score 0 · Answer 2 · answered Jan 06 '21 at 02:50

We have similar requirement

need to display the contents of a google sheet (rows & columns) on a html page (not using google web app), and sadly data not being displayed as a html page??

the code loads the google sheet files, but no content is displayed???

We have used code from following URL:
    https://www.w3schools.com/xml/xml_http.asp

note:
a/ have made public and publish the google sheet contents
a.1/ using google drive, obtain following link:

https://docs.google.com/spreadsheets/d/1HVmBfKjQiUyXOfy-q5iWVvDYOSKnMLPiDr18W2EtU9s/edit?usp=sharing

a.2/ publish google sheet to the web

https://docs.google.com/spreadsheets/d/e/2PACX-1vQ3ZHpAYDBhjSelXk-GFuFJACQzsqlufZ0d5UCLw8iJNJwdHglY7388fYHL4632wgXDIfgnrd238Htg/pubhtml

    <!DOCTYPE html>
<html>
<body>

<h2>Using the amstras XMLHttpRequest Object</h2>

<div id="demo">
<button type="button" onclick="loadXMLDoc()">Load Data</button>
</div>

<script>
function loadXMLDoc() {
  var xhttp = new XMLHttpRequest();
  var vUrl = "https://docs.google.com/spreadsheets/d/e/2PACX-1vQ3ZHpAYDBhjSelXk-GFuFJACQzsqlufZ0d5UCLw8iJNJwdHglY7388fYHL4632wgXDIfgnrd238Htg/pubhtml?gid=0&single=true&output=csv";
  xhttp.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
      document.getElementById("demo").innerHTML =
      this.responseText;
    }
  };
  xhttp.open("GET", vUrl , true);
  xhttp.send();
}
</script>

</body>
</html>

Send Request body to a google sheet using sheets API and XMLHttpRequest

2 Answers2

Modification points:

Modified script:

Note:

References:

Linked