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).
 

53 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
    2. Is it possible to do the same thing but with content manager rights?

      Thanks

      Delete
    3. Hi JC

      This script is suited for 'My Drive' as opposed to 'Shared Drive' - hence it's not quite a quick alteration I'm afraid, as the Drive API would need to be used and the process of adding permissions re-written. There is a bit of an example here: https://www.pbainbridge.co.uk/2021/06/set-permissions-on-shared-drive.html

      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
    2. Hi Phil, your script works great. However, I am having the same requirement of not intimating the users when they are given the writer access. Is there is an easy way to make some change in your script to make it work? Many thanks,
      Zen.

      Delete
    3. Hi Zen

      Yes, as linked above it is possible with the Drive API and whilst I have an example of how to use this, I don't have a version here with it built in I'm afraid.

      Kind regards
      Phil

      Delete
  13. This is so helpful! is there a way to change the script so that each folder doesn't have a - after? My folders are just one word, but when I run the script with either just the first "Folder Name" column filled in, or deleting the second 'Folder Name" column, the folders are created corrected, just with a dash (-) after. Is there a way to fix this? If the folder is called "Test" it shows up as "Test - " in Google Drive

    ReplyDelete
    Replies
    1. Hi

      Yes this is possible so you can just use the first Folder Name column in the Sheet:

      1) Make a copy of the file from the Blog and open it.
      2) Go to 'Tools' and then 'Script editor'.
      3) On the left ensure '2) createFolders.gs' is selected.
      4) Go to line 40 in the main window and change "var folderName = folderNameP1 + ' - ' + folderNameP2;" to "var folderName = folderNameP1;"
      5) Click the Save project icon near the top of the screen, then close the Script editor.

      Now you can use just the one column without the unwanted hyphen appearing in the name.

      Kind regards
      Phil

      Delete
  14. Hi! I was running a script in google script wherein it auto creates subfolders inside a masterfolder and I ran into a roadblock. Whenever the script has completed, there are also subfolders created inside a different master folder. This only happens in 1 exact master folder. For example, I created folder "New" and ran a script so subfolders would auto populate inside the "New" folder. Once it has completed, new subfolders are also auto populated inside "Existing" folder. Can you help me with this, please?

    ReplyDelete
    Replies
    1. Hi Megan

      It sounds like you may have something in your script that is picking up the 'existing' folder (probably to create your 'new' folder) and that variable is making its way into the rest of your code to create all of the other folders.

      Kind regards
      Phil

      Delete
  15. hi phil,

    this is tremendous. exactly what i need. how can i add this functionality to an existing sheet? that is, i have a sheet that i use each week for a large mail merge, and i'd like to incorporate this functionality into it, so that i don't have to import my data into the "bulk create..." sheet to make my folders, and then again into my mail merge sheet. i tried copying and pasting everything into the script editor on my other sheet, but a test run returned an error: "TypeError: Cannot read property 'getDataRange' of null", so obviously i've missed something. is this an easy fix?

    thank you!
    brian

    ReplyDelete
    Replies
    1. Hi Brian

      Thank you very much!

      So with Apps Script everything has to be in a specific place - each row, column, the Config sheet, etc all line up with what the code expects to find when it runs.

      You would need to go through line-by-line and match up/change what I have setup here with how your Sheet looks, in order to mould it around what you have.

      Kind regards
      Phil

      Delete
    2. thanks phil! appreciate the guidance. happy new year!

      Delete
  16. Hey Phil,

    First of all I love this script, I really appreciate you sharing this with all of us!
    I was wondering, if I am making the same folder but with the same sub-folders, it's about 5 subfolders each time.
    How can I add this?

    ReplyDelete
    Replies
    1. Thank you!

      Firstly you may find this blog post more applicable: https://www.pbainbridge.co.uk/2021/05/bulk-create-google-drive-folders-with.html

      It does however only create 1 extra sub-folder so you would need to modify it for more by updating the Apps Script code and potentially adding more columns in the Sheet.

      Kind regards
      Phil

      Delete
  17. Phil, this is so useful, thanks!

    Could it be adapted to create bulk copies of single files, say a pdf or google docs file, and give each their own unique file name, and then place them in a their own newly created sub-folder on Drive...?

    If it could that would be amazingly helpful for processing my admin tasks for my training projects.

    Thanks in advance!

    Kind regards

    Adrian

    ReplyDelete
    Replies
    1. Hi Adrian

      Thank you!

      Are you referring to something like this: https://www.pbainbridge.co.uk/2021/11/bulk-create-google-drive-folders-and.html

      Kind regards
      Phil

      Delete
  18. I'm getting "Exception: Cannot convert " to int." in message details.

    ReplyDelete
    Replies
    1. Hi

      On what line? There is a 'Log' sheet to help show the errors - is that all it says in there?

      Kind regards
      Phil

      Delete
  19. Phil,
    This has the potential to save me a ton of time, but I get this error every time:

    'createFolder' Function error
    Unable to get Parent folder: Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it.

    I have tried several different folder IDs from my own Drive as well as a couple of our Shared Drives, but I always get the same error. I'm the tech admin at my school, so I have access to pretty much everything. Any insights you could offer would be much appreciated.

    ReplyDelete
    Replies
    1. Hi

      A few things to check:
      1) Make sure you are only getting the Folder ID from the URL when you paste it into the Config Sheet - no other extra bits.
      2) Make sure you are not logged into any other Google Accounts at the same time.

      If you still have a problem feel free to use the Contact Form to share a copy of your Google Sheet with me so that I can see what you are entering.

      Kind regards
      Phil

      Delete
    2. Hi! I'm running this and receive the error "Exception: CAnnot convert " to int." When I click on details, it says the same thing, and when I go to Log it states

      "Script has started"
      "Add Permissions option is: Yes"

      I've tried removing all emails from Column C on Data tab and updating Config to state no emails were provided, and I get the same error (but the 2nd item on log is "Add Permissions option is: No").

      Can you assist?

      Delete
    3. Hi Laurin

      If you're happy you have filled in the relevant cells like in the video, then I suggest you use the Contact Form on my blog to share a copy of your Google Sheet with me so that I can see what information you are entering, with a view to hopefully spotting the error.

      Kind regards
      Phil

      Delete
  20. Hi, Thanks for sharing. It's work

    ReplyDelete
  21. This is a fantastic script. Thanks for your work on this!

    I'm looking for an idea on a slight tweak. I am currently importing names from a Google Sheet that generates new names based on Google Form submissions. I am using =importrange to sync data from that separate spreadsheet into a copy of the spreadsheet you made.

    I'd like each new form submission to automatically create a new folder. I have set up permissions via Google Drive for the master folder. I'm just looking to modify the script so it automatically generates a new folder when information comes into Column 1 and that it will stop creating new folders once it runs into an empty column A.

    ReplyDelete
    Replies
    1. Thank you Ifranco

      So are you just looking for something that creates the folder on a Form submit (this will help: https://www.pbainbridge.co.uk/2020/05/create-google-doc-from-form-submission.html)? Or are you wanting to try and combine it with something else happening?

      Kind regards
      Phil

      Delete