Send personalized appreciation certificate to employees
Automatically customize an existing Google Slide certificate template with employee data in Google Sheets and share them using Gmail.
Last updated: September, 2020
Manually creating customized employee certificates in Google Slides can be tiresome. Having to download, attach and send them to each individual can get equally cumbersome. Google Apps Script is the perfect tool to simplify such repetitive work and also eliminate any accidents that end-up having one employee get an appreciation certificate intended for another.
This setup makes use of the 'Employee Certificate' template from Google Slides and a Google Sheet with all the employee details. The script starts by making a copy of the template and replace some of the key placeholders (like Employee Name, Company Name etc.) with data from the sheet. Once a slide for every employee is created, we then run another function that extracts individual slides as a PDF attachment and send it to each employee's email ID.
DriveAppservice is used to make copies of the original Google Slides template
SpreadsheetAppservice is used to refer employee details and update the status against each of them.
- We'll also make use of Custom Menus in Google Workspace.
SlidesAppservice is used to replace the placeholders from the template, with actual employee data.
GmailAppservice is used to get the individual slides as a PDF and send it to respective employee's email ID.
Select the templatized slide deck
- Make a copy of the Employee Certificate slide or select it from the Slides template gallery here.
- Identify the unique ID of your Slides document. The ID can be derived from the URL:
- Create an empty folder in Google Drive and identify its unique ID as well. The ID can be derived from the URL:
Set up the employee spreadsheet
- Make a copy of the sample Employee data spreadsheet and fill it with all the required details.
- From the spreadsheet, open the script editor by selecting Tools > Script editor.
- Copy and paste your Slides document ID into line 1 of
SLIDE-ID-GOES-HEREwhile maintaining the quotes and the Drive folder ID into line 2 by replacing
- Save the changes by navigating to File > Save.
- Run the
onOpenfunction to authorize the setup by navigating to Run > Run function > onOpen.
Create and send certificates
- From the spreadsheet, click on custom menu item Appreciation > Create certificates to start the process.
- Wait untill the creation process is complete; you'll see a Finished script message at the end.
- Once all the rows have the Status as "CREATED", then navigate to Appreciation > Send certificates to start sending the certificates to each employee's email ID.
To get started with Google Apps Script, try out the codelab which guides you through the creation of your first script.
Were you able to get the solution up and running?
If you have an idea for another solution you'd like to see featured in our gallery submit a request on the GitHub issue tracker.