function sendCertificates() {
  // Get all employee data from the spreadsheet and identify the headers
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getDataRange().getValues();
  const headers = values[0];
  const empNameIndex = headers.indexOf('Employee Name');
  const dateIndex = headers.indexOf('Date');
  const managerNameIndex = headers.indexOf('Manager Name');
  const titleIndex = headers.indexOf('Title');
  const compNameIndex = headers.indexOf('Company Name');
  const empEmailIndex = headers.indexOf('Employee Email');
  const empSlideIndex = headers.indexOf('Employee Slide');
  const statusIndex = headers.indexOf('Status');

  // Iterate through each row to capture individual details
  for (let i = 1; i < values.length; i++) {
    const rowData = values[i];
    const empName = rowData[empNameIndex];
    const date = rowData[dateIndex];
    const managerName = rowData[managerNameIndex];
    const title = rowData[titleIndex];
    const compName = rowData[compNameIndex];
    const empSlideId = rowData[empSlideIndex];
    const empEmail = rowData[empEmailIndex];

    // Load the employee's personalized Google Slide file
    const attachment = DriveApp.getFileById(empSlideId);

    // Setup the required parameters and send them the email
    const senderName = 'CertBot';
    const subject = empName + ', you\'re awesome!';
    const body = 'Please find your employee appreciation certificate attached.' +
    '\n\n' + compName + ' team';
    GmailApp.sendEmail(empEmail, subject, body, {
      attachments: [attachment.getAs(MimeType.PDF)],
      name: senderName
    });

    // Update the spreadsheet with email status
    sheet.getRange(i + 1, statusIndex + 1).setValue('SENT');
    SpreadsheetApp.flush();
  }
}
Send an email to each individual employee
with a PDF attachment of their appreciation certificate