Madison's Super Dope FOIA Tracker Tutorial - 2024

Explore

Madison's Super Dope FOIA Tracker Tutorial - 2024

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

Featuring: A completely necessary number of Taylor Swift GIFs

⭐ 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.)

Video

⁠

Okay, ugh, fine.... I’m convinced. What do I need to do?

To get started, you’ll need:

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. 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.

Expand

Hot Tips to Navigate This Page

Expand toggles

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.

Watch Videos

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.

FYIs & FAQs

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 2 - Basics FAQ & FYI⁠

⁠

Part 3 - Google Automations FAQ & FYI⁠

⁠

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 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.

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!

As mentioned, I’ve usually used Zapier but I’ll be working through

Make.com⁠

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

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

⁠

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’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

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, 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

⁠

Loading…⁠

⁠

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.

⁠

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)

Gallery

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.

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

⭐ Welcome, and get excited for some super FOIA organization!!! ⭐

Overview ​What 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?

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

How to use Madison’s Super Dope FOIA Tracking Template

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

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

Choose your own adventure

A note on automation programs

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

Okay here are the infrastructure steps

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

Now we’re getting to the good stuff!

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.

The Steps

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

The Steps

Google Automations - FAQ & FYI

In Conclusion, You are a FOIA Organization Master.

To recap, here is what you just built for yourself

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

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

Overview What a FOIA system anyway? Ew.

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