1. Project Overview
Project Name: IR Form Database Script
Project Manager/Owner: Dominque Stone
Stakeholders: VP of Compliance, VP of Administration, VP of IT
Objective:
To automate the process of identifying and cataloging all documents that begin with “IR” in the Forms Directory. This includes capturing key details such as the IR number, form name, link, and last updated date into a Google Sheet. The project also aims to streamline the identification of outdated forms to minimize confusion among site teams and promote the use of current, accurate forms.
2. Project Goals
- Create an automated script to extract and list relevant “IR” documents from the Forms Directory.
- Provide a centralized Google Sheet that includes:
- IR number
- Form name
- Document link
- Last updated date
- Identify and flag outdated forms to support compliance and usability.
- Simplify processes for site teams to ensure they access the correct forms.
3. Scope
In-Scope:
- Forms stored in the designated Forms Directory.
- Automated data extraction using Google Apps Script.
- Real-time updates to the Google Sheet.
- Collaboration with compliance, administration, and IT teams for requirements and review.
Out-of-Scope:
- Forms stored outside the Forms Directory.
- Manual intervention for document updates or corrections.
4. Deliverables
- A functioning Google Apps Script to automate document identification and data capture.
- A well-organized Google Sheet listing relevant “IR” forms with the required details.
- Documentation on the process and script for future maintenance.
5. Timeline
- Week 1: Requirements gathering and directory analysis.
- Week 2-3: Script development and initial testing.
- Week 4: Stakeholder review and adjustments.
- Week 5: Final implementation and documentation handover.
6. Risks and Mitigation
- Risk: Missing or misidentified documents due to inconsistent naming conventions.
Mitigation: Collaborate with stakeholders to confirm naming patterns and standardize directory structure. - Risk: Limited stakeholder availability for testing and feedback.
Mitigation: Schedule early review sessions and provide clear documentation for asynchronous feedback.
7. Success Metrics
- Comprehensive catalog of “IR” documents available in the Google Sheet.
- Positive feedback from site teams on ease of access to updated forms.
- Reduction in the use of outdated forms by site teams.
function createFormDatabase() {
var sharedDriveFolderId = ‘0AIEJyDfqLIiqUk9PVA’;
var sheetId = ‘1MPKwEFU-jiGiHYWF2sSraInfEV4VJMPXSRWc9VBC2ys’;
var sheet;
try {
sheet = SpreadsheetApp.openById(sheetId).getSheetByName(‘Form Database’) || SpreadsheetApp.openById(sheetId).insertSheet(‘Form Database’);
} catch (e) {
Logger.log(‘Error opening or creating sheet: ‘ + e.message);
MailApp.sendEmail(‘[email protected]’, ‘Script Error: createFormDatabase’, ‘Error opening or creating sheet: ‘ + e.message);
return;
}
try {
sheet.clear();
sheet.getRange(‘A1:F1’).setValues([[‘IR Number’, ‘Name’, ‘Link’, ‘Last Updated’, ‘Folder Name’, ‘Folder Link’]]);
} catch (e) {
Logger.log(‘Error clearing sheet or setting headers: ‘ + e.message);
MailApp.sendEmail(‘[email protected]’, ‘Script Error: createFormDatabase’, ‘Error clearing sheet or setting headers: ‘ + e.message);
return;
}
var formsMap = {};
try {
var folder = DriveApp.getFolderById(sharedDriveFolderId);
processFolder(folder, formsMap, folder.getName(), folder.getUrl());
} catch (e) {
Logger.log(‘Error processing shared drive folder: ‘ + e.message);
MailApp.sendEmail(‘[email protected]’, ‘Script Error: createFormDatabase’, `Error processing shared drive folder (ID: ${sharedDriveFolderId}): ${e.message}`);
}
var formsArray = Object.values(formsMap);
formsArray.sort((a, b) => a.irNumber.localeCompare(b.irNumber));
var row = 2;
formsArray.forEach(form => {
try {
sheet.getRange(row, 1, 1, 6).setValues([[form.irNumber, form.text, form.link, form.lastUpdated, form.folderName, form.folderLink]]);
row++;
} catch (e) {
Logger.log(‘Error writing to sheet: ‘ + e.message);
MailApp.sendEmail(‘[email protected]’, ‘Script Error: createFormDatabase’, `Error writing to sheet for IR Number: ${form.irNumber} – ${e.message}`);
}
});
}
function processFolder(folder, formsMap, parentFolderName, parentFolderLink) {
try {
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
processFile(file, formsMap, parentFolderName, parentFolderLink);
}
var subfolders = folder.getFolders();
while (subfolders.hasNext()) {
var subfolder = subfolders.next();
var subfolderName = parentFolderName + ‘/’ + subfolder.getName();
processFolder(subfolder, formsMap, subfolderName, subfolder.getUrl());
}
} catch (e) {
Logger.log(‘Error processing folder: ‘ + e.message);
MailApp.sendEmail(‘[email protected]’, ‘Script Error: processFolder’, `Error processing folder: ${parentFolderName} – ${e.message}`);
}
}
function processFile(file, formsMap, folderName, folderLink) {
var name = file.getName();
var match = name.match(/^(IR|ir)\s*-?\s*(\d+)(.*)$/i);
if (match) {
var irNumber = ‘IR-‘ + match[2];
var text = match[3].trim();
var link = file.getUrl();
var lastUpdated = file.getLastUpdated().toLocaleDateString();
if (!formsMap[irNumber] || new Date(formsMap[irNumber].lastUpdated).getTime() < file.getLastUpdated().getTime()) {
formsMap[irNumber] = {
irNumber: irNumber,
text: text,
link: link,
lastUpdated: lastUpdated,
folderName: folderName,
folderLink: folderLink
};
}
}
}