Update from Oct. 2024
You’ve probably reached this page because you are looking for my tutorial on how to set up automations for your FOIA tracker using either Make.com or Zapier. This used to live in the , but I’ve recently found a much easier way to do this whole process through Google Apps Script. I now include instructions on how to set that up in the main tutorial. But if you previously adopted the Make.com/Zapier method and need to reference any of those steps, you can find them below.
Part 3: Supercharge with automations
We’re only cryptic and machiavellian cause we care (about transparency)
Let’s get ready to automate some FOIAs!!!
Not going to lie, this is going to be a painful process. But the point of these next steps are to give you a system that:
Automatically logs a URL to your FOIA response emails in your tracking spreasheet Automatically creates a folder on google drive of your responsive records and also puts a link to that in your FOIA tracking spreadsheet
AKA at the end of this process, you’ll have your own (mostly) automatic treasure trove of FOIA documents and you’ll never have to remember where you saved a file again.
But first, some caveats...
First off, some thoughts on Google vs. the rest of the world of note-taking tools:
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.
No matter how far you decide to take it, I still recommend starting off with automating your basic google sheet. I do this because first off, I tend to bounce around to different note taking programs every year or so, so it’s nice to have a consistent tracking system in google. Also, google sheets is more universally used than most of the other note-taking programs, so it’s nice to have my requests in that format when I want collaborate with someone on a project.
Second, 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 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.
The Takeaway: We’re about to get into some more complicated automation tasks. While I’m 100% confident you can all do this, it will take some time (and patience) upfront to set it up and ultimately, it may be more than you need from a FOIA system. Assess if the basic level works for you first before moving on.
Jan. 2024 Update: Zapier Option
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. 1) Okay one last thing before we get going.... we need to set up some infrastructure!!!
Yes, really.
Before we get started, we need to set up your Gmail account for FOIA automation success.
This step is somewhat tedious, but necessary for everything going forward.
As I mentioned earlier when we were setting up your Google Drives for the Google Form and Sheet, I recommend taking on this process with a personal email account. I made an entirely new Gmail account specifically to manage my FOIAs and related information. While that move isn’t for everyone, I’m going to assume for the purposes of this tutorial that everyone is at least using a different Gmail address than their work accounts, meaning they are likely logged into multiple accounts at one time.
Okay here are the actual 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” Note: 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)
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.
Remember: For any of the automation processes below to work, you need to consistently make sure that any FOIA emails you send to your alias account only include the MY_ID in the subject line. This means that when you are forwarding in a FOIA response you’ve already received, you’ll need to delete the existing subject line and replace it with your MY_ID.
2) 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.
The Takeaway: The two main automations we are going to go over only involve the Google suite of apps. However, they can be easily adapted to most note-taking programs that are based in spreadsheets. I recommend getting tarted with Google and later syncing with your app of choice if you decide that works best for you.
Part 2A: Gmail + Make.com: Syncing your email threads with your FOIA tracking spreadsheet
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.
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. 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 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’ve done it again! Your first super-dope FOIA automation process is complete and it only gets easier from here.
That one was a doozy, so pat yourself on the back and have a drink (assuming you didn’t already start in the middle of that mess). You’ve earned it.
Part 2B: Gmail + Make.com: Syncing folders of your responsive records with your FOIA tracking spreadsheet
Good news!
This is a very similar process to the workflow we just set up to sync your Gmail threads with your FOIA Tracking spreadsheet. We’ll need to do one more gut-wrenching authorization process, but this one should go much quicker now that you have the hang of it.
So shake off any lingering stress and angst at and let’s take your automation game one step further!
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 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.
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 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 is the tutorial they put out and see below for my video on how to set this up.
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.
3) 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 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.
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.
