Coding level: Beginner
Duration: 15 minutes
Project type: Automation with a custom menu
About this solution
Automatically customize the Google Slides Employee Certificate template with employee data in Google Sheets, then send the certificates using Gmail.
How it works
The script uses the Employee Certificate presentation template from Slides and a Sheets spreadsheet with the employee details. The script copies the template and replaces the placeholders with data from the spreadsheet. Once the script creates a slide for every employee, it extracts each individual slide as a PDF attachment and sends the certificates to the employees.
Apps Script services
This solution uses the following services:
- Drive Service: Copies the Slides Employee Certificate template.
- Spreadsheet Service: Provides the employee details and updates the status against each listed employee.
- Slides Service: Replaces the placeholders in the presentation with the employee data from the spreadsheet.
- Gmail Service: Gets the individual slides as PDFs and sends them to the employees.
Before you begin
To use this sample, you need the following prerequisites:
- A Google Account (Google Workspace accounts might require administrator approval)
- A web browser with access to the internet
Try it
Step 1: Create the Slides presentation and a Drive folder
Click the button below to make a copy of the Employee certificates Slides template.
Make a copyTake note of your presentation ID for use in a later step. You can find the ID in the URL:
https://docs.google.com/presentation/d/PRESENTATION_ID/edit
In Drive, create a new folder to hold the certificates.
Take note of your folder ID for use in a later step. You can find the ID in the URL:
https://drive.google.com/drive/folders/FOLDER_ID
Step 2: Set up the spreadsheet
Click the button below to make a copy of the Employee certificates sample spreadsheet.
Make a copyIn the spreadsheet, open the Apps Script project by clicking Extensions > Apps Script.
For the
slideTemplateId
variable, replacePRESENTATION_ID
with the ID of your presentation.For the
tempFolderId
variable, replaceFOLDER_ID
with the ID of your folder.Click Save
.
Step 3: Create and send the certificates
- Switch back to the spreadsheet and click Appreciation > Create certificates. You might need to refresh the page for this custom menu to appear.
- When prompted, authorize the script.
- Click Appreciation > Create certificates again.
- Once the status column of all the rows has been updated to Created, click Appreciation > Send certificates.
Open the Apps Script project
The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.
Contributors
This sample was created by Sourabh Choraria, blogger and Google Developer Expert.
- Find Sourabh on Twitter @schoraria911.
- Read Sourabh's blog.
This sample is maintained by Google with the help of Google Developer Experts.