向员工发送个性化的感谢证书
使用集合让一切井井有条
根据您的偏好保存内容并对其进行分类。
编码水平:初级
时长:15 分钟
项目类型:使用自定义菜单实现自动化
目标
- 了解解决方案的功能。
- 了解 Apps 脚本服务在解决方案中的作用。
- 设置环境。
- 设置脚本。
- 运行脚本。
关于此解决方案
使用 Google 表格中的员工数据自动自定义 Google 幻灯片员工证书模板,然后使用 Gmail 发送证书。

运作方式
该脚本使用 Google 幻灯片中的“员工证书”演示文稿模板以及包含员工详细信息的 Google 表格电子表格。该脚本会复制模板,并将占位符替换为电子表格中的数据。脚本为每位员工创建幻灯片后,会将每张幻灯片提取为 PDF 附件,然后将证书发送给员工。
Apps 脚本服务
此解决方案使用以下服务:
前提条件
如需使用此示例,您需要满足以下前提条件:
- Google 账号(Google Workspace 账号可能需要管理员批准)。
- 可访问互联网的网络浏览器。
设置环境
点击以下按钮,复制员工证书幻灯片模板。
制作副本
记下演示 ID,以便在后续步骤中使用。您可以在网址中找到该 ID:
https://docs.google.com/presentation/d/PRESENTATION_ID/edit
在云端硬盘中,创建一个新文件夹来存放证书。
记下您的文件夹 ID,以便在后续步骤中使用。您可以在网址中找到该 ID:
https://drive.google.com/drive/folders/FOLDER_ID
设置脚本
点击下方按钮,复制员工证书示例电子表格。此解决方案的 Apps 脚本项目已附加到电子表格。
制作副本
在电子表格中,依次点击扩展 > Apps 脚本,打开 Apps 脚本项目。
对于 slideTemplateId
变量,请将 PRESENTATION_ID
替换为演示的 ID。
对于 tempFolderId
变量,请将 FOLDER_ID
替换为您的文件夹 ID。
点击“保存”图标
。
运行脚本
- 切换回电子表格,然后依次点击表彰
> 创建证书。您可能需要刷新页面才能看到此自定义菜单。
根据提示为脚本授权。
如果 OAuth 权限请求页面显示警告此应用未经过验证,请继续操作,依次选择高级 >
前往“{项目名称}”(不安全)。
依次点击感谢 > 再次点击创建证书。
当所有行的“状态”列都更新为 Created 后,依次点击感谢 > 发送证书。
查看代码
如需查看此解决方案的 Apps 脚本代码,请点击下方的查看源代码:
贡献者
此示例由博主兼 Google 开发者专家 Sourabh Choraria 创建。
此示例由 Google 在 Google 开发者专家的帮助下维护。
后续步骤
如未另行说明,那么本页面中的内容已根据知识共享署名 4.0 许可获得了许可,并且代码示例已根据 Apache 2.0 许可获得了许可。有关详情,请参阅 Google 开发者网站政策。Java 是 Oracle 和/或其关联公司的注册商标。
最后更新时间 (UTC):2025-08-31。
[null,null,["最后更新时间 (UTC):2025-08-31。"],[[["\u003cp\u003eThis solution automates the process of creating and sending personalized employee certificates using Google Slides, Sheets, and Gmail.\u003c/p\u003e\n"],["\u003cp\u003eThe script copies a certificate template, replaces placeholders with employee data from a spreadsheet, and sends the certificates as PDF attachments via email.\u003c/p\u003e\n"],["\u003cp\u003eYou'll need a Google Account, a web browser, and copies of the provided Google Slides template and spreadsheet to use this solution.\u003c/p\u003e\n"],["\u003cp\u003eBefore running the script, you need to set up your environment by making copies of the template and spreadsheet, creating a folder for certificates, and updating the script with the relevant IDs.\u003c/p\u003e\n"],["\u003cp\u003eTo execute the solution, you'll use custom menu options within the spreadsheet to create and send the certificates after authorizing the script.\u003c/p\u003e\n"]]],[],null,["# Send personalized appreciation certificates to employees\n\n**Coding level** : Beginner \n\n**Duration** : 15 minutes \n\n**Project type** : Automation with a [custom menu](/apps-script/guides/menus)\n\nObjectives\n----------\n\n- Understand what the solution does.\n- Understand what the Apps Script services do within the solution.\n- Set up your environment.\n- Set up the script.\n- Run the script.\n\nAbout this solution\n-------------------\n\nAutomatically customize the Google Slides Employee Certificate template\nwith employee data in Google Sheets, then send the certificates using\nGmail.\n\n### How it works\n\nThe script uses the Employee Certificate presentation template from\nSlides and a Sheets spreadsheet with the employee\ndetails. The script copies the template and\nreplaces the placeholders with data from the spreadsheet. Once the script\ncreates a slide for every employee, it extracts each individual slide as a PDF\nattachment and sends the certificates to the employees.\n\n### Apps Script services\n\nThis solution uses the following services:\n\n- [Drive service](/apps-script/reference/drive)--Copies the Slides Employee Certificate template.\n- [Spreadsheet service](/apps-script/reference/spreadsheet)--Provides the employee details and updates the status against each listed employee.\n- [Slides service](/apps-script/reference/slides)--Replaces the placeholders in the presentation with the employee data from the spreadsheet.\n- [Gmail service](/apps-script/reference/gmail)--Gets the individual slides as PDFs and sends them to the employees.\n\nPrerequisites\n-------------\n\nTo use this sample, you need the following prerequisites:\n\n- A Google Account (Google Workspace accounts might require administrator approval).\n- A web browser with access to the internet.\n\nSet up your environment\n-----------------------\n\n1. Click the following button to make a copy of the **Employee certificates**\n Slides template.\n\n [Make a copy](https://docs.google.com/presentation/d/1gEHDjlSqnKnfi_Ahbt9Jhhz4oW2badtd8kLKjzC_tM0/copy)\n\n2. Take note of your presentation ID for use in a later step. You can\n find the ID in the URL:\n\n `https://docs.google.com/presentation/d/`\u003cvar translate=\"no\"\u003ePRESENTATION_ID\u003c/var\u003e`/edit`\n3. In Drive, create a new folder to hold the certificates.\n\n4. Take note of your folder ID for use in a later step. You can find the\n ID in the URL:\n `https://drive.google.com/drive/folders/`\u003cvar translate=\"no\"\u003eFOLDER_ID\u003c/var\u003e\n\nSet up the script\n-----------------\n\n1. Click the button below to make a copy of the **Employee certificates** sample\n spreadsheet. The Apps Script project for this\n solution is attached to the spreadsheet.\n\n [Make a copy](https://docs.google.com/spreadsheets/d/14qudjFSn9PP7-aZ1JkmUmd37-lpZMyfE-0IdD5ZW5Uo/copy)\n\n2. In the spreadsheet, open the Apps Script project by clicking\n **Extensions** \\\u003e **Apps Script**.\n\n3. For the `slideTemplateId` variable, replace\n \u003cvar translate=\"no\"\u003ePRESENTATION_ID\u003cvar translate=\"no\"\u003e\u003c/var\u003e\u003c/var\u003e with the ID of your presentation.\n\n4. For the `tempFolderId` variable, replace \u003cvar translate=\"no\"\u003eFOLDER_ID\u003cvar translate=\"no\"\u003e\u003c/var\u003e\u003c/var\u003e\n with the ID of your folder.\n\n5. Click Save .\n\nRun the script\n--------------\n\n1. Switch back to the spreadsheet and click **Appreciation** \\\u003e **Create certificates**. You might need to refresh the page for this custom menu to appear.\n2. When prompted, authorize the script.\n If the OAuth consent screen displays the warning, **This app isn't verified** ,\n continue by selecting **Advanced** \\\u003e\n **Go to {Project Name} (unsafe)**.\n\n3. Click **Appreciation** \\\u003e\n **Create certificates** again.\n\n4. Once the status column of all the rows has been updated to **Created** , click\n **Appreciation** \\\u003e **Send certificates**.\n\nReview the code\n---------------\n\nTo review the Apps Script code for this solution, click\n**View source code** below: \n\n#### View source code\n\n### Code.gs\n\n\u003cbr /\u003e\n\nsolutions/automations/employee-certificate/Code.js \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/solutions/automations/employee-certificate/Code.js) \n\n```javascript\n// To learn how to use this script, refer to the documentation:\n// https://developers.google.com/apps-script/samples/automations/employee-certificate\n\n/*\nCopyright 2022 Google LLC\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n https://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n*/\n\nconst slideTemplateId = 'PRESENTATION_ID';\nconst tempFolderId = 'FOLDER_ID'; // Create an empty folder in Google Drive\n\n/**\n * Creates a custom menu \"Appreciation\" in the spreadsheet\n * with drop-down options to create and send certificates\n */\nfunction onOpen() {\n const ui = SpreadsheetApp.getUi();\n ui.createMenu('Appreciation')\n .addItem('Create certificates', 'createCertificates')\n .addSeparator()\n .addItem('Send certificates', 'sendCertificates')\n .addToUi();\n}\n\n/**\n * Creates a personalized certificate for each employee\n * and stores every individual Slides doc on Google Drive\n */\nfunction createCertificates() {\n // Load the Google Slide template file\n const template = DriveApp.getFileById(slideTemplateId);\n\n // Get all employee data from the spreadsheet and identify the headers\n const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();\n const values = sheet.getDataRange().getValues();\n const headers = values[0];\n const empNameIndex = headers.indexOf('Employee Name');\n const dateIndex = headers.indexOf('Date');\n const managerNameIndex = headers.indexOf('Manager Name');\n const titleIndex = headers.indexOf('Title');\n const compNameIndex = headers.indexOf('Company Name');\n const empEmailIndex = headers.indexOf('Employee Email');\n const empSlideIndex = headers.indexOf('Employee Slide');\n const statusIndex = headers.indexOf('Status');\n\n // Iterate through each row to capture individual details\n for (let i = 1; i \u003c values.length; i++) {\n const rowData = values[i];\n const empName = rowData[empNameIndex];\n const date = rowData[dateIndex];\n const managerName = rowData[managerNameIndex];\n const title = rowData[titleIndex];\n const compName = rowData[compNameIndex];\n\n // Make a copy of the Slide template and rename it with employee name\n const tempFolder = DriveApp.getFolderById(tempFolderId);\n const empSlideId = template.makeCopy(tempFolder).setName(empName).getId();\n const empSlide = SlidesApp.openById(empSlideId).getSlides()[0];\n\n // Replace placeholder values with actual employee related details\n empSlide.replaceAllText('Employee Name', empName);\n empSlide.replaceAllText('Date', 'Date: ' + Utilities.formatDate(date, Session.getScriptTimeZone(), 'MMMM dd, yyyy'));\n empSlide.replaceAllText('Your Name', managerName);\n empSlide.replaceAllText('Title', title);\n empSlide.replaceAllText('Company Name', compName);\n\n // Update the spreadsheet with the new Slide Id and status\n sheet.getRange(i + 1, empSlideIndex + 1).setValue(empSlideId);\n sheet.getRange(i + 1, statusIndex + 1).setValue('CREATED');\n SpreadsheetApp.flush();\n }\n}\n\n/**\n * Send an email to each individual employee\n * with a PDF attachment of their appreciation certificate\n */\nfunction sendCertificates() {\n // Get all employee data from the spreadsheet and identify the headers\n const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();\n const values = sheet.getDataRange().getValues();\n const headers = values[0];\n const empNameIndex = headers.indexOf('Employee Name');\n const dateIndex = headers.indexOf('Date');\n const managerNameIndex = headers.indexOf('Manager Name');\n const titleIndex = headers.indexOf('Title');\n const compNameIndex = headers.indexOf('Company Name');\n const empEmailIndex = headers.indexOf('Employee Email');\n const empSlideIndex = headers.indexOf('Employee Slide');\n const statusIndex = headers.indexOf('Status');\n\n // Iterate through each row to capture individual details\n for (let i = 1; i \u003c values.length; i++) {\n const rowData = values[i];\n const empName = rowData[empNameIndex];\n const date = rowData[dateIndex];\n const managerName = rowData[managerNameIndex];\n const title = rowData[titleIndex];\n const compName = rowData[compNameIndex];\n const empSlideId = rowData[empSlideIndex];\n const empEmail = rowData[empEmailIndex];\n\n // Load the employee's personalized Google Slide file\n const attachment = DriveApp.getFileById(empSlideId);\n\n // Setup the required parameters and send them the email\n const senderName = 'CertBot';\n const subject = empName + ', you\\'re awesome!';\n const body = 'Please find your employee appreciation certificate attached.' +\n '\\n\\n' + compName + ' team';\n GmailApp.sendEmail(empEmail, subject, body, {\n attachments: [attachment.getAs(MimeType.PDF)],\n name: senderName\n });\n\n // Update the spreadsheet with email status\n sheet.getRange(i + 1, statusIndex + 1).setValue('SENT');\n SpreadsheetApp.flush();\n }\n}\n```\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\nContributors\n------------\n\nThis sample was created by Sourabh Choraria, blogger and Google Developer\nExpert.\n\n- Find Sourabh on Twitter [@schoraria911](https://twitter.com/schoraria911).\n- Read Sourabh's [blog](https://script.gs/).\n\nThis sample is maintained by Google with the help of Google Developer Experts.\n\nNext steps\n----------\n\n- [Custom menus in Google Workspace](/apps-script/guides/menus)"]]