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();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.
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,
test, "Method: spreadsheets.values.update" is required to be used.When this script is run, the value of "test" is put to the cell "A1" of the 1at tab of Spreadsheet.