Created
February 23, 2022 09:23
-
-
Save Tomokatsu-Sakamoto/245e285baa986c42b7db74697a5618a1 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let slideTemplateId = "PRESENTATION_ID"; | |
let tempFolderId = "FOLDER_ID"; // Create an empty folder in Google Drive | |
/** | |
* Creates a custom menu "Appreciation" in the spreadsheet | |
* with drop-down options to create and send certificates | |
*/ | |
function onOpen(e) { | |
let ui = SpreadsheetApp.getUi(); | |
ui.createMenu('Appreciation') | |
.addItem('Create certificates', 'createCertificates') | |
.addSeparator() | |
.addItem('Send certificates', 'sendCertificates') | |
.addToUi(); | |
} | |
/** | |
* Creates a personalized certificate for each employee | |
* and stores every individual Slides doc on Google Drive | |
*/ | |
function createCertificates() { | |
// Load the Google Slide template file | |
let template = DriveApp.getFileById(slideTemplateId); | |
// Get all employee data from the spreadsheet and identify the headers | |
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); | |
let values = sheet.getDataRange().getValues(); | |
let headers = values[0]; | |
let empNameIndex = headers.indexOf("Employee Name"); | |
let dateIndex = headers.indexOf("Date"); | |
let managerNameIndex = headers.indexOf("Manager Name"); | |
let titleIndex = headers.indexOf("Title"); | |
let compNameIndex = headers.indexOf("Company Name"); | |
let empEmailIndex = headers.indexOf("Employee Email"); | |
let empSlideIndex = headers.indexOf("Employee Slide"); | |
let statusIndex = headers.indexOf("Status"); | |
// Iterate through each row to capture individual details | |
for (let i = 1; i < values.length; i++) { | |
let rowData = values[i]; | |
let empName = rowData[empNameIndex]; | |
let date = rowData[dateIndex]; | |
let managerName = rowData[managerNameIndex]; | |
let title = rowData[titleIndex]; | |
let compName = rowData[compNameIndex]; | |
// Make a copy of the Slide template and rename it with employee name | |
let tempFolder = DriveApp.getFolderById(tempFolderId); | |
let empSlideId = template.makeCopy(tempFolder).setName(empName).getId(); | |
let empSlide = SlidesApp.openById(empSlideId).getSlides()[0]; | |
// Replace placeholder values with actual employee related details | |
empSlide.replaceAllText("Employee Name", empName); | |
empSlide.replaceAllText("Date", "Date: " + Utilities.formatDate(date, Session.getScriptTimeZone(), "MMMM dd, yyyy")); | |
empSlide.replaceAllText("Your Name", managerName); | |
empSlide.replaceAllText("Title", title); | |
empSlide.replaceAllText("Company Name", compName); | |
// Update the spreadsheet with the new Slide Id and status | |
sheet.getRange(i + 1, empSlideIndex + 1).setValue(empSlideId); | |
sheet.getRange(i + 1, statusIndex + 1).setValue("CREATED"); | |
SpreadsheetApp.flush(); | |
} | |
} | |
/** | |
* Send an email to each individual employee | |
* with a PDF attachment of their appreciation certificate | |
*/ | |
function sendCertificates() { | |
// Get all employee data from the spreadsheet and identify the headers | |
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); | |
let values = sheet.getDataRange().getValues(); | |
let headers = values[0]; | |
let empNameIndex = headers.indexOf("Employee Name"); | |
let dateIndex = headers.indexOf("Date"); | |
let managerNameIndex = headers.indexOf("Manager Name"); | |
let titleIndex = headers.indexOf("Title"); | |
let compNameIndex = headers.indexOf("Company Name"); | |
let empEmailIndex = headers.indexOf("Employee Email"); | |
let empSlideIndex = headers.indexOf("Employee Slide"); | |
let statusIndex = headers.indexOf("Status"); | |
// Iterate through each row to capture individual details | |
for (let i = 1; i < values.length; i++) { | |
let rowData = values[i]; | |
let empName = rowData[empNameIndex]; | |
let date = rowData[dateIndex]; | |
let managerName = rowData[managerNameIndex]; | |
let title = rowData[titleIndex]; | |
let compName = rowData[compNameIndex]; | |
let empSlideId = rowData[empSlideIndex]; | |
let empEmail = rowData[empEmailIndex]; | |
// Load the employee's personalized Google Slide file | |
let attachment = DriveApp.getFileById(empSlideId); | |
// Setup the required parameters and send them the email | |
let senderName = "CertBot"; | |
let subject = empName + ", you're awesome!"; | |
let 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(); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment