Tuesday, 23 June 2020

Bulk create Google Drive folders 2.0

The following Google Apps Script code was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It is an improved version of the tool built in April 2019 (original blog link here).

Improvements that have been made include:
  • A Log sheet to support troubleshooting problems.
  • More use of 'try/catch' to handle errors.
  • Adding permissions is optional.
  • Some of the column numbers can be tweaked in the Config sheet rather than diving into the code.
1) getSpreadsheet.gs
This Function gets data from the Google Sheet that is then used throughout the rest of the script:
// get current spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();


// get TimeZone
var timeZone = ss.getSpreadsheetTimeZone();


// get Parent Google Folder ID for storing created Folders in
var destinationFolderId = configSheet.getRange(1, 2).getValue();
2) createFolders.gs
This Function loops through each row and initiates the creation of a Google Drive Folder and the necessary permissions (if applicable). It checks to make sure a folder link does not already exist before proceeding (so nothing is overwritten):
// get last Row number
var lastRow = dataSheet.getLastRow();


// create a name for the new Folder
var folderName = folderNameP1 + ' - ' + folderNameP2;


// display Toast notification
ss.toast(folderName, 'Creating Folder');


// set Folder Link cell using new Folder Url
var newFolderLink = '=HYPERLINK("' + newFolderUrl + '","' + folderName + '")';
dataSheet.getRange(i+1, folderLinkCol).setFormula(newFolderLink);
3) createFolder.gs
This Function creats a new Google Drive Folder and returns its Url/Id:
try {
    // get destination Folder
    var destinationFolder = DriveApp.getFolderById(destinationFolderId);
}
catch(e) {
    logEvent('Error getting destination folder: ' + e + e.stack);
    var destinationFolder = false;
}


// create new Folder in destination
var newFolder = destinationFolder.createFolder(folderName);

     
// get new Drive Folder Url/Id and return to Parent Function
var newFolderUrl = newFolder.getUrl();
var newFolderId = newFolder.getId();
4) addPermissions.gs
This Function adds 'Edit' permission to each Folder from the provided email address(es) in the spreadsheet:
// split up email address array to be able to loop through them separately
var emailAddresses = permissionEmail.split(', ');


try {
    // add 'Edit' permission using email address
    newFolder.addEditor(emailAddress);
    var addEditor = true;
}
catch(e) {
    logEvent('Error adding Editor: ' + e + e.stack);
    var addEditor = false;
}


if (addEditor) {
    // write timestamp into 'Permission Added?' cell
    var date = new Date;
    var timeStamp = Utilities.formatDate(date, timeZone, "dd/MM/yy @ HH:mm:ss");
    dataSheet.getRange(currentRow, permAddedCol).setValue(timeStamp);
}
logEvent.gs
This Function is used to output information to the Log sheet and can be called from anywhere else in the script:
// get the user running the script
var theUser = Session.getActiveUser().getEmail();


// create array of data for pasting into log sheet
var logData = [niceDateTime, theUser, action];

 
// append details into next row of log sheet
logSheet.appendRow(logData);
onOpen.gs
This Function creates a menu item to run the tool:
SpreadsheetApp.getUi()
.createMenu('Admin')
.addItem('Create folders', 'getSpreadsheetData')
.addToUi();


Download
Bulk create Google Drive folders 2.0 download (please use 'File' > 'Make a copy' for your own version).
 

28 comments:

  1. Hi,
    Thank you for sharing this, the log is definitely good to understand the problems.

    I have an error after running 1 getspreadsheets and then 2) Create Folders
    the bug is starting at line 9 for each properties of function createFolders(spreadsheetData) {

    // extract data from name:value pair array
    var ss = spreadsheetData['ss'];
    etc
    TypeError: Cannot read property 'ss' of undefined (line 9, file "2) createFolders")
    Dismiss

    and it is the same for other properties.

    do you have an idea on how to resolve it ?

    thank you
    Francois

    ReplyDelete
  2. Hi François

    You must run the code from within the Google Sheet (not the Script Editor). Use the menu item under 'Admin' to do so.

    Thanks
    Phil

    ReplyDelete
  3. I am using your sheet and it works great! I am looking to make a slight modification.

    1. I would like to set share permissions at a later date, so I try to re-run the script. The result is that it creates a second folder with the exact same name (which I didn't figure was possible). Is there a way to modify the script so if the same folder name exists, it just modifies the permission on the existing folder with the same name?

    Thanks for any assistance on this.

    ReplyDelete
    Replies
    1. Hi

      Thanks for the feedback.

      The script is designed to run in one single go - either with permissions added or not. It will not create a duplicate folder unless you clear out the Folder Links in column D. I'm afraid it is not possible to run the 'addPermissions' Function separately at a later date.

      I understand exactly what you would like but it's not so much a slight modification as an entirely different script to be able to run standalone. The closest I can point you at this current time is my blog post on changing ownership of items in Google Drive (https://www.pbainbridge.co.uk/2020/04/bulk-change-ownership-of-google-drive.html).

      Maybe you could tweak this to '.addEditor()' instead.

      Kind regards
      Phil

      Delete
  4. Good Morning. I hope you can provide assistance for your script. We are trying to run the script in a Domain setting for a junior high school and the script begins to create the folders...it hangs and throws an error:

    "Error adding Editor: Exception: Invalid argument: permission.valueException: Invalid argument: permission.value
    at addPermissions (4) addPermissions:37:19)
    at createFolders (2) createFolders:66:36)
    at getSpreadsheetData (1) getSpreadsheet:54:27)
    at __GS_INTERNAL_top_function_call__.gs:1:8"

    ...after the first folder is created. We are running into a problem because we don't have EDITOR as a choice in our folder permissions. We have Viewer, Commenter, Contributor, Content Manager and Manager. I would like to try and edit your script, but I don't think I should just change EDITOR to CONTRIBUTOR throughout the script...but would that work? Can you provide any help with this? We'd love to get this up and running :)

    ReplyDelete
    Replies
    1. Hhhhmmmmm I'm not sure what is going on here. It sounds like you are using 'Shared Drive' rather than 'My Drive' (the latter being what I created the tool in). I have just migrated this to a Shared Drive however and it worked perfectly fine for me - translating 'Editor' to 'Contributor' without me needing to change any code.

      Does it actually work for the first folder and break on the second? Or not work at all?

      I assume you're aware that sharing folders in Shared Drive is only a very recent ability: https://gsuiteupdates.googleblog.com/2020/09/share-folders-shared-drives-ga.html

      Kind regards
      Phil

      Delete
    2. Good morning again Phil. Yes we're aware its a recent release. What we ended up doing was selecting NO for the emails so it would create all of the folders but not assign permissions, then we went in and assigned permissions manually. On second thought, I also believe I also translated "edit" to "contribute" and that may have been the issue.

      Appreciate you replying. Would love any additional insights, tips or tricks you have as we've also been recently migrated over to these shared folders and they're causing headaches all over our school building :)

      Delete
    3. Hey

      No worries, glad you managed to get something sorted.

      We strongly encourage people to give blanket permissions on Shared Drives (as opposed to My Drive) in Google to reduce the burden of changing lots of access rights. At the end of the day a team should need mutual access to their project documents, then just have another Shared Drive for another project with other colleagues. Though I'll admit it's not always that straight forward in reality.

      Hoping to post a tool for bulk syncing Google Group users from a Google Sheet soon, just been a manic few months unfortunately.

      Thank you for your feedback/contribution.
      Phil

      Delete
  5. Hi Phil, I configured my sheet the same as yours and run the script and keep getting this error "Script function not found: crtGdriveFolder". I put the folder I wanted created in the first column of the Data Sheet and then the name of the Subfolder in the second column in the Data Sheet and put No in the Email Address for permission, Folder Link, and Permission Added column

    ReplyDelete
    Replies
    1. Hi Mitchell

      Did you do this on the 'Config' sheet? This needs completing before adding items to the 'Data' sheet.

      Kind regards
      Phil

      Delete
  6. Hi Phil, nice script, works really well. Do you think it would be feasible to use this to create subfolders within the newly created folders? So, changing the Google Parent Folder ID from line to line? Thanks, Andy

    ReplyDelete
    Replies
    1. Hi Andy

      Thank you. Yes it absolutely would be possible. Have you seen my blog post on creating test folders/files in Google Drive (https://www.pbainbridge.co.uk/2020/02/bulk-create-test-foldersfiles-in-google.html)? This shows how you have the loops within loops to pass the Parent IDs into.

      Kind regards
      Phil

      Delete
    2. aah ok, with the loops is a good idea. I had a look myself and adjusted the script on this page. I added a new column where you can choose which of the already created folders should act as the parent folder for that line. Works well and allows you to create as many sub-levels as necessary. Let me know if you want to see it.. Thanks for the quick response! Rgds, Andy

      Delete
    3. Hi Andy

      Awesome! I'd love to take a look at what you've got sorted - do you want to use the 'Contact Form' on the website to get in touch directly with a link?

      Kind regards
      Phil

      Delete
    4. Hey guys I was wondering if you could share how to create the subfolders with this method, I am looking to bulk create folders and have the same subfolders in every folder i create. thanks!

      Delete
    5. Hi

      I've got a blog post scheduled for 04/05/2021 @ 9am which will do this.

      Kind regards
      Phil

      Delete
  7. Does this work using a shared team drive? I have it working on my own drive within my school system, but we're looking at needing to make folders for each student and they are going to be in a shared google team drive.

    Thanks

    ReplyDelete
    Replies
    1. Hi Dan

      Yes, looking back through my previous comments above, it looks like I've tried this on a Shared Drive successfully.

      Kind regards
      Phil

      Delete
  8. Thanks for sharing what I absolutely need.

    I have one additional question.

    How can I make it work automatically using a trigger when a new row is added?

    Thanks
    Yunseong

    ReplyDelete
    Replies
    1. Hi Yunseong

      One way would be to create a Time-driven Trigger (https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers) to run as frequently as you require. Though I appreciate it won't run immediately after a new row has been added (but you could set it hourly for instance).

      The code already loops through each row and ignores any where a Folder link exists, so it would just do this each time your Trigger is set to run.

      Kind regards
      Phil

      Delete
  9. Thank you for sharing this script. It was a HUGE help. It worked perfectly.

    ReplyDelete
  10. Thank you for this script - its brilliant! How difficult would it be to add in two further features - and could you offer any pointers for a newbie please?
    1. After creating a folder, upload a document to it
    2. After uploading said document, e-mail the users who are in the permissions list to say that the folder has been created and send them a link to the folder.

    Any pointers would be really appreciated.

    Many thanks in advance,
    James

    ReplyDelete
    Replies
    1. Hi James

      Thank you for the nice feedback.

      1) Bulk creating folders and uploading file(s) will be coming towards the end of this month. Feel free to subscribe to the blog if you want to be notified as soon as it appears.
      2) Yes this would be possible - one way would be to simply use the 'sendEmail' method (https://developers.google.com/apps-script/reference/mail/mail-app#sendemailrecipient,-subject,-body,-options) in the code after adding permissions. So that would be say after Line 263 in my example above, where the script is iterating through each email address anyway.

      Kind regards
      Phil

      Delete
  11. Great tool!!! I got a question, if you can answer me would be awesome! I've used your amazing script to distribute a personal folder to each of my students, but I need give them only viewer permission not editor one. Can I modify this option in the template? May be in the number 5 column?

    ReplyDelete
    Replies
    1. Hi Mauricio

      Thank you!

      Yes that would be feasible: in the downloaded file you will need to go to the 'Tools' menu and then 'Script Editor' to open up where the Apps Script code is stored.
      In the left menu click '4) addPermissions.gs' and scroll down to Line 37. You need to replace 'addEditor' with 'addViewer'.
      Click the 'Save project' icon along the top of the screen and then close that page down. Now you can run the tool again, only adding 'Viewer' access.

      For reference, in the above GitHub code for those who are using that, it would be Line 263 to make the change.

      Kind regards
      Phil

      Delete
  12. Hi Phil
    This is fantastic! Thank you! Thank you! Thank you!
    I'm wondering if there is a way to disable notifications to people who have been granted permissions?
    Thanks in advance
    Josh

    ReplyDelete
    Replies
    1. Hi Josh

      Many thanks for the comment.

      Whilst I have not incorporated it here, I do have this Blog post that uses the Drive API to share a file without sending the email notification: https://www.pbainbridge.co.uk/2020/04/drive-api-share-file-without-email.html

      It should be feasible to incorporate it into this tool.

      Kind regards
      Phil

      Delete