⭐ Welcome, and get excited for some super FOIA organization!!! ⭐
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.
Overview
What a FOIA system anyway? Ew.
Wait.... what even is all of this? Fair question. At a super high level, this is a system to track your FOIA requests through a google form/google sheet, and automate processes to store your FOIA communications and responses. This doc will walk you through the process of setting it up for yourself.
Why in the world would I ever care enough about FOIA to spend time setting all this up? Another fair question. The answer is because FOIA is super powerful and important tools in any reporter’s arsenal (not to mention critical to functioning democracy and all that). But the process of requesting records, duking it out with FOIA officers and finally reviewing your new treasure trove of public information can get complicated. Staying super organized helps.
Still have your doubts? Check out the overview video below of how I use my FOIA organization system to see what’s in store. (Also, I promise this is the longest video in the whole tutorial.)
Okay, ugh, fine.... I’m convinced. What do I need to do? To get started, you’ll need:
It’s my recommendation that you do this on a personal-professional account rather than your actual work account but it’s totally up to you. (By personal-professional I mean a gmail account you created yourself to use for work purposes, but it is not affiliated with any employer, i.e. mjhreporter@gmail.com.) While I use my work email account to file all my FOIAs, I use my personal email address to archive and organize my responses. I find that this arrangement makes it easier to set up some automation workflows that we’ll get into later on. Also, ya’ll are some super impressive journalists and although we’d love to keep you on our team forever, chances are you will one day work somewhere else. When that day comes, it’s nice to still have all access to all your hard-won records responses. Probably about an hour or less of free time to set up the basic level process. An hour more for the automations. A love (or at least a toleration) for organization, process building and TSwift. Wait one more question... what is this weird giant document and how do I use it? This is a document built on Coda.io, a note-taking program that I’m trying out. It is very similar to Notion, which you might recognize from my overview video above. For the purposes of this tutorial, you don’t need to have a Coda account or worry too much about how to use it. Click the little arrow next to “Expand” below for some tips on how to navigate this page and get the most out of the instructions. Hot Tips to Navigate This Page
I like to nest things inside toggle lists, indicated by the little arrow signs to the left of text blocks. If you see one of those, it means you can click for more information. I also made tutorial videos of myself going through most of the instructions in the various steps. Whenever you see a toggle icon next to the word “Video” that means I have made a tutorial video for that step. I’ve put together two groups of FYIs and FAQs for some additional information on the two main types of workflows we’re setting up here. You can access those in their respective sections or click here to jump to them now: 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
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.
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 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. 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 (formerly Integromat) and will note if anything requires a paid subscription. For the most part, the logical steps 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. 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 - . 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. In the next window, check “Skip the inbox” and “Mark as Read” Then check “Apply the Label” and create a new label of “FOIA”
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!
As mentioned, I’ve usually used Zapier but I’ll be working through for this tutorial to try to do it all on a free plan. However, Make.com has some of its own headaches to get through so to get started, so bear with me as we get this set up. I promise it gets easier.
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 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. 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 has put out their own , 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.
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 for the Gmail trigger FIlter type: Simple filter Mark email message(s) as read when fetched: check Empty Maximum number of results: 1 Then select OK in the bottom right corner. 5) Add a step to search your google sheet… For the next step, we are going to tell 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 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 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 6) … And tell 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 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 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 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.
You’re done again! Your first super-dope FOIA automation process is complete and it only gets easier from here.
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
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 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, 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 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 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 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 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 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.
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 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. 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 . 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.
In Conclusion, You are a FOIA Organization Master.
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)
Madison’s Super Dope FOIA Tracking Template: A Complete Guide
