How To Rename A Bunch Of Files In Google Drive Using Google Sheets And Apps Script

Recently, I had to convert about 1000 files worth 20GB into smaller jpegs totalling about 40MB. I then had to rename all these files to specific indexes tied to the individuals in those photos. Here is my frankeisteinish way of how to do something like that!

First, we convert the photos. I did this using Photoshop's feature; Actions! It allows you to record a set of actions performed on a single photo, then replicates those actions across a whole folder of photos. I found this sweet article that explains that in detail.

Now, with 40MB, 1000ish files in hand, it's time for some JavaScript magic. See, the problem I had was, the photos, while having unique names, thankfully! they were mixed up. Let's say, photos of the pcmasterrace were mixed with consolepeasants, but I only had to pull out and rename the consolepeasants photos. But when given to me, they were all mixed up.

Here, Google Sheets enters the chat. With it's multitude of formulas I was able to filter the sheets accompanying these photos to obtain a list with just; The names, their respective indexes, and their respective current photo name, something that looks like this:

First Name   Last Name   Index    CurrentPhotoName
Adam         Somebody    0A1      Photo0001.jpg
Catherine    LeGreat     0A2      Photo0002.jpg
Angels       R'Dust      0A3      Photo0003.jpg
...

In case you don't already have an assorted list of the files, don't worry, all is not lost. With the files total size so small, we can just dump them all in a Google Drive folder and deal with them there using JavaScript and some Apps Script functions.

Once you have created a Google folder and dumped all your photos in it, create a new Google Sheet Doc. Once it's finished loading, click tools then script editor. Rename and save the resulting Apps Script file.

For this demo, we've named our Google Sheet spreadsheet, the Google Drive folder and the Apps Script file all the same name, renameSumFilesSheet.

Now, we're ready to code. First, let's add a way to control the script, we'll use the inbuilt onOpen method to create a custom menu on our sheet.

The code below creates a custom menu with two buttons, one will fetch file names form our Google Drive folder when clicked. The other will use values from the CurrentPhotoName column, search through the folder, then rename the files it finds with values from the column Index. For this example, we are going to paste the name we fetch in a separate sheet because the expectation is, you will clean your data using whatever Google Sheet functions suit you.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
  .addItem('Fetch File Names', 'fetchFileNamesFunction')
  .addSeparator()
  .addItem('Rename Files', 'renameFilesFunction')
  .addToUi();
}

Result:

Before we create our functions, there are some variables we must define. The name of our folder and the instance of our sheets (we've created two, data and roughsheet).

let ourSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let roughSheet = ourSpreadsheet.getSheetByName("roughsheet");
let dataSheet = ourSpreadsheet.getSheetByName("data");
let folderName = "renameSumFilesSheet";

See, the beauty of Apps Script is, if you put your variables at the beginning of the Code.gs file, they will be available to every function, in every file, same goes for functions created in other files. So, throughout the demo, any new important variable we create, we'll just add to that list.

Now, let's make take a look at our fetch names function.

function fetchFileNamesFunction() {

  var folders = DriveApp.getFoldersByName(folderName);
  var folder = folders.next();
  var myFileIterator = folder.getFiles();
  var fileNumber = 1;

  while(myFileIterator.hasNext()){
    var currentFile = myFileIterator.next();
    var currentFileName = currentFile.getName();
    roughSheet.appendRow([
      fileNumber,
      currentFileName]);
    fileNumber++
  }

}

JavaScript
This functions simply access our Google Drive, searches for the folder name we've given it, it then goes through all the files in the folder, gets their names, then along with a unique temporary index are appended as new rows in our roughsheet.

Depending on your use case, cleaning the data here is up to you. But as I've said, there's a lot of Google Sheets functions to help you.

So, we'll skip to the part where you have your data cleaned and arranged in the data sheet like this:

Now, for the pièce de résistance, the function that renames the files.

function renameFilesFunction() {

  var folders = DriveApp.getFoldersByName(folderName);
  var folder = folders.next();
  var lastRow = dataSheet.getLastRow();
  var oldNamesArr = [];
  var newNamesArr = [];

  //you will have to edit these variables
  var columnOfOldNames = 4;
  var columnOfNewNames = 3;
  //you will have to edit these variables

  oldNamesArr = dataSheet.getRange(2, columnOfOldNames, lastRow-1).getValues();
  newNamesArr = dataSheet.getRange(2, columnOfNewNames, lastRow-1).getValues();

  for(let i = 0; i < oldNamesArr.length ; i++){

    var myFileIterator = folder.getFilesByName(oldNamesArr[i]);

    while(myFileIterator.hasNext()){
        var currentFile = myFileIterator.next();
        currentFile.setName(newNamesArr[i]);  
    }

      } 

}

The only part of this function you'll have to edit before using is commented. columnOfOldNames is the index of the column were we have our current photo names in the data sheet. columnOfNewNames is the index of the column with the new names. For sheets, row and column index are counted from one. So, index of row 1 is 1, 2 is 2, 3 is 3 and so forth. And column A is 1, B is 2 and so on.

You can make a copy of the final sheet for your use here

Happy renaming a bunch of files!