Skip to content
A Google Drive account Tip 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 + another half hour if you continue with the fancier automations.A love (or at least a toleration) for organization, process building and TSwift.
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.ExpandExpand togglesI 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. Watch VideosI 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. FYIs & FAQsI’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.
A google form to standardize all the information you track about your FOIA requests, that populates...A google sheet, with information on:When you filed the requestWhat agency you requested records fromHow you sent the request (email, portal, etc.)What records you requestedWhen your response is due (assuming you are in a state that has deadlines)An automatically created ID for each request that you can use to facilitate later automations or just keep your responses organized Essentially, one centralized place to keep track of all the FOIAs you’ve ever filed, what happened with them, and what you got back.
Columns in your FOIA tracking spreadsheet with automatically populated URLs to:The email showing your FOIA responseA newly created folder on your google drive containing the responsive records, and a google doc copy of the final email
Video
Video
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 because you can just 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
Setting up the MY_ID is one of the more complicated parts of this first step so you might be wondering if it’s necessary. In my opinion, the answer is 100% yes. First off, having a unique ID for each request will be critical for any automation steps later on, if you choose to go that route. But even if you only stick with this base level, it’s really handy to be able to identify exactly what FOIA you are working with at any given time. Here are some example use cases:You may think since you don’t file many FOIA requests, it’s easy enough to identify what response you’re following up on by looking at your spreadsheet and checking date and the agency that is the subject of the request. But sometimes you and the agency won’t agree on when your request was filed. The next thing you know you filed three FOIA requests with Agency A on three different dates, 7/12, 7/18 and 8/4. Then you get an acknowledgement email from the agency three weeks later (side note: totally illegal in MO and many other states but nevertheless, this happens a lot) saying they are writing in response to your 7/21 records request. Now you have no idea which one they’re talking about and you need to go back and cross reference your notes every time you discuss it. The solution? Create new labels in your Gmail with your MY_ID on each response thread. That way whenever they reply back on there, you’ll quickly and easily know what they’re talking about and be able to talk notes on the communication!You end up filing multiple requests for similar information on the same day. You may not think this will happen to you, but I promise if you file enough requests you’ll inevitably run into a situation where some FOIA officer has weird rules requiring you to file individual requests for five different police reports, or something like it. Again, you have to be able to keep track of it all so it’s best to set yourself up for success from the beginning. You can use them to organize your files. Even if you don’t end up doing any of the automation workflows, you can use the MY_IDs to organize files on your desktop or Google Drive. This is particularly helpful if you are going to modify the records in anyway and later need to go back to double check something from the originals. For example, I worked on a project that required looking at a ton of fire reports. We filed records requests for ten incident reports at a time and when we received them they were always in one giant PDF. So to make them more manageable for our reporting process, we broke up the pages of the PDF into individual incident files and kept each report in its own neat and organized little folder, keeping the MY_ID in the new file names. But then we inevitably ran into situations where a page was cut off from the end or we found a random page from an unrelated incident report accidentally tucked in. Because we kept the MY_ID on this new file, we were able to quickly go back to the original and see what went wrong.
This is critical to ensure that the new form responses add to the end of the spreadsheet (and update the MY_ID) rather than the top.
I keep the Timestamp field hidden because I think it’s unnecessary where there is the date column as well. I care less when I actually documented the request than when I am reporting as the request was filed. I also keep the first two rows after the header (so rows 3 and 4) hidden because those shouldn’t be changed. They are required for the formulas. Row 4 can be deleted once you make a copy of the template if you want. Remember that if you want your IDs to start at 001, delete this row before you complete your first form response. But make sure you always keep row 3 intact.If you want to add in new columns in between those that are auto filled with the form, make sure you insert a whole new column rather than moving around the auto-filled ones. If you move those around, the form will still populate what it thinks was the original and everything will just get all messed up and ugly. (But also remember that you can always revert the google sheet back to an earlier version if you get in a pickle.) You can also easily add in due date trackers if you live in a state that has FOIA deadline (unlike MO and KS unfortunately). I think the easiest way to do this is through the WORKDAY function on google sheet. You can read more about that
First, we’ll set up some infrastructure by creating a new rule in Gmail to automatically label emails that we forward to a specific address as “FOIA”Then, we are going to create an automation through Google Apps Script that looks for new emails with the “FOIA” labelWhen one appears, the script will check to see if the subject line of that email matches a MY_ID in our Super Dope FOIA tracking spreadsheet. If it does, the script will create a new Google Drive folder on our drive with this naming convention: MY_ID - Summary So for example: 2022000 - Squirrel dataWithin that folder, our script will put two things:A copy of the new email that was labeled with “FOIA” in a Google Doc form (helpful because Google Drive doesn’t count Google Docs against your overall storage like it does with PDFs)Any attachments to that Email (AKA, probably the responsive records for your FOIA)Then, the script goes back to our Super Dope FOIA Tracking Spreadsheet, and adds in URLs to:A direct link to the actual email thread in gmail A link to the newly created folder that corresponds to that row
You receive an email with your responsive records. Yay! Hopefully the files are just attached to the email, but if they provide links for you to download them, you’ll need to do that at this time. (Yes, this is a kinda annoying sticking point, particularly as portals like GovQA have become more and more common. But I still think this is the easiest way to handle it. I will update this page with a better workaround if I come up with one.)Go to your FOIA Tracking Spreadsheet and figure out what the MY_ID is for this request. Back in your email, forward the FOIA response email and attachments to your Gmail alias for FOIAs. In the subject line, delete anything that is already there and replace it with only your MY_ID.
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 just pick your poison babe, it’s poison either way. Check out the video below to see what I’m talking about. VideoSet 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.comIf 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 - 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 filterGo 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” Important: in the video (and an earlier version of this tutorial) I told you to also check “Mark as Read” - Don’t do that with the Google Apps Script automation. Only check “Skip the Inbox.”Then check “Apply the Label” and create a new label of “FOIA” - (no quotes in the actual label)Click “Create Filter”Video
Video
Video
Press the Save button on the top menu bar. Then press Run. Google will show you a warning about the permissions, but since it's your script, click Advanced and then select your script to proceed. Log in to your google account and click Allow. Go to your spreadsheet and check it out to see if there are now links to your gmail thread and the new folder. If it didn’t, and you got an error message, double check that you have the right folder ID, spreadsheet ID and that you changed the sheet name to “Form Responses” or whatever is applicable in your case. Video
I’ve found that usually when it suggests a fix for a certain part of the code, it will only give you the replacement code for just that part. And when I’ve tried to copy/paste that in the correct place in my existing code, I somehow manage to mess it up. So if it gives you a small snippet to fix your larger script, ask if it’ll give you the entire script with the new fix included. Then copy/paste the whole thing in, replacing your existing script. Dont’ forget to replace your IDs if you need to. As soon as you send one message, the Bot will start replying and you won’t be able to type anything else until it’s done or you cancel it. In order to prevent it from running off full steam ahead before you’ve even given it all the information, try to type out your entire question, with the script and error messages before you hit send the first time.
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” queries 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!
So you wanna get organized...
- Pages
Madison’s Super Dope FOIA Tracking Template: A Complete Guide
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.
Overview
What’s a FOIA system anyway? Ew.
Okay, ugh, fine.... I’m convinced. What do I need to do?
To get started, you’ll need:
Wait one more question... what is this weird giant document and how do I use it?
Hot Tips to Navigate This Page
Part 1: The Plan
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:
So what what exactly am I even going to get after going through this ridiculously long page?
If you go through the basic steps (Part 2) you’ll end up with:
If you take it further and set up the response automations (Part 3) you’ll also end up with:
AKA - with this step you’ll build yourself your very own personal treasure trove of public records. I swear - it’s super dope.
And with that, hopefully you’re convinced. So now let’s actually get into it.
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 get your own Super Dope FOIA Tracking System
2) Check that the right response destination is set up.
Open up the Form editor file, click the Responses tab and then click the little Google Sheet icon in the upper right corner of the main page. This should take you to the new copy version of the google sheet of responses.
Troubleshooting: If the Google Sheet link from the Form doesn’t automatically open in the new Sheet you just copied, go back to the template and make a new copy of the Sheet. Rename this, put it in your folder and delete the old one. Then go back to the Form editor, select the three dot menu icon and select “Select response destination” then “Select existing spreadsheet” and select the correct sheet.
Note: If you do have to go through the process of selecting the correct additional sheet, it will likely make a new tab on that sheet for the responses. If you copy/paste everything from the original tab to the new tab and delete any extra rows, it should still work.
3) Get the “Copy Down” add on.
This is required to get the MY_ID column to continue to auto-populate down the page as new form responses are collected.
On the Google Sheet, go to Extensions > Add-ons > Get add-ons
Search “Copy Down” and add it
Note: This is the extension you’re looking for -
Once it’s installed, we need to set up the appropriate settings. Back on the spreadsheet, go to Extensions > CopyDown > CopyDown Settings
Note: If you get an error message that says Google Drive refused to connect, try doing this in an incognito tab.
Press the big button at the top to “on” then set Formula Row to 3, then press Save Settings at the bottom of the page.
4) Test it out and clean things up!
5) Figure out how you want to document your communications and store your records.
6) Reminder: Set yourself up for a new year
Wrapping up Part 2 - FYIs and FAQs
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!
FAQ: Why do I need MY_ID?
FYI: Always make sure there are no additional rows underneath your most recent form response.
FYI: You can customize the response sheet.
. If you make sure you put the formula in row 3, the CopyDown settings will work the same as the MY_ID and apply them to any new form response.
Part 3: Supercharge with Automations
Ooo, Look What You Made (Your FOIAs) Do
Update Oct. 2024 - Looking for Zapier/Make.com steps?
In previous versions of this tutorial, I gave instructions on how to set this whole thing up using no-code automation programs like Make.com and Zapier. But I’ve recently found a way easier method to accomplish the same thing without a bunch of the headaches. This new process uses Google Apps Scripts, which might look kinda scary/intimidating to non-coders (myself included) but I promise it is much simpler and quicker than it looks. I fully reccomend people of any techy-ness level try this method first, but if you already bought into the Make.com/Zapier method and want to refer back to those steps, you can access them here:
Okay ya’ll, let’s get ready to automate our little hearts out!!!
Here’s a high-level overview of how we are going to accomplish that:
And that’s it! So once you have this set up, this will be your workflow for processing new FOIA responses.
Then your work is done and the automations kick in to create the folder, store your records and add the links to your spreadsheet. So go back and check them out whenever you’re ready.
One last thing before we get into the nitty-gritty: A note on Google vs. the rest of the world of note-taking tools
The Actual Steps
Okay hopefully you’re excited about this possibility. Let that fuel you through this part of the process.
1) First things first, we need to set up some Gmail infrastructure
Okay here are the actual infrastructure steps
. 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.
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.
Okay moving on to the more fun stuff...
2) Make a new folder on your Google Drive to store your FOIA responses
First up, you need to make a folder on google drive where you want to store all of your FOIA responses. This is going to be the umbrella folder that contains all of the sub-folders with your MY_IDs and responsive records. I recommend making a top-level folder for “Working FOIAs” and within that, storing your Google Form, Super Dope FOIA Tracking Sheet and a new folder called “FOIA Responses” or something super creative like that. it in the same spIt’s helpful to have all of this in one folder because you can then just bookmark the whole thing.
3) Get the Folder ID of the new “FOIA Response” folder and your Spreadsheet ID
We need to get the IDs of both of these things for later use in the Apps Script. So for now, follow the steps below to find the IDs and store them somewhere you can reference, like a google doc or just a scratch note.
Note: To be clear, we are now getting the ID of the folder where you want to keep all the FOIA responses - so not the top-level folder where you are also storing your Google Sheet and Form.
Here’s how to find the IDs:
Folder ID
4) Send a test email to set yourself up for the next step
Finally, before we make the script, forward a test email to your new FOIA gmail alias with an MHID that is in your existing FOIA tracking spreadsheet. So you could go with 2024000 to be safe since that is in the template. Include a random file as an attachment. This is just to test our script once we get it going.
5) Create the Google Apps Script
Go to your Google Sheet and select “Extensions” from the menu bar, then “Apps Script.” You’ll then get to a screen that looks like this. Rename the script to something that makes sense, like “FOIA automation script” and delete all of the existing text in the Code.gs file (the default file it brings you to when you first open the screen).
Copy and paste this long-ass code block into the Code.gs file
We are going to make some minor adjustments in the next step, but for now just copy and paste all the text from the code block below. As a reminder, you should have deleted all the existing text in the Code.gs file, if you haven’t then do that now or just paste over it.
The Script
6) Add in your Spreadsheet ID, Folder ID and Tab Name
7) Save and do a test run
8) Set a trigger to check for new emails
Then, all that is left to do is set up a trigger to automatically check for any new, unread emails that are labeled “FOIA” every five minutes. To do so, go back to your Apps Script and click on the drop down menu next to createTrigger (to the immediate left of “execution log”). This should show you a list of all the functions in your script. Select createTrigger and then press Run.
9) Celebrate!!! (And possibly troubleshoot)
You’re done!! You do not need to do anything else to make this process work. All that you are responsible for now is forwarding in your FOIA responses and attachments, and generally being a badass liberator of public records. Yay!!!
But if things are quite running smoothly yet...
See below for some FAQs and Troubleshooting tips.
FAQ
FAQ: What do I do if I followed all the instructions but I keep getting an error message and idk wtf to do?
I have good news and bad news for you. The bad news is that I have no idea how to fix your problem. But the good news is, ChatGPT does. I figured out how to do all of this by using This bot. I recommend copy and pasting your entire code into a message to this bot, including any error messages you are seeing and asking it if it can figure out what the problem is. It might take a couple tries, but so far it has worked for me. Some tips to keep in mind while using the bot:
FAQ: I sent in my email, but it isn’t finding a match with my MY_IDs. What’s up with that?
Make sure the subject line of your email only contains the MY_ID without any leading or trailing spaces. If that isn’t working, make sure that the emails are showing up in your FOIA label gmail as Unread. If they aren’t, you’ll need to adjust that in your filter settings.
FAQ: What is the point of this Google Doc copy of the email when I also have the URL to the actual email?
I included this because I’ve found that sometimes FOIA officers include some relevant information in the actual body of the email that I sometimes want to share with co-reporters or editors. For example, if I just got a denial email with no attachments, I’d want the record of that to be accessible in the same way that responsive records would be. If someone else clicks on your gmail thread URL, they will just be taken to their own Gmail inbox and not be able to see the email. While a PDF of the email would be a bit more aesthetically pleasing, PDFs count toward your Google Drive storage capacity and IMO, that’s not worth it. But if you would prefer that, I’d suggest using the chat bot option described above to modify your script.
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 specific FOIA form or portal. 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 in 2024: It has def become more of a problem. My bandaid solution right now 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.
TROUBLESHOOTING
I set up the system and everything seems okay... but I keep getting this annoying email from Google listing my “summary of failures.”
TL;DR - Don’t worry about it.
Part 4: Wrapping up
Who’s afraid of little old you? (Reluctant FOIA officers, that’s who)
To recap, here is what you just built for yourself
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 for in your Wildest Dreams.
Bottom line: You’re dope. Be proud of yourself.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.