Admit Card System
// Google Apps Script - Admit Card PDF Generation and Email System
// ========== CONFIGURATION - UPDATE THESE VALUES ==========
const CONFIG = {
// Google Sheets ID (from the URL of your sheet)
SHEET_ID: 'BHmkI2w68IlwzRkvgI5VT0SrUwZ8',
// Google Docs template ID (your admit card template)
TEMPLATE_DOC_ID: '12i3NQv3Eb-In8xfVhopOT8fbjFq4JI3R2E',
// Your email details
SENDER_NAME: '',
EMAIL_SUBJECT: 'Your Admit Card',
// Google Drive folder to save PDFs (optional - leave empty to save in root)
PDF_FOLDER_NAME: 'Admit Cards 2025'
};
// ========== MAIN FUNCTIONS ==========
/**
* Main function to process all students
* Run this function to start the entire process
*/
function processAllStudents() {
try {
console.log('🚀 Starting admit card generation process...');
// Get clean student data
const students = getCleanStudentData();
console.log(`📊 Processing ${students.length} unique students`);
// Get or create PDF folder
const pdfFolder = getPdfFolder();
// Process each student
let successCount = 0;
let errorCount = 0;
students.forEach((student, index) => {
try {
console.log(`📝 Processing ${index + 1}/${students.length}: ${student.name} (${student.trade})`);
// Generate PDF
const pdfBlob = generateAdmitCardPdf(student);
// Save PDF to Drive
const pdfFile = savePdfToDrive(pdfBlob, student, pdfFolder);
// Send email
sendEmailWithPdf(student, pdfFile);
successCount++;
console.log(`✅ Success: ${student.name}`);
} catch (error) {
errorCount++;
console.error(`❌ Error processing ${student.name}:`, error.toString());
}
});
// Final report
console.log('\n' + '='.repeat(50));
console.log('🎉 PROCESS COMPLETED!');
console.log(`✅ Successful: ${successCount}`);
console.log(`❌ Failed: ${errorCount}`);
console.log(`📁 PDFs saved in: ${CONFIG.PDF_FOLDER_NAME || 'My Drive'}`);
console.log('='.repeat(50));
// Show summary popup
const message = `Process Completed!\n\nSuccessful: ${successCount}\nFailed: ${errorCount}\n\nCheck the console for detailed logs.`;
SpreadsheetApp.getUi().alert('Admit Card System', message, SpreadsheetApp.getUi().Button.OK);
} catch (error) {
console.error('Fatal error:', error.toString());
SpreadsheetApp.getUi().alert('Error', 'Fatal error occurred. Check console for details.', SpreadsheetApp.getUi().Button.OK);
}
}
/**
* Get clean student data from Google Sheets (removes duplicates)
*/
function getCleanStudentData() {
const sheet = SpreadsheetApp.openById(CONFIG.SHEET_ID).getActiveSheet();
const data = sheet.getDataRange().getValues();
// Get headers (first row)
const headers = data[0].map(header => header.toString().toLowerCase().trim());
// Find column indices
const nameCol = findColumnIndex(headers, ['name']);
const emailCol = findColumnIndex(headers, ['email']);
const tradeCol = findColumnIndex(headers, ['trade']);
const regNoCol = findColumnIndex(headers, ['reg no', 'reg no.', 'registration no', 'regno']);
if (nameCol === -1 || emailCol === -1 || tradeCol === -1 || regNoCol === -1) {
throw new Error('Required columns not found. Expected: Name, Email, Trade, Reg No.');
}
// Process data (skip header row)
const students = [];
const duplicateCheck = new Set();
for (let i = 1; i < data.length; i++) {
const row = data[i];
// Skip empty rows
if (!row[nameCol] || !row[emailCol] || !row[tradeCol] || !row[regNoCol]) {
continue;
}
const student = {
name: row[nameCol].toString().trim(),
email: row[emailCol].toString().trim(),
trade: row[tradeCol].toString().trim(),
regNo: row[regNoCol].toString().trim()
};
// Create unique key for duplicate checking
const uniqueKey = `${student.name.toLowerCase()}_${student.trade.toLowerCase()}`;
// Skip if duplicate
if (duplicateCheck.has(uniqueKey)) {
console.log(`⚠️ Duplicate skipped: ${student.name} (${student.trade})`);
continue;
}
duplicateCheck.add(uniqueKey);
students.push(student);
}
console.log(`📋 Total records: ${data.length - 1}`);
console.log(`✨ Unique students: ${students.length}`);
console.log(`🔄 Duplicates removed: ${(data.length - 1) - students.length}`);
return students;
}
/**
* Generate admit card PDF for a student
*/
function generateAdmitCardPdf(student) {
// Make a copy of the template
const templateDoc = DriveApp.getFileById(CONFIG.TEMPLATE_DOC_ID);
const tempDoc = templateDoc.makeCopy(`temp_${student.name}_${Date.now()}`);
try {
// Open the copied document
const doc = DocumentApp.openById(tempDoc.getId());
const body = doc.getBody();
// Get current date
const currentDate = new Date().toLocaleDateString('en-IN');
// Replace placeholders
const replacements = {
'{{NAME}}': student.name.toUpperCase(),
'{{TRADE}}': student.trade.toUpperCase(),
'{{REG_NO}}': student.regNo,
'{{EMAIL}}': student.email,
'{{DATE}}': currentDate
};
// Perform replacements
Object.keys(replacements).forEach(placeholder => {
body.replaceText(placeholder, replacements[placeholder]);
});
// Save the document
doc.saveAndClose();
// Convert to PDF
const pdfBlob = tempDoc.getAs('application/pdf');
pdfBlob.setName(`${student.name}_${student.trade}_AdmitCard.pdf`);
return pdfBlob;
} finally {
// Clean up temporary document
DriveApp.getFileById(tempDoc.getId()).setTrashed(true);
}
}
/**
* Save PDF to Google Drive
*/
function savePdfToDrive(pdfBlob, student, folder) {
const fileName = `${student.name}_${student.trade}_AdmitCard.pdf`;
if (folder) {
return folder.createFile(pdfBlob);
} else {
return DriveApp.createFile(pdfBlob);
}
}
/**
* Send email with PDF attachment
*/
function sendEmailWithPdf(student, pdfFile) {
const subject = `${CONFIG.EMAIL_SUBJECT} - ${student.name} (${student.trade})`;
const htmlBody = `
<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;">
<h2 style="color: #2c3e50;">Admit Card</h2>
<p>Dear <strong>${student.name}</strong>,</p>
<p>Please find your admit card attached for the following details:</p>
<div style="background-color: #f8f9fa; padding: 15px; border-radius: 5px; margin: 15px 0;">
<p><strong>Trade:</strong> ${student.trade}</p>
<p><strong>Registration Number:</strong> ${student.regNo}</p>
<p><strong>Date:</strong> ${new Date().toLocaleDateString('en-IN')}</p>
</div>
<p>Please bring a printed copy of this admit card to the examination center.</p>
<p>Best regards,<br>
<strong>${CONFIG.SENDER_NAME}</strong></p>
<hr style="margin-top: 30px; border: none; border-top: 1px solid #eee;">
<p style="font-size: 12px; color: #666;">
This is an automated email. Please do not reply to this message.
</p>
</div>
`;
const plainBody = `
Dear ${student.name},
Please find your admit card attached for the trade: ${student.trade}
Registration Number: ${student.regNo}
Date: ${new Date().toLocaleDateString('en-IN')}
Please bring a printed copy of this admit card to the examination center.
Best regards,
${CONFIG.SENDER_NAME}
`;
GmailApp.sendEmail(
student.email,
subject,
plainBody,
{
htmlBody: htmlBody,
attachments: [pdfFile.getBlob()],
name: CONFIG.SENDER_NAME
}
);
}
/**
* Get or create PDF folder in Google Drive
*/
function getPdfFolder() {
if (!CONFIG.PDF_FOLDER_NAME) {
return null;
}
const folders = DriveApp.getFoldersByName(CONFIG.PDF_FOLDER_NAME);
if (folders.hasNext()) {
return folders.next();
} else {
return DriveApp.createFolder(CONFIG.PDF_FOLDER_NAME);
}
}
/**
* Helper function to find column index
*/
function findColumnIndex(headers, possibleNames) {
for (let name of possibleNames) {
const index = headers.indexOf(name.toLowerCase());
if (index !== -1) return index;
}
return -1;
}
// ========== UTILITY FUNCTIONS ==========
/**
* Test function - process only first 3 students
*/
function testWithFewStudents() {
try {
console.log('🧪 Testing with first 3 students...');
const allStudents = getCleanStudentData();
const testStudents = allStudents.slice(0, 3);
console.log(`📊 Processing ${testStudents.length} test students`);
const pdfFolder = getPdfFolder();
testStudents.forEach((student, index) => {
try {
console.log(`📝 Processing ${index + 1}/${testStudents.length}: ${student.name}`);
const pdfBlob = generateAdmitCardPdf(student);
const pdfFile = savePdfToDrive(pdfBlob, student, pdfFolder);
sendEmailWithPdf(student, pdfFile);
console.log(`✅ Success: ${student.name}`);
} catch (error) {
console.error(`❌ Error: ${student.name}:`, error.toString());
}
});
console.log('🎉 Test completed!');
SpreadsheetApp.getUi().alert('Test Completed', 'Check console for results. If everything looks good, run processAllStudents()', SpreadsheetApp.getUi().Button.OK);
} catch (error) {
console.error('Test error:', error.toString());
SpreadsheetApp.getUi().alert('Test Error', error.toString(), SpreadsheetApp.getUi().Button.OK);
}
}
/**
* Setup function - validates configuration
*/
function validateSetup() {
const errors = [];
// Check Sheet ID
try {
SpreadsheetApp.openById(CONFIG.SHEET_ID);
console.log('✅ Sheet access: OK');
} catch (e) {
errors.push('❌ Sheet ID invalid or no access');
}
// Check Template Doc ID
try {
DocumentApp.openById(CONFIG.TEMPLATE_DOC_ID);
console.log('✅ Template document access: OK');
} catch (e) {
errors.push('❌ Template document ID invalid or no access');
}
if (errors.length > 0) {
const errorMessage = 'Setup Issues Found:\n\n' + errors.join('\n');
console.error(errorMessage);
SpreadsheetApp.getUi().alert('Setup Validation', errorMessage, SpreadsheetApp.getUi().Button.OK);
return false;
}
console.log('🎉 Setup validation passed!');
SpreadsheetApp.getUi().alert('Setup Validation', 'All configurations are valid! You can now run the system.', SpreadsheetApp.getUi().Button.OK);
return true;
}
ADMIT CARD
EXAMINATION - 2024
Name: {{NAME}}
Trade: {{TRADE}}
Registration Number: {{REG_NO}}
Email: {{EMAIL}}
Date of Issue: {{DATE}}
Instructions:
1. Bring this admit card to the examination center
2. Carry a valid photo ID
3. Report 30 minutes before exam time
Comments
Post a Comment