Making multiple copies in Google Drive is a slow, boring task. You select the file you want, make a copy, then have to rename it, then repeat this again and again. Here you’ll learn how to make multiple copies of a file and name them individually, really fast using Apps Script.
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 file name want, i.e. The part that is the same in all the copies of the file.
In column B, we type the variable parts of the file name, i.e. The parts we want to be unique in each file.
Finally, in cell C3 we paste the full URL of the file we want to copy.
We then choose Make Multiple Files from the Creator menu.
Here are the files it made, all individually named:
Obviously, it works best when there are a lot of files to make.
Let’s go through the code step-by-step:
Line 1: First, we set up our function, here I’ve called it makeFiles().
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 take away 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 fileUrl.
Line 17: Then, I check if the variable fileUrl 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, a king 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 fileUrl isn’t blank. If they both aren’t empty it runs the following code.
Line 25: Now, we need the file ID from the file URL, in order to make copies of the file. We use the match() method (more info here) and use some regular expressions to extract just the key 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 fileId, to get the file by its ID, using the DriveApp class and the getFileById() method, which basically, gets the file we want to copy.
Now, we finally get to the point where we make the multiple copies. 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 makes a copy of the file we want. We use the fileId stored in the variable fileToCopy we created in line 27. Then, use the makeCopy() 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:
This code can of course be modified to suit your needs, as for example, you may want to let users create files without a fixed part to the file name or maybe without any variable names so all the files are named the same.
Here’s the complete code:
The regular expression in line 25 is from here:
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” - iBooks store / Kindle store
- "Beginner's Guide to Google Sheets" - iBooks store / Kindle store
- "Beginner's Guide to Google Docs" - iBooks Store / Kindle store
- "Beginner's Guide to Google Slides" - iBooks Store / Kindle store
- "Google Sheet Functions - A step-by-step guide" - iBooks Store / Kindle Store