Skip to main content

Renaming photos with Sheets and batch scripting

Use Google Sheets to create a Windows .bat or Mac/Linux .sh script to rename your exported Fulcrum photos.

Kat Kim avatar
Written by Kat Kim
Updated over a year ago

This is a guide to an alternative, third-party method to renaming exported photos from Fulcrum. It uses tools that are available for free and should work for users across all operating system platforms. While this guide uses Google Sheets, the process is likely similar for Microsoft Office Excel and LibreOffice Calc.

Getting Started

Step One

To begin, you need a CSV file exported from Fulcrum using the exporter that includes photos. Then, import your CSV into a blank Sheet.


Step Two

If your Fulcrum app contains multiple photos per photo-field, you will need to prepare the Sheet accordingly. If not, please skip this step. 

Use Sheets' Split text to columns functionality to create separate columns for each photo ID (both Excel and Calc have similar functionality). You may have to do this more than once if you have more than one photo-field in your app.

Step Three

Insert columns to the right of your photo-field columns. Give them a name in the first row header; you can use the corresponding columns' name and append the suffix _rename to it, like below.

Step Four

Now we will use a formula that will build a new filename for the photos by referencing a couple other columns in our exported data. This particular formula is specific for a demonstration project, but hopefully it can serve as a model for you to achieve your own similar task.

Here's what the formula is doing:

  • If photo-field 1 is empty, leave rename-field cell as blank.

  • If not empty, start building the rename-filename from the created_at column.

  • Include a custom ID from the cgid column. If it's blank, insert the text undefined instead.

  • Also include a custom suffix _Post1.jpg. Post1 would be my first photo after the project work was completed, Post2 would be the second.

  • Example: 20161005_2211_73084_Post1.jpg 

=IF(ISBLANK(fulcrum-photo-1),"",(CONCATENATE(SUBSTITUTE(MID($created-at,1,10),"-",""),"_",SUBSTITUTE(MID($created-at,12,5),":",""),IF(ISBLANK($cgid),"_undefined",CONCATENATE("_",$cgid)),"_Post1.jpg")))

Below, we already applied the formula to the first rename-field column and are applying it to the second column (changing Post1 to Post2). Run a quick test on a few rows and if it looks good, paste it into the rest of the column. Also check to make sure the custom ID field is inserted when appropriate. 

Step Five

This step is where we will build our rename commands. You will use ren for Windows OR mv for Mac/Linux. We'll use mv in this example. Again, we will use a formula to check for empty values and build our command:

=CONCATENATE(IF(ISBLANK($fulcrum-photo-1),"",CONCATENATE("mv ",($fulcrum-photo-1),".jpg ")),$fulcrum-photo-1-rename, CHAR(10) ,IF(ISBLANK($fulcrum-photo-2),"",CONCATENATE("mv ",($fulcrum-photo-2),".jpg ")),$fulcrum-photo-2-rename)

Here's what the formula is doing:

  • If photo-field 1 is empty, leave cell as blank.

  • If not empty, suffix the photo-ID with .jpg , add a space in between, then the rename-filename, and finally insert a line break CHAR(10) .

  • Repeat as necessary, exclude line break from final column.

Step Six

Next, we will copy the rename commands into a text editor for cleanup. Copy the cells containing your commands from your column, omitting the first row header.

Paste this into a text editor such as Sublime, Atom, or Notepad++ (something that includes Find & Replace with support for RegEx) and save your the file. For example: rename-photos.sh (Mac/Linux) or rename-photos.bat (Windows).

Step Seven

Now let's cleanup the script. We are going to use Find & Replace with RegEx to tidy up each line containing the rename commands. There will be 3 parts:

Part 1

Remove the double-quotes at the beginning of each line by finding ^" and leaving "Replace With" blank.

Part 2 

Remove the double-quotes at the end of each line by finding "$ and leaving "Replace With" blank.

Part 3 

Remove blank lines by finding ^(?:[\t ]*(?:\r?\n|\r))+ and leaving "Replace With" blank.

Step Eight

Finally, run the script.

Windows

To run a batch file (example: rename-photos.bat ) from the Command Prompt, open Command Prompt and enter into the directory containing the file and type the name of the batch file and press enter. To run a batch file from the Windows Desktop, it runs like any other executable file by double-clicking the file. However, because a batch file runs in a command prompt, it immediately exits when completed - so you may only briefly see a black box for a second.

Mac/Linux

To run a script (example: rename-photos.sh ) from a Mac/Linux terminal, open the terminal and navigate to the directory containing the file and type sh rename-photos.sh.

Conclusion

This guide has shown you how to work with spreadsheet formulas to modify filenames and build new commands for a script. Find & Replace was also used (with RegEx) to cleanup text. Hopefully this guide provides an example of an alternative method of building a script for batch processing files.

Did this answer your question?