Tuesday 23 June 2015

Using Metadata in Drive descriptions and display these using an Awesome Table

Update 19/10/2015 - Due to Google's update to their security sandbox - the code below has been updated to work with the new IFRAME method.  


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

When a user uploads a file, they then get a confirmation of upload and a link to the file as per the below. The metadata goes in the description field of the file in Google Drive

For this I created a script and deployed as a web app

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





  
Division / Specialty Document Type Choose a File Upload
 
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

7 comments:

  1. This is very much in need and thank you for sharing. Will try to implement it and share a link and feedback here : )

    ReplyDelete
    Replies
    1. Thanks for the feedback :-) Just as a note, for my production systems I expanded the picker so as to be able to upload more files without having to refresh the page. A screenshot is here of that
      https://342e852e291c4ec2d6331fba2de5db72377c1ee3.googledrive.com/host/0BysVSZMkrof5OWlibzRnVkVEMTg/biggerpicker.PNG
      I did not include the full code for this in the blog for reasons of space and clarity, but it is just a matter of creating some duplicates in the Code.gs and the HTML for each additional picker. If you want the full code give me a shout on Google+ and Ill be glad to share (its a bit messy, but it works OK) ;-)

      Delete
  2. Hey James! This looks like such an awesome tool! You have no idea how much this will benefit my day-to-day statement of work. As usual though, I'm tripping over this stuff trying to run the script. Here is a link of the error I keep getting: http://goo.gl/3FZo4r . And here is a link to my script: https://script.google.com/d/1plm95PpP_aRySNpcOgZIsIqJ--bdGr51QyTL8wWICEqqqRdNuX72xhM4/edit?usp=sharing . Any suggestions?

    ReplyDelete
    Replies
    1. Hi Edward, glad this is potentially of use. My code did not come out correctly on Blogger - Apologies! I have amended the blog post now - please let me know if you have any further issues.

      Delete
    2. James you are awesome! You have a great skillset and I am a HUGE fan/user of awesome tables. I'm currently in the process of trying to find a developer to do some small freelance work for a couple of tools I'm needing built. They'd be a walk in the park for someone like you! Please let me know if this is something you might be interested in and if there is a better communication channel to have this conversation. Thanks!

      Delete
    3. Hi Edward, thank you. Sounds interesting. I have sent you a hangout chat request.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete