Admit Card Auto System Main
/**
* @fileoverview Enterprise-Grade Admit Card Generation System
* @author Professional Developer (Modified by: sahaniaman)
* @version 2.0.0
* @license MIT
* @lastUpdated 2025-06-29 11:52:13
*
* This system automates the generation and distribution of admit cards
* from spreadsheet data using Google Apps Script. It features:
* - Duplicate detection and filtering
* - Email quota management
* - Comprehensive logging and error handling
* - Admin dashboard
* - Progress tracking and reporting
* - Self-recovery mechanisms
*/
// ==========================================================================================
// CONFIGURATION SECTION - Edit these values to customize the application
// ==========================================================================================
/**
* System configuration object - central place for all configurable parameters
* @const {Object}
*/
const CONFIG = {
// Document IDs and names
documents: {
spreadsheetId: '1PFu9Ir-PPBtairQY-iQUJeJuwbq4tzogbkSYzSMjD7o', // Google Sheet ID here
sheetName: 'Sheet2', // sheet name
templateDocId: '1PGqBOLKIk1ysx0ff0K4bG50XmH8J5dHK4tjcLCzZFT0', // template Doc ID here
pdfFolderName: 'Admit Cards ' + new Date().getFullYear(), // Auto-updates year
},
// Email settings
email: {
senderName: 'IAF Apprenticeship Training',
subject: 'Admit Card for Apprenticeship Training',
dailyLimit: 100,
batchSize: 20, // Number of emails to process before taking a break
batchPauseTime: 5000, // Time in ms to pause between batches (5 sec)
retryCount: 3, // Number of retries for failed emails
},
// Admin dashboard location
dashboard: {
headerCell: 'T1',
startRow: 2,
startCol: 'T',
width: 3,
},
// Exam details - centralized for easy updates
exam: {
date: '20 July 25',
reportingTime: '0800h',
venue: 'IAF Training Center'
},
// System settings
system: {
loggingEnabled: true,
debugMode: false,
maxConcurrentProcesses: 10,
scriptTimeoutBuffer: 30000, // 30 seconds before the 6-min timeout
backupEnabled: true,
versionNumber: '2.0.0',
lastUpdated: '2025-06-29 11:52:13',
createdBy: 'sahaniaman'
}
};
/**
* Column mapping - maps spreadsheet columns to array indices (0-based)
* If the sheet structure changes, just update these mappings
* @const {Object}
*/
const COLS = {
// Basic Application Info
APPLICATION_CODE: 0, // A - Application Code
OPPORTUNITY_NAME: 1, // B - Opportunity Name
ESTABLISHMENT_NAME: 2, // C - Establishment Name
COURSE: 3, // D - Course
// Candidate Personal Info
CANDIDATE_NAME: 4, // E - Candidate Name
EKYC_COMPLETED: 5, // F - eKYC Completed
CANDIDATE_EMAIL: 6, // G - Candidate Email
CANDIDATE_MOBILE: 7, // H - Candidate Mobile
CANDIDATE_DOB: 8, // I - Candidate DOB
CANDIDATE_AGE: 9, // J - Candidate Age
CANDIDATE_FATHER_NAME: 10, // K - Candidate Father Name
CANDIDATE_MOTHER_NAME: 11, // L - Candidate Mother Name
// Demographics & Qualifications
SOCIAL_CATEGORY: 12, // M - Candidate Social Category
GENDER: 13, // N - Gender
QUALIFICATION_ONE: 14, // O - Qualification one
QUALIFICATION_TWO: 15, // P - Qualification two
QUALIFICATION_THREE: 16, // Q - Qualification three
// Location
STATE: 17, // R - State
DISTRICT: 18, // S - District
// Application Status
APPLICATION_STATUS: 19, // T - Application Status
APPLIED_ON: 20, // U - Applied On
PROFILE_STATUS: 21, // V - Profile Status
// Mailing Status
MAILED: 22, // W - Mailed?
TIME_MAILED: 23 // X - Time Mailed
};
// ==========================================================================================
// INITIALIZATION AND MENU SETUP
// ==========================================================================================
/**
* Use this function to install the script to your spreadsheet
*/
function installScript() {
const spreadsheetId = CONFIG.documents.spreadsheetId;
try {
// Create properties
PropertiesService.getScriptProperties().setProperty('installed', 'true');
PropertiesService.getScriptProperties().setProperty('installedOn', new Date().toISOString());
// Log success
Logger.log('✅ Script installed successfully! Please open your spreadsheet to use it.');
} catch (error) {
Logger.log('❌ Installation failed: ' + error.message);
}
}
/**
* Initializes the application and creates the custom menu in the spreadsheet
* This function runs automatically when the spreadsheet is opened
* @param {Object} e - Event object (optional)
*/
function onOpen(e) {
try {
// Try to get the active spreadsheet first
let ss;
try {
// First try with active spreadsheet (works when user opens the sheet)
ss = SpreadsheetApp.getActiveSpreadsheet();
} catch (err) {
// If active spreadsheet fails, use the ID from config (works for triggers)
ss = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId);
Logger.log('Using spreadsheet from config ID instead of active spreadsheet');
}
if (!ss) {
Logger.log('Could not get spreadsheet - menu will not be created');
return;
}
/**
* Creates and adds the custom menu to the spreadsheet UI
* @param {SpreadsheetApp.Spreadsheet} ss - The active spreadsheet
*/
function createCustomMenu(ss) {
try {
const ui = ss.getUi();
const currentUser = Session.getEffectiveUser().getEmail();
const currentTime = new Date('2025-06-29T12:34:26Z'); // Current UTC time
// Create menu with emojis and clear structure
const menu = ui.createMenu('🎓 Admit Card System')
// Main process
.addItem('▶️ Generate Admit Cards', 'runAdmitCardProcess')
.addSeparator()
// Testing and validation
.addSubMenu(ui.createMenu('🔧 Setup & Testing')
.addItem('🧪 Test (First 3 Records)', 'testWithSample')
.addItem('🔍 Validate Configuration', 'validateConfiguration')
.addItem('📊 Setup Admin Dashboard', 'setupAdminDashboard'))
.addSeparator()
// Maintenance options
.addSubMenu(ui.createMenu('⚙️ Maintenance')
.addItem('🔄 Reset Daily Counter', 'resetDailyCounter')
.addItem('📝 View Process Logs', 'viewProcessLogs'))
// Help and information
.addSeparator()
.addItem('ℹ️ About/Help', 'showAboutInfo');
// Add menu to UI
menu.addToUi();
// Log menu creation with UTC timestamp
logInfo(`Menu created by ${currentUser} at ${currentTime.toISOString()}`);
// Store access information in script properties
const accessInfo = {
lastAccessTime: currentTime.toISOString(),
lastAccessUser: currentUser,
lastAccessType: 'menu_creation'
};
PropertiesService.getScriptProperties().setProperties({
'lastAccess': JSON.stringify(accessInfo),
'lastAccessTime': currentTime.toISOString(),
'lastAccessUser': currentUser
});
// Update dashboard if it exists
try {
const dashboard = getAdminDashboard();
if (dashboard) {
updateDashboard(dashboard, {
'Last Access': `${currentTime.toLocaleString()} by ${currentUser}`,
'Menu Status': 'Active'
});
}
} catch (error) {
// Don't throw error if dashboard update fails
logWarning(`Could not update dashboard: ${error.message}`);
}
} catch (error) {
logError('Failed to create menu:', error);
// Try to show error alert if UI is available
try {
ss.getUi().alert(
'⚠️ Menu Creation Error',
'Could not create the menu. Please check the logs for details.\n\n' +
'Error: ' + error.message
);
} catch (e) {
// If UI alert fails, just log
Logger.log('Could not show error alert: ' + e.message);
}
}
}
/**
* Initialization function that runs when the spreadsheet is opened
* @param {Object} e - Event object (optional)
*/
function onOpen(e) {
let ss;
try {
// Try to get the active spreadsheet first
try {
ss = SpreadsheetApp.getActiveSpreadsheet();
} catch (err) {
// Fallback to opening by ID if active sheet isn't available
ss = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId);
logInfo('Using spreadsheet from config ID');
}
if (!ss) {
throw new Error('Could not access spreadsheet');
}
// Create the custom menu
createCustomMenu(ss);
// Validate configuration silently on open
try {
const isValid = validateConfigurationSilent();
if (!isValid) {
logWarning('System configuration needs attention');
}
} catch (error) {
logWarning(`Configuration validation failed: ${error.message}`);
}
} catch (error) {
logError('Error in onOpen:', error);
}
}
/**
* Utility function to log messages with current timestamp
* @param {string} level - Log level (INFO, WARNING, ERROR)
* @param {string} message - Message to log
* @param {Error} [error] - Optional error object
*/
function logMessage(level, message, error = null) {
const timestamp = new Date('2025-06-29T12:34:26Z').toISOString();
const user = 'sahaniaman';
let logEntry = `[${timestamp}] [${user}] [${level}] ${message}`;
if (error) {
logEntry += `\nError: ${error.message}`;
if (CONFIG.system.debugMode && error.stack) {
logEntry += `\nStack: ${error.stack}`;
}
}
Logger.log(logEntry);
}
// Convenience logging functions
const logInfo = (message) => logMessage('INFO', message);
const logWarning = (message) => logMessage('WARNING', message);
const logError = (message, error) => logMessage('ERROR', message, error);
// Auto-validate configuration on open
try {
const isValid = validateConfigurationSilent();
if (!isValid) {
Logger.log('⚠️ System configuration needs attention. Please run Validate Configuration.');
}
} catch (error) {
Logger.log('⚠️ Error during auto-validation: ' + error.message);
}
} catch (error) {
Logger.log('Error creating menu: ' + error.message);
// Continue execution even if menu creation fails
}
}
/**
* Gets UI safely - returns null if UI is not available
* @returns {Object|null} UI object or null if not available
*/
/**
* Gets UI safely with proper error handling and logging
* @returns {GoogleAppsScript.Base.Ui|null} UI object or null if not available
* @lastUpdated 2025-06-29 12:36:09
* @author sahaniaman
*/
function getSafeUi() {
const timestamp = new Date('2025-06-29T12:36:09Z');
const currentUser = 'sahaniaman';
try {
// First try getting UI from active spreadsheet
function getSpreadsheetUi() {
const timestamp = new Date('2025-06-29T12:39:37Z');
const currentUser = 'sahaniaman';
try {
// Get active spreadsheet UI
const ss = SpreadsheetApp.getActiveSpreadsheet();
if (!ss) {
throw new Error('No active spreadsheet found');
}
const ui = ss.getUi();
// Log successful UI access
Logger.log(`[${timestamp.toISOString()}] [${currentUser}] Successfully accessed spreadsheet UI`);
return ui;
} catch (error) {
// Try fallback method
try {
const ss = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId);
const ui = ss.getUi();
Logger.log(`[${timestamp.toISOString()}] [${currentUser}] Accessed UI via fallback method`);
return ui;
} catch (fallbackError) {
Logger.log(`[${timestamp.toISOString()}] [${currentUser}] Failed to access UI: ${error.message}`);
return null;
}
}
}
// Log successful UI access
function logUiAccess(ui) {
const timestamp = new Date('2025-06-29T12:42:36Z');
const currentUser = 'sahaniaman';
// Log successful UI access with detailed information
logMessage('INFO', {
event: 'UI_ACCESS',
user: currentUser,
timestamp: timestamp.toISOString(),
status: 'SUCCESS',
details: 'UI accessed successfully'
});
return ui;
}
} catch (error) {
// Try fallback method using spreadsheet ID
try {
const ss = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId);
const ui = ss.getUi();
logMessage('WARNING',
`UI accessed via fallback method by ${currentUser} at ${timestamp.toISOString()}`
);
return ui;
} catch (fallbackError) {
// Log both original and fallback errors if debug mode is on
if (CONFIG.system.debugMode) {
logMessage('ERROR',
`UI access failed - Original error: ${error.message}\n` +
`Fallback error: ${fallbackError.message}`,
fallbackError
);
} else {
logMessage('ERROR',
`UI not available for user ${currentUser}`,
fallbackError
);
}
return null;
}
}
}
/**
* Helper function for logging with consistent format
* @private
*/
function logMessage(level, message, error = null) {
const timestamp = new Date('2025-06-29T12:36:09Z').toISOString();
let logEntry = `[${timestamp}] [${level}] ${message}`;
if (error && CONFIG.system.debugMode) {
logEntry += `\nStack trace: ${error.stack}`;
}
Logger.log(logEntry);
}
/**
* Shows an alert safely - logs message if UI is not available
* @param {string} title - Alert title
* @param {string} message - Alert message
* @param {Object} buttonSet - UI button set (optional)
* @returns {Object|null} Button response or null if UI not available
*/
function showAlert(title, message, buttonSet) {
const timestamp = new Date('2025-06-29T12:42:36Z');
const currentUser = 'sahaniaman';
try {
const ui = getSafeUi();
if (!ui) {
logMessage('WARNING', {
event: 'SHOW_ALERT_FAILED',
user: currentUser,
timestamp: timestamp.toISOString(),
title: title,
message: message,
reason: 'UI not available'
});
return null;
}
// Show alert and log the action
const result = ui.alert(
title,
message,
buttonSet || ui.ButtonSet.OK
);
logMessage('INFO', {
event: 'SHOW_ALERT',
user: currentUser,
timestamp: timestamp.toISOString(),
title: title,
result: result.toString()
});
return result;
} catch (error) {
logMessage('ERROR', {
event: 'SHOW_ALERT_ERROR',
user: currentUser,
timestamp: timestamp.toISOString(),
title: title,
error: error.message
});
return null;
}
}
/**
* Main entry point for the admit card process
*/
function runAdmitCardProcess() {
try {
// First validate the configuration
if (!validateConfigurationSilent()) {
showAlert(
'⚠️ Configuration Issue',
'Please run "Validate Configuration" first and fix any issues.'
);
return;
}
// Confirm with the user
const ui = getSafeUi();
if (!ui) {
logInfo('Running in non-interactive mode, skipping confirmation');
} else {
const response = ui.alert(
'Confirm Process',
'This will generate and email admit cards to eligible candidates. Continue?',
ui.ButtonSet.YES_NO
);
if (response !== ui.Button.YES) {
showAlert('Process Cancelled', 'No emails were sent.');
return;
}
}
// Start the process
logInfo('🚀 Starting admit card generation process...');
// Initialize state tracking for possible resume
saveProcessState({
status: 'started',
startTime: new Date().toISOString(),
currentRow: 0
});
// Run the actual processing
const result = processAdmitCards();
// Update final state
saveProcessState({
status: 'completed',
endTime: new Date().toISOString(),
result: result
});
// Show completion message
showAlert(
'Process Complete',
`Successfully sent: ${result.successCount}\n` +
`Pending: ${result.pendingCount}\n` +
`Errors: ${result.errorCount}\n\n` +
`Check the admin dashboard for details.`
);
logInfo(`✅ Process completed successfully. Sent: ${result.successCount}, ` +
`Pending: ${result.pendingCount}, Errors: ${result.errorCount}`);
} catch (error) {
logError('❌ Fatal error in main process', error);
// Try to save state for resume capability
try {
saveProcessState({
status: 'failed',
errorMessage: error.message,
errorTime: new Date().toISOString()
});
} catch (e) {
// Just log, don't throw
Logger.log('Could not save error state: ' + e.message);
}
// Show error to user
showAlert(
'❌ Process Error',
'An error occurred: ' + error.message + '\n\n' +
'Check the logs for more details.'
);
}
}
// ==========================================================================================
// CORE PROCESSING FUNCTIONS
// ==========================================================================================
/**
* Main admit card processing function
* @returns {Object} Result statistics
*/
function processAdmitCards() {
// Initialize counters and state
const result = {
successCount: 0,
pendingCount: 0,
errorCount: 0,
skippedCount: 0,
startTime: new Date(),
processingTime: 0
};
// Get sheet and data
const sheet = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId)
.getSheetByName(CONFIG.documents.sheetName);
const data = sheet.getDataRange().getValues();
const headers = data[0];
// Setup for batch updates to improve performance
const mailStatusUpdates = [];
const timeMailedUpdates = [];
// Get the admin dashboard for updates
const dashboard = getAdminDashboard();
// Get the PDF folder
const pdfFolder = getPdfFolder();
// Get daily email count
let emailsSentToday = getEmailsSentToday();
updateDashboardValue(dashboard, 'Daily Email Count', emailsSentToday);
updateDashboardValue(dashboard, 'Daily Email Limit', CONFIG.email.dailyLimit);
// Process duplicate detection
logInfo('Identifying unique candidates...');
const { uniqueStudents, duplicateRecords, pendingCount: initialPendingCount } =
identifyUniqueStudents(data);
result.pendingCount = initialPendingCount;
updateDashboardValue(dashboard, 'Pending', initialPendingCount);
updateDashboardValue(dashboard, 'Duplicates Found', duplicateRecords.size);
logInfo(`Found ${Object.keys(uniqueStudents).length} unique students and ${duplicateRecords.size} duplicates.`);
logInfo(`${initialPendingCount} students pending for email.`);
// Batch counter
let batchCounter = 0;
// Process each row
for (let i = 1; i < data.length; i++) {
try {
// Check for timeout approaching
if (isTimeoutApproaching()) {
logWarning('⚠️ Script timeout approaching, saving progress...');
saveProcessState({
status: 'interrupted',
currentRow: i,
processed: result.successCount,
remaining: result.pendingCount
});
break;
}
// Skip if this is a duplicate record
if (duplicateRecords.has(i)) {
result.skippedCount++;
continue;
}
// Check if we've hit the daily limit
if (emailsSentToday >= CONFIG.email.dailyLimit) {
logWarning(`Daily email limit (${CONFIG.email.dailyLimit}) reached. Stopping.`);
break;
}
const row = data[i];
// Skip if not eligible
if (row[COLS.PROFILE_STATUS] !== 100 ||
row[COLS.MAILED] === 'yes' ||
!row[COLS.CANDIDATE_EMAIL]) {
continue;
}
// Create student object
const student = getStudentFromRow(row);
logInfo(`Processing: ${student.name} (Application Code: ${student.applicationCode})`);
updateDashboardValue(dashboard, 'Currently Processing',
`${student.name} (${student.applicationCode})`);
// Generate PDF
const pdfBlob = generateAdmitCardPdf(student);
// Save to Drive
const pdfFile = savePdfToDrive(pdfBlob, pdfFolder, student);
// Send email
sendEmailWithPdf(student, pdfFile);
// Queue updates for batch processing (more efficient)
mailStatusUpdates.push({
row: i + 1,
col: COLS.MAILED + 1,
value: 'yes'
});
timeMailedUpdates.push({
row: i + 1,
col: COLS.TIME_MAILED + 1,
value: new Date()
});
// Update counters
result.successCount++;
emailsSentToday++;
result.pendingCount--;
// Update dashboard with progress
updateDashboardValue(dashboard, 'Processed', result.successCount);
updateDashboardValue(dashboard, 'Pending', result.pendingCount);
updateDashboardValue(dashboard, 'Daily Email Count', emailsSentToday);
updateDashboardValue(dashboard, 'Last Processed', new Date().toLocaleString());
logInfo(`✅ Successfully processed: ${student.name} (${student.applicationCode})`);
// Batch pause to avoid rate limits
batchCounter++;
if (batchCounter >= CONFIG.email.batchSize) {
logInfo(`Pausing for ${CONFIG.email.batchPauseTime/1000} seconds after processing ${CONFIG.email.batchSize} records...`);
Utilities.sleep(CONFIG.email.batchPauseTime);
batchCounter = 0;
}
} catch (error) {
result.errorCount++;
logError(`Error processing row ${i + 1}:`, error);
// Update dashboard with error
updateDashboardValue(dashboard, 'Last Error',
`Row ${i+1}: ${error.message.substring(0, 100)}`);
}
}
// Apply all updates in batch for better performance
logInfo('Applying batch updates to the spreadsheet...');
applyBatchUpdates(sheet, mailStatusUpdates, timeMailedUpdates);
// Save the count of emails sent today
saveEmailsSentToday(emailsSentToday);
// Calculate processing time
result.processingTime = (new Date() - result.startTime) / 1000;
updateDashboardValue(dashboard, 'Processing Time', `${result.processingTime.toFixed(2)} seconds`);
// Final updates
updateDashboardValue(dashboard, 'Last Run', new Date().toLocaleString());
updateDashboardValue(dashboard, 'Status', 'Complete');
// Create backup if enabled
if (CONFIG.system.backupEnabled) {
try {
createBackup();
updateDashboardValue(dashboard, 'Last Backup', new Date().toLocaleString());
} catch (error) {
logError('Error creating backup:', error);
}
}
return result;
}
/**
* Identifies unique students and counts pending emails
* @param {Array<Array>} data - Sheet data
* @returns {Object} Unique students, duplicate records, and pending count
*/
function identifyUniqueStudents(data) {
const uniqueStudents = {};
const duplicateRecords = new Set();
let pendingCount = 0;
// First pass - identify unique students based on application code + name
for (let i = 1; i < data.length; i++) {
const row = data[i];
const appCode = row[COLS.APPLICATION_CODE];
const name = row[COLS.CANDIDATE_NAME];
// Skip if invalid data
if (!appCode || !name || row[COLS.PROFILE_STATUS] !== 100) {
continue;
}
const key = `${appCode}-${name.toLowerCase()}`;
// If we've already seen this combination, mark as duplicate
if (key in uniqueStudents) {
duplicateRecords.add(i);
} else {
uniqueStudents[key] = i;
}
}
// Count pending emails (only for unique entries)
for (const rowIndex of Object.values(uniqueStudents)) {
const row = data[rowIndex];
if (row[COLS.PROFILE_STATUS] === 100 &&
row[COLS.MAILED] !== 'yes' &&
row[COLS.CANDIDATE_EMAIL]) {
pendingCount++;
}
}
return { uniqueStudents, duplicateRecords, pendingCount };
}
/**
* Applies batch updates to the spreadsheet for better performance
* @param {Sheet} sheet - Google Sheet object
* @param {Array<Object>} mailStatusUpdates - Mail status updates
* @param {Array<Object>} timeMailedUpdates - Time mailed updates
*/
function applyBatchUpdates(sheet, mailStatusUpdates, timeMailedUpdates) {
// Update "Mailed" column in batch
if (mailStatusUpdates.length > 0) {
const mailStatusRange = sheet.getRange(
mailStatusUpdates[0].row,
mailStatusUpdates[0].col,
mailStatusUpdates.length,
1
);
mailStatusRange.setValues(mailStatusUpdates.map(update => [update.value]));
}
// Update "Time Mailed" column in batch
if (timeMailedUpdates.length > 0) {
const timeMailedRange = sheet.getRange(
timeMailedUpdates[0].row,
timeMailedUpdates[0].col,
timeMailedUpdates.length,
1
);
timeMailedRange.setValues(timeMailedUpdates.map(update => [update.value]));
}
}
// ==========================================================================================
// ADMIT CARD GENERATION FUNCTIONS
// ==========================================================================================
/**
* Extracts student information from a row
* @param {Array} row - Row data from spreadsheet
* @returns {Object} Student object with all properties
*/
function getStudentFromRow(row) {
return {
applicationCode: row[COLS.APPLICATION_CODE],
opportunity: row[COLS.OPPORTUNITY_NAME],
establishment: row[COLS.ESTABLISHMENT_NAME],
course: row[COLS.COURSE],
name: row[COLS.CANDIDATE_NAME],
email: row[COLS.CANDIDATE_EMAIL],
mobile: row[COLS.CANDIDATE_MOBILE],
dob: formatDate(row[COLS.CANDIDATE_DOB]),
age: row[COLS.CANDIDATE_AGE],
motherName: row[COLS.CANDIDATE_MOTHER],
gender: row[COLS.GENDER],
state: row[COLS.STATE],
district: row[COLS.DISTRICT],
appliedOn: row[COLS.APPLIED_ON],
// Derived fields
examDate: CONFIG.exam.date,
reportingTime: CONFIG.exam.reportingTime,
venue: CONFIG.exam.venue,
rollNo: `CD/APTC/2023/${row[COLS.APPLICATION_CODE]}`
};
}
/**
* Formats date to DD-MM-YYYY
* @param {Date|string} dateValue - Date to format
* @returns {string} Formatted date
*/
function formatDate(dateValue) {
if (!dateValue) return '';
let date;
if (typeof dateValue === 'string') {
// If it's already a string in YYYY-MM-DD format
const parts = dateValue.split('-');
if (parts.length === 3) {
return `${parts[2]}-${parts[1]}-${parts[0]}`; // Convert to DD-MM-YYYY
}
date = new Date(dateValue);
} else {
date = new Date(dateValue);
}
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'dd-MM-yyyy');
}
/**
* Generates an admit card PDF from the template
* @param {Object} student - Student data
* @returns {Blob} PDF blob
*/
function generateAdmitCardPdf(student) {
logInfo(`Generating PDF for ${student.name} (${student.applicationCode})`);
const templateDoc = DriveApp.getFileById(CONFIG.documents.templateDocId);
const tempDoc = templateDoc.makeCopy(`temp_admit_card_${student.applicationCode}_${Date.now()}`);
try {
const doc = DocumentApp.openById(tempDoc.getId());
const body = doc.getBody();
// Replace placeholders in the document
const replacements = {
'«ApplicationCode»': student.applicationCode,
'«CandididateName»': student.name.toUpperCase(),
'«DOB»': student.dob,
'«Category»': 'GENERAL', // Default if not available
'«ID»': student.applicationCode,
'«CurrentDate»': Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd-MM-yyyy')
};
// Replace all placeholders
for (const [key, value] of Object.entries(replacements)) {
body.replaceText(key, value || '');
}
doc.saveAndClose();
// Convert to PDF
const pdfBlob = tempDoc.getAs('application/pdf');
pdfBlob.setName(`AdmitCard_${student.applicationCode}_${student.name.replace(/\s+/g, '_')}.pdf`);
return pdfBlob;
} finally {
// Clean up the temporary document
try {
tempDoc.setTrashed(true);
} catch (e) {
// Just log, don't throw
logWarning(`Could not trash temp doc: ${e.message}`);
}
}
}
/**
* Saves PDF to Drive folder
* @param {Blob} pdfBlob - PDF blob
* @param {Folder} folder - Drive folder
* @param {Object} student - Student data
* @returns {File} Google Drive file
*/
function savePdfToDrive(pdfBlob, folder, student) {
try {
// Check if file already exists (to avoid duplicates)
const fileName = pdfBlob.getName();
const existingFiles = folder.getFilesByName(fileName);
if (existingFiles.hasNext()) {
// If exists, delete the old version
const existingFile = existingFiles.next();
existingFile.setTrashed(true);
}
// Save the new file
return folder.createFile(pdfBlob);
} catch (error) {
logError(`Error saving PDF for ${student.name}:`, error);
throw new Error(`Failed to save PDF: ${error.message}`);
}
}
/**
* Sends email with PDF attachment
* @param {Object} student - Student data
* @param {File} pdfFile - Google Drive file
*/
function sendEmailWithPdf(student, pdfFile) {
try {
const subject = `${CONFIG.email.subject} - ${student.applicationCode}`;
const htmlBody = getEmailHtmlTemplate(student);
const plainBody = getEmailPlainTemplate(student);
// Send with retry logic
let success = false;
let attempts = 0;
let lastError = null;
while (!success && attempts < CONFIG.email.retryCount) {
attempts++;
try {
GmailApp.sendEmail(
student.email,
subject,
plainBody,
{
htmlBody: htmlBody,
attachments: [pdfFile.getBlob()],
name: CONFIG.email.senderName
}
);
success = true;
} catch (error) {
lastError = error;
logWarning(`Email attempt ${attempts} failed: ${error.message}. Retrying...`);
// Wait before retry
if (attempts < CONFIG.email.retryCount) {
Utilities.sleep(1000 * attempts); // Progressive backoff
}
}
}
if (!success) {
throw new Error(`Failed to send email after ${attempts} attempts: ${lastError.message}`);
}
logInfo(`Email sent successfully to ${student.email}`);
} catch (error) {
logError(`Error sending email to ${student.email}:`, error);
throw new Error(`Failed to send email: ${error.message}`);
}
}
/**
* Generates HTML email template
* @param {Object} student - Student data
* @returns {string} HTML email body
*/
function getEmailHtmlTemplate(student) {
return `
<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;">
<div style="background-color: #003366; padding: 20px; text-align: center;">
<h1 style="color: white; margin: 0;">Apprenticeship Training: Admit Card</h1>
</div>
<div style="padding: 20px; border: 1px solid #ddd; border-top: none;">
<p>Dear <strong>${student.name}</strong>,</p>
<p>Please find attached your admit card for the Apprenticeship Training written exam and on-job testing.</p>
<div style="background-color: #f5f5f5; padding: 15px; border-left: 4px solid #003366; margin: 15px 0;">
<p><strong>Application Code:</strong> ${student.applicationCode}</p>
<p><strong>Applied For:</strong> ${student.opportunity}</p>
<p><strong>Date of Exam:</strong> ${student.examDate}</p>
<p><strong>Reporting Time:</strong> ${student.reportingTime}</p>
<p><strong>Venue:</strong> ${student.venue}</p>
</div>
<p><strong>Important Instructions:</strong></p>
<ul>
<li>Print this admit card and bring it to the examination center</li>
<li>Report to the exam center on or before ${student.reportingTime} on the exam date</li>
<li>Bring all required documents as mentioned in the admit card</li>
<li>Carry Black/Blue ink pen and pencil for the exam</li>
<li>No electronic devices are allowed in the examination hall</li>
</ul>
<p style="font-size: 0.9em; color: #666; margin-top: 30px;">This is an automated email. Please do not reply to this message.</p>
<div style="margin-top: 30px; padding-top: 20px; border-top: 1px solid #ddd;">
<p>Best regards,<br><strong>${CONFIG.email.senderName}</strong></p>
</div>
</div>
</div>
`;
}
/**
* Generates plain text email template
* @param {Object} student - Student data
* @returns {string} Plain text email body
*/
function getEmailPlainTemplate(student) {
return `Dear ${student.name},
Please find attached your admit card for the Apprenticeship Training written exam and on-job testing.
APPLICATION DETAILS:
Application Code: ${student.applicationCode}
Applied For: ${student.opportunity}
Date of Exam: ${student.examDate}
Reporting Time: ${student.reportingTime}
Venue: ${student.venue}
IMPORTANT INSTRUCTIONS:
- Print this admit card and bring it to the examination center
- Report to the exam center on or before ${student.reportingTime} on the exam date
- Bring all required documents as mentioned in the admit card
- Carry Black/Blue ink pen and pencil for the exam
- No electronic devices are allowed in the examination hall
This is an automated email. Please do not reply to this message.
Best regards,
${CONFIG.email.senderName}`;
}
// ==========================================================================================
// ADMIN DASHBOARD FUNCTIONS
// ==========================================================================================
/**
* Sets up the admin dashboard in the spreadsheet
*/
function setupAdminDashboard() {
try {
const sheet = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId)
.getSheetByName(CONFIG.documents.sheetName);
// Header style
const headerCell = sheet.getRange(CONFIG.dashboard.headerCell);
headerCell.setValue('ADMIT CARD SYSTEM DASHBOARD');
headerCell.setFontWeight('bold');
headerCell.setBackground('#003366');
headerCell.setFontColor('#ffffff');
headerCell.setFontSize(14);
headerCell.merge(1, 2); // Merge 3 cells horizontally
// Create dashboard rows
const dashboardData = [
['Status', 'Ready'],
['Version', CONFIG.system.versionNumber],
['Last Updated', CONFIG.system.lastUpdated],
['Last Run', 'Not yet run'],
['Processed', '0'],
['Pending', '0'],
['Daily Email Count', '0'],
['Daily Email Limit', CONFIG.email.dailyLimit],
['Processing Time', '0 seconds'],
['Last Processed', ''],
['Last Error', ''],
['Currently Processing', ''],
['Last Backup', ''],
['Duplicates Found', '0']
];
// Calculate start row and column
const startRow = CONFIG.dashboard.startRow;
const startCol = columnToNumber(CONFIG.dashboard.startCol);
// Set dashboard labels and values
for (let i = 0; i < dashboardData.length; i++) {
const row = startRow + i;
const labelCell = sheet.getRange(row, startCol);
const valueCell = sheet.getRange(row, startCol + 1);
labelCell.setValue(dashboardData[i][0]);
valueCell.setValue(dashboardData[i][1]);
// Style the label
labelCell.setFontWeight('bold');
labelCell.setBackground('#f1f1f1');
// Style the value
valueCell.setBackground('#ffffff');
}
// Auto-resize columns
sheet.autoResizeColumn(startCol);
sheet.autoResizeColumn(startCol + 1);
// Add a note about the dashboard
const noteCell = sheet.getRange(startRow + dashboardData.length + 1, startCol);
noteCell.setValue('Dashboard created on: ' + new Date().toLocaleString());
noteCell.setFontStyle('italic');
noteCell.setFontColor('#666666');
noteCell.merge(1, 2);
showAlert(
'Dashboard Created',
'The admin dashboard has been set up successfully.'
);
} catch (error) {
logError('Error setting up dashboard:', error);
showAlert(
'Dashboard Error',
'Error creating dashboard: ' + error.message
);
}
}
/**
* Gets the admin dashboard range
* @returns {Object} Dashboard range information
*/
function getAdminDashboard() {
const sheet = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId)
.getSheetByName(CONFIG.documents.sheetName);
const startRow = CONFIG.dashboard.startRow;
const startCol = columnToNumber(CONFIG.dashboard.startCol);
return {
sheet: sheet,
startRow: startRow,
startCol: startCol
};
}
/**
* Updates a value in the admin dashboard
* @param {Object} dashboard - Dashboard object
* @param {string} label - Label to update
* @param {*} value - New value
*/
function updateDashboardValue(dashboard, label, value) {
try {
const { sheet, startRow, startCol } = dashboard;
// Search for the label in the dashboard
const data = sheet.getRange(
startRow,
startCol,
20, // Maximum expected rows in dashboard
2 // Two columns: label and value
).getValues();
for (let i = 0; i < data.length; i++) {
if (data[i][0] === label) {
// Found the label, update the value
sheet.getRange(startRow + i, startCol + 1).setValue(value);
break;
}
}
} catch (error) {
// Don't throw, just log - dashboard updates should not break the main process
logWarning(`Error updating dashboard value for ${label}: ${error.message}`);
}
}
// ==========================================================================================
// UTILITY FUNCTIONS
// ==========================================================================================
/**
* Converts column letter to number
* @param {string} column - Column letter (A, B, C, etc.)
* @returns {number} Column number (1-based)
*/
function columnToNumber(column) {
let result = 0;
for (let i = 0; i < column.length; i++) {
result = result * 26 + (column.charCodeAt(i) - 64);
}
return result;
}
/**
* Gets or creates the PDF folder in Drive
* @returns {Folder} Google Drive folder
*/
function getPdfFolder() {
const folders = DriveApp.getFoldersByName(CONFIG.documents.pdfFolderName);
if (folders.hasNext()) {
return folders.next();
} else {
return DriveApp.createFolder(CONFIG.documents.pdfFolderName);
}
}
/**
* Gets the count of emails sent today
* @returns {number} Count of emails sent today
*/
function getEmailsSentToday() {
const props = PropertiesService.getScriptProperties();
const lastEmailDate = props.getProperty('lastEmailDate');
const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
if (lastEmailDate !== today) {
// Reset counter for new day
props.setProperty('emailsSentToday', '0');
return 0;
}
return parseInt(props.getProperty('emailsSentToday') || '0');
}
/**
* Saves the count of emails sent today
* @param {number} count - Count to save
*/
function saveEmailsSentToday(count) {
const props = PropertiesService.getScriptProperties();
props.setProperty('emailsSentToday', count.toString());
props.setProperty('lastEmailDate', Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd'));
}
/**
* Resets the daily email counter
*/
function resetDailyCounter() {
PropertiesService.getScriptProperties().setProperty('emailsSentToday', '0');
PropertiesService.getScriptProperties().setProperty('lastEmailDate', '');
// Update dashboard
try {
const dashboard = getAdminDashboard();
updateDashboardValue(dashboard, 'Daily Email Count', '0');
} catch (e) {
// Just log, don't throw
Logger.log('Error updating dashboard: ' + e.message);
}
logInfo('Daily email counter reset');
showAlert(
'Counter Reset',
'Daily email counter has been reset to 0.'
);
}
/**
* Checks if the script is approaching its timeout limit
* @returns {boolean} True if approaching timeout
*/
function isTimeoutApproaching() {
// Google Apps Script has a 6-minute timeout
// Check if we're within buffer of the timeout
const MAX_RUNTIME_MS = 6 * 60 * 1000 - CONFIG.system.scriptTimeoutBuffer;
const scriptStartTime = PropertiesService.getScriptProperties().getProperty('scriptStartTime');
if (!scriptStartTime) {
// First time checking, set the start time
PropertiesService.getScriptProperties().setProperty('scriptStartTime', new Date().getTime().toString());
return false;
}
const elapsedTime = new Date().getTime() - parseInt(scriptStartTime);
return elapsedTime > MAX_RUNTIME_MS;
}
/**
* Saves process state for possible resume
* @param {Object} state - Process state to save
*/
function saveProcessState(state) {
PropertiesService.getScriptProperties().setProperty('processState', JSON.stringify(state));
}
/**
* Creates a backup of the spreadsheet
*/
function createBackup() {
const ss = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId);
const backupName = `Backup - ${CONFIG.documents.sheetName} - ${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm')}`;
const backupFile = DriveApp.getFileById(CONFIG.documents.spreadsheetId).makeCopy(backupName);
logInfo(`Backup created: ${backupName} (${backupFile.getId()})`);
return backupFile;
}
// ==========================================================================================
// LOGGING FUNCTIONS
// ==========================================================================================
/**
* Logs info level messages
* @param {string} message - Message to log
*/
function logInfo(message) {
if (CONFIG.system.loggingEnabled) {
Logger.log(`[INFO] ${message}`);
}
}
/**
* Logs warning level messages
* @param {string} message - Message to log
*/
function logWarning(message) {
if (CONFIG.system.loggingEnabled) {
Logger.log(`[WARNING] ${message}`);
}
}
/**
* Logs error level messages
* @param {string} message - Message to log
* @param {Error} [error] - Optional error object
*/
function logError(message, error) {
if (CONFIG.system.loggingEnabled) {
if (error) {
Logger.log(`[ERROR] ${message} ${error.message}`);
if (CONFIG.system.debugMode) {
Logger.log(`[DEBUG] Stack trace: ${error.stack}`);
}
} else {
Logger.log(`[ERROR] ${message}`);
}
}
}
/**
* Shows the process logs
*/
function viewProcessLogs() {
const logs = Logger.getLog();
if (!logs) {
showAlert(
'No Logs',
'No logs available. Please run a process first.'
);
return;
}
// Create a temporary HTML dialog to show the logs
const htmlOutput = HtmlService
.createHtmlOutput(`
<html>
<head>
<style>
body { font-family: monospace; padding: 20px; }
.log-entry { margin-bottom: 8px; }
.info { color: #333; }
.warning { color: #f90; }
.error { color: #f00; }
h2 { color: #003366; }
</style>
</head>
<body>
<h2>Process Logs</h2>
<div id="logs">
${logs.replace(/\[INFO\]/g, '<span class="info">[INFO]</span>')
.replace(/\[WARNING\]/g, '<span class="warning">[WARNING]</span>')
.replace(/\[ERROR\]/g, '<span class="error">[ERROR]</span>')
.replace(/\n/g, '<br>')}
</div>
</body>
</html>
`)
.setWidth(700)
.setHeight(500);
const ui = getSafeUi();
if (ui) {
ui.showModalDialog(htmlOutput, 'System Logs');
} else {
Logger.log('Cannot show logs dialog - UI not available');
}
}
// ==========================================================================================
// VALIDATION FUNCTIONS
// ==========================================================================================
/**
* Validates configuration and shows results to user
* @returns {boolean} True if configuration is valid
*/
function validateFullConfiguration() {
const timestamp = new Date('2025-06-29T12:49:09Z');
const currentUser = 'sahaniaman';
try {
// Run complete validation
const results = validateConfiguration();
if (results.isValid) {
showAlert(
'✅ Configuration Valid',
'All configuration settings are valid.\n\n' +
`Validated by: ${currentUser}\n` +
`Time: ${timestamp.toLocaleString()}`
);
}
return results;
} catch (error) {
logMessage('ERROR', {
event: 'VALIDATION_ERROR',
user: currentUser,
timestamp: timestamp.toISOString(),
error: error.message
});
}
}
/**
* Validates configuration silently (without UI)
* @returns {boolean} True if configuration is valid
*/
function validateConfigurationSilent() {
const results = performConfigValidation();
return results.isValid;
}
/**
* Performs the actual configuration validation
* @returns {Object} Validation results
*/
function performConfigValidation() {
const errors = [];
// Validate spreadsheet ID and sheet name
try {
const sheet = SpreadsheetApp.openById(CONFIG.documents.spreadsheetId);
if (!sheet) {
errors.push('❌ Spreadsheet ID is invalid or inaccessible');
} else {
const namedSheet = sheet.getSheetByName(CONFIG.documents.sheetName);
if (!namedSheet) {
errors.push(`❌ Sheet name "${CONFIG.documents.sheetName}" does not exist in the spreadsheet`);
} else {
logInfo('✅ Spreadsheet and sheet name are valid');
}
}
} catch (e) {
errors.push(`❌ Spreadsheet error: ${e.message}`);
}
// Validate template document
try {
const doc = DocumentApp.openById(CONFIG.documents.templateDocId);
if (!doc) {
errors.push('❌ Template document ID is invalid or inaccessible');
} else {
logInfo('✅ Template document is valid');
// Check if template contains required placeholders
const docText = doc.getBody().getText();
const requiredPlaceholders = ['«ApplicationCode»', '«CandididateName»', '«DOB»'];
const missingPlaceholders = [];
for (const placeholder of requiredPlaceholders) {
if (!docText.includes(placeholder)) {
missingPlaceholders.push(placeholder);
}
}
if (missingPlaceholders.length > 0) {
errors.push(`⚠️ Warning: Template is missing these placeholders: ${missingPlaceholders.join(', ')}`);
}
}
} catch (e) {
errors.push(`❌ Template document error: ${e.message}`);
}
// Validate PDF folder
try {
getPdfFolder();
logInfo('✅ PDF folder is valid');
} catch (e) {
errors.push(`❌ PDF folder error: ${e.message}`);
}
// Validate email settings
if (CONFIG.email.dailyLimit <= 0) {
errors.push('❌ Daily email limit must be greater than 0');
}
if (!CONFIG.email.senderName) {
errors.push('❌ Email sender name is not set');
}
// Check if dashboard is set up
try {
const dashboard = getAdminDashboard();
const headerCell = dashboard.sheet.getRange(CONFIG.dashboard.headerCell);
if (!headerCell.getValue().includes('DASHBOARD')) {
errors.push('❌ Admin dashboard is not set up. Please run "Setup Admin Dashboard"');
}
} catch (e) {
errors.push('❌ Admin dashboard error: ' + e.message);
}
return {
isValid: errors.length === 0,
errors: errors
};
}
// ==========================================================================================
// TEST FUNCTIONS
// ==========================================================================================
/**
* Tests the process with a small sample
*/
function testWithSample() {
const originalLimit = CONFIG.email.dailyLimit;
CONFIG.email.dailyLimit = 3; // Override limit for testing
try {
// First validate the configuration
if (!validateConfigurationSilent()) {
showAlert(
'⚠️ Configuration Issue',
'Please run "Validate Configuration" first and fix any issues.'
);
return;
}
// Confirm with the user
const ui = getSafeUi();
if (ui) {
const response = ui.alert(
'Confirm Test',
'This will test the process with up to 3 students. Continue?',
ui.ButtonSet.YES_NO
);
if (response !== ui.Button.YES) {
return;
}
}
logInfo('🧪 Running test with sample data...');
const result = processAdmitCards();
showAlert(
'Test Complete',
`Test results:\n\n` +
`Successfully sent: ${result.successCount}\n` +
`Pending: ${result.pendingCount}\n` +
`Errors: ${result.errorCount}\n\n` +
`Processing time: ${result.processingTime.toFixed(2)} seconds`
);
} catch (error) {
logError('❌ Test error:', error);
showAlert(
'Test Error',
'An error occurred during testing: ' + error.message
);
} finally {
CONFIG.email.dailyLimit = originalLimit; // Restore original limit
}
}
/**
* Shows information about the system
*/
function showAboutInfo() {
const htmlOutput = HtmlService
.createHtmlOutput(`
<html>
<head>
<style>
body { font-family: Arial, sans-serif; padding: 20px; line-height: 1.6; }
h1, h2 { color: #003366; }
.section { margin-bottom: 20px; }
.footer { margin-top: 30px; font-size: 0.9em; color: #666; border-top: 1px solid #eee; padding-top: 10px; }
code { background: #f5f5f5; padding: 2px 4px; border-radius: 3px; }
.highlight { background-color: #fff8dc; padding: 10px; border-left: 4px solid #ffd700; }
</style>
</head>
<body>
<h1>Admit Card Generation System</h1>
<p>Version ${CONFIG.system.versionNumber} | Last Updated: ${CONFIG.system.lastUpdated}</p>
<p>Developed by: ${CONFIG.system.createdBy}</p>
<div class="section">
<h2>Overview</h2>
<p>This system automates the process of generating and emailing admit cards to candidates based on spreadsheet data.</p>
</div>
<div class="section">
<h2>Key Features</h2>
<ul>
<li><strong>Duplicate Detection:</strong> Automatically detects and handles duplicate applications</li>
<li><strong>Email Management:</strong> Respects daily email limits to avoid quota issues</li>
<li><strong>Admin Dashboard:</strong> Provides real-time process monitoring</li>
<li><strong>Error Handling:</strong> Robust error recovery and logging</li>
<li><strong>Performance Optimization:</strong> Batch processing for better performance</li>
</ul>
</div>
<div class="section highlight">
<h2>Template Placeholder Format</h2>
<p>When creating your template document, use the following placeholders:</p>
<ul>
<li><code>«ApplicationCode»</code> - Student's application code</li>
<li><code>«CandididateName»</code> - Student's full name</li>
<li><code>«DOB»</code> - Date of birth</li>
<li><code>«Category»</code> - Category (General, SC, ST, OBC, etc.)</li>
<li><code>«ID»</code> - ID number (used in roll number)</li>
<li><code>«CurrentDate»</code> - Current date (optional)</li>
</ul>
<p>Note: Make sure to use exactly these placeholders with the special «angle quotes».</p>
</div>
<div class="section">
<h2>How to Use</h2>
<ol>
<li><strong>Setup:</strong> Run "Validate Configuration" to check your setup</li>
<li><strong>Dashboard:</strong> Run "Setup Admin Dashboard" to create the monitoring panel</li>
<li><strong>Test:</strong> Run "Test (First 3 Records)" to test with a small sample</li>
<li><strong>Process:</strong> Run "Process Admit Cards" to start the full process</li>
</ol>
</div>
<div class="footer">
<p>Current Date/Time: ${new Date().toLocaleString()}</p>
<p>User: ${Session.getEffectiveUser().getEmail()}</p>
<p>For support or modifications, please contact the system administrator.</p>
</div>
</body>
</html>
`)
.setWidth(700)
.setHeight(600);
const ui = getSafeUi();
if (ui) {
ui.showModalDialog(htmlOutput, 'Admit Card System Help');
} else {
Logger.log('Cannot show help dialog - UI not available');
}
}
Comments
Post a Comment