We are using google sheets for expense claims. At the end of every month each employee fills out their expense claim and attaches their receipts on google drive through the sheet and then submits the data via email. This has been already implemented and works well.
However we currently have a copy of the script attached to each sheet that we share with the employees so if there is an update to the code we have to do the update on every single document which is time consuming and error prone.
I would like to use one standalone script that is accessed from all of the google sheets shared with individuals.
I am having trouble with getting the drive picker working in the Standalone google script.
here is my picker.html file
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<script>
// IMPORTANT: Replace the value for DEVELOPER_KEY with the API key obtained
// from the Google Developers Console.
var DEVELOPER_KEY = 'purposely removed';
var DIALOG_DIMENSIONS = {width: 600, height: 425};
var pickerApiLoaded = false;
/**
* Loads the Google Picker API.
*/
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 spreadsheets. This function
* uses advanced options to hide the Picker's left navigation panel and
* default title bar.
*
* @param {string} token An OAuth 2.0 access token that lets Picker access the
* file type specified in the addView call.
*/
function createPicker(token) {
if (pickerApiLoaded && token) {
var picker = new google.picker.PickerBuilder()
// Instruct Picker to display only spreadsheets in Drive. For other
// views, see https://developers.google.com/picker/docs/#otherviews
.addView(google.picker.ViewId.SPREADSHEETS)
// Hide the navigation panel so that Picker fills more of the dialog.
.enableFeature(google.picker.Feature.NAV_HIDDEN)
// Hide the title bar since an Apps Script dialog already has a title.
.hideTitleBar()
.setOAuthToken(token)
.setDeveloperKey(DEVELOPER_KEY)
.setCallback(pickerCallback)
.setOrigin(google.script.host.origin)
// Instruct Picker to fill the dialog, minus 2 pixels for the border.
.setSize(DIALOG_DIMENSIONS.width - 2,
DIALOG_DIMENSIONS.height - 2)
.build();
picker.setVisible(true);
} else {
showError('Unable to load the file picker.');
}
}
/**
* A callback function that extracts the chosen document's metadata from the
* response object. For details on the response object, see
* https://developers.google.com/picker/docs/result
*
* @param {object} data The response object.
*/
function pickerCallback(data) {
var action = data[google.picker.Response.ACTION];
if (action == google.picker.Action.PICKED) {
var doc = data[google.picker.Response.DOCUMENTS][0];
var id = doc[google.picker.Document.ID];
var url = doc[google.picker.Document.URL];
var title = doc[google.picker.Document.NAME];
document.getElementById('result').innerHTML =
'<b>You chose:</b><br>Name: <a href="' + url + '">' + title +
'</a><br>ID: ' + id;
} else if (action == google.picker.Action.CANCEL) {
document.getElementById('result').innerHTML = 'Picker canceled.';
}
}
/**
* 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>
<body>
<div>
<button onclick='getOAuthToken()'>Select a file</button>
<p id='result'></p>
</div>
<script src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
</body>
</html>
I then get this: Google Drive Picker
but when I click the Select a file button i get this:
Uncaught TypeError: google.script.run.withSuccessHandler(...).withFailureHandler(...).getOAuthToken is not a function
at getOAuthToken (userCodeAppPanel:20)
at HTMLButtonElement.onclick (userCodeAppPanel:1)
my Code.gs contains this function along with functions to process the selected files.
function getOAuthToken() {
DriveApp.getRootFolder();
return ScriptApp.getOAuthToken();
}
Let me know if you need further information.
I already tried this solution and it didn't work: Cannot call Google Script API Functions from Web App (TypeError: Cannot read property 'run' of undefined)
Any help is much appreciated.
Thank you
How to reproduce the problem:
go to: https://script.google.com/ Create a new script. In the Code.gs file put this:
function saonopen() { var ui = SpreadsheetApp.getUi(); // add some functions to the UI ui.createMenu('Menu') .addItem('Add Attachments', 'tsc.showPicker') .addToUi(); } function showPicker() { var html = HtmlService.createHtmlOutputFromFile('picker.html') .setWidth(600) .setHeight(425) .setSandboxMode(HtmlService.SandboxMode.IFRAME); SpreadsheetApp.getUi().showModalDialog(html, 'Select a file'); } function processFilesChosen(ids, titles, urls) { var targetSheetCost = switchTargetMonth('sheet').cost; // record the files on the target cost sheet for (var i in titles) { i = parseInt(i) targetSheetCost.getRange("H"+(i+3)).setFormula('=HYPERLINK("'+urls[i]+'","'+titles[i]+'")'); targetSheetCost.getRange("I"+(i+3)).setValue(ids[i]); // Because of Google security update, we can not use this setSharing. // DriveApp.getFileById(ids[i]).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.COMMENT); } } function getOAuthToken() { DriveApp.getRootFolder(); return ScriptApp.getOAuthToken(); }Add a new html file, call it picker.html and put the code posted earlier under picker.html.
Publish the script as a API Executable and note the API ID
create a new google sheet and import the standalone script as a library from resources menu on the script view using the API ID noted earlier. It will as for an identifier - put:
tsc
- In the Code.gs file put:
function onOpen() {
tsc.saonopen()
}
- Finally enable Picker and Drive API Access for both projects.
