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


93 comments:

  1. The formula for making a unique reference is returning:

    Error
    In FIND evaluation, cannot find '#' within 'bpugh@ahschools.com '.

    Col A is Timestamp; Col B is Username.

    Any idea of what I'm doing incorrectly?

    Thanks very much!

    Brian.

    ReplyDelete
    Replies
    1. Hi Brian - I have been unable to recreate that error, though I guess you could try the following - this would work either on an email address or a first name / last name field

      =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)&ROW( A:A ))))))

      Sample sheet here
      https://docs.google.com/spreadsheets/d/1z2HygzFvxzmMMUgsogq3jvwCk6Pj12HVJxNsPtRYPno/edit#gid=1454573290

      Cheers

      James

      Delete
    2. Excellent, James! Thank you!

      One other question: is there any way that the person who submits the form can access only his/her editable URL and folder?

      Thanks for a useful and needed form/upload feature!

      Brian.

      Delete
    3. Hi Brian - no worries :-) To give rights to the folder to the person submitting the form, you need to change line 56 in the example above to:

      var folderpath = DriveFolder.createFolder(folderName).addEditor(emailAddress).getUrl();

      and just move line 60 (var emailAddress...etc) so that it is above line 56 so that the variable is declared earlier.

      To restrict the view in the Awesome Table to just the person who has submitted the form (and access the editable URL) you need the proxy script which can be found on the below link

      https://docs.google.com/presentation/d/1GQkhm9mizwY-o7r4i093ASGonuRs7JPpJx4QgK2GRRw/edit

      Hope this helps

      James

      Delete
    4. Hi James,

      I have created a staff directory using AT and would like to restrict only a group of administrative people can view certain columns of staff directory. For example, all staffs can view columns A to G, however only top management people can view columns A to J.

      I have looked around on the row-level permission, but that is for 'row', i would like to set permission for 'column'.

      Any idea how to do the restriction?

      Thanks.

      Delete
    5. Hi - have answered on the Awesome table community - thread here for reference https://plus.google.com/116877273079682767918/posts/aghRSE2EoLK

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

    ReplyDelete
  3. Hello, James.
    Do you may show your Google Forms as at example ?

    ReplyDelete
    Replies
    1. TypeError: Can not call method "getSheetByName" object null. (line 30, the file code)

      Delete
    2. Hi - there is nothing special with the form - this form just contains the fields corresponding to B,C,D,E,F in the example spreadsheet
      https://docs.google.com/spreadsheets/d/1IQu3apOHUS3ZyD33xEvuKrMFRqfCL67DW9hym8SSLLQ/edit#gid=1454573290 Screenshot here, as the form is restricted to my domain https://sites.google.com/a/nihr.ac.uk/external-demo-site/screenshot form.PNG

      Delete
    3. Check the name of your form sheet - in my example this was "Form responses 1" yours may be different and will need adjusting in the code if so

      Delete
  4. Good Day James,

    This sounds like a very stupid question but where do i find the root folder ID? I can't seem to find it and other codes I've tried running to find it don't return anything.

    ReplyDelete
    Replies
    1. Hi - to get your folder ID, this should be the last bit of the URL when you navigate to the folder in your browser. e.g. for https://drive.google.com/drive/folders/0B8gXXLblsnEalFmYmxqbTEDDQnc you would use 0B8gXXLblsnEalFmYmxqbTEDDQnc

      Delete
  5. Hi James,

    I've managed to replicate this and think I can now integrate into my own form, this is brilliant as it will save our volunteers hours of manual work, especially as I have no coding experience!

    The only part part I'm struggling with in the users who fill out the form wont have a google account, I've shared the drive folder so everyone has full permissions, but they dont get an upload option :-( . any work around you can think of?

    Thanks

    Mark

    ReplyDelete
    Replies
    1. Hi Mark, glad this has been of use. The solution has really been designed with Google apps for Business / education in mind, as it does assume that all users will have a Google account. The only way to get anonymous uploads into a drive folder that I have seen and used was based on this blog post http://ctrlq.org/code/19747-google-forms-upload-files. However I don't think is can easily be combined with the methodology in my post, apologies.

      Delete
    2. Thanks James, I wonder if I can incorporate both your script and the suggestion you've supplied into a google site!

      One for when I get time to play.

      regards

      Mark

      Delete
  6. James, great solution.. one slight problem is that the link returned in the email still requires the user to click the Open in Drive button. Is there anyway to open the drive folder directly?

    ReplyDelete
    Replies
    1. Certainly - change line 56 to this:

      var folderpath = DriveFolder.createFolder(folderName).getId();
      var pathtoemail = "https://drive.google.com/drive/folders/"+folderpath;

      and then line 61 to this:

      var message = 'Thank you for sumbitting a purchase order requisition for supplier: '+ supplier +' - please upload the purchase order(s) relating to this to the following folder ' +pathtoemail ;

      Delete
  7. James, I'm not familiar with using Google scripts so I have a couple of questions.

    1) Would you clarify: Where does the script go? In the form or in the sheet? I see a Tools > Script Editor option from both. 2) When you say, "Both scripts should be set to trigger when the form submits." How does one configure for this?

    Most grateful for your help.

    ReplyDelete
    Replies
    1. Hi - the script should go in the Google sheet. To configure the form submit trigger, please have a look at this page, where there is a step by step guide with screenshots
      http://gassnippets.blogspot.co.uk/2012/11/understanding-form-submission-trigger.html?_sm_au_=iVVQqP8pv7RJ0r2F

      Delete
    2. Many thanks for your swift and informative reply James!!!

      Delete
  8. Hi James

    Firstly, thank you for the script, it is exactly what I am looking for. However, I do not use the unique reference part as we have our own format. The script executed perfectly even after the changes. Now, as I developed the spreadsheet more, by adding in more add-ons (ie "copyDown" and "formMule") the script now only runs from the script editor and not based on the triggers.

    Any ideas what I have done wrong?

    ReplyDelete
    Replies
    1. Hi Dave, sorry for delay I replying, glad the script is of use. I doubt it is something you had done wrong here, I can only assume that the triggers have messed up due to the add-ons though I have not seen this happen before. Only thing I could advise would be to reinstall the triggers, or try triggers with an alt account to see if this resolves things,

      Delete
  9. I am having difficulty understanding the following error message. Maybe you can point out why I am getting this?

    TypeError: Cannot call method "getSheetByName" of null. (line 12, file "Code")

    Line 12 looks like
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

    I have the sheet name as Form Response 1 in the spreadsheet and this is what the debugger is saying.

    This: Object (346274251) : ({createFolder:function createFolder() {var FOLDER_CREATED = "Travel Upload";var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheetByName("Form Responses 1");var data = ss.getDataRa...

    The above area that it is referencing looks like...

    var FOLDER_CREATED = "Travel Upload";

    // 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

    I have tried renaming the sheet as well but to no avail.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I figured it out. You need to remove the spacing in Form Response 1. I just renamed the sheet FormResponse1 and it worked.

      Delete
    3. Hi Johnson, glad you got this working

      Delete
  10. Good morning, James.
    I have your script running just as I want it too, having gleaned just about everything I need from your blog and your responses to various questions, including my own.

    I do have two - perhaps last - questions. The form that I have created is for teachers to report absence due to illness. At the end of the form, the teacher is asked to provide a lesson plan. They have an option of pasted the Google doc URL of their lesson plan or upload their lesson plan (e.g., if it was created in Word).

    1. Is there any way of running the upload script only condition of a teacher selecting "upload lesson plan" (instead of "paste URL...)?

    2. Could the name of the teacher who wishes to upload his/her lesson plan be the name of the folder created?

    Thank you very much for sharing some very useful tools, James!

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. I've managed to get everything working exactly as we need, except for one thing.

    When someone submits the form, not only does s/he get the email notification, everyone who submitted previously does, too.

    Puzzled...

    Thank you very much, Mr Pearson.

    ReplyDelete
    Replies
    1. Hi - it looks like the column containing the flag to prevent duplicates is not being identified correctly. If your column where you have the "FOLDER_CREATED" flag is column P for example then try changing this line "var folderstatus = row[9]; // column J" to "var folderstatus = row[15]; // column P"

      Delete
  13. That worked. Great!

    Thank you very much. I really appreciate your help.
    Brian.

    ReplyDelete
    Replies
    1. A pleasure, glad it is all working :-)

      Delete
  14. I'm looking for help in making the code do organization for me. I am in the beginning process but really unsure as to what to do. Let me preface this as your code is great and it allows my organization to do something that we can track that would otherwise be a nightmare. The one downside to this is that a folder is created each time someone submits a form. When you take into account that 50 people will be submitting multiple documents a week then that leads to a bit of scrolling after awhile. What I am attempting to do is have a new folder be created each time within a sub folder. (drive folder, sub folder, submitting folder). This will be based on the email address (which will then be tied to a folder name or ID. This means that when someone submits a document it sends them a link to their folder that I created. I can then share permissions with individuals to protect privacy information and only allow individuals to view what they have submitted in their folder. Is this even possible?

    ReplyDelete
    Replies
    1. Hi Johnson - sure this is possible. I have made a copy of the demo sheet and script on the following link sheet: https://docs.google.com/spreadsheets/d/1bM6OfLD46ckH91SQG96EOOWCNzEtYatrBCPUKemrHrI/edit#gid=1454573290 script here: https://goo.gl/0ZM78k
      The script takes the users email address, and extracts everything before the "@" symbol and uses that text to create the folder name. The script checks to see if a folder with that name already exists, if it does, it finds the ID of that folder and sends that out to the user, if not, then it creates the folder and runs as per the original script. As a bonus, if the folder does not exist, when it is created, it is automatically shared with the user too. Its been working here in testing. Let me know if you find any bugs!

      Delete
    2. Sorry, didn't spot you wanted subfolders creating. Here's a revised sheet https://goo.gl/6e1JeX and revised script https://goo.gl/MjC0Vx. If the script finds that a folder exists already for a user, then this now creates a subfolder underneath it. As a name for the subfolder, it uses the timestamp of the form submission.

      Delete
    3. Good Afternoon James,

      The code is working great! Thank you very much.

      Delete
  15. Hi James,
    Such a sweet setup. I am getting an error, however....
    16-03-17 13:48:11:741 PDT] Execution failed: No item with the given ID could be found, or you do not have permission to access it. (line 39, file "Code") [0.958 seconds total runtime]

    Any ideas?

    ReplyDelete
    Replies
    1. Hi Josh - you need to change the dummy drive folder ID on that line to the ID of your actual folder

      Delete
  16. James, wonderful "upgrade!"

    Everything is working except the folder link. I get the folder id, but not the actual link.

    FOLDER_CREATED 0B1coMP8GEmC5MDd2eHlBMGhkOUU

    I noticed that there is no "var folderpath = DriveFolder.createFolder(folderName).getUrl();" line in the new script. Would that possibly be the problem? Thank you very much this work!

    ReplyDelete
    Replies
    1. Hi Brian - thank you, yes you are quite right this was the problem (i forgot to update the script with the improved url method) I have updated the scripts now. Many thanks. James

      Delete
    2. I think everything is now "perfect," James. Thanks so very much!

      Delete
  17. How do you find out the ID of folder?

    ReplyDelete
    Replies
    1. Hi - to get your folder ID, this should be the last bit of the URL when you navigate to the folder in your browser. e.g. for https://drive.google.com/drive/folders/0B8gXXLblsnEalFmYmxqbTEDDQnc you would use 0B8gXXLblsnEalFmYmxqbTEDDQnc

      Delete
  18. Thanks for that, my next issue is the email address. It is telling me... [16-03-18 08:02:09:693 CST] Execution failed: Invalid email: 112 (line 64, file "Code") [1.815 seconds total runtime] In my script I have my email addresses in column C, so I switched your code to var emailAddress = row[3] did I do that right?

    ReplyDelete
    Replies
    1. Hi Josh - apologies, I spotted a mistake in my comment for that line which I have now corrected. Since in this situation A=0,B=1,C=2 etc, then if your email address is in column C, you need var emailAddress = row[2]

      Delete
  19. Hi James, I did that as well, with still no luck...
    Does it have anything to do with that this form is for everyone. So I have them enter their email address for a field. I don't have them sign in to this form to fill out. Hope that make sense.

    ReplyDelete
    Replies
    1. Actually, I do get an email, but its the email from my last submission. And then it looks like the error is coming from the submission I just did.
      [16-03-18 07:16:57:517 PDT] SpreadsheetApp.getActiveRange() [0 seconds]
      [16-03-18 07:16:57:517 PDT] Range.getRow() [0 seconds]
      [16-03-18 07:16:57:517 PDT] Range.getLastRow() [0 seconds]
      [16-03-18 07:16:57:518 PDT] Range.getColumn() [0 seconds]
      [16-03-18 07:16:57:518 PDT] Range.getLastColumn() [0 seconds]
      [16-03-18 07:16:57:518 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
      [16-03-18 07:16:57:628 PDT] Starting execution
      [16-03-18 07:16:57:637 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
      [16-03-18 07:16:57:776 PDT] Spreadsheet.getSheetByName([Form responses 1]) [0.138 seconds]
      [16-03-18 07:16:58:164 PDT] Spreadsheet.getDataRange() [0.387 seconds]
      [16-03-18 07:16:58:756 PDT] Range.getValues() [0.591 seconds]
      [16-03-18 07:16:59:177 PDT] DriveApp.getFolderById([0ByfaIZfqOTetd2JUeVFtTDRHdFU]) [0.199 seconds]
      [16-03-18 07:16:59:546 PDT] Folder.createFolder([j9]) [0.368 seconds]
      [16-03-18 07:16:59:546 PDT] Folder.getUrl() [0 seconds]
      [16-03-18 07:16:59:581 PDT] MailApp.sendEmail([jbrown@mygiantsteps.org, Please upload related documentation, Thank you for submitting a purchase order requisition for supplier: 112 - please upload the purchase order(s) relating to this to the fol...) [0.034 seconds]
      [16-03-18 07:16:59:582 PDT] Sheet.getRange([9, 10]) [0 seconds]
      [16-03-18 07:16:59:583 PDT] Range.setValue([FOLDER_CREATED]) [0 seconds]
      [16-03-18 07:16:59:584 PDT] Sheet.getRange([9, 11]) [0 seconds]
      [16-03-18 07:16:59:584 PDT] Range.setValue([https://docs.google.com/a/mygiantsteps.org/folderview?id=0ByfaIZfqOTetV1JmQ2VqSFRWOVE&usp=drivesdk]) [0 seconds]
      [16-03-18 07:16:59:698 PDT] SpreadsheetApp.flush() [0.113 seconds]
      [16-03-18 07:17:00:071 PDT] Folder.createFolder([j10]) [0.372 seconds]
      [16-03-18 07:17:00:071 PDT] Folder.getUrl() [0 seconds]
      [16-03-18 07:17:00:105 PDT] MailApp.sendEmail([jbrown@mygiantsteps.org, Please upload related documentation, Thank you for submitting a purchase order requisition for supplier: 123 - please upload the purchase order(s) relating to this to the fol...) [0.033 seconds]
      [16-03-18 07:17:00:106 PDT] Sheet.getRange([10, 10]) [0 seconds]
      [16-03-18 07:17:00:158 PDT] Range.setValue([FOLDER_CREATED]) [0.051 seconds]
      [16-03-18 07:17:00:159 PDT] Sheet.getRange([10, 11]) [0 seconds]
      [16-03-18 07:17:00:160 PDT] Range.setValue([https://docs.google.com/a/mygiantsteps.org/folderview?id=0ByfaIZfqOTeta3dSYjBVa1h3NFE&usp=drivesdk]) [0 seconds]
      [16-03-18 07:17:00:293 PDT] SpreadsheetApp.flush() [0.133 seconds]
      [16-03-18 07:17:00:662 PDT] Folder.createFolder([11]) [0.368 seconds]
      [16-03-18 07:17:00:662 PDT] Folder.getUrl() [0 seconds]
      [16-03-18 07:17:00:663 PDT] MailApp.sendEmail([, Please upload related documentation, Thank you for submitting a purchase order requisition for supplier: - please upload the purchase order(s) relating to this to the following folder https://docs...) [0 seconds]
      [16-03-18 07:17:00:667 PDT] Execution failed: Failed to send email: no recipient (line 64, file "Code") [3.027 seconds total runtime]

      Delete
  20. Ok i think I figured out the email issue. I have another issue now. I am trying to give the person who submitted the form only rights to that folder. I used your code, var folderpath = DriveFolder.createFolder(folderName).addEditor(emailAddress).getUrl(); and moved the variable for emailAddress above to declare it earlier.

    When I click on the link it sends through email, it starts to open that persons google drive but then i get a pop up that says...
    "The folder that you were viewing no longer exists or you no longer have permission to access it. You have been moved to My Drive." however, it is there if i search under shared with me.

    ReplyDelete
  21. Hi James,

    I am fumbling my way through this but your examples and responses has gotten me to a point where I am functional. I am hoping to have this trigger by a question in the form on whether someone has attachments and not have this happen for every submission. In my sheet column H has the response to whether someone has attachments or not. The question on the for is to only check yes to the response will either yield a "yes" or be null.

    ReplyDelete
  22. Everything was working so well, but now I get this error: "Invalid argument: permission.value (line 74, file "Code")." I must have inadvertently changed something. I've spent several hours looking, but without resolution.

    I've made a video of my version of your script. Would you please view it and advise? Thank you very much! I've sent you the link via Google Hangouts. Here is the link: https://goo.gl/uCECkd

    ReplyDelete
  23. Eureka!

    The final error that I encountered was ["", "", "", "", "", "", "#VALUE!", "", "", "", "", "", "", "", "", "", ""]

    The unique reference continues past the last row of data and produces this error. Writing if (folderName == "#VALUE!") break; just before
    if (folderstatus != FOLDER_CREATED)
    stops the error.

    Just wondering if there is something in the formula that can be changed so that it stops creating a unique reference when it encounters an empty row.

    Is that possible?

    (I tried just deleting the empty rows, but a single form entry produces dozens of new empty rows.)

    Brian.

    ReplyDelete
    Replies
    1. Hi Brian - sure this just needs another IF clause. Adapting my example in my blogpost, this would be =ArrayFormula(IF(ROW(A:A)=1,"Reference",IF(ROW(A:A)=2,"NoFilter",if(A1:A<>"",((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 ))),""))))

      Delete
  24. You continue to be very helpful, James. Thank you very much!
    Brian.

    ReplyDelete
  25. First wanted to say that what you have here is pretty awesome, this is a great solution for what our company needs, so thank you!

    I am having a little issue with the folder creation. Each time a folder is created, it has the same name as the very first id in the list, it never moves down the list. I fear there may be an issue with the for() loop, as the i never increments, but I'm unsure. Have you had any issues with this, or any ideas as to a solution? Thank you in advance for any help! Also, thanks again for the solution!

    ReplyDelete
    Replies
    1. Hi Amanda, Glad this is of use. I have not come across the error you are getting unfortunately. I have tested using the code in my demo sheet https://docs.google.com/spreadsheets/d/1IQu3apOHUS3ZyD33xEvuKrMFRqfCL67DW9hym8SSLLQ/edit#gid=1454573290 with multiple lines, and all the folders are getting the correct reference. If you have a demo sheet with non confidential data you can share with me, am happy to troubleshoot this for you. Cheers. James

      Delete
    2. James,
      I think I figured out what is happening. For some reason, my "Folder_Created" reference isn't being placed into my spreadsheet, therefore, when the folder is being created and refers to the column where it's supposed to show up, it's not working correctly.
      Here is the code I have running in the sheet:

      var formURL = 'https://docs.google.com/a/quilmont.com/forms/d/1MwpJUxn9JSpD8aZQXWcLVJqlFZuAHbSyyMQqhFWy6Fk/viewform'; // enter the url for the form
      var sheetName = 'Job Application'; // enter the name of the sheet
      var columnIndex = 11;  //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("Job Application"); //name of the sheet within the spreadsheet
        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("Job Application");
        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 = 8;  //column I
       
      // Get the ID of the root folder, where the sub folders will be created
        var DriveFolder = DriveApp.getFolderById("0B_uJbbFeuSw3WW1YWV9sdDlwdWs");
       
        // 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 folderstatus = row[10];  // 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' +folderpath ;  
       
            var subject = "Please upload related documentation" +i;   
           MailApp.sendEmail(emailAddress, subject, message, {
         cc: 'ablair@quilmont.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();
                
             //  }
                        
          }
        }
      }
      I fear I may have made a mistake when changing the column numbers to make the link and reference and such display in the desired columns. Perhaps something is overlapping? It's always good to have another set of eyes, Thanks!

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

      Delete
    4. Aha - I think I can see the problem
      If the folder status flag is going to go into your column K as per this bit of the script
      var folderstatus = row[10];

      Then towards the end of the script you need use 11 rather than 10 (as A=1 for this bit not A=0)

      sheet.getRange(startRow + i, 11).setValue(FOLDER_CREATED);

      Hope that helps, let me know if it doesn't fix it!

      Cheers

      James

      Delete
  26. Hi James,
    Is there a way to have the email sent to respondents come from another email other than the one the form was created under? Specifically, we are planning on using this for our writing center so students can submit papers for review. I would like the email sent to students to come from our writing center general email rather than my own email address. Is there a way to change the "from" email?

    ReplyDelete
    Replies
    1. I don't think MailApp.sendEmail allows for a different 'from' to be specified. The only way to do this I can see would be to authorise and run the scripts from the account that you want the emails to be from.

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

    ReplyDelete
  28. Hi James,

    This is so sweet! Thank you in advance! I do have a couple issues that are probably easy fixes. I have it all set up with using the pathtoemail and using the script to create subfolders within a username. I have a couple things going on.

    1. It seems that everytime a form gets submitted, the script runs through the whole spreadsheet and creates more unique folders for my already submitted users and then replaces the unique folder ID in the spread sheet to the newly created unique folder.

    2. I cant seem to get the Link column in the spreadsheet to show up to display the url. I only get the folder ID to show up in the folder column.

    Thanks again for your help. script below...


    var formURL = 'url'; // enter the 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("abcdefghijklmnop0123456789");

    // 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 timestamp = row[0]; //column A
    var supplier = row[3]; //column D
    var folderstatus = row[9]; // column J
    var emailAddress = row[2]; // seccond column (Column B)

    if (folderstatus != FOLDER_CREATED) {


    var folder, folders = DriveApp.getFoldersByName(folderName);


    /* Find the folder, create if the folder does not exist */
    if (folders.hasNext()) {
    folderpath = folders.next().createFolder(timestamp).getUrl();
    Logger.log(folderpath);


    } else {
    //create the folder
    var folderpath = DriveFolder.createFolder(folderName).addEditor(emailAddress).getUrl();

    }


    //send an email


    var message = 'Thank you for sumbitting 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";
    Logger.log(emailAddress);
    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();

    }

    }



    }

    ReplyDelete
  29. Hi James,

    I am so glad I found this site. And thank you for this write up. Works awesome, however, I do have a couple issues. I am using pathtoemail and the subfolder code...

    1. When a user submits form it seems that the script goes through my whole spreadsheet and creates another unique folder within the subfolder. It also sends out the email again to all my users already in the spreadsheet.

    2. Other issue, is that it doesn't seem to produce a clickable link for me in the spreadsheet. All I get is the unique folder ID, and that is in a different column..

    here is my script...sorry, dont know how to share the script so I copied it to a doc.
    https://docs.google.com/document/d/1cUzDcFRyMqlneSOBkPT_0snEKDwh-x4H5jb1XVOW_Ng/edit?usp=sharing

    thanks again!

    ReplyDelete
    Replies
    1. Hi Josh - please could you share with me a copy of the sheet you are using. If data is confindential please could you just remove this from the copy. I really need to see your structure to work out how to tweak the script for you. My email is James.pearson(at)nihr.ac.uk

      Delete
  30. Hi James, im getting a TypeError: Cannot call method "getSheetByName" of null. (line 8, file "Code") error even though the name is the same as the spreadsheet. both function getEditResponseUrls() and createFolder () have the same issue. please help!

    ReplyDelete
    Replies
    1. Hi - the sheet name should be the name of your sheet (tab) rather than the spreadsheet name. Does this fix the problem for you?

      Delete
  31. Hi James,

    I've tried to modify your code slightly, but for some strange reason, the folders created in my Google Drive are all named "undefined".

    The main modifications are as follows:
    - the arrayformula was causing an error whereby my data.length defaulted to 1000, so I found a workaround using sheet.getRange.setFormula to define my unique ID instead,
    - I used a copyTo function with {contentsOnly:true} to ensure that the folder name is a text string and not read as the function.

    I wonder if the error is because my unique ID is set up to have spaces in it.

    Code below; sorry it's a little messy, as I was trying to set up different sections as individual functions to test them separately.

    var formURL = "https://docs.google.com/forms/d/e/abcdefg/viewform"; // enter the url for the form
    var sheetName = 'Form responses 1'; // enter the name of the sheet
    var columnIndex = 9; //the number here is a count with A = 1, it indicates where the link should go

    // script to check data length

    function test () {
    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
    Logger.log(data.length);
    }

    // script to move values only

    function moveValuesOnly() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Form responses 1");
    var range = sheet.getDataRange()
    var last = range.getLastRow()
    var ranges = sheet.getRange(last,8)
    ranges.copyTo(sheet.getRange(last,9),{contentsOnly:true})
    }

    // 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(1); // Remove header lines

    // Gets the last row in order to input the reference formula
    var range = sheet.getDataRange()
    var last = range.getLastRow()

    // This puts the reference formula in column H
    sheet.getRange(last,8,1,1).setFormula('=((B1:B)&" "&(C1:C))')

    // something to change it from a formula to a value
    moveValuesOnly()
    SpreadsheetApp.flush()

    // Define column number unique reference. Array starts at 0.
    var CLASS = 8; //column I

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

    // 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 = 2; // First row of data to process
    var row = data[i];
    var class = data[i][CLASS];
    var folderName = class;
    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[6]; // Column G)
    var message = 'Thank you for submitting your Report - please upload the photos relating to this to the following folder ' +folderpath ;

    var subject = "Please upload photos";
    MailApp.sendEmail(emailAddress, subject, message, {
    // 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();

    }

    }
    }

    ReplyDelete
    Replies
    1. ah, i fixed it, sort of.

      I got rid of this section:

      for (var i=0; i<data.length; i++){

      var startRow = 2; // First row of data to process
      var row = data[i];
      var class = data[i][CLASS];
      var folderName = class;

      and replaced it with

      var folderName = data.getRange(last,9,1,1).getValue()

      So my script no longer uses the for loop, which I think may be an issue if ever my form data gets out of sync with the google sheet. Not an ideal workaround, but it does what I need for now.

      Delete
  32. Hi James - thanks for this article.

    I have facing two issues.

    1. function getEditResponseUrls() is not working and is giving below error.

    No item with the given ID could be found, or you do not have permission to access it. (line 10, file "Code")

    I have created form and just clicked on view button to view it and provided that link for formURL parameter in your script.

    2. Secon issue i am facing is that whenever any response is added using form, it is getting inserted in my worksheet's second row whereas it shall get inserted into 3 row because i have filter in my second row.

    ReplyDelete
    Replies
    1. Hi Kathar - I have updated the script - I think since Google brought in new forms, any forms you create using the new version will need you to use the edit link rather than the view link in your script. I have tested here with the revised code and it works fine.

      For the responses, once you have a response, insert a line above, and put your filters in, any responses will then go from line 3 downwards and leave your filters on line two as desired.

      Hope this helps :-)

      Delete
  33. You are awesome James!!!

    I could never image google form with upload button can be created!!!

    I followed your suggestion and all sorted now. Able to run the script using form edit link and also able to bring response to row 3rd.

    Thanks again for your help :)

    ReplyDelete
  34. You are awesome James!!!

    I could never image google form with upload button can be created!!!

    I followed your suggestion and all sorted now. Able to run the script using form edit link and also able to bring response to row 3rd.

    Thanks again for your help :)

    ReplyDelete
    Replies
    1. A pleasure :-) Glad all is working well

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

    ReplyDelete
  36. Hi James,

    Reference to your article ( Folders as a workaround for Google form attachments) would you please support me as after i followed the mentioned steps and run the script , i got the below errors

    TypeError: Cannot call method "getDataRange" of null. (line 13, file "Code")

    and

    TypeError: Cannot call method "getRange" of null. (line 45, file "Code")

    however i followed your steps carefully

    Thanks for your support and great article ,also sorry for any inconvenience.

    ReplyDelete
    Replies
    1. Hi motaz - check to see if your sheet name is correctly defined. If it is, its a bit difficult to see exactly where the error is occurring. Can you share your sheet perhaps?

      Delete
    2. i have a new error after fixing the last error here is the new one

      Invalid argument: permission.value (line 56, file "Code")

      Delete
  37. yes its correct and the name is (Event Registration) and here is the code



    {


    //This part of the code is what James posted on his blog
    var formURL = 'https://docs.google.com/forms/d/1IuvZAImVIJOej0JcL0PQsHVHkmqkayHCUZdBLCoHcHo/edit'; // enter the url for the form
    var sheetName = 'Event Registration'; // enter the name of the actual Google Sheet you are working in
    var columnIndex = 12; //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("Event Registration"); //name of the sheet within the spreadsheet
    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();
    }
    }
    }

    // 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("Event Registration"); //name of the sheet within the spreadsheet
    var data = ss.getDataRange() // Get all non-blank cells
    .getValues() // Get array of values
    .splice(2); // Remove header lines

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


    //This is where the code differentiates from James' post
    //Add items to the row
    var i= data.length +1 ;//find the last item in the row
    sheet.getRange(i+1, 10).setValue(i+"i"); //set the J column to the value of i for debugging. Can be changed to another appropriate column number by changing the number 10
    var row = data[i]; //set the current item to a variable called row

    var folderName = sheet.getRange(i+1,2).getValue() ; //name from column B
    sheet.getRange(i+1, 11).setValue(folderName+i);//test the folder name
    i++
    if (sheet.getRange(i,12).getValue() == "") { //the value of 12 means column L, so check column L for a URL, L may be changed to any suitable column number
    //Get the email address
    var emailAddress = sheet.getRange(i,3).getValue() ; // Column C, which is where the spreadsheet would hold the email address. Can be changed to the suitable column number where the correct info is located
    //create the folder
    var folderpath = DriveFolder.createFolder(zizo).addEditor(emailAddress).getId();
    //get the path to the folder
    var pathtoemail = "https://drive.google.com/drive/folders/"+folderpath;
    sheet.getRange(i, 12).setValue(pathtoemail);//show the folder path. The value of 12 means column L, this can be changed, but should be a direct match with the value of the column for the IF statement above.
    }else {};
    //Create the message and subject of the email
    var message = 'Thank you for submitting. Here is the link to your drive folder. Please use this folder to upload and receive related documentation. ' +pathtoemail ; //a custom message, feel free to change anything inside the quotes
    var subject = "Form Related Documentation." ;
    //send the email
    MailApp.sendEmail(emailAddress, subject, message, {
    // cc: 'email@email.com' // optional cc

    });

    }


    }


    ReplyDelete
  38. I am a very comfortable with spreadsheets myself but the script part doesn't make any sense to me. For one, your instructions began with: "To begin with, I created a Google form, and a Google sheet to collect the information." Which I also have, and my form is a little lengthy. Now I'm looking for the second step as to how or where to place the script. But I don't think there is any part in this blog page where you talk about step two. You just immediately began with all the scripts that you used. I'm sure this is very helpful to others, which I have noted in the comment section. But can you please make it helpful to someone like me who doesn't know where step two goes? Thank you so much.

    ReplyDelete
    Replies
    1. Hi - the script goes into the Google sheet. If you go into the demo sheet linked in the blog post (https://docs.google.com/spreadsheets/d/1IQu3apOHUS3ZyD33xEvuKrMFRqfCL67DW9hym8SSLLQ/edit#gid=1454573290) then go to tools-> script editor you can see the script used. For a basic guide to adding scripts to sheets, here's some information from Google https://developers.google.com/apps-script/guides/sheets Hope this helps

      Delete
    2. O gosh, that was a very quick response considering the last comment was way back October 2016.hahaha Thank you so much!

      Delete
    3. Also thank you for the link to the basic guide to scripting. :D

      Delete
  39. Hi James. Could you check if the demos are still working properly?
    Links (column H) are not generated.
    Buttons are nor displayed on the AT.
    The second demo does not generate subfolders for submissions with the same user.

    ReplyDelete
    Replies
    1. Hi Bolbo, thanks - demos seem to be fine. here's a quick AT based of the first https://awesome-table.com/-KtCBJ1jIMF1mkxJHfPk/view - (NB there is dummy data here, so the links to edit and folder dont go to a valid ID) If your buttons are not displaying, ensure you have have referenced the template - e.g Template!A1:C2 in the field Template range in the awesome table setup.

      Delete
  40. Thanks! I think I had not used the edit form link but the view one. Now buttons on the AT are visible.

    Please, can you see why your demo from 17 march 2016 does not generate time-stamped subfolders for existing users?

    ReplyDelete
    Replies
    1. Hi Bolbo - I have made a copy of the demo from 17th March and it did generate a timestamped subfolder OK here. All I needed to change in the code was the URL of the form (these days the code needs the edit URL rather than the view URL to work) The code created me a folder under my name, and when I next did this, the code created a subfolder under that, with the timestamp as the name.

      Delete
    2. It does not work for me.. I cannot open the script editor (the sheet is on view mode) perhaps the code link you provided is not the same as the one within the spreadheet?

      Delete
    3. Hi Bolbo, you need to take a copy of the sheet, you can then use the script editor and change the form edit url section to the URL of the form that will be generated with the copy, and change the google drive folder ID, and it should be good to go

      Delete