Create-and-Send-Personalized-Certificates-using-GAS

Google apps script to create and send personalized certificates to anyone automatically.

AI Summary

Create and Send Personalized Certificates using GAS

A Google Apps Script that automatically generates and emails personalized certificates using data from Google Sheets and a Google Slides template.

Target Users

Google Workspace users with intermediate GAS skills who need to mass-produce event or training certificates.

Problems Solved

Manually creating and emailing certificates for large events is time-consuming and error-prone.

Main Features

Sheet-driven Mail-Merge

Uses SpreadsheetApp to read each row and SlidesApp to replace placeholders, generating individual certificate slides.

Automatic Drive Storage

DriveApp saves each certificate as a PDF in a designated folder, eliminating manual uploads.

Bulk Email via Gmail

Uses GmailApp to attach the generated certificates and send personalized emails to each recipient automatically.

Usage Examples

Minimal setup: Generate and email certificates

// Set up your IDs
const slideTemplateId = 'SLIDE_TEMPLATE_ID';
const sheetId        = 'SHEET_ID';
const outputFolderId = 'DRIVE_FOLDER_ID';

/**
 * Main routine: Sheet → Slide → PDF → Email
 */
function createAndSendCertificates() {
  const sheet   = SpreadsheetApp.openById(sheetId).getActiveSheet();
  const data    = sheet.getDataRange().getValues();
  const headers = data.shift();

  data.forEach(row => {
    const record = headers.reduce((o, k, i) => ({...o, [k]: row[i]}), {});

    // Duplicate slide template
    const slideCopy = DriveApp.getFileById(slideTemplateId).makeCopy();
    const pres      = SlidesApp.openById(slideCopy.getId());

    // Replace placeholders
    pres.getSlides()[0].replaceAllText('{{NAME}}',  record.Name);
    pres.getSlides()[0].replaceAllText('{{EVENT}}', record.Event);
    pres.saveAndClose();

    // Export to PDF and store in Drive
    const pdfBlob = slideCopy.getBlob().getAs('application/pdf');
    const pdfFile = DriveApp.getFolderById(outputFolderId)
                    .createFile(pdfBlob.setName(`${record.Name}_cert.pdf`));

    // Send via Gmail
    GmailApp.sendEmail(record.Email,
      'Your Certificate',
      'Please find your certificate attached.',
      {attachments:[pdfFile]});

    // Clean up the copied slide
    slideCopy.setTrashed(true);
  });
}

Paste this script into a GAS project, replace the IDs with real ones, and run createAndSendCertificates() to create PDFs for each row and email them automatically.

Create-and-Send-Personalized-Certificates-using-GAS

AI Summary