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

5 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