Pages

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();

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

11 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