Skip to content
Draft update for FOIA tracker
First up, here’s a high-level overview of what we are going to do.
And that’s it! So once you have this set up, this would 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.
The Actual Steps
Okay hopefully you’re excited about this possibility. Let that fuel you through this part of the process.
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
processedSubjects[subject] = true;
// Check if the subject matches any "MY_ID"
let matchedRow = null;
for (let j = 0; j < idColumn.length; j++) {
if (String(idColumn[j][0]) === subject) {
matchedRow = j + 2; // Row in the spreadsheet (starts at 2 since data starts at B2)
break;
}
}
if (matchedRow === null) {
console.log(`No MY_ID match for subject: ${subject}`);
continue; // Skip to the next email if no match found
}
// Get the corresponding summary for the matched "MY_ID"
const summary = summaryColumn[matchedRow - 2][0];
// Create a new folder in the root folder with subject + summary as title
const folderName = `${subject} - ${summary}`;
const rootFolder = DriveApp.getFolderById(ROOT_FOLDER_ID);
const newFolder = rootFolder.createFolder(folderName);
// Create a Google Doc from the email's plain text content and upload to the folder
const emailPlainText = mostRecentMessage.getPlainBody(); // Get the plain text of the most recent email
const docFile = createGoogleDocFromEmail(subject, emailPlainText);
const fileInFolder = docFile.makeCopy(`${subject} - Email`, newFolder); // Rename and move the file into the new folder
// Check for attachments and upload them to the new folder
const attachments = mostRecentMessage.getAttachments();
attachments.forEach(attachment => {
newFolder.createFile(attachment); // Upload each attachment with original name
});
// Add the folder and email thread links to the sheet
const folderUrl = newFolder.getUrl();
const threadUrl = thread.getPermalink();
sheet.getRange(`P${matchedRow}`).setValue(threadUrl); // Gmail Thread URL in column P
sheet.getRange(`Q${matchedRow}`).setValue(folderUrl); // Google Drive Folder URL in column Q
// Update the "Automation Response Status" column with the current date and time in CST (Chicago time) in ISO format
const currentDate = new Date();
const cstDate = Utilities.formatDate(currentDate, "America/Chicago", "yyyy-MM-dd'T'HH:mm:ssXXX");
sheet.getRange(`R${matchedRow}`).setValue(cstDate); // Column R is "Automation Response Status"
// Mark the email thread as read after processing
thread.markRead();
}
}
// Function to create a Google Doc from email plain text content
function createGoogleDocFromEmail(subject, plainText) {
// Create a new Google Doc
const doc = DocumentApp.create(subject);
const docBody = doc.getBody();
// Append the plain text content to the Google Doc
docBody.appendParagraph('Email Content:');
docBody.appendParagraph(plainText);
// Save and close the document
doc.saveAndClose();
return DriveApp.getFileById(doc.getId()); // Return the Google Doc file
}
// Function to set up a time-driven trigger to run checkEmails every 5 minutes
function createTrigger() {
// First, clear any existing triggers for the checkEmails function to avoid duplicates
deleteExistingTriggers();
// Create a new time-driven trigger to run checkEmails every 5 minutes
ScriptApp.newTrigger('checkEmails')
.timeBased()
.everyMinutes(5) // Runs every 5 minutes
.create();
}
// Helper function to delete existing triggers for the 'checkEmails' function
function deleteExistingTriggers() {
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === 'checkEmails') {
ScriptApp.deleteTrigger(trigger);
}
});
}
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.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.