Send personalized appreciation certificates to employees

  • This solution automatically customizes Google Slides employee certificates with employee data from Google Sheets and sends them via Gmail.

  • The script uses the Drive, Spreadsheet, Slides, and Gmail Apps Script services to copy templates, retrieve data, replace placeholders, and send emails with PDF attachments.

  • To set up the environment, you need to copy the provided Google Slides template and create a new folder in Google Drive to hold the certificates.

  • Setting up the script involves copying the provided Google Sheets spreadsheet with the attached Apps Script project and updating variables in the script with the IDs of your copied presentation and folder.

  • The script is run from a custom menu in the spreadsheet to create and send certificates, requiring authorization the first time.

Coding level: Beginner
Duration: 15 minutes
Project type: Automation with acustom menu

Objectives

  • Understand what the solution does.
  • Understand what the Apps Script services do within thesolution.
  • Set up your environment.
  • Set up the script.
  • Run the script.

About this solution

Automatically customize the Google Slides Employee Certificate templatewith employee data in Google Sheets, then send the certificates usingGmail.

Employee certificate creation

How it works

The script uses the Employee Certificate presentation template fromSlides and a Sheets spreadsheet with the employeedetails. The script copies the template andreplaces the placeholders with data from the spreadsheet. Once the scriptcreates a slide for every employee, it extracts each individual slide as a PDFattachment and sends the certificates to the employees.

Apps Script services

This solution uses the following services:

  • Drive service–Copies theSlides EmployeeCertificate template.
  • Spreadsheet service–Provides theemployee details and updates the status against each listed employee.
  • Slides service–Replaces theplaceholders inthe presentation with the employee data from the spreadsheet.
  • Gmail service–Gets theindividual slides asPDFs and sends them to the employees.

Prerequisites

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts mightrequire administrator approval).
  • A web browser with access to the internet.

Set up your environment

  1. Click the following button to make a copy of theEmployee certificatesSlides template.
    Make a copy

  2. Take note of your presentation ID for use in a later step. You canfind the ID in the URL:

    https://docs.google.com/presentation/d/PRESENTATION_ID/edit

  3. In Drive, create a new folder to hold the certificates.

  4. Take note of your folder ID for use in a later step. You can find theID in the URL:https://drive.google.com/drive/folders/FOLDER_ID

Set up the script

  1. Click the button below to make a copy of theEmployee certificates samplespreadsheet. The Apps Script project for thissolution is attached to the spreadsheet.
    Make a copy

  2. In the spreadsheet, open the Apps Script project by clickingExtensions>Apps Script.

  3. For theslideTemplateId variable, replacePRESENTATION_ID with the ID of your presentation.

  4. For thetempFolderId variable, replaceFOLDER_IDwith the ID of your folder.

  5. Click SaveSave icon.

Run the script

  1. Switch back to the spreadsheet and clickAppreciation>Create certificates. You might needto refresh the page for this custom menu to appear.

  2. When prompted, authorize the script.If the OAuth consent screen displays the warning,This app isn't verified,continue by selectingAdvanced>Go to {Project Name} (unsafe).

  3. ClickAppreciation>Create certificates again.

  4. Once the status column of all the rows has been updated toCreated, clickAppreciation>Send certificates.

Review the code

To review the Apps Script code for this solution, clickView source code below:

View source code

Code.gs

solutions/automations/employee-certificate/Code.js
// To learn how to use this script, refer to the documentation:// https://developers.google.com/apps-script/samples/automations/employee-certificate/*Copyright 2022 Google LLCLicensed under the Apache License, Version 2.0 (the "License");you may not use this file except in compliance with the License.You may obtain a copy of the License at    https://www.apache.org/licenses/LICENSE-2.0Unless required by applicable law or agreed to in writing, softwaredistributed under the License is distributed on an "AS IS" BASIS,WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.See the License for the specific language governing permissions andlimitations under the License.*/constslideTemplateId="PRESENTATION_ID";consttempFolderId="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 */functiononOpen(){constui=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 */functioncreateCertificates(){// Load the Google Slide template fileconsttemplate=DriveApp.getFileById(slideTemplateId);// Get all employee data from the spreadsheet and identify the headersconstsheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();constvalues=sheet.getDataRange().getValues();constheaders=values[0];constempNameIndex=headers.indexOf("Employee Name");constdateIndex=headers.indexOf("Date");constmanagerNameIndex=headers.indexOf("Manager Name");consttitleIndex=headers.indexOf("Title");constcompNameIndex=headers.indexOf("Company Name");constempEmailIndex=headers.indexOf("Employee Email");constempSlideIndex=headers.indexOf("Employee Slide");conststatusIndex=headers.indexOf("Status");// Iterate through each row to capture individual detailsfor(leti=1;i <values.length;i++){constrowData=values[i];constempName=rowData[empNameIndex];constdate=rowData[dateIndex];constmanagerName=rowData[managerNameIndex];consttitle=rowData[titleIndex];constcompName=rowData[compNameIndex];// Make a copy of the Slide template and rename it with employee nameconsttempFolder=DriveApp.getFolderById(tempFolderId);constempSlideId=template.makeCopy(tempFolder).setName(empName).getId();constempSlide=SlidesApp.openById(empSlideId).getSlides()[0];// Replace placeholder values with actual employee related detailsempSlide.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 statussheet.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 */functionsendCertificates(){// Get all employee data from the spreadsheet and identify the headersconstsheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();constvalues=sheet.getDataRange().getValues();constheaders=values[0];constempNameIndex=headers.indexOf("Employee Name");constdateIndex=headers.indexOf("Date");constmanagerNameIndex=headers.indexOf("Manager Name");consttitleIndex=headers.indexOf("Title");constcompNameIndex=headers.indexOf("Company Name");constempEmailIndex=headers.indexOf("Employee Email");constempSlideIndex=headers.indexOf("Employee Slide");conststatusIndex=headers.indexOf("Status");// Iterate through each row to capture individual detailsfor(leti=1;i <values.length;i++){constrowData=values[i];constempName=rowData[empNameIndex];constdate=rowData[dateIndex];constmanagerName=rowData[managerNameIndex];consttitle=rowData[titleIndex];constcompName=rowData[compNameIndex];constempSlideId=rowData[empSlideIndex];constempEmail=rowData[empEmailIndex];// Load the employee's personalized Google Slide fileconstattachment=DriveApp.getFileById(empSlideId);// Setup the required parameters and send them the emailconstsenderName="CertBot";constsubject=`${empName}, you're awesome!`;constbody=`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 statussheet.getRange(i+1,statusIndex+1).setValue("SENT");SpreadsheetApp.flush();}}

Contributors

This sample was created by Sourabh Choraria, blogger and Google DeveloperExpert.

This sample is maintained by Google with the help of Google Developer Experts.

Next steps

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-11 UTC.