Draft - Airtable FactChecking Tutorial

Madison's super dope fact-checking template and tutorial

Scratch

Admin - FC

Draft update for FOIA tracker

Explore

Draft update for FOIA tracker

First up, here’s a high-level overview of what we are going to do.

Create 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” label

When one appears, it 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 data

Within 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

And that’s it! So once you have this set up, this would be your workflow for processing new FOIA responses.

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.

Go to your FOIA Tracking Spreadsheet and figure out what the MY_ID is for this request.

Back in email, forward the whole thing to your Gmail alias for FOIAs. In the subject line, delete anything that is already there and replace it with only your MY_ID.

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.

The Actual Steps

Okay hopefully you’re excited about this possibility. Let that fuel you through this part of the process.

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 it in the same spot you store your Google Form and Tracking Google Sheet.

Then, you need to get the Folder ID for that new umbrella folder, as well as the Spreadsheet ID from your Super Dope FOIA Tracking Sheet. I’ll explain where to find both below, but I recommend you copy and paste both somewhere you can reference again.

Folder ID:

Go to the folder you just created to store all of your FOIA response sub-folders. In the URL, copy everything after folders/

⁠

2024ot-10-10 - 16-37 - CleanShot - Google Chrome- Test Folder for Copy - Google Drive.jpg

⁠

Spreadsheet ID

Go to your spreadsheet and copy everything after d/ and before /edit

⁠

2024ot-10-10 - 16-40 - CleanShot - Google Chrome- Copy of MJH FOIA Requests Template - Responses - Google Sheets.jpg

⁠

Finally, 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. Note: don’t open it in your gmail, we want it to remain unread.

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.

⁠

2024ot-10-10 - 15-53 - CleanShot - Google Chrome- Untitled project - Project Editor - Apps Script.jpg

⁠

Rename the script to something that makes sense, like “FOIA automation script”

Delete all of the existing text in the

Code.gs⁠

screen, that is the file that should be open by default when you first get to the Apps Script screen.

Copy this entire script and paste it into the

Code.gs⁠

file

function checkEmails() {

// Define your label and the root folder ID

const LABEL_NAME = "FOIA";

const ROOT_FOLDER_ID = "1tHEReKBeDvvTPP7IbvZj-v03zODQ-LH2"; // Replace with your actual root folder ID

const SPREADSHEET_ID = "16ttwBoCQAY6LzITTRIrTmtQpafz0U8WOv-hln9lLNhw"; // Replace with your actual spreadsheet ID

// Get Gmail label and search for unread messages

const label = GmailApp.getUserLabelByName(LABEL_NAME);

// Check if the label exists

if (!label) {

console.log(`Label "${LABEL_NAME}" not found`);

return; // Stop the function if the label doesn't exist

}

const threads = label.getThreads(0, 10); // Adjust range for more threads

if (threads.length === 0) {

console.log("No unread threads found under the label.");

return; // Exit if no threads are found

}

// Open the spreadsheet using its ID and get the relevant sheet

let sheet;

try {

sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName('Form Responses'); // Use your actual sheet name

} catch (error) {

console.error("Error accessing spreadsheet:", error);

return; // Exit if there's an error opening the sheet

}

const idColumn = sheet.getRange('B2:B').getValues(); // "MY_ID" in column B

const summaryColumn = sheet.getRange('I2:I').getValues(); // Summary in column I

const processedSubjects = {}; // To track already processed email subjects

for (let i = 0; i < threads.length; i++) {

const thread = threads[i];

// Process only unread threads

if (!thread.isUnread()) {

continue; // Skip if the thread has already been read

}

const messages = thread.getMessages();

const mostRecentMessage = messages[messages.length - 1]; // Get the most recent message

const subject = mostRecentMessage.getSubject();

// Skip if this subject has already been processed in this run

if (processedSubjects[subject]) {

continue;

}

// Mark this subject as processed

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.