Multiple FILE Maker

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 “1-Make multiple files” from the “Files & Folders” menu.

Here are the files it made, all individually named:

Obviously, it works best when there are a lot of files to make.

THE CODE

Let’s go through the code step-by-step.

1. //Get Files sheet, get fixed name, get variable names
2. //Extract ID from file URL, get master file
3. //Make copy of master with fixed and variable names
4. function makeFiles() {
5.   //Get data on Files sheet
6.   const ss = SpreadsheetApp.getActiveSpreadsheet();
7.   const shFiles = ss.getSheetByName("Files");
8.   const fixedName = shFiles.getRange(3, 1).getValue();

Line 4: First, we set up our function. Here I’ve called it makeFiles().

Line 6: Then, we get the current active spreadsheet, using getActiveSpreadsheet() and store it in the variable ss.

Line 7: Next, we get the sheet called “Files” and store that in the variable shFiles.

Line 8: 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().

  9. let variableNames = shFiles.getRange(1, 2)
  10.  .getDataRegion(SpreadsheetApp.Dimension.ROWS)
  11.  .getValues();
  12. variableNames.splice(0, 2);

Line 9: Now, we need the variable names. We can use the method getDataRegion() to get a specific column of data. First, we get the top of that column (column B, cell B1).

Line 10: Get the data region we want, which are the rows in that column.

Line 11: Then we get the values in those rows and these values are stored in the variable variableNames.

Line 12: We don’t want the first two rows, as these are blank and contain the column description. To do so, we can use splice() to remove the first two values of the array. 0 states the position, and 2 is the number of values to remove.

  14. //Get file URL
  15. const fileUrl = shFiles.getRange(3, 3).getValue();

Line 15: Next, we get the URL of the file we want to copy, which is in cell C3.

17. //Check file URL isn't blank
18.  if (fileUrl !== "") {

Line 18: Let’s add a check to make sure the file URL has been added. It will throw an error if it’s absent.

  20.  //Extract file ID and get master file 
  21.  const fileId = fileUrl.match(/[-\w]{25,}/);
  22.  const masterFile = DriveApp.getFileById(fileId);

Line 21: Now, we need to extract 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 a regular expression 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 22: 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.

24.    //Copy master for each of the variable names
25.    //and rename each copy with the fixed and variable name 
26.    variableNames.forEach((variableName) => {
27.      masterFile.makeCopy(fixedName + variableName);
28.    })

Line 26: Now, we’re going to make the copies of the file. First, we use forEach() to loop through the variable names stored in the variable variableNames. Each variable name will be in variableName, e.g. A, B.

Line 27: Each round of the loop, we’ll make a copy of the master file. We use makeCopy() to do that and pass the fixed name and current variable name to give that file an individual name.

29.    ss.toast("Your files have been made! :) Baz", "Finished", 3);
30.  }
31. }

Line 29: 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 gets the active Spreadsheet ss, then in the brackets contains 3 parts: message, title, number of seconds it will be displayed.

Lines 30 and 31: This closes the if statement from line 18, and the function.

Adding a menu

1. // Add menu
2. function onOpen() {
3.   const ui = SpreadsheetApp.getUi();
4.   ui.createMenu('Files & Folders')
5.     .addItem('1-Make multiple files', 'makeFiles')
6.     .addToUi();
7. }

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 2: Call the function onOpen(), so that it opens automatically every time the spreadsheet is opened.

Line 3: We get the spreadsheet UI (user inferface).

Line 4: We create a menu in the UI and let’s call it “Files & Folders”.

Line 5: We add a menu item to that menu called “1-Make multiple files” and link it to the makeFiles function.

Lines 6 and 7: then we add it all to the UI and close the function.

Run the function and authorise the script. The newly-created files will appear in the same folder as the copied file.

Here’s the link to the Sheet and code. It also contains others versions of this: File copier; folder copier; File copier and links; Folder copier and links. The last two store the links of each of the newly-created files or folders on the sheet.

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.

You can also make multiple Drive folders quickly with Apps Script. Check out my post to see how to do that.

a