One of the things I liked particularly about SharePoint was when you uploaded a document to a document library, you could add any number of custom fields for metadata, making displaying specific sets of documents easy on a site.
With Google Drive, although the search has its uses, I wanted to recreate if possible a way of adding metadata to documents on upload, and be able to display just certain documents on a site using this. The method I have used is a script deployed as a web app, which gives a user interface to upload the files to a folder, with a Google sheet to periodically list the contents of that folder, for displaying in an Awesome Table.
The first bit is to create a picker, to select files and add metadata. The end result will look like this
The first bit goes in Code.gs - I have included some sample metadata categories, but it is easy to change these as required.
/* The script is deployed as a web app and renders the form */
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('DrivePickerHTML.html')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
/* This function will process the submitted form */
function uploadFiles(form) {
try {
var folder = DriveApp.getFolderById("abcdefghijklmnop12345");
/* Get the file uploaded though the form as a blob */
var blob1 = form.myFile;
var file1 = folder.createFile(blob1);
/* Set the file description as the name of the uploader */
file1.setDescription(form.Division + "," + form.DocType);
var file1url = file1.getUrl();
/* Return the download URL of the file once its on Google Drive */
return "File Uploaded - <a href='" + file1url +"' target='_blank'>click here to see on Drive</a>"
} catch (error) {
/* If there's an error, show the error message */
return error.toString();
}
}
The next bit goes in a HTML file which I have named "DrivePickerHTML.html
Next in a Google sheet, I added a script to list all the files in a folder. This creates the necessary headers and filters for the Awesome Table. The script then lists all the files and details, including the contents of the description field, which now contains the metadata. A trigger can be set to refresh at whatever frequency is required. As part of this script, it also checks to see if a file has been added within the last few days, and if so, gives it a "!New" icon.
var ss = SpreadsheetApp.getActiveSpreadsheet();
function GetFolders(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
sheet.clear();
sheet.appendRow(["Icon", "Name", "Date Modified", "Size", "Category", "File / Folder"]);
sheet.appendRow(["NoFilter", "StringFilter", "DateFilter", "NoFilter", "StringFilter", "CategoryFilter"]);
var mainfolder = DriveApp.getFolderById("abcdefghijklmonop12345"); //change this bit to your folder ID
var contents = mainfolder.getFolders();
var file, data, rows = [];
for (var i = 0; i < contents.length; i++) {
file = contents[i];
data = [
"<a href=" +'"'+ file.getUrl() +'"'+ " target='_blank'><img src='https://ssl.gstatic.com/docs/doclist/images/icon_8_collection_list.png'</img></a>",
"<a href=" +'"'+ file.getUrl() +'"'+ " target='_blank'>" + file.getName() + "</a> ",
file.getLastUpdated(),
"",
file.getDescription(),
"Folder"
];
rows.push(data);
sheet.appendRow(data);
}
listFilesInFolder()
}
function listFilesInFolder() {
var folder = DriveApp.getFolderById("abcdefghijklmnop12345"); //change this bit to your folder ID
var contents = folder.getFiles();
var sheet = ss.getSheetByName("Sheet1");
var file, data, rows = [];
while (contents.hasNext()) {
var file = contents.next();
var filetype = "application/icon_10_generic_list";
var mime = file.getMimeType();
if (file.getBlob().getContentType() == "application/pdf") {
filetype = "application/icon_10_pdf_list";
}
if (file.getMimeType() == "application/vnd.google-apps.spreadsheet") {
var filetype = "application/icon_11_spreadsheet_list";
}
if (file.getMimeType() == "application/vnd.google-apps.presentation") {
filetype = "application/icon_11_presentation_list";
}
if (file.getMimeType() == "application/vnd.google-apps.document") {
filetype = "application/icon_11_document_list";
}
if (file.getBlob().getContentType() == "application/vnd.openxmlformats-officedocument.presentationml.presentation") {
filetype = "application/icon_10_powerpoint_list";
}
if (file.getBlob().getContentType() == "application/vnd.openxmlformats-officedocument.wordprocessingml.document") {
filetype = "application/icon_10_word_list";
}
if (file.getBlob().getContentType() == "application/msword") {
filetype = "application/icon_10_word_list";
}
if (file.getBlob().getContentType() == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {
filetype = "application/icon_10_excel_list";
}
if (file.getBlob().getContentType() == "application/vnd.ms-excel") {
filetype = "application/icon_10_excel_list";
}
if (file.getBlob().getContentType() == "application/vnd.ms-powerpoint") {
filetype = "application/icon_10_powerpoint_list";
}
if (file.getBlob().getContentType() == "application/vnd.ms-powerpoint") {
filetype = "application/icon_10_powerpoint_list";
}
if (file.getBlob().getContentType() == "image/png") {
filetype = "application/icon_11_image_list";
}
if (file.getBlob().getContentType() == "image/jpeg") {
filetype = "application/icon_11_image_list";
}
if (file.getBlob().getContentType() == "image/jpg") {
filetype = "application/icon_11_image_list";
}
if (file.getBlob().getContentType() == "image/gif") {
filetype = "application/icon_11_image_list";
}
if (file.getBlob().getContentType() == "video/x-msvideo") {
filetype = "application/icon_11_video_list";
}
if (file.getBlob().getContentType() == "video/webm") {
filetype = "application/icon_11_video_list";
}
if (file.getBlob().getContentType() == "text/css") {
filetype = "application/icon_10_generic_list";
}
if (file.getBlob().getContentType() == "application/x-javascript") {
filetype = "application/icon_10_generic_list";
}
if (file.getBlob().getContentType() == "application/xml") {
filetype = "application/icon_10_generic_list";
}
Logger.log(mime + ":" + file + ":" + filetype);
var nowdate = new Date();
var nowtime = nowdate.getTime();
var createddate = file.getLastUpdated();
var createdtime = createddate.getTime();
var timediff = nowtime-createdtime
var differencecompare = timediff/1000/60/60/24
var newicon ="";
if(differencecompare < 3) {
var newicon = "<img src='https://81ccb0dfc0272fd19e7ad1429a55d800d850f8c4.googledrive.com/host/0BysVSZMkrof5aTd2anN6UWx4ODg/New.jpg'</img>";
}
data = [
"<a href=" +'"'+ file.getUrl() +'"'+ " target='_blank'><img src=" +'"'+ "https://ssl.gstatic.com/docs/doclist/images/" + filetype.substring( filetype.indexOf('/') + 1 ) + ".png" +'"'+"</img></a>",
"<a href=" +'"'+ file.getUrl() +'"'+ " target='_blank'>" + file.getName() + "</a> "+newicon,
file.getLastUpdated(),
file.getSize(),
file.getDescription(),
"File"
];
rows.push(data);
sheet.appendRow(data);
}
}
Finally, the Awesome Table can just be created to read from this sheet, allowing documents to either by manually filtered or searched for by metadata, or hardcoded in the Awesome Table to just show documents containing specific metadata
Hope this is of interest, any questions or comments welcomed
