Project Overview
The Property Notices Auto-Forwarding Script is a strategic initiative aimed at centralizing and automating the distribution of critical property-related communications. By establishing a dedicated email address, [email protected], this project seeks to streamline the processing and forwarding of important notices such as tax credits, HUD inspections, compliance reviews, and tenant file audits. This automation will ensure timely and accurate distribution of these notices to the appropriate stakeholders, enhancing operational efficiency and compliance management
Scope
- Email Address Creation: Establish [email protected] as the central point for receiving important property-related communications.
- Automated Processing: Implement an automated script to identify keywords and property names within incoming emails and forward them to relevant stakeholders.
- Stakeholder Notification: Automate the notification process for key stakeholders, including Property Managers, Regional Property Managers (RPMs), Vice Presidents (VPs), and Compliance Specialists.
- Document Management: Save relevant attachments in the appropriate Google Drive folders for easy access and reference
Script
function checkEmails() {
var sheet = SpreadsheetApp.openById(‘your-sheet-id’); // Replace with your Google Sheets ID
var logSheet = SpreadsheetApp.openById(‘log-sheet-id’).getSheetByName(‘Log’); // Replace with your Log Sheet ID
var data = sheet.getSheetByName(‘Sheet1’).getDataRange().getValues();
var adminEmails = [‘[email protected]’, ‘[email protected]’, ‘[email protected]’]; // Replace with the admin emails
var rmadisonEmail = ‘[email protected]’;
var threads = GmailApp.search(‘label:inbox is:unread’);
threads.forEach(function(thread) {
try {
var messages = thread.getMessages();
messages.forEach(function(message) {
if (message.isUnread()) {
var subject = message.getSubject();
var body = message.getPlainBody();
var attachments = message.getAttachments();
var foundKeywords = body.match(/Tax Credit|HUD|Inspection|agency|file audit|MOR|tenant file audit/i);
var matchedProperty = data.find(function(row) {
return body.includes(row[0]); // Assuming first column is Property Name
});
if (matchedProperty) {
var propertyEmail = matchedProperty[1];
var managerEmail = matchedProperty[2];
var vpEmail = matchedProperty[3];
var complianceEmail = matchedProperty[4];
var folderId = matchedProperty[5];
// Build the recipient list
var recipientEmails = [propertyEmail, managerEmail, vpEmail, complianceEmail];
if (foundKeywords && foundKeywords.some(keyword => [‘MOR’, ‘file audit’, ‘tenant file audit’].includes(keyword))) {
recipientEmails.push(dstoneEmail);
}
recipientEmails = recipientEmails.join(‘,’);
// Forward the email
GmailApp.sendEmail(recipientEmails, subject, body, {attachments: attachments});
// Save attachments to Drive
attachments.forEach(function(attachment) {
DriveApp.getFolderById(folderId).createFile(attachment);
});
// Log the action
logSheet.appendRow([new Date(), ‘Email processed’, subject, recipientEmails, ‘Success’]);
// Mark as read after processing
message.markRead();
}
}
});
} catch (error) {
// Log the error
logSheet.appendRow([new Date(), ‘Error processing email’, error.message, thread.getFirstMessageSubject(), ‘Failed’]);
// Notify the administrators
adminEmails.forEach(function(adminEmail) {
GmailApp.sendEmail(adminEmail, ‘Error in Email Processing Script’, `An error occurred: ${error.message}\nSubject: ${thread.getFirstMessageSubject()}`);
});
}
});
}
function setTriggers() {
// Delete existing triggers to avoid duplicates
ScriptApp.getProjectTriggers().forEach(trigger => ScriptApp.deleteTrigger(trigger));
// Set up a time-based trigger to run every hour between 8 am and 6 pm from Monday to Friday
ScriptApp.newTrigger(‘checkEmails’)
.timeBased()
.everyHours(1)
.inTimezone(“America/New_York”)
.between(8, 18)
.onWeekDays(ScriptApp.WeekDay.MONDAY, ScriptApp.WeekDay.FRIDAY)
.create();
}