Screenshot of Google Picker in Drive |
The reason for making use of the Google Picker is typically a user of a script (not usually a Coder themselves) needs to occasionally enter the ID of a folder/file in order for a script to perform its tasks. It can be cumbersome and error-prone to request they extract this from the Url in Google Drive and then enter into a specific cell in a Google Sheet for instance.
With the Google Picker we can create a simple button that they click instead which lets them browse for the necessary item (a folder in this example) and select it. A small piece of Apps Script code can then get the necessary ID/Url we require and automatically put them in the correct area of the spreadsheet.
Download
Google Drive Picker for Folder ID download here (please use 'File' > 'Make a copy' for your own version).
function onOpen() { | |
SpreadsheetApp.getUi().createMenu('Google Picker') | |
.addItem('Insert Drive Folder ...', 'showPicker') | |
.addToUi(); | |
} | |
/** | |
* Displays an HTML-service dialog in Google Sheets that contains client-side | |
* JavaScript code for the Google Picker API. | |
*/ | |
function showPicker() { | |
var html = HtmlService.createHtmlOutputFromFile('Picker.html') | |
.setWidth(600) | |
.setHeight(425) | |
.setSandboxMode(HtmlService.SandboxMode.IFRAME); | |
SpreadsheetApp.getUi().showModalDialog(html, 'Select Folder'); | |
} | |
function getOAuthToken() { | |
DriveApp.getRootFolder(); | |
return ScriptApp.getOAuthToken(); | |
} | |
/* | |
Function that takes item Id from Picker.html once user has made selection. | |
Creates clickable Url in spreadsheet. | |
Pastes in item Id to spreadsheet. | |
*/ | |
function insertFolderURL(id){ | |
// get Google Drive folder by Id from Picker | |
var folder = DriveApp.getFolderById(id); | |
// get Googel Drive folder name | |
var folderName = folder.getName(); | |
// get Google Drive folder Url | |
var folderUrl = folder.getUrl(); | |
// get current spreadsheet | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
// get relevant cells for pasting in values | |
var urlCell = sheet.getRange(4, 3); | |
var idCell = sheet.getRange(6, 3); | |
// create and set Url link to Google Drive folder | |
var formula = '=HYPERLINK("' + folderUrl + '", "' + folderName + '")'; | |
urlCell.setFormula(formula); | |
// set folder Id into spreadsheet cell | |
idCell.setValue(id); | |
} |
<!DOCTYPE html> | |
<html> | |
<head> | |
<!-- Add the standard Google Style Sheet. --> | |
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> | |
<script type="text/javascript"> | |
var DIALOG_DIMENSIONS = { | |
width: 600, | |
height: 425 | |
}; | |
var pickerApiLoaded = false; | |
function onApiLoad() { | |
gapi.load('picker', { | |
'callback': function() { | |
pickerApiLoaded = true; | |
} | |
}); | |
google.script.run.withSuccessHandler(createPicker).withFailureHandler(showError).getOAuthToken(); | |
} | |
function createPicker(token) { | |
if (pickerApiLoaded && token) { | |
var docsView = new google.picker.DocsView() | |
.setIncludeFolders(true) | |
.setMimeTypes('application/vnd.google-apps.folder') | |
.setSelectFolderEnabled(true); | |
var picker = new google.picker.PickerBuilder() | |
.addView(docsView) | |
.enableFeature(google.picker.Feature.NAV_HIDDEN) | |
.hideTitleBar() | |
.setSize(DIALOG_DIMENSIONS.width - 2, DIALOG_DIMENSIONS.height - 2) | |
.setOAuthToken(token) | |
.setCallback(pickerCallback) | |
.setOrigin('https://docs.google.com') | |
.build(); | |
picker.setVisible(true); | |
} else { | |
showError('Unable to load the file picker.'); | |
} | |
} | |
/* | |
Function used as SuccessHandler for 'pickerCallback' so will only close dialogue | |
box if that codes runs correctly. | |
*/ | |
function folderInserted(){ | |
google.script.host.close(); | |
} | |
/** | |
* 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]; | |
/* | |
Runs Apps Script Function 'insertFolderURL' and passes in item Id from Google Picker. | |
A failure to run will trigger below 'showError' Function. | |
A success run will trigger above 'folderInserted' Function and close dialogue box. | |
*/ | |
google.script.run.withSuccessHandler(folderInserted).withFailureHandler(showError).insertFolderURL(id); | |
} | |
else if (action == google.picker.Action.CANCEL) { | |
google.script.host.close(); | |
} | |
} | |
function showError(message) { | |
document.getElementById('result').innerHTML = 'Error: ' + message; | |
} | |
</script> | |
</head> | |
<body> | |
<div> | |
<p id='result'></p> | |
</div> | |
<script type="text/javascript" src="https://apis.google.com/js/api.js?onload=onApiLoad"></script> | |
</body> | |
</html> |
thanks!
ReplyDeletegreat! thanks!
ReplyDeleteThank you for sharing this wonderful picker with us!
ReplyDeleteYou're welcome.
Delete