Hi there, this is Erin Oz from the Premier Support Team with a workflow idea to share.
Background Info
Do you have a lot of files in your Google Drive, but you can never find what you’re looking for?
Or have you thought about doing a file reorganization project, and wondered if there’s a way to automate a record of which files have been added to the new folder?
Folks with large collections of video files, audio files, or other libraries of information often try to use nesting folders in a Drive to keep things organized, but it can be hard to browse through items that meet certain criteria.
For example, my brass quintet has a large library of sheet music. A “work” (a song) consists of parts for each member of the group. So for a standard brass quintet, there will be five “parts” for each work: Trumpet 1, Trumpet 2, Horn, Trombone, and Bass Trombone (Tuba). We keep a folder with the title of the work, and each folder contains five PDF files for each part.
Any given work also has a number of attributes that are impossible to capture in a single file name. For example, let’s think about “Somewhere Over the Rainbow”. This is an arrangement of a popular ballad, from a movie, about 3 minutes long, etc.
The challenge with a large library is that it can be difficult to browse and find results that match a given set of attributes. We decided to create an Airtable database that contains a single record for each part with a direct link to the Google Drive file. The database also contains a Works record for each piece, and details attributes like work length and musical style.
Entering this information manually would take, for lack of a better term, FOREVER.
I created a new folder in Google Drive for our music library, and I would add the files for the database to that folder.
I created a Zap that triggers on a New File in Google Drive, parses the part details from the file name, creates new Records in Airtable, and pings our group Slack so we can manually add finer details to the newly created records.
Example
-
The first step is to create our Google Drive trigger.
If you’d like to trigger on a specific folder, you can select New File in Folder. The catch here is that the Zap will only trigger on files added to that specific folder, it will not trigger on subfolders. In other words, if I added a folder of five parts, the Zap would not trigger.
For my workflow, I used the New File trigger. This triggers on any new File added to Google Drive.
-
I then added a Filter step to only allow certain files to continue. I knew that my file names would contain certain keywords, and that the filetype would be a PDF.
I set up my filter to only continue if one of those keywords appeared in the file name field, AND they were a PDF file.
-
Next, I used a series of Split Text Formatter steps to split the file title and return the composer name, arranger name, work title, and instrument. I created a naming convention for each file so my Formatter steps can reliably return the data.
Our naming convention looks like this:
Title, Composer (arr. Arranger) - Instrument
-
I then added an Airtable Create Record step that finds or creates a new Works record. This Works record will populate my table showing all the Works in our library.
Remember, each Work contains 5 parts known as Sheet Music! The Zap is going to run on each Sheet Music item added to the Drive.
We only need to create the Works record once for each set of 5 Sheet Music parts, so the Find step will return the Works record on the other 4 runs.
-
Next, a Create Record step creates a new Sheet Music record, and adds the Google Drive link to the record field.
-
I then added a Delay After Queue step to enable Slack threading - you’ll see why in the final step!
-
Then, a Find Message step in Slack searches a specific channel I created to see if a message has already been posted about the Works record. Remember, only one Works record is created for every five Sheet Music records!
The Zap is going to run when a group of five PDF files are added in a folder to the Drive. That means the Zap will run five times on each Work, but only create the Work record once.
I’d like the first run of the Zap to post a Slack message, and for the remaining Slack messages to be threaded. That way, I can see at a glance that all five parts were added, but the main message contains the action item for me to add attributes.
This step will allow the Zap to thread subsequent messages about the same Work.
-
The final step of my Zap will post to a Slack channel and request that specific attributes of each Work be added.
Since we don’t currently have a database or spreadsheet containing the attributes we’d like to add to the Works records, we need to crowdsource those details from the group and add them in.
If there are any auxiliary instruments required for each Sheet Music part, we can also add in those details!
Whew, thanks for sticking with me!
This is a heavy lift of a workflow, but the upfront investment of time means that each time we acquire a new piece, I can simply drag a folder of (properly-named) PDF files into my Google Drive and the records are created in Airtable automatically.
This valuable database in Airtable gives me a lot of flexibility. I can create a view in Airtable that only shows me pieces that are less than three minutes long and in a jazz style, or I can look for all the Works we have for a certain composer without needing to drill down into multiple levels of folders.
You can apply this concept to video files, social media content libraries, photo libraries, and more.
What can you organize in your working life with a dynamic database created automatically by Zapier?
