MULTIPLE FOLDER MAKER
Making multiple copies of folders in Google Drive is a slow, boring task. You select the folder you want, make a copy, then have to rename it, then repeat this again and again. Here you’ll learn how to create multiple folders and name them individually, really fast using Apps Script.
If you’ve already read my post “Multiple FILE maker”, then this code is essentially the same, just with two main differences, we use the methods getFolderbyId() and we use createFolder(). So, if you read it, you may want to skip through and just note the differences.
First, we have a Sheet where we will enter the details. There are three parts.
In cell A3 we type the fixed part of the folder name want, i.e. The part that is the same in all the newly-created folders.
In column B, we type the variable parts of the folder name, i.e. The parts we want to be unique in each folder.
Finally, in cell C3 we paste the full URL of the folder we want to put the new folders into.
We then choose ‘Make Multiple Folders’ from the Creator menu.
Here are the folders it made, all individually named:
Obviously, it works best when there are a lot of folders to make.
THE CODE
Let’s go through the code step-by-step:
Line 1: First, we set up our function, here I’ve called it makeFolders().
Line 4: Then, we get the current active spreadsheet, using getActiveSpreadsheet().
Line 5: Then, we get the active sheet, using getActiveSheet().
Line 6: Now, let’s get the fixed name. It’s in row 3, column 1 (I.e. A3), so in the getRange() method we add (3,1), then get its value using getValue().
Line 7: Now, we need the variable names. First, we need to get all the cells in the second column (B), from row 3 to the bottom row. We need to find out where the bottom row is, so here I’ve used getMaxRows() which gets the last row on the sheet, whether it has any values or not. I subtract 2, as we will be starting from row 3, so need to discount the first 2 rows.
Line 8: We use getRange() again, but this time we’re getting a series of values, so we need to tell it where to start and where to end. In the brackets we state: starting row, starting column, number of rows, number of columns. So, here we’re starting in row 3, column 2, down to the bottom row using the bottomRow variable, and we only want 1 column. Then we get all the values with getValues(), which will store them in the variableNames array variable.
Lines 10 to 22, are ways to check the user has entered information into the relevant cells. The program could work without them, but often it’s important to include some kind of checks to deal with users doing things that they shouldn’t, as invariably, they will always do something, that’s not expected or what in your mind wasn’t logical! Here I’m going to use some simple if statements to check that they’ve filled in the sheet correctly before running the program.
Line 11: First, I want to check that they’ve entered something into the fixed name cell. So, I’ve used an if statement to check if the variable fixedName is blank, using the double speech marks.
Line 12: If it is blank, I want it to show an alert message, telling the user it’s blank. To do so, we use getUI() and the type of message is an alert. Then in the brackets we add the text we want to display. Line 13 closes the if statement.
Line 16: Here I get the URL from cell C3 (row 3, column 3) and store it in the variable folderUrl.
Line 17: Then, I check if the variable folderUrl is empty.
Line 18: if it is empty, as before, I want to display an alert.
Now, I want to check that both fixedName AND fileUrl are NOT empty. If they aren’t empty it will run the rest of the code and make the copies, if one is empty, it won’t run the code. This means that if one of the alerts is shown above, it won’t run any code afterwards, making sure no copies are made by mistake.
Line 22: Here we first check if the fixedName isn’t equal to a blank. Then, use the double ampersands, which mean AND, to also check if the folderUrl isn’t blank. If they both aren’t empty it runs the following code.
Line 25: Now, we need the folder ID from the folder URL, in order to create folders in that folder. We use the match() method (more info here) and use some regex to extract just the ID part. Don’t worry about how this works exactly, but if you’re interested in the use of regular expressions, go to this page.
Line 27: Now, we use that ID stored in folderId, to get the folder by its ID, using the DriveApp class and the getFolderById() method, which basically, gets the folder we want to create folders in, then stores it in the variable getRootFolderId.
Now, we finally get to the point where we make the multiple folders. We’ll use a simple for loop for this. We want it to loop down column B making copies as it goes down, but then stop when it reaches an empty cell in that column, i.e. The end of the list.
Line 31: I’ve set up a counter variable called n, and we’ll keep going down until we hit the bottom row.
Line 32: But to prevent it making files with no variable names, I’ve included an if statement to check to see if the current variable name is blank, using variableNames and the current array number in the square bracket. Remember this is looping down the array variable variableNames, so it’s going from zero to potentially the bottom row. If it does find a blank it breaks out of the loop, i.e. It doesn’t continue going through the array. Also, note the use of a double equals sign, not a triple one, as we are comparing an array value with something empty and they are not the same type, so a triple equals sign will fail.
Line 34: This is the line that creates the folders we want. We use the folder ID stored in the variable getRootFolderId we created in line 27. Then, use the createFolder() method and in the brackets state the name we want. Here, we’ll going to join the fixed name with the variable name). Line 35 closes the loop.
Line 36: Finally, just to tell the user the process has finished, I’ve added a toast message, which will pop up in the bottom right-hand corner, when the program has finished. Here it frees to the active Spreadsheet ss, then in the brackets contains 3 parts: message, title, number of seconds it will be displayed.
Line 37 and 38: This closes the if statement from line 22, and the function.
ADDING A MENU
To run the program from a menu in the spreadsheet, I’ve also added a separate script file called onOpen, which will add a little menu to run the code.
Line 1: Call the function onOpen(), so that it opens automatically every time the spreadsheet is opened.
Line 2: Get the active spreadsheet, note I’ve left a comma at the end not a semi-colon, as the next 2 lines are connected to it.
Lines 3-4: Set up a variable called menuItems to add the menu details. The keyword var isn’t needed here as it using the one from line 2. Then we set up an array with the sub-menu name and the function it will run. Carefully note the syntax used here.
Line 5: Now, we add the menu to the spreadsheet using the addMenu() method to the active spreadsheet. In the brackets, we include 2 parts: menu name, and the variable menuItems.
Here’s the link to the Sheet and code.
Here’s the complete code:
The r
No hay comentarios:
Publicar un comentario