Thursday 23 March 2017

Using Awesome Tables with Google Form Attachments

Google recently added an attachment upload function to Google GSuite customers.  This functionality has replaced some workarounds, including one I have blooged about before using Folders

Google creates a Drive folder for the attachments, and lists the attachments URLs in the response sheet, separated by commas.  This is great, but to display the information in a Google site or similar, it is most useful to use an Awesome Table to display the results.  To get this formatted to suit an Awesome Table is a bit fiddly however.   This post explains a way to get this set up

First - here's the demo of the completed table

_____________________________________________________________________________


_____________________________________________________________________________

To begin with I created a new tab which is the one that the Awesome table will read from, manually inputted the column names in the first row, added filters on the second, and in the third row added in a query to bring the data in from the form. 


As the file URLs are all in one column, separated by a comma and a space, it would not be possible to hyperlink these with a formula - the files need to be split out initially - this is done with an array formula with a split function, using ", " as the separator


Next a Google Apps script was added to list the URLs and names of the folder that was created automatically to contain the form attachments.  The code should be set on a trigger, on form submit.
The script formats the URL to match the same syntax that the Google form produces and will output to a tab I created called 'FileList'.


function search()  {
 
    var folder =  DriveApp.getFolderById("***INSERT YOUR GOOGLE DRIVE FOLDER ID HERE**");
     
  var files = folder.getFiles();
   
    
     var sheetName = 'FileList';  
      
var SheetIds = 2;
 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
 
  sheet.clear();
 
  sheet.appendRow(["URL","FileName"]);
 
 while (files.hasNext()) {
 
   var string = 'https://drive.google.com/open?id=';
   var file = files.next();
   Logger.log(file);
   Logger.log(string);
 var data;
    data = [
      string + file.getId(),
     file.getName(),
      

    ];
 
    sheet.appendRow(data);
 
  }
  SpreadsheetApp.flush();

  
};



End Result is as per the below screenshot when it runs

Now we have the URL and the Name, vlookups can be added to each of the 'Doc' columns to bring through the filenames


With all this done, the final step is to add a tab called template, which can then be used in the Awesome table setup.   Screenshot below, but the file is linked at the end of this post, in case you want to copy and paste.  The documents column takes the URLs and file names and hyperlinks each one with a generic document icon



In the Awesome Table setup - just reference the template to apply this



A copy of the sheet is here: https://docs.google.com/spreadsheets/d/1Y2bvKyA71GEAjf0RmIJOgADZyOHuAjGH5EzgRKfL8RU/edit#gid=1206792601

Hope this is of use!

1 comment:

  1. Brilliant, James! And your timing could not have been better for us as we are launching our online teacher absence process which includes Google form uploads for lesson plans.

    Your instructions were very easy to follow.

    Thank you!

    Brian

    ReplyDelete