Script for Auto Mailing
//CONFIGURATION
const CONFIG = {
// Google Sheets ID
SHEET_ID: '',
SHEET_NAME: '',
// Google Docs template ID (your admit card template)
TEMPLATE_DOC_ID: '',
// Your email details
SENDER_NAME: 'IAF',
EMAIL_SUBJECT: 'Your Admit Card',
// Google Drive folder to save PDFs
PDF_FOLDER_NAME: 'Admit Cards 2025'
};
// ====================== MAIN PROCESS ======================
// Process all students in the sheet
function processAllStudents() {
try {
console.log('Starting admit card generation...');
const sheet = SpreadsheetApp.openById(CONFIG.SHEET_ID).getSheetByName(CONFIG.SHEET_NAME);
const data = sheet.getDataRange().getValues();
const headers = data[0];
const pdfFolder = getPdfFolder();
let successCount = 0;
let errorCount = 0;
for (let i = 1; i < data.length; i++) {
try {
const student = getStudentFromRow(data[i], headers);
if (!student.email) continue;
console.log(`Processing: ${student.name}`);
const pdfBlob = generateAdmitCardPdf(student);
const pdfFile = savePdfToDrive(pdfBlob, pdfFolder);
sendEmailWithPdf(student, pdfFile);
successCount++;
console.log(`✅ Success: ${student.name}`);
} catch (error) {
errorCount++;
console.error(`❌ Error processing row ${i + 1}:`, error.message);
}
}
console.log(`\nCompleted! Success: ${successCount}, Failed: ${errorCount}`);
Logger.log(`Process Completed: Successful: ${successCount}, Failed: ${errorCount}`);
} catch (error) {
console.error('Fatal error:', error.message);
SpreadsheetApp.getUi().alert('Fatal Error', error.message, SpreadsheetApp.getUi().Button.OK);
}
}
// ====================== CORE FUNCTIONS ======================
// Generate admit card PDF from template
function generateAdmitCardPdf(student) {
const templateDoc = DriveApp.getFileById(CONFIG.TEMPLATE_DOC_ID);
const tempDoc = templateDoc.makeCopy(`temp_${Date.now()}`);
try {
const doc = DocumentApp.openById(tempDoc.getId());
const body = doc.getBody();
const formattedDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy');
const replacements = {
'{{NAME}}': student.name.toUpperCase(),
'{{TRADE}}': student.trade.toUpperCase(),
'{{REG_NO}}': student.regNo,
'{{EMAIL}}': student.email,
'{{DATE}}': formattedDate
};
Object.entries(replacements).forEach(([key, value]) => {
body.replaceText(key, value);
});
doc.saveAndClose();
const pdfBlob = tempDoc.getAs('application/pdf');
pdfBlob.setName(`${student.name}_AdmitCard.pdf`);
return pdfBlob;
} finally {
tempDoc.setTrashed(true);
}
}
// Save PDF to Drive folder
function savePdfToDrive(pdfBlob, folder) {
return folder ? folder.createFile(pdfBlob) : DriveApp.createFile(pdfBlob);
}
// Email admit card as attachment
function sendEmailWithPdf(student, pdfFile) {
const formattedDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy');
const subject = `${CONFIG.EMAIL_SUBJECT} - ${student.name}`;
const htmlBody = `
<div style="font-family: Arial, sans-serif;">
<h2>Admit Card</h2>
<p>Dear <strong>${student.name}</strong>,</p>
<p>Please find your admit card attached.</p>
<div style="background-color: #f8f9fa; padding: 15px; margin: 15px 0;">
<p><strong>Trade:</strong> ${student.trade}</p>
<p><strong>Registration Number:</strong> ${student.regNo}</p>
<p><strong>Date:</strong> ${formattedDate}</p>
</div>
<p>Please bring a printed copy to the examination center.</p>
<p>Best regards,<br><strong>${CONFIG.SENDER_NAME}</strong></p>
</div>
`;
GmailApp.sendEmail(student.email, subject, `Dear ${student.name},\n\nPlease find your admit card attached.\n\nBest regards,\n${CONFIG.SENDER_NAME}`, {
htmlBody,
attachments: [pdfFile.getBlob()],
name: CONFIG.SENDER_NAME
});
}
// ====================== UTILITIES ======================
// Create or get Drive folder
function getPdfFolder() {
if (!CONFIG.PDF_FOLDER_NAME) return null;
const folders = DriveApp.getFoldersByName(CONFIG.PDF_FOLDER_NAME);
return folders.hasNext() ? folders.next() : DriveApp.createFolder(CONFIG.PDF_FOLDER_NAME);
}
// Extract student object from row
function getStudentFromRow(row, headers) {
const get = key => row[headers.indexOf(key)] || '';
return {
name: get('Name'),
trade: get('Trade'),
regNo: get('Registration No'),
email: get('Email')
};
}
// ====================== TEST & VALIDATION ======================
// Test with first 3 students only
function testWithFewStudents() {
try {
console.log('Testing with first 3 students...');
const sheet = SpreadsheetApp.openById(CONFIG.SHEET_ID).getSheetByName(CONFIG.SHEET_NAME);
const data = sheet.getDataRange().getValues();
const headers = data[0];
const pdfFolder = getPdfFolder();
for (let i = 1; i <= Math.min(3, data.length - 1); i++) {
try {
const student = getStudentFromRow(data[i], headers);
if (!student.email) continue;
console.log(`Testing: ${student.name}`);
const pdfBlob = generateAdmitCardPdf(student);
const pdfFile = savePdfToDrive(pdfBlob, pdfFolder);
sendEmailWithPdf(student, pdfFile);
console.log(`✅ Test success: ${student.name}`);
} catch (error) {
console.error(`❌ Test error on row ${i + 1}:`, error.message);
}
}
SpreadsheetApp.getUi().alert('Test Completed', 'Check logs for results.', SpreadsheetApp.getUi().Button.OK);
} catch (error) {
console.error('Test error:', error.message);
SpreadsheetApp.getUi().alert('Test Error', error.message, SpreadsheetApp.getUi().Button.OK);
}
}
// Validate configuration setup
function validateSetup() {
const errors = [];
try {
SpreadsheetApp.openById(CONFIG.SHEET_ID).getSheetByName(CONFIG.SHEET_NAME);
console.log('✅ Sheet access: OK');
} catch (e) {
errors.push('❌ Sheet ID or Sheet Name is invalid');
}
try {
DocumentApp.openById(CONFIG.TEMPLATE_DOC_ID);
console.log('✅ Template document: OK');
} catch (e) {
errors.push('❌ Template document ID is invalid');
}
if (errors.length > 0) {
const errorMessage = 'Setup Issues:\n' + errors.join('\n');
console.error(errorMessage);
SpreadsheetApp.getUi().alert('Setup Validation Failed', errorMessage, SpreadsheetApp.getUi().Button.OK);
return false;
}
console.log('✅ Setup validation passed!');
SpreadsheetApp.getUi().alert('Setup Validation', 'All systems OK!', SpreadsheetApp.getUi().Button.OK);
return true;
}
Doc temp. for dynamic
Name: {{NAME}}
Trade: {{TRADE}}
Registration Number: {{REG_NO}}
Email: {{EMAIL}}
Date of Issue: {{DATE}}
Comments
Post a Comment