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

Contributed by Sourabh Choraria. Find me on Twitter @schoraria911, 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.

employee-certificate-gif

Technology highlights

Try it

Select the templatized slide deck

  1. Make a copy of the Employee Certificate slide or select it from the Slides template gallery here.
  2. Identify the unique ID of your Slides document. The ID can be derived from the URL: https://docs.google.com/presentation/d/slideId/edit
  3. 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.

Set up the employee spreadsheet

  1. Make a copy of the sample Employee data spreadsheet and fill it with all the required details.
  2. From the spreadsheet, open the script editor by selecting Tools > Script editor.
  3. Copy and paste your Slides document ID into line 1 of Code.gs replacing SLIDE-ID-GOES-HERE while maintaining the quotes and the Drive folder ID into line 2 by replacing TEMPORARY-FOLDER-ID-GOES-HERE.
  4. Save the changes by navigating to File > Save.
  5. Run the onOpen function to authorize the setup by navigating to Run > Run function > onOpen.

Create and send certificates

  1. 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.
  2. 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.

Next steps

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.

Feedback

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.