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

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