I have this script I found online here that creates a folder on google drive from my google sheet and one sub folder. I know it should be possible to add multiple sub folders but I am completely stuck.
Sample sheet: https://docs.google.com/spreadsheets/d/1m4DNKXF3DGL227YFlT9eCtT7g2Nz27EfKMYiSJ1Opl4/edit#gid=0
Code:
// Log starting of the script
logEvent('Script finished');
}
/*
This Function loops through each row and initiates the creation of a Google Drive Folder
along with a sub-folder.
Permissions are added if applicable.
*/
function createFolders(spreadsheetData) {
// extract data from name:value pair array
var ss = spreadsheetData['ss'];
var timeZone = spreadsheetData['timeZone'];
var dataSheet = spreadsheetData['dataSheet'];
var destinationFolderId = spreadsheetData['destinationFolderId'];
var permissionFlag = spreadsheetData['permissionFlag'];
var folderLinkCol = spreadsheetData['folderLinkCol'];
var permAddedCol = spreadsheetData['permAddedCol'];
var data = spreadsheetData['data'];
// get last Row number
var lastRow = dataSheet.getLastRow();
// start of loop to go through each row in turn ********************************
for (var i = 1; i < lastRow; i ) {
// extract values from row of data so easier to work with
var folderNameP1 = data[i][0];
Logger.log('folderNameP1 is: ' folderNameP1);
var folderNameP2 = data[i][1];
Logger.log('folderNameP2 is: ' folderNameP2);
var subFolderName = data[i][2];
Logger.log('subFolderName is: ' subFolderName);
var permissionEmail = data[i][3];
Logger.log('permissionEmail is: ' permissionEmail);
var folderLink = data[i][4];
Logger.log('folderLink is: ' folderLink);
// check Folder Link column is empty before proceeding, so no existing Folder
if (folderLink == '') {
// create a name for the new Parent Folder
var folderName = folderNameP1 ' - ' folderNameP2;
Logger.log('Folder name is: ' folderName);
// display Toast notification
ss.toast(folderName, 'Creating Folders');
// run Function to create Parent Folder and sub-folder
var folderDetails = createFolder(folderName, destinationFolderId, subFolderName);
// check new Folders created successfully
if (folderDetails) {
// extract Url/Ids
var newFolderUrl = folderDetails['newFolderUrl'];
var newFolderId = folderDetails['newFolderId'];
var subFolderID = folderDetails['subFolderID'];
// set Folder Link cell using new Parent Folder Url
var newFolderLink = '=HYPERLINK("' newFolderUrl '","' folderName '")';
dataSheet.getRange(i 1, folderLinkCol).setFormula(newFolderLink);
// check if Permissions need adding - set in 'Config' sheet
if (permissionFlag == 'Yes') {
// run Function to add Folder permissions
var currentRow = i 1;
var addPermissionsFlag = addPermissions(timeZone, dataSheet, permissionEmail,
newFolderId, subFolderID, currentRow, permAddedCol);
// if problem adding Permissions return for status message
if (addPermissionsFlag == false) {
// display Toast notification and return false flag
ss.toast('Problem adding Permissions to: ' folderName, 'Error');
return false;
}
else {
// no problem adding permissions
}
}
else {
// do nothing as permissions not required
}
// flush spreadsheet to update each row as completed
SpreadsheetApp.flush();
}
else {
// write error into 'Permission Added?' cell and return false value
dataSheet.getRange(i 1, folderLinkCol).setValue('Error creating folder. Please see Logs');
// new Folder not created successfully
return false;
}
}
else {
// Folder Link column not empty so do nothing
}
}// end of loop to go through each row in turn **********************************
// completed successfully
return true;
}
/*
Function to create new Google Drive Folder and return its Url/Id.
*/
function createFolder(folderName, destinationFolderId, subFolderName) {
try {
// get destination Folder
var destinationFolder = DriveApp.getFolderById(destinationFolderId);
}
catch (e) {
logEvent('Error getting destination folder: ' e e.stack);
var destinationFolder = false;
}
// proceed if successfully got destination folder
if (destinationFolder) {
try {
// create new Folder in destination
var newFolder = destinationFolder.createFolder(folderName);
// get new Drive Folder Url/Id
var newFolderUrl = newFolder.getUrl();
var newFolderId = newFolder.getId();
// create sub-folder and get its Id
var subFolderID = DriveApp.getFolderById(newFolderId).createFolder(subFolderName).getId();
// return Folder details for logging in Sheet and adding permissions
var folderDetails = { newFolderUrl: newFolderUrl, newFolderId: newFolderId, subFolderID: subFolderID };
return folderDetails;
}
catch (e) {
logEvent('Error creating new Folders: ' e e.stack);
return false;
}
}
else {
// return false as unable to get destination folder
return false;
}
}
/*
Function to add 'Edit' permission to each Folder from the provided
email address(es).
*/
function addPermissions(timeZone, dataSheet, permissionEmail, newFolderId, subFolderID, currentRow, permAddedCol) {
// split up email address array to be able to loop through them separately
var emailAddresses = permissionEmail.split(', ');
Logger.log('emailAddresses array is: ' emailAddresses);
// get length of array for loop
var emailAddressesLength = emailAddresses.length;
try {
// get Google Drive Folder
var newFolder = DriveApp.getFolderById(newFolderId);
var newSubFolder = DriveApp.getFolderById(subFolderID);
}
catch (e) {
logEvent('Error getting destination folder: ' e e.stack);
var newFolder = false;
}
// proceed if successfully got destination folder
if ((newFolder) && (newSubFolder)) {
// loop through each email address and add as 'Editor' *******************
for (var i = 0; i < emailAddressesLength; i ) {
var emailAddress = emailAddresses[i];
Logger.log('emailAddress for adding permission is: ' emailAddress);
try {
// add 'Edit' permission using email address
newFolder.addEditor(emailAddress);
newSubFolder.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);
}
else {
// write error into 'Permission Added?' cell and return false value
dataSheet.getRange(currentRow, permAddedCol).setValue('Error adding Editor. Please see Logs');
return false;
}
}
// loop through each email address and add as 'Editor' *******************
}
else {
// write error into 'Permission Added?' cell and return false value
dataSheet.getRange(currentRow, permAddedCol).setValue('Error getting folder. Please see Logs');
// return false as unable to get Google Drive Folder
return false;
}
// return true as all permissions added successfully
return true;
}
/*
Function to output messages to the 'Log' sheet.
Can be called anywhere else in script.
*/
function logEvent(action) {
// get the user running the script
var theUser = Session.getActiveUser().getEmail();
// get the relevant spreadsheet to output log details
var ss = SpreadsheetApp.getActiveSpreadsheet();
var logSheet = ss.getSheetByName('Log');
// create and format a timestamp
var dateTime = new Date();
var timeZone = ss.getSpreadsheetTimeZone();
var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");
// 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);
}
/*
This Function creates a menu item to run this script.
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Admin')
.addItem('Create folders', 'getSpreadsheetData') // label for menu item, name of function to run.
.addToUi();
}
CodePudding user response：
If you check the line here that your script has. What it does is it gets the folder id of the folder that was just created newFolderId. Then it creates a new folder under that with the name of subFolderName.
// create sub-folder and get its Id
var subFolderID = DriveApp.getFolderById(newFolderId).createFolder(subFolderName).getId();
The easiest thing to do would be just to copy that line and create a second folder.
// create sub-folder and get its Id
var subFolderID = DriveApp.getFolderById(newFolderId).createFolder(subFolderName2).getId();
CodePudding user response：
For creating multiple subfolders inside of one folder, you need to get the parent folder instance of Folder, and create the desired folders inside of it. In shorthand:
function createFolderAndSubfolders() {
var parentFolder = DriveApp.createFolder('Folder With Subfolders')
var subFolderNames = ["Test1", "Test2", "Test3"]
subFolderNames.forEach(subfolder => parentFolder.createFolder(subfolder))
}
There are several parts of the code you refer to that point to the creation of the subfolder.
// Getting the name from a Sheets
var subFolderName = data[i][2];
// Creating the folder
var folderDetails = createFolder(folderName, destinationFolderId, subFolderName);
// Creating the Subfolder
var subFolderID = DriveApp.getFolderById(newFolderId).createFolder(subFolderName).getId();
// Returning the info
var folderDetails = { newFolderUrl: newFolderUrl, newFolderId: newFolderId, subFolderID: subFolderID };
As @DalmTo says, a quick approach would be to call the function in charge of creating the subfolder twice. You could also create a small loop to add the desired subfolders.
I leave you how it would be using a small loop to create 3 subfolders.
var subFoldersIDs = []
for (let i = 0; i < 3; i ) {
var subFolderId = DriveApp.getFolderById(newFolderId).createFolder(subFolderName `-${i}`).getId()
subFolderIDs.push(subFolderId)
}
var folderDetails = { newFolderUrl: newFolderUrl, newFolderId: newFolderId, subFolderIDs: subFolderIDs };
Note 1 : Your code uses this information to perform other tasks, those parts of the code should also be accommodated for the correct functioning of the script.
Note 2: On the page you refer to, there is a question with the same content as yours.