I am using the Google Sheets API for client-side javascript. I cannot read a public spreadsheet when the user is signed/logged in, but can read it when the user is signed/logged out.
I adapted the quickstart guide: https://developers.google.com/sheets/api/quickstart/js
However, I purposefully do not want my app to ask for read-only permission on all the user's spreadsheets.
Since the spreadsheet is public, I can also listMajors() (which reads the spreadsheet) when the user is not signed in by adding a line in updateSigninStatus. And this works.
However, with a different set of permissions than
var SCOPES = "https://www.googleapis.com/auth/spreadsheets.readonly"; , I am not able to read the spreadsheet when the user is signed in.
I would expect it would work, because anything that can be done when signed out should also work when signed in.
I use these permissions:
var SCOPES = "https://www.googleapis.com/auth/drive.appdata https://www.googleapis.com/auth/drive.file";
These permissions enable me to write to the AppFolder and read files created by my app.
Why is my expectation wrong? Is it a bug or work as intended? Is there a scope that doesn't require more permissions that I can use to read the spreadsheet when signed in? Alternatively, can I make a request pretending I am not signed in when signed in (I don't want to sign out the user)?
Here is the full example:
<!DOCTYPE html>
<html>
<head>
<title>Google Sheets API Quickstart</title>
<meta charset="utf-8" />
</head>
<body>
<p>Google Sheets API Quickstart</p>
<!--Add buttons to initiate auth sequence and sign out-->
<button id="authorize_button" style="display: none;">Authorize</button>
<button id="signout_button" style="display: none;">Sign Out</button>
<pre id="content" style="white-space: pre-wrap;"></pre>
<script type="text/javascript">
// Client ID and API key from the Developer Console
var CLIENT_ID = '...';
var API_KEY = '...';
// Array of API discovery doc URLs for APIs used
var DISCOVERY_DOCS = ["https://www.googleapis.com/discovery/v1/apis/drive/v3/rest", "https://sheets.googleapis.com/$discovery/rest?version=v4"];
// in quickstart:
//var DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];
// Authorization scopes required by the API; multiple scopes can be
// included, separated by spaces.
var SCOPES = "https://www.googleapis.com/auth/drive.appdata https://www.googleapis.com/auth/drive.file";
// in quickstart:
//var SCOPES = "https://www.googleapis.com/auth/spreadsheets.readonly";
var authorizeButton = document.getElementById('authorize_button');
var signoutButton = document.getElementById('signout_button');
/**
* On load, called to load the auth2 library and API client library.
*/
function handleClientLoad() {
gapi.load('client:auth2', initClient);
}
/**
* Initializes the API client library and sets up sign-in state
* listeners.
*/
function initClient() {
gapi.client.init({
apiKey: API_KEY,
clientId: CLIENT_ID,
discoveryDocs: DISCOVERY_DOCS,
scope: SCOPES
}).then(function () {
// Listen for sign-in state changes.
gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);
// Handle the initial sign-in state.
updateSigninStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
authorizeButton.onclick = handleAuthClick;
signoutButton.onclick = handleSignoutClick;
}, function(error) {
console.log(error);
appendPre(JSON.stringify(error, null, 2));
});
}
/**
* Called when the signed in status changes, to update the UI
* appropriately. After a sign-in, the API is called.
*/
function updateSigninStatus(isSignedIn) {
if (isSignedIn) {
authorizeButton.style.display = 'none';
signoutButton.style.display = 'block';
} else {
authorizeButton.style.display = 'block';
signoutButton.style.display = 'none';
}
// get the spreadsheet regardless of login status
listMajors();
}
/**
* Sign in the user upon button click.
*/
function handleAuthClick(event) {
gapi.auth2.getAuthInstance().signIn();
}
/**
* Sign out the user upon button click.
*/
function handleSignoutClick(event) {
gapi.auth2.getAuthInstance().signOut();
}
/**
* Append a pre element to the body containing the given message
* as its text node. Used to display the results of the API call.
*
* @param {string} message Text to be placed in pre element.
*/
function appendPre(message) {
var pre = document.getElementById('content');
var textContent = document.createTextNode(message + '\n');
pre.appendChild(textContent);
}
/**
* Print the names and majors of students in a sample spreadsheet:
* https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
*/
function listMajors() {
gapi.client.sheets.spreadsheets.values.get({
spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
range: 'Class Data!A2:E',
}).then(function(response) {
var range = response.result;
if (range.values.length > 0) {
appendPre('Name, Major:');
for (i = 0; i < range.values.length; i++) {
var row = range.values[i];
// Print columns A and E, which correspond to indices 0 and 4.
appendPre(row[0] + ', ' + row[4]);
}
} else {
appendPre('No data found.');
}
}, function(response) {
console.log(response);
appendPre('Error: ' + response.result.error.message);
});
}
</script>
<script async defer src="https://apis.google.com/js/api.js"
onload="this.onload=function(){};handleClientLoad()"
onreadystatechange="if (this.readyState === 'complete') this.onload()">
</script>
</body>
</html>