| title | description | labels | material_icon | create_time | update_time |
|---|---|---|---|---|---|
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. |
Slides, Sheets, Apps Script, Gmail, Drive |
emoji_events |
2020-09-01 |
2020-09-01 |
Contributed by Sourabh Choraria. Find me on Twitter @choraria, LinkedIn or via my blog on everything Google Apps Script - script.gs.
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.
- The
DriveAppservice is used to make copies of the original Google Slides template - The
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.
- The
SlidesAppservice is used to replace the placeholders from the template, with actual employee data. - The
GmailAppservice is used to get the individual slides as a PDF and send it to respective employee's email ID.
- 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:
https://docs.google.com/presentation/d/slideId/edit - Create an empty folder in Google Drive and identify its unique ID as well. The ID can be derived from the URL:
https://drive.google.com/drive/folders/folderId.
- 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 Extensions > Apps Script.
- Copy and paste your Slides document ID into line 1 of
Code.gsreplacingSLIDE-ID-GOES-HEREwhile maintaining the quotes and the Drive folder ID into line 2 by replacingTEMPORARY-FOLDER-ID-GOES-HERE. - Save the changes by navigating to File > Save.
- Run the
onOpenfunction to authorize the setup by navigating to Run > Run function > onOpen.
- 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.
You can also view the full source code of this solution on GitHub or check out this blog post to learn more about how it was built.