I've scoured this website for the better part of the week for any information in regards to this issue, but most resources are either a few years old and the documentation hasn't been helpful to me.
I'm creating a script inside Google Sheets that allows the user to retrieve their files and folders from the Google Picker. When a folder is selected, the script will copy the folders and files into a new folder.
I've followed the documentation from https://developers.google.com/picker/docs and https://developers.google.com/apps-script/guides/dialogs. I've created a Google Cloud Profile, enabled the necessary APIs (Google Picker API & Google Drive API), called the appropriate scopes (/auth/drive, /auth/spreadsheets.currentonly). Everything works at an Outer Script level (from the Google Sheets) and works for multiple users. Fantastic.
The goal however is to implement the script into a library as a means to protect the code from unnecessary modifications, for easy updates, and to be easily referenced if users create a copy of the Google Sheet. This is where things start to break.
When running the script as a library and through my development account (the same one that is linked to the GCP), everything works well, as I can authenticate both the Outer Script AND the library script (because I have access to both). The end-user will NOT have access to the library script and won't be able to run a function in order to authenticate.
When using a separate account for testing purposes, completely disconnected from the development side, I receive a ScriptError: Authorization is required to perform that action. error. This is likely due to the fact that I haven't been able to authenticate the library.
Outer script:
function onOpen() {
lib_.onOpen();
}
function doGet(e) {
lib_.doGet(e);
}
function getOAuthToken() {
lib_.getOAuthToken();
}
Code.gs:
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Menu
function onOpen(e) {
SpreadsheetApp.getUi().createAddonMenu()
.addItem("Select Folders", "doGet")
.addToUi();
}
// Display Google Picker
function doGet(e) {
// Display Google Picker.
var html = HtmlService.createHtmlOutputFromFile('picker')
.setWidth(1051)
.setHeight(650)
.setSandboxMode(HtmlService.SandboxMode.IFRAME.ALLOWALL);
SpreadsheetApp.getUi().showModalDialog(html, 'Select a folder');
}
// Get the user's OAuth 2.0 Token //
function getOAuthToken() {
DriveApp.getRootFolder();
return ScriptApp.getOAuthToken();
}
picker.html:
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<script type="text/javascript">
var DEVELOPER_KEY = '{DEVELOPER_KEY}'; // Developer Key from Google Cloud Project.
var DIALOG_DIMENSIONS = {width: 1051, height: 650}; // The max dimensions for the dialog box is 1051,650.
var pickerApiLoaded = false;
// Use the Google API Loader script to load the google.picker script.
function onApiLoad() {
gapi.load('picker', {'callback': function() {
pickerApiLoaded = true;
}});
}
/**
* Gets the user's OAuth 2.0 access token from the server-side script so that
* it can be passed to Picker. This technique keeps Picker from needing to
* show its own authorization dialog, but is only possible if the OAuth scope
* that Picker needs is available in Apps Script. Otherwise, your Picker code
* will need to declare its own OAuth scopes.
*/
function getOAuthToken() {
google.script.run.withSuccessHandler(createPicker)
.withFailureHandler(showError).getOAuthToken();
}
/**
* Creates a Picker that can access the user's folders.
*
* @param {string} token An OAuth 2.0 access token that lets Picker access the
* file type specified in the addView call.
*/
function createPicker(token) {
console.log(pickerApiLoaded,token)
if (pickerApiLoaded && token) {
var docsView = new google.picker.DocsView()
.setIncludeFolders(true) // As we're copying folders, we want to include folders in the picker.
.setParent('root') // By setting the parent, we can see the directory properly. If not set, then we see ALL folders in one screen.
.setSelectFolderEnabled(true); // Enables the user to select folders.
//.setMimeTypes('application/vnd.google-apps.folder') // If we want, we can make it that ONLY folders are viewable.
var picker = new google.picker.PickerBuilder()
.addView(docsView) // Adds the settings set above.
.enableFeature(google.picker.Feature.MULTISELECT_ENABLED) // Allows the user to select multiple folders at once. Disable this to only allow one folder at a time.
.enableFeature(google.picker.Feature.NAV_HIDDEN) // Removes the header. Disable to allow view of the header.
//.enableFeature(google.picker.Feature.MINE_ONLY) // Only allows the person to view their own folders. Disable to allow shared folders (not shared drives).
.setSelectableMimeTypes('application/vnd.google-apps.folder') // Only allows the selection of folders. Disable to allow all files and folders.
.hideTitleBar() // Hides title bar as it only takes up space.
.setOAuthToken(token)
.setDeveloperKey(DEVELOPER_KEY)
.setCallback(pickerCallback)
.setOrigin(google.script.host.origin)
.setSize(DIALOG_DIMENSIONS.width - 2,
DIALOG_DIMENSIONS.height - 2) // Reducing width & height to see the border.
.build();
picker.setVisible(true); // Make picker visible.
} else {
showError('Unable to load the file picker.');
}
}
/**
* Perform pickerCallback function... and rest of code.
*/
/**
* Displays an error message within the #result element.
*
* @param {string} message The error message to display.
*/
function showError(message) {
document.getElementById('result').innerHTML = 'Error: ' + message;
}
</script>
</head>
<!-- Display the Picker as soon as the dialog loads. -->
<body onload="getOAuthToken()">
<div>
<p id='result'></p>
</div>
<script type="text/javascript" src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
</body>
</html>
A couple of notes:
- I've logged in console whether the Picker API is able to load. It returns as True. However, the OAuthToken returns as
undefined. - I'm running the script from a single account at a time and testing with a separate account with incognito mode. I understand that being logged in with multiple accounts in a single browser will often trigger this error.
- EDIT The standalone script is deployed as a web app (but not verified by Google). I'm not too familiar on how to deploy web apps, and could use some guidance on this front as well.
One solution I've tried was to implement the oAuth2 Library which can be found here: https://github.com/googleworkspace/apps-script-oauth2. However, this also does not work as it requires the scriptID from the Outer Script for the URI Redirect in GCP (which won't work if users create a copy of the outer script). Using the library scriptID will return as a 400 Mismatch error. In theory, however, this library returns an oAuth key, but suspect it's the one for the Outer Script (not the library script required to make the Picker work).
My theory (and correct me if I'm wrong), is that the getOAuthToken() function in code.gs isn't able to pull the oAuthToken as it's a standalone script, and not in a Google Sheets. There's theoretically "nothing to get", which is why it returns as undefined. I'm just a little stumped on how to proceed from here.
Is there a way to "force" authentication of a library to the user without asking them to open the library script, run an empty function, and authorize it, in order for the user to be able to see the Google Picker? Or is there an easy way to get the oAuthToken from the Outer Script and push it to the library to be used to authenticate the Picker?
I hope this is clear enough. Let me know if you need additional info.
