0

I'm currently trying to write data to cell A1 of a Google Spreadsheet via Javascript and the Google Sheets API. Since writing access and the .update method needs OAuth2 identification, I followed the tutorial of this fine post.

Unfortunately, I keep receiving 401 errors, meaning I'm not authorized despite offering a refresh token and an access token.

I'm unsure where and how to process the access token since I only used read requests in the past which can be completed with an API key.

The Code in question:

function get_access_token_using_saved_refresh_token() {
  const refresh_token = "INSERT REFRESH TOKEN";
  const client_id = "INSERT CLIENT ID";
  const client_secret = "INSERT CLIENT KEY";
  const refresh_url = "https://www.googleapis.com/oauth2/v4/token";

  const post_body = `grant_type=refresh_token&client_id=${encodeURIComponent(client_id)}&client_secret=${encodeURIComponent(client_secret)}&refresh_token=${encodeURIComponent(refresh_token)}`;

  let refresh_request = {
      body: post_body,
      method: "POST",
      headers: new Headers({
          'Content-Type': 'application/x-www-form-urlencoded'
      })
  }

fetch(refresh_url, refresh_request).then( response => {
            return(response.json());
        }).then( response_json =>  {
            console.log(response_json);
            SheetRequest(response_json.access_token);
    });
}

function SheetRequest(access_token, callback, request){
var url = 'https://sheets.googleapis.com/v4/spreadsheets/{INSERT SHEETID}/values/A1&key={INSERT API KEY}';
var request = {
    "majorDimension": "ROWS",
    "values": [
     [
      "test",
     ]
    ]
  };
var xhr = new XMLHttpRequest();
  xhr.open("POST", url);
  xhr.setRequestHeader('Authorization', 'Bearer ' + access_token);
  xhr.setRequestHeader('Access-Control-Allow-Origin', '*');
  xhr.setRequestHeader('Content-Type', 'application/json');
  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));
}

get_access_token_using_saved_refresh_token();
Le Wolf
  • 13
  • 3
  • 1
    Can I ask you your question? 1. Your refresh token is the valid token, and the retrieved access token can be used for putting a value on Google Spreadsheet. Is my understanding correct? 2. Using the retrieved access token from the refresh token, you want to put a value of `test` to "A1" of the 1st tab in a Google Spreadsheet. Is my understanding correct? 3. It seems that in your script, both `fetch` and `XMLHttpRequest` are used. In your situation, both are required to be used? – Tanaike Jun 06 '22 at 23:51
  • Hi, thanks for the reply! To your questions: 1 Correct, the access token seems to authenticate everything I wish to do 2 That's correct, in my own Google Spreadsheet to be specific 3 I got the fetch function from the tutorial I followed, but couldn't reproduce the same function with a XMLHttpRequest yet. It's not required to have both, I'd even prefer to handle everything via XMLHttpRequest. I just didn't succeed in rewriting the function. – Le Wolf Jun 07 '22 at 11:00
  • Thank you for replying. From your reply, I proposed a modified script as an answer. Could you please confirm it? If that was not useful, I apologize. – Tanaike Jun 07 '22 at 12:29

1 Answers1

2

I thought that your script for retrieving the access token is correct. So, when your access token can be used for updating the Spreadsheet using Sheets API, how about the following modification?

In this modification, your function of SheetRequest is modified using fetch API of Javascript.

Modified script:

function SheetRequest(access_token) {
  var url = 'https://sheets.googleapis.com/v4/spreadsheets/{INSERT SHEETID}/values/A1?valueInputOption=USER_ENTERED';
  fetch(url, {
    method: "PUT",
    headers: {
      "Authorization": "Bearer " + access_token,
      "Content-Type": "application/json"
    },
    body: JSON.stringify({ "values": [["test"]] })
  })
    .then(res => res.json())
    .then(res => console.log(res))
    .catch(err => console.log(err));
}

  • In your script,

    • In order to update the cell "A1" of the 1st tab of Spreadsheet by a value of test, "Method: spreadsheets.values.update" is required to be used.
    • The endpoint is not correct.
    • The request is required to be PUT method.

  • When this script is run, the value of "test" is put to the cell "A1" of the 1at tab of Spreadsheet.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    @Le Wolf Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. – Tanaike Jun 07 '22 at 22:54