tech cogitation: 2015

Tuesday 24 November 2015

Adding comments to an Awesome Table via a toggle sidebar

****Update August 2017 - Due to recent changes by Awesome Table to their background code, this solution no longer will work. Ill try to revisit this in future to see if a revision to the code below can get this working again, time permitting*****

This solution came about from a requirement we had for colleagues to easily add updates and comments to a monthly report which was itself refreshed each month with new data. As the data changed in the report it was necessary to find a way to get the existing comments to be kept and to show against the appropriate matching lines in the new data set. To do this, I used an Awesome Table, with a sidebar, linked to a form which could be toggled to show and hide. The form has a few fields which are pre-filled depending on the line of data, so that the comments then get matched up and shown in the table.

A demo of the solution is below, using some dummy data (a copy of the spreadsheet can be found at the bottom of the post)

To start I created a Google Sheet with the data in and a Google form to collect the comments

To get a unique value per row that can match against the appropriate comment, I concatenated the first three columns of my data (Trust, site and study ID). The formula puts the title of the column in the first cell, 'Nofilter' in the second cell (used to define the Awesome Table filter) and in the remaining rows with data, the concatenation:

=ArrayFormula(IF(ROW(A:A)=1,"Concatenate",IF(ROW(A:A)=2,"NoFilter",IF(LEN(D1:D),(D1:D&"|"&C1:C&"|"&B1:B),IFERROR(1/0)))))

with a similar formula on the form result sheet, any matching results can be brought in with a query, but with a join function, to combine multiple matching entries, sorting the matching comments by most recent.

=join("",query(Comments!A$2:H,"select G Where A = """&A3&""" order by B desc"))

(NB - one annoyance is that the query cannot be combined with an array formula, so this formula is filled down in a standard way. If the data is submitted via a form as well, then an Importrange or a query function can always be used to bring in that data to another sheet which would then be used for the remaining calcs.)

The returned column combines the date stamp, formatted to show just the date, with the comment, and some HTML line breaks so that this displays well in the Awesome Table, which was obtained by the following formula.

=ArrayFormula(TEXT(B2:B," dd/mm/yy - ")&F2:F&"<br/><br/>")

Awesome Tables now use Templates which is a fantastic new feature. In the template I added
what would appear in the sidebar, which is an iframe of the form, prefilling three of the fields using the data in the rows.

<iframe Height="610px" src="https://docs.google.com/a/nihr.ac.uk/forms/d/abcdefghijklmnop123456789/viewform?entry.1027802729=${"Study ID"}&entry.1197506384=${"Site Name"}&entry.1805865420=${"Trust Name"}"></iframe>

I then added some JavaScript to the template so that when a button is clicked, the sidebar is shown or hidden, with the table width expanded or reduced accordingly.

function changeClass()    {  
document.getElementById("sidebar").className = "sideactive";
document.getElementById("parentChart1").className = "mainactive"; 
document.getElementById("parentChart1").setAttribute('style', 'width: 74%!important;height 1200px!important'); 
 }   
function changeClassBack(){
document.getElementById("sidebar").className = "sideinactive";
document.getElementById("parentChart1").className = "maininactive"; 
document.getElementById("parentChart1").setAttribute('style', 'width: 100%!important'); 
}

The spreadsheet used for this demo can be found here
https://docs.google.com/spreadsheets/d/1qFr7dJ9ibdz4p2T6iJeD1u2pVmsrFMmEu2MCB1Ho7pQ
Please feel free to take a copy.

Friday 10 July 2015

Folders as a workaround for Google form attachments

Update March 2017 - A file upload feature has now been added to Google forms for GSuite customers for forms within their domain. To get an Awesome Table to work with this new feature, see my blogpost here

Update December 2015 - for enhancements such as automatically sharing folders with recipients and a link directly to the folder view rather than the 'open in drive' view, please see responses to comments below this post

Google forms are really useful, but a limitation is that it is not possible to attach a document to a record (as you can do with a SharePoint list for example)
This script aims to give a workaround for that - when a user submits a form, the form entry is assigned a unique reference, a Drive folder is created with that reference, and they receive an email containing a link to that folder to upload related documentation. Using an Awesome table, the link to the folder as well as the details in the form can be displayed together to make it easy to match documents to form items.

To being with, I created a Google form, and a Google sheet to collect the information.
To create a unique reference I used the following formula in the first row of the first empty column (where a username is in column B) The formula puts the title "Reference" in the first row, in the second row it puts "NoFilter" (for use in the Awesome Table) and for any subsequent lines, it takes the first letter of the users first name, two letters from the surname, and the row number to make the unique reference

=ArrayFormula(IF(ROW(A:A)=1,"Reference",IF(ROW(A:A)=2,"NoFilter",((LEFT(B1:B)&MID(B1:B,FIND("#",SUBSTITUTE(B1:B&" "," ","#",1))+1,2)&MID(B1:B,FIND("#",SUBSTITUTE(B1:B&" "," ","#",2))+1,1)&ROW( A:A ))))))

Scripts are here:

var formURL = 'https://docs.google.com/a/nihr.ac.uk/forms/d/0123456789abcdsefghijklmnop/edit'; // enter the edit url for the form
var sheetName = 'Form responses 1'; // enter the name of the sheet
var columnIndex = 8;  //the number here is a count with A = 1, it indicates where the link should go

// script to get the edit link

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 2; i < data.length; i++) {
    if(data[i][0] != '' && (data[i][columnIndex-1] == '' || !data[i][columnIndex-1])) {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex).setValue(editResponseUrl); 
    }
  }
}

// script to create a folder for document uploads

function createFolder () {

var FOLDER_CREATED = "FOLDER_CREATED";

  // Get current spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form responses 1");
  var data = ss.getDataRange()  // Get all non-blank cells
               .getValues()     // Get array of values
               .splice(2);      // Remove header lines

  // Define column number unique reference. Array starts at 0.
  var CLASS = 6;  //column G

// Get the ID of the root folder, where the sub folders will be created
  var DriveFolder = DriveApp.getFolderById("0123456789101112131415161718abcdefghijklmnop");

  // For each email address (row in a spreadsheet), create a folder,
  // name it with the data from the Class
  
  for (var i=0; i<data.length; i++){

    var startRow = 3;  // First row of data to process 
    var row = data[i];
    var class = data[i][CLASS];
    var folderName = class;
    var supplier = row[3];  //column D
    var folderstatus = row[9];  // column J
   
   if (folderstatus != FOLDER_CREATED) {
   
  //create the folder
  var folderpath = DriveFolder.createFolder(folderName).getUrl();

//send an email

  var emailAddress = row[2];  // Column C)
  var message = 'Thank you for submitting a purchase order requisition for supplier: '+ supplier +' - please upload the purchase order(s) relating to this to the following folder ' +folderpath ;   

      var subject = "Please upload related documentation";    
     MailApp.sendEmail(emailAddress, subject, message, {
  //  cc: 'sample@email.com'         // optional cc
 
 });
 
      //set the flag to avoid duplicates
      sheet.getRange(startRow + i, 10).setValue(FOLDER_CREATED);
      sheet.getRange(startRow + i, 11).setValue(folderpath);
      // Make sure the cell is updated right away 
     SpreadsheetApp.flush();
         
         } 
                 
    }
  }

Both scripts should be set to trigger when the form submits.
When a user enters information in the form, the scripts will generate an edit link to the item, and create a Drive sub folder with the unqiue ID. They then will get an email which contains a link to the newly created folder...

On a Google site, the results can be displayed in an Awesome Table...

The Awesome Table setup used is below:

One quick note, as the unique ID is generated from the row number, deleting a row in the sheet would cause a mismatch between the references and any already created folders...
In my example above in the 'Advanced parameters', the Awesome Table is set to only show rows where A is not blank, so the method for removing any unwanted records from displaying in the Awesome table should just be a matter of deleting the timestamp for the unwanted row in the sheet.

Hope this is of interest. A copy of the demo sheet, including scripts can be found here

Tuesday 7 July 2015

Enhancing Google forms via a customised webpage

Google forms have some great strengths, particularly in the way that Google Apps Scripts can work with the collected data. There's not much you can do though to the out of the box Google form to add functionality such as comparing fields for validation, adding word counts or any other JavaScript tricks. A way of getting the best of both worlds is to extract the Google form and put it into a web page, which can be hosted on Google Drive, and then enhance the design and functionality as you like with HTML and JavaScript.

The below video shows a few of the enhancements - including

-automatic word counts as you type in paragraph text fields

-email address and tickbox validation to prevent form being submitted unless boxes are ticked

-accordion dropdowns to reduce form size

-working hyperlinks in the text.

While these features are in the front end, in the back, as the fields are all still Google form fields, there is functionality in the collecting Google sheet so that when the form is submitted, Google Apps Scripts run, to provide an edit link, emailing the recipient with confirmation of the data and a link to edit, and a set timer so that the recipient is contacted every x months to review their data and amend if necessary. As normal with Google sheets, an add-on can alert the owners of the sheet when a form is submitted, and Awesome tables can summarise the data and provide searching and filtering.

Some of the code snippets are below:

I included a reference to jquery in the <head> for the accordion and field validation scripts

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>

If you have fields set to required in the Google form, if you try to submit the webpage version without completing these fields, then you are taken to the original Google form to complete these. This clearly isn't ideal, so its necessary to make the fields required instead using JavaScript
Sample code here for one of the fields

<script type="text/javascript">
// validate the First Name field
$('form').submit(function () {
    // Get the Login Name value and trim it
    var name2 = $.trim($('#entry_12345678').val());
    // Check if empty of not
    if (name2  === '') {
    $('#errorwarning2').text("Required field 'First Name' is empty, please correct and re-submit.");
        return false;
    }
});
</script>

To do a word count, I used the following script

<script type="text/javascript">
function wordCount( val ){
    return {
        charactersNoSpaces : val.replace(/\s+/g, '').length,
        characters         : val.length,
        words              : val.match(/\S+/g).length,
        lines              : val.split(/\r*\n/).length
    };
}
var $div1 = $('#count1');

$('#entry_987654321').on('input', function(){
var a = wordCount( this.value );
$div1.html(
"<br>Word count: "+ a.words
);
});
</script>

The Div that shows the word count can then be put next to the relevant field
To stop the form submitting if the email address fields do not match, I used the following:

<script type="text/javascript">

// form id value, default is ss-form
var formID = 'ss-form';
var formKey = 'abcdefghijklmnop123456789';
var submitted = false;

$(document).ready(function () {
    var ssForm = $('#' + formID);

    ssForm.submit(function (evt) {
         var email = document.getElementById("entry_123456789").value;
        var confemail = document.getElementById("entry_1011121314").value;
if(email == confemail) {
            ssForm.attr({'action' : 'https://docs.google.com/forms/d/' + formKey + '/formResponse'}); 
            return true;
        } else {
    
    $('#errorwarning').text("Error - email addresses do not match, please correct and re-submit.")
            return false;
        }
    });
});
</script>

If the user has mistyped an email address and has been unable to submit the form, I wanted the error message relating to this to hide as soon as they start typing in the email field again. For this I just used this:

$('#entry_123456789').on('input', function(){
 $('#errorwarning1').text("");
});

Away from JavaScript - in the Google Sheet, to let users update their details every x months, I used a variant of the script in my blogpost here

Hope this is of interest, any comments or queries welcomed

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

Monday 27 April 2015

Archive Gmail messages to Google Drive folders - using DriveApp

In the past we have been using a great script from Marcello Scacchetti from this blog. The script allows you to archive Gmail messages to Google Drive. Please see that blog for full details.

Since DocsList was depreciated this script no longer works. I have made a few tweaks to the original to make this work with DriveApp instead. One problem was the DriveApp does not have an identical equivalent to GetFolderbyName so it was necessary to add a quick custom function to replace this (the "getFolders" function at the end of the script)

Here's the new script...

/**
 * Main function run at spreadsheet opening
 */
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ 
    {name: "Initialize", functionName: "init"},
    {name: "Archive Gmail Messages", functionName: "ScanGmail"}
  ];
  ss.addMenu("Gmail Archiver", menuEntries);
}
    
/**
 * Initialize the system
 */
function init() {
    // Create the needed Gmail label
    GmailApp.createLabel("Archive to Drive");
    
    // Create Google Drive folder if doesn't exists
    try {
       var folder = DriveApp.getFolders("Email Archive");
    } catch(e) {
       // Folder doesn't exists
       DriveApp.createFolder("Email Archive");
    }
    
    Browser.msgBox("Created Gmail label: Archive to Drive and Google Drive folder: Email Archive");
}

/**
 * Scan Gmail account for message archive requests
 */
function ScanGmail() {
  // Default Drive folder where to archive messages
  var baseFolder = "Email Archive";
  
  // Get the label
  var label = GmailApp.getUserLabelByName("Archive to Drive");
    var threadsArr = getThreadsForLabel(label);
    for(var j=0; j<threadsArr.length; j++) {
      var messagesArr = getMessagesforThread(threadsArr[j]);
      for(var k=0; k<messagesArr.length; k++) {
        var messageId = messagesArr[k].getId();
        var messageDate = Utilities.formatDate(messagesArr[k].getDate(), Session.getTimeZone(), "dd/MM/yyyy - HH:mm:ss");
        var messageFrom = messagesArr[k].getFrom();
        var messageSubject = messagesArr[k].getSubject();
        var messageBody = messagesArr[k].getBody();
        var messageAttachments = messagesArr[k].getAttachments();
        
        // Create the new folder to contain the message
        var newFolderName = messageDate + " - " + messageFrom + " - " + messageSubject + " - " + messageId;
        var newFolder = createDriveFolder(baseFolder, newFolderName);
        
        // Create the message PDF inside the new folder
        var htmlBodyFile = newFolder.createFile('body.html', messageBody, "text/html");
        var pdfBlob = htmlBodyFile.getAs('application/pdf');
        pdfBlob.setName(newFolderName + ".pdf");
        newFolder.createFile(pdfBlob);
        htmlBodyFile.setTrashed(true);
        

        // Save attachments
        for(var i = 0; i < messageAttachments.length; i++) {
            var attachmentName = messageAttachments[i].getName();
            var attachmentContentType = messageAttachments[i].getContentType();
            var attachmentBlob = messageAttachments[i].copyBlob();
            newFolder.createFile(attachmentBlob);
        }

      }
      // Remove Gmail label from archived thread
      label.removeFromThread(threadsArr[j]);
    }
    Browser.msgBox("Gmail messages successfully archived to Google Drive");
}


/**
 * Find all user's Gmail labels that represent mail message
 * movement requests es: moveto->xx@yyyy.com
 *
 * @return {GmailLabel[]} Array of GmailLabel objects
 */
function scanLabels() {
  // logs all of the names of your labels
  var labels = GmailApp.getUserLabels();
  var results = new Array();
  for (var i = 0; i < labels.length; i++) {
    if(labels[i].getName() == "Archive to Drive") {
      results.push(labels[i]);
    }
  }
  return results;
}

/**
 * Get all Gmail threads for the specified label
 *
 * @param {GmailLabel} label GmailLabel object to get threads for
 * @return {GmailThread[]} an array of threads marked with this label
 */
function getThreadsForLabel(label) {
  var threads = label.getThreads();
  return threads;
}

/**
 * Get all Gmail messages for the specified Gmail thread
 *
 * @param {GmailThread} thread object to get messages for
 * @return {GmailMessage[]} an array of messages contained in the specified thread
 */
function getMessagesforThread(thread) {
  var messages = thread.getMessages();
  return messages;
}


/**
 * Get methods of an object
 * @param {Object} object to scan
 * @return {Array} object's methods
 */
function getMethods(obj) {
  var result = [];
  for (var id in obj) {
    try {
      if (typeof(obj[id]) == "function") {
        result.push(id + ": " + obj[id].toString());
      }
    } catch (err) {
      result.push(id + ": inaccessible");
    }
  }
  return result;
}

/**
 * Create a Google Drive Folder
 *
 * @param {String} baseFolder name of the base folder
 * @param {String} folderName name of the folder
 * @return {Folder} the folder object created representing the new folder 
 */
function createDriveFolder(baseFolder, folderName) {
  var baseFolderObject = getFolders(baseFolder);
  return baseFolderObject.createFolder(folderName);
}

// new function to simulate GetFolderbyName

function getFolders(folderName)
{      
  var folders = DriveApp.getFolders();     
 while (folders.hasNext()) {
   var folder = folders.next();
   if(folderName == folder.getName()) {         
     return folder;
   }
 }
  return null;
}

Wednesday 4 February 2015

Awesome Tables allowing scrolling on an iPad

-update - December 2015 -unfortunately this hack no longer works....
___________________________________________________________________________

I use Awesome Tables frequently on my sites, and find them really useful - one small annoyance however is that on an iPad it is not possible to swipe to see any columns that are not immediately visible in the view, as the default swipe action is to move the page around, not the table contents. I have found a way round this, which is a bit of a hack and a bit fiddly, but if your table contains lots of columns and it is essential that the content can be viewed on an iPad, then I hope this solution is of help.... A demo of a working table can be found here

The way this works is that after creating the table you want to use, you then create a separate gadget using the generated code, wrapped within a scroll wrapper <div> with some css which instructs iPADs to swipe the content within that div.

To start, I created a gadget, added some css style overides specific to iPADs in style tags, and then inspected the code generated by the original awesome table and copied it to the gadget, In the code sample below, this is the bit from <iframe height="900" to the closing </script>

An important note is that this only renders exactly how your original table is designed. If you edit the gadget you don't have the Awesome table UI, and so if you want to change the look feel or functionality, you will need to amend your original table, and re-copy the code to the gadget.

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
  <ModulePrefs title="iframe" width="900" height="1600"/>
  <Content type="html">
  <Require feature="dynamic-height"/>
  <![CDATA[
  
  <style>
   
   @media only screen and (device-width: 768px) {
   html {
    overflow-y: hidden
}
    
    .scroll-wrapper {
  position: fixed; 
  right: 0; 
  bottom: 0; 
  left: 0;
  top: 0;
  -webkit-overflow-scrolling: touch;
  overflow-y: scroll;
}

.scroll-wrapper iframe {
 height: 99%;
  width: 99%;
  margin-bottom: -5px;
}
   }

@media only screen and (min-device-width: 481px) and (max-device-width: 1024px) and (orientation:portrait) {
 html {
    overflow-y: hidden
}
    
    .scroll-wrapper {
  position: fixed; 
  right: 0; 
  bottom: 0; 
  left: 0;
  top: 0;
  -webkit-overflow-scrolling: touch;
  overflow-y: scroll;
}

.scroll-wrapper iframe {
 height: 99%;
  width: 99%;
  margin-bottom: -5px;
}
}

@media only screen and (min-device-width: 481px) and (max-device-width: 1024px) and (orientation:landscape) {
 html {
    overflow-y: hidden
}
    
    .scroll-wrapper {
  position: fixed; 
  right: 0; 
  bottom: 0; 
  left: 0;
  top: 0;
  -webkit-overflow-scrolling: touch;
  overflow-y: scroll;
}

.scroll-wrapper iframe {
 height: 99%;
  width: 99%;
  margin-bottom: -5px;
}
}

</style>
   

<div class="scroll-wrapper">

<iframe height="1050" frameborder="0" width="600" src="//kkgeef8a95adr2tsvjoqlq0mu85vkipc-a-sites-opensocial.googleusercontent.com/gadgets/ifr?url=https://sites.google.com/site/scriptsexamples/awesome-table.xml&amp;container=enterprise&amp;view=default&amp;lang=en&amp;country=ALL&amp;sanitize=0&amp;v=8c887f508e9dc524&amp;libs=core:dynamic-height:setprefs&amp;mid=108&amp;parent=https://sites.google.com/a/nihr.ac.uk/external-demo-site/#up_visualizationType=Table&amp;up_range=A1:AH&amp;up_queryOpt=Where+A+%3E%3D+1&amp;up_categoryCaption=Choose+a+value...&amp;up_backgroundColor=white&amp;up_codeAnalytics&amp;up_ImpressionContenuSidebar&amp;up_pageSize=15&amp;up_download=false&amp;up_mapRegion&amp;up_url=https://docs.google.com/a/nihr.ac.uk/spreadsheets/d/1J2xpo6EuMn_g_hx2XfgH4w3A2b_0ADkf6gTzfIC0pNE/edit%23gid%3D0&amp;up_customCSS=https://342e852e291c4ec2d6331fba2de5db72377c1ee3.googledrive.com/host/0BysVSZMkrof5OWlibzRnVkVEMTg/lifecyclebarsforExternalDemo.css&amp;up_rangeTemplate&amp;up_scriptProxy&amp;up_mapHeight&amp;up_displayOrder&amp;up_dateFormat=MM/dd/yy&amp;up_sheet=Sheet1&amp;st=e%3DAIHE3cCPvWKyJFWXCjMLpYB8PLczZbggMQQeFD05ADqRZwk3VHxcGTs40mZKzs5UTVaWBdNvXXOigzGWxKPK8Z%252FUKBXPrSfpXqnVNNJEbxMhChBNWMHTBX0wVGuX%252FtJUlAngUfQ9sNKM%26c%3Denterprise&amp;rpctoken=7741563352861330227" class="igm" allowtransparency="true" name="1506222097" id="1506222097" scrolling="auto" title="Awesome Table (with interactive controls, using Google Sheets)" style="height: 1016px;"></iframe><script>JOT_postEvent('registerForRpc', this, ['7741563352861330227', 1506222097, '//kkgeef8a95adr2tsvjoqlq0mu85vkipc-a-sites-opensocial.googleusercontent.com/gadgets/ifr?url\75https://sites.google.com/site/scriptsexamples/awesome-table.xml\46container\75enterprise\46view\75default\46lang\75en\46country\75ALL\46sanitize\0750\46v\758c887f508e9dc524\46libs\75core:dynamic-height:setprefs\46mid\075108\46parent\75https://sites.google.com/a/nihr.ac.uk/external-demo-site/#up_visualizationType\75Table\46up_range\75A1:AH\46up_queryOpt\75Where+A+%3E%3D+1\46up_categoryCaption\75Choose+a+value...\46up_backgroundColor\75white\46up_codeAnalytics\46up_ImpressionContenuSidebar\46up_pageSize\07515\46up_download\75false\46up_mapRegion\46up_url\75https://docs.google.com/a/nihr.ac.uk/spreadsheets/d/1J2xpo6EuMn_g_hx2XfgH4w3A2b_0ADkf6gTzfIC0pNE/edit%23gid%3D0\46up_customCSS\75https://342e852e291c4ec2d6331fba2de5db72377c1ee3.googledrive.com/host/0BysVSZMkrof5OWlibzRnVkVEMTg/lifecyclebarsforExternalDemo.css\46up_rangeTemplate\46up_scriptProxy\46up_mapHeight\46up_displayOrder\46up_dateFormat\75MM/dd/yy\46up_sheet\75Sheet1\46st\75e%3DAIHE3cCPvWKyJFWXCjMLpYB8PLczZbggMQQeFD05ADqRZwk3VHxcGTs40mZKzs5UTVaWBdNvXXOigzGWxKPK8Z%252FUKBXPrSfpXqnVNNJEbxMhChBNWMHTBX0wVGuX%252FtJUlAngUfQ9sNKM%26c%3Denterprise\46rpctoken\0757741563352861330227'])</script>

</div>

  ]]>
  </Content>
</Module>

A strange quirk is that when you upload the gadget to host it, you appear to need to save this as an html file and upload. e.g. ATgadget.html. Once you have uploaded it to your Drive, you can rename this as xml. e.g. ATgadget.xml
The gadget can then be uploaded to your site using the insert gadget by URL method.

Hope this of interest, any comments, questions, or code improvements welcomed!