Gallery
So you wanna get organized...
Share
Explore
Gallery
So you wanna get organized...
So you wanna get organized...

icon picker
Madison’s Super Dope FOIA Tracking Template: A Complete Guide

Featuring: A completely necessary number of Taylor Swift GIFs.
Featuring: A completely necessary number of Taylor Swift GIFs.

⭐ Let’s get ready to FOIA!!! ⭐

On this page, I’ll go over how to use my FOIA tracking template, set up some google automations and set yourself up to be a god damn FOIA organizational mastermind. This process is annoying and tedious at times, so I’ve incorporated wise words from Mother herself to get us through. If you hate Taylor (as in, if you are just a very sad/angry person inside) this is probably not the tutorial for you.

For everyone else...
Hey! Let’s be friends. I’m dying to see how this one ends.
(JK, I know exactly how this ends - with super organized FOIA requests and it’s gonna be fabulous.)
Start your FOIA organizational journey with the Overview section below or for repeat visitors, jump ahead to Part 2.

⁠
image.gif
⁠
⁠

Overview ​What’s a FOIA system anyway? Ew.

⁠

Part 1: Getting Started Are You Ready For It?

First things first: What do we really need from a FOIA tracking system?

At it’s most basic level, your FOIA tracking process should have the following:

⁠

Part 2: A Google Form and Sheet This is me trying

In its simplest form, all of the FOIA tracking needs we identified above can be accomplished within Google’s suite of free tools. We’ll go through how to copy and customize my template for your own needs. After that, I’ll show some other options to take things a few steps further with automation workflows. But IMO, it might best to start with this setup and use it for a few weeks before deciding what else you want to add on.

How to use Madison’s Super Dope FOIA Tracking Template

Video
⁠
⁠
⁠
4) Test it out and clean things up!
Now let’s make sure it’s all working correctly. Go back to your form editor and click the purple Send button in the upper right corner. Select the link icon (the second option after “Send Via.” This will give you a hyperlink that you can save wherever is convenient for you so that you can always fill out the form. You’ll want to access it this way rather than through the editor because it’s much more efficient. I saved this link to a bookmark on my browser.
Open this link and fill out a test form. You can use a real FOIA or fake info as you can easily delete it later.
Then go to your spreadsheet and make sure the new information appeared. If CopyDown worked correctly, you should also have an updated MY_ID for your new row of data.
Now that everything is working correctly, you can hide some of the unnecessary columns and rows to just focus on your FOIAs. See the FYI and FAQ section for what I normally hide.
Video
⁠
⁠
⁠

5) Set yourself up to document your communications and store your records.


6) Reminder: Set yourself up for a new year


FYIs and FAQs - Part 1

IT’S DONE!!!!!!
At least for now… but that’s it for the basic level! You can continue on for more information on how to automate more processes to store your responses or just sit back and enjoy your new super dope, organized FOIA starter kit. Congrats!





⁠

⁠

Part 3: Supercharge with automations ​We’re only cryptic and machiavellian cause we care (about transparency)

Jan 2024 Update: I wrote all of this out a couple years ago and used
Make.com
⁠
to show how the automations could be done on a free account. It worked then and I believe it still works now, but Make.com can be a huge headache. So I also made a template for the automation I use in Zapier. It requires a paid plan so only use it if you really want to commit. I’m just dropping the template link for now but I’ll work on making videos/writing out the steps soon if there is any interest.
⁠
Upload FOIA Gmail attachments to Google Drive and update FOIA Tracking Google Sheet with links
Anytime a new email is received in Gmail with the FOIA label do the following: 1. Lookup spreadsheet row in Google Sheets that has the same MY_ID as the subject line in the email. 2. Update spreadsheet row in Google Sheets with a link to that email 3. Only continue with Zap if the email has attachments 4. Create a new sub-folder within an existing Google Drive folder for FOIA responses. Name this new folder as "MYID - Agency" (but with the relevant request details) 5. Upload the attachments from the FOIA email to that folder 6. Update FOIA Tracking google sheet with a URL to the new folder
zapier.com
⁠
⁠

Choose your own adventure

There are lots of ways you can further customize your setup to suit your needs. I’m going to show you how to continue to update the Google Sheet we made in the basic level step.
However, if you like to work in a different note-taking or organization program, most of these workflows can be adjusted to fit anything that works with databases or spreadsheets online. For example, I do all of this through Notion (as seen in the overview video). If you have any questions on how to customize any of this to your program of choice, let me know and I’ll do my best to help you figure it out.

A note on automation programs

Most of the workflows I’m going to show you involve third-party automation services. Many of these require paid subscriptions, so take that into account when deciding what you want to do. I also don’t know enough about the privacy standards of each option to recommend one based on that measure.
For example, I’ve used Zapier for my FOIA Tracking setup for several years, but they’ve recently upped their prices so I’m considering switching over to a different program. I’m going to try to set up the automations below in a free version of
Make.com
⁠
(formerly Integromat) and will note if anything requires a paid subscription.
For the most part, the plan listed at the beginning of each section will remain the same no matter what service you’re using, so feel free to shop around or use whatever program you are already comfortable with.



Wait! We need to set up some infrastructure!!!




Okay here are the infrastructure steps

1.) If you’re using more than one Gmail account, decide on a log in order and stick to it.
If you are regularly logged into multiple Gmail accounts, the order in which you log into each matters. This is a very annoying thing with Google and there are some workarounds if you just can’t bring yourself to keep up with a system like this, but they are all kind of a pain so it’s really just a matter of picking your poison. Check out the video below to see what I’m talking about.
Video
⁠
⁠
⁠
2.) Set up a Gmail alias to automatically receive a FOIA label.
Pick a google account: This next part relies on creating Gmail aliases. If you’ve never heard that term before, it’s basically a way to send things to an existing Gmail account that allows Gmail to treat those messages differently than your general inbox. The way we do this depends on what type of Gmail account you have.
If your email address ends in “@gmail.com” - AKA the most common way
If your gmail ends in gmail (i.e. mjhreporter@gmail.com) you can use an alias by adding the + sign and whatever text you want after your username but before the “@” symbol.
Example: mjhreporter+FOIA@gmail.com
If you have a Google Workplace account (i.e. info@madisonhopkins.com)
With this option, you can add an alias in the gmail settings. Look at this page for more info -
link
⁠
. For this tutorial, I’m going to use a regular “@gmail.com” account, so if you’re going this route keep in mind that you’ll need to make a minor few tweaks along the way.
Note: This costs money (unless you’re using an existing work account, like your work email). This is what I personally use because I already have my own workplace account to power my personal website. I use it because I think it looks cleaner, I have more storage and a few other upsides, but IMO it wouldn’t be worth the cost just to get a Marketplace account for anything in these FOIA tutorials.
Pick an alias and save it to your address book as something you’ll remember
I recommend going simple, like the mjhreporter+FOIA@gmail.com stated above. To make sure you don’t misspell anything or forget the alias, I think it’s easiest to just create a new contact in your Gmail or whatever mail client you use and call it “Madison’s FOIA Requests,” but customized for yourself of course.
Set up your Gmail label and filter
Go to the Gmail settings for your select account and select the “Filters and Blocked Addresses” tab. Then click “Create new filter”
In the “To” field, put in the alias email address you decided on.
Select “Create Filter”
In the next window, check “Skip the inbox” and “Mark as Read”
Then check “Apply the Label” and create a new label of “FOIA”
Click “Create Filter”
Video
⁠
⁠
⁠


Infrastructure: Complete ☑️
That might have felt a bit anticlimactic, but you’re now set up to dive into the automation workflows. Even if you don’t adopt any of the later steps, you can use this process to store your FOIA gmail threads on your personal account, neatly tucked away and organized under their own label.

⁠

The Google automations AKA the building blocks for all the dope things to come

Now we’re getting to the good stuff!


Part 2A: Gmail + Make.com: Syncing your email threads with your FOIA tracking spreadsheet

The Plan
Before we get into the nitty-gritty, here is a high-level explanation of the steps we’re going to set up our automation to take.
Watch the FOIA folder in our Gmail account for new emails.
Note: When we forward emails to the alias that sorts them into the FOIA folder, we will have already put the relevant MY_ID as the subject line of the email.
Search our FOIA Response Google Sheets for the row that has the same MY_ID as the subject of the new email thread in our FOIA Folder.
Add a link to that Gmail thread in the relevant row in our FOIA Responses Google Sheet.

The Steps
1) Go to
Make.com
⁠
and create a new account.
It is not necessary to use the same Gmail you’ll use for the rest of these steps, but IMO it makes it simpler to do so.
2) Once logged in, create a new Scenario.
Select Scenarios from the menu on the left side, then select Create a new scenario in the upper right corner.
Click on the giant purple + sign button flashing in the middle of the screen.
Search for Gmail out of the screen that pops up, and select the trigger Watch emails.
Video
⁠
⁠
⁠
3) Brace yourself for a total pain in the ass … and create a new connection to your Gmail account.
For the record, this step is VERY annoying and unlike anything I’ve had to do with any other automation program. However, for the sake of doing this through free accounts, I’m going to try and walk you through it. Just note that most automation programs require nowhere near this process to connect your Google accounts.
If you are working with an email account that ends in “@gmail.com” you’re going to have to jump through some hoops to create this connection. I wasn’t going to make a video going through the whole process because
Make.com
⁠
has put out their own
tutorial video here
⁠
, but after reviewing that it seems like some parts of theres are a little dated. You can still get the gist of it, but you can follow along with my video as well. It’s a little choppy because I had to stop and start it often to rewatch their instructions. Meet me back here when you’re done.
My Tutorial Video
⁠
⁠
⁠
Note: If you have a Google Marketplace account (ie. one that ends in a custom domain) you don’t need to do this. Instead, select the orange Add button under the Connection subhead on the popup box and go through the much simpler steps to connect your account.
4) Set up your Gmail trigger
Now that you’ve finally gotten through that horrible Google/Make.com process, we can get down to business of setting up the automation.
After you select the connection you just made, make the following selections in the first pop-up box on
Make.com
⁠
for the Gmail trigger
Folder: FOIA
FIlter type: Simple filter
Criteria: All emails
Mark email message(s) as read when fetched: check Empty
Maximum number of results: 1
Then select OK in the bottom right corner.
Video
⁠
⁠
⁠
5) Add a step to search your google sheet…
For the next step, we are going to tell
Make.com
⁠
to search our Google Sheet of FOIA Responses for the MY_ID of the relevant FOIA request we just emailed into our Gmail alias.
To do so, click on the big plus sign to the right of the Gmail circle. Search for Google Sheet and select it. For the Trigger, scroll toward the bottom and select Search Rows
Video
⁠
⁠
⁠
You’ll need to add a new connection to your Google Drive account, but it won’t be nearly as painful as the Gmail process. Select the green Add button on the pop-up box. Click through to the appropriate Gmail account and authorize the connection.
Then fill out the rest of the pop-up box:
Spreadsheet: Select your FOIA Responses Sheet that you made in the basic steps
Sheet: Select Form Responses 1 unless you renamed your responses tab
Table contains headers: Yes
Column range: leave the default information
Filter:
On the first text box select MY_ID
This is telling the workflow which column you want it to search
On the second text box, various options will pop up from the Gmail in the first step. Select the Subject
Leave everything else the same and click OK in the bottom right corner
Video
⁠
⁠
⁠
6) … And tell
Make.com
⁠
to update the row with a URL to the Gmail thread with the same MY_ID
Click the plus sign to the right of the Google Sheets step to add a new module and select Google Sheet again. For the Trigger, select Update a Row.
Leave the connection at the same connection you used for the prior step. Then fill out the rest of the fields:
Choose a Method: Select from the list
Choose a Drive: My Drive
Spreadsheet ID: Navigate through to select your FOIA Responses Google Sheet
Row Number: Out of the options that pop-up from Step 2, select Row Number
Table contains headers: Yes
Automation Response Status: Type in “Gmail URL Received”
Email Thread URL: From the options for Step 1, select Message Link
Note: I was unsure if this was correct while I was making the video but it is right so ignore my hesitation.
Leave everything else empty and click OK in the bottom right corner
Video
⁠
⁠
⁠
7) Test it out
Now we want to make sure that everything is running smoothly so we are going to put our automation through a trial run. Remember for this whole process to work, we need to include only the MY_ID in the subject line of any emails we send in to our alias FOIA Gmail account.
To test this out, go to your FOIA Requests Google Sheet and copy one of your sample MY_IDs.
Write a new email to your alias account and paste that MY_ID as the subject.
Send the email to your FOIA alias account
Back on
Make.com
⁠
click the purple play button next to Run once to try out your workflow.
If everything works, turn the automation on by clicking the toggle in the bottom left corner.
Video
⁠
⁠
⁠

You’ve done it again! Your first super-dope FOIA automation process is complete and it only gets easier from here.

Part 2B: Gmail + Make.com: Syncing folders of your responsive records with your FOIA tracking spreadsheet

Good news!
The Plan
Here’s the logical steps of what we’re going to tell the automation workflow to do in this case
Watch the FOIA folder in our Gmail account for new emails
Check to see if the email has attachments. If it doesn’t, stop the automation process and don’t do anything else.
If the email has attachments (AKA a bunch of super dope responsive records), create a new folder on Google Drive and name it as the same MY_ID as the email subject.
Upload the attachments from the email to this folder.
Search our FOIA Tracking spreadsheets to find the row that matches the subject line of the new email we received in the FOIA folder
Put a link to the new folder containing the email attachments in the row of the FOIA Tracking spreadsheet that corresponds to the MY_ID
Note: We’re going to go through this process by creating an entirely new scenario on
Make.com
⁠
to give people the option to choose which automations they want to use. But if you’re all in, you could do it as an add on to your initial workflow. If you are curious about that process, let me know and I’d be happy to go over it one-on-one.

The Steps
1) Create a new scenario on Make.com
From the side menu bar select Scenarios then click on the purple button to Create a new scenario in the upper right corner
Click the big purple button and set up a trigger to Watch emails in the same way we did in the first step of the previous automation tutorial.
However instead of using a Simple Filter we are going to select Gmail filter under Filter type
In the Query text box type in “label:foia has:attachment”
This is allowing us to filter for only emails in this folder that have attachments
Leave the rest the same and click OK
Video
⁠
⁠
⁠
2) Create a new module to set up the step to Google Drive folder for the records
Press the + button to add a new module and select Google Drive
Okay brace yourself - you’re going to need to go through the super annoying Google authorization process again. I don’t know why they don’t let you use your Google Sheet connection to make this work, but
here
⁠
is the tutorial they put out and see below for my video on how to set this up.
Video
⁠
⁠
⁠

Okay, now that you’re back from that mess, we can actually finish setting up the step. Yay!
Fill out the following fields this way:
New Drive Location: My Drive
New Folder Location: Navigate through the options until you select the new folder you created in your larger folder to track your FOIAs. For me, this folder is called “FOIA Responses”
New Folder Name: Navigate through the options populated from the first step with your Gmails and select Subject
Share Folder: I leave this as No, but it’s up to you if you want to make it shareable for whatever reason
Click OK
Video
⁠
⁠
⁠
3) Create a module to upload the files to your new folder
This is really a two-part step. Because first, we need to add in a tool to make sure that
Make.com
⁠
will upload all the attachments included in your email.
To do so, select the green button labeled Tools from the bottom menu. Then select Iterator.
From there, navigate through the options for Array to select Attachemtns[] from the Gmail step and click OK.
Next create a new Google Drive module and set the action to Upload File.
From there select Enter Manually from the Enter a Folder ID dropdown menu.
On Folder ID select the Folder ID option from the Create a Folder step.
For File, check Flow Control - Iterator, then press OK
Video
⁠
⁠
⁠
5) Create a new module to search for rows inside your FOIA Responses spreadsheet
Now we want to go back and search for the applicable row in our FOIA Responses spreadsheet so we can upload a link to this new folder.
These steps are going to be exactly the same as the previous automation’s step 5 (I didn’t make a video, just reference the earlier steps for this one)
6) Create a module to update the relevant MY_ID row
Create another new Google Sheet module to Update a row
Fill out the rest of the fields like this:
Choose a Method: Select from the list
Choose a Drive: My Drive
Spreadsheet ID: Navigate through the options until you get to your FOIA Tracking spreadsheet
Sheet Name: Form Responses 1 (unless you changed it)
Row Number: Select Row Number from the Search Rows step options
Table Contains headers: Yes
Automation Response Status: Attachments Uploaded
Note: This will override the Automation Response Status we set up in the Gmail automation step. I like this because it quickly shows me which ones have attachments and which ones don’t, but it’s up to you if you want to include it.
Google Drive Folder: Select Web View Link from the options for the module to create a folder
Leave everything else blank and press OK
Video
⁠
⁠
⁠
7) Double check your work and CELEBRATE! (Hopefully)
Send yourself a new email to your Gmail alias with one of your MY_IDs. Make sure to include at least two attachments to see it the iterator is working.
Assuming it is, then CELEBRATE! You did it! Turn your automation on by clicking on the lever in the lower left corner.
Video
⁠
⁠
⁠

Google Automations - FAQ & FYI

HUZZAH! YOU’RE DONE!
Probably for real this time! The only other automation processes I have to offer you at this time are how to take this setup and adapt it to your note-taking program of choice, but the basic functionality of the workflows will stay the same.

To be sure, there are some limitations to these processes and issues that will come up. Check out the Automation FYI and FAQ below to get into any of these.
FAQ & FYI - The Automation Workflows
FAQ: What happens if I have to file a request through an online form? Or they send me a link to download records from a portal? Doesn’t that mess everything up?
Answer: Yes and no. First off, you should know that in general, FOIA Officers cannot make you file a request through a FOIA form. They have to let you file it in a way that is accessible to the general public, which means email or even via snail mail. But in practice, insisting upon that can sometimes just annoy the FOIA Officer and make more trouble than it’s worth.
My workaround in these situations is to first off, make a note in my initial Google Form that I filed the request through a web form, providing the link if I can. Then if or when I get some kind of acknowledgment email, I include that email address in my communication notes so I know where to search for if I need it later.
Finally, when it comes time to send the responsive records through the automation process, I’m sometimes SOL. In some cases, you’ll get an automated email from the portal saying your records are available to download. I still send that through the Gmail process with the MY_ID in the subject line so I can track the email. But then I have to go in, download the files to my computer, create a new Google Drive folder by hand, upload the files and put the link in the spreadsheet. Yes, it’s a pain but for the most part, it happens pretty rarely. It’s infrequent enough that I haven’t devoted any time to figuring out an automation workaround but if/when I get to that point, I’ll let ya’ll know.
Update on this: Another option is to download the files and attach them to the email when you forward it to your FOIA email address. This will treat the email the same way as if the FOIA officer had attached the records that way originally.
FAQ: What happens if they send me five emails in a row with different attachments and I want to put all of those into my system?
Answer: This is another annoying issue that unfortunately pops up a bit more frequently than the online portal problem, at least in my experience. And right now, I don’t have a great solution. I usually forward in the first email to my alias with a note to myself at the top that it is email 1 of X. Then I download the files from the other emails to my computer and upload them to the appropriate folder that was made through the automation process for the first email.
FYI: These are some other no-code automation programs to try out.
After going through this process with
Make.com
⁠
my opinion is that it does a good job for a free plan. There are quite a bit of wonky tech issues and I’m not sure if I’ll run into usage limits down the road, but for now I think it’s a decent option that I expect to only get better as they work out the bugs with Google. But if you’re looking for something a bit easier to manage (none of these require the complicated authorization processes we went through), and are willing to pay for an upgraded membership, here are some others you may want to try out.
⁠
Zapier
⁠
⁠
As I mentioned, this was how I originally built all of my FOIA automations. It’s probably the biggest automation program out there right now, but also one of the priciest. I find Zapier much easier to use than
Make.com
⁠
.
⁠
IFTT
⁠
⁠
Internal Automations
Many note-taking programs have their own methods of doing some of these automations. For example, when you use Evernote, you are automatically given an email address that you can forward files to and it creates a new “note” based on the subject. Airtable has similar options as well, but it requires a paid account.


⁠
⁠

Part 4: Wrapping up

To recap, here is what you just built for yourself

A way to standardize information about every FOIA Request you file, assign them unique IDs and keep track of your notes and communications related to each request. What does this mean for the next time you forget the date you filed a records request? Nothing! Because as long as you work this system, that will NEVER happen to you again!
A quick and easy process to access the original email threads containing your FOIA responses (or denials). No more hunting through endless search results of “Responsive Records” search results in your Gmail.
A standardized location to store, access and work with your responsive records. By creating new sub-folders for each request (organized by MY_IDs, of course) and linking them to your FOIA Tracking spreadsheet, you will always know where your records are. On top of that, if you get in the habit of using those folders to analyze your records, play with your data or do whatever else, you’ll always be able to quickly find and pick up your work where you last left off.
Bonus Perk! You also now have an inventory of contact information for every FOIA officer you’ve ever communicated with! Dope!


To paraphrase the great TSwift one more time: you’re out of the woods. The (error message and authorization nightmare) monsters turned out to be just trees. You came back (from frustrated breaks) stronger than a 90s trend. I had a marvelous time ruining (your old FOIA habits) with you. I hope your new system is all you hoped it’d be in your Wildest Dreams.


Bottom line: You’re dope. Be proud of yourself.

⁠

Who’s afraid of little old you? (Reluctant FOIA officers, that’s who)

Gallery
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.