编码级别：初级
时长：10 分钟
项目类型：使用事件驱动型触发器和基于时间的触发器的自动化
目标
- 了解该解决方案的用途。
- 了解 Apps Script 服务在解决方案中的作用。
- 设置脚本。
- 运行脚本。
关于此解决方案
新员工通常需要 IT 部门提供系统访问权限和设备。如需管理这些请求，您可以使用 Google 表单创建一个表单，供用户指明员工需要哪些访问权限和设备。IT 团队处理完请求并更新其状态后，请求者会收到电子邮件通知。
工作原理
该脚本会创建设备申请表单。您可以在示例脚本的代码中自定义表单中的项。当有人提交表单后，该脚本会向指定的请求联系人发送电子邮件通知。当电子表格中的请求状态更改为“已完成”后，脚本会向提交表单的人员发送确认电子邮件。
Apps Script 服务
此解决方案使用以下服务：
- 表单服务 - 为 IT 请求创建表单。
- 电子表格服务 - 检查请求表单是否已存在，以减少重复。根据需要将表单回复移至待处理和已完成表格，以管理表单回复。
- 邮件服务 - 创建并发送请求和完成通知电子邮件。
- 脚本服务 - 用于创建触发器。其中一个触发器会在表单提交时触发，另一个触发器会每 5 分钟触发一次，以检查请求的状态是否已标记为“已完成”。
前提条件
如需使用此示例，您需要满足以下前提条件：
- Google 账号（Google Workspace 账号可能需要管理员批准）。
- 一个能够访问互联网的网络浏览器。
设置脚本
创建 Apps 脚本项目
- 点击以下按钮，复制管理员工设备申请电子表格。此解决方案的 Apps 脚本项目已附加到电子表格中。
复制
- 依次点击扩展程序 >
Google Apps 脚本。
- 在
REQUEST_NOTIFICATION_EMAIL 变量旁边，将示例电子邮件地址替换为您的电子邮件地址。
- 点击“保存”图标
。
设置电子表格
- 返回电子表格，然后依次点击设备申请
> 设置。您可能需要刷新页面，此自定义菜单才会显示。
根据提示为脚本授权。如果 OAuth 意见征求界面显示此应用未经验证警告，请依次选择高级 >
前往 {Project Name}（不安全）以继续操作。
依次点击设备申请
> 设置。
运行脚本
- 依次点击工具 > 管理表单
> 前往正式版表单。
- 填写并提交表单。
- 请查看您的电子邮件，了解有关设备申请的通知。
- 返回电子表格，然后在“待处理的请求”工作表中，将请求的状态更改为已完成。
- 脚本会在 5 分钟内再发送一封电子邮件，告知您请求已处理完毕。该脚本会将请求从“待处理的请求”工作表移至“已完成的请求”工作表。
查看代码
如需查看此解决方案的 Apps 脚本代码，请点击下方的查看源代码：
查看源代码
Code.gs
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/equipment-requests
/*
Copyright 2022 Google LLC
Licensed 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.0
Unless required by applicable law or agreed to in writing, software
distributed 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 and
limitations under the License.
*/
// Update this variable with the email address you want to send equipment requests to.
const REQUEST_NOTIFICATION_EMAIL = 'request_intake@example.com';
// Update the following variables with your own equipment options.
const AVAILABLE_LAPTOPS = [
'15" high Performance Laptop (OS X)',
'15" high Performance Laptop (Windows)',
'15" high performance Laptop (Linux)',
'13" lightweight laptop (Windows)',
];
const AVAILABLE_DESKTOPS = [
'Standard workstation (Windows)',
'Standard workstation (Linux)',
'High performance workstation (Windows)',
'High performance workstation (Linux)',
'Mac Pro (OS X)',
];
const AVAILABLE_MONITORS = [
'Single 27"',
'Single 32"',
'Dual 24"',
];
// Form field titles, used for creating the form and as keys when handling
// responses.
/**
* Adds a custom menu to the spreadsheet.
*/
function onOpen() {
SpreadsheetApp.getUi().createMenu('Equipment requests')
.addItem('Set up', 'setup_')
.addItem('Clean up', 'cleanup_')
.addToUi();
}
/**
* Creates the form and triggers for the workflow.
*/
function setup_() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
if (ss.getFormUrl()) {
let msg = 'Form already exists. Unlink the form and try again.';
SpreadsheetApp.getUi().alert(msg);
return;
}
let form = FormApp.create('Equipment Requests')
.setCollectEmail(true)
.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId())
.setLimitOneResponsePerUser(false);
form.addTextItem().setTitle('Employee name').setRequired(true);
form.addTextItem().setTitle('Desk location').setRequired(true);
form.addDateItem().setTitle('Due date').setRequired(true);
form.addListItem().setTitle('Laptop').setChoiceValues(AVAILABLE_LAPTOPS);
form.addListItem().setTitle('Desktop').setChoiceValues(AVAILABLE_DESKTOPS);
form.addListItem().setTitle('Monitor').setChoiceValues(AVAILABLE_MONITORS);
// Hide the raw form responses.
ss.getSheets().forEach(function(sheet) {
if (sheet.getFormUrl() == ss.getFormUrl()) {
sheet.hideSheet();
}
});
// Start workflow on each form submit
ScriptApp.newTrigger('onFormSubmit_')
.forForm(form)
.onFormSubmit()
.create();
// Archive completed items every 5m.
ScriptApp.newTrigger('processCompletedItems_')
.timeBased()
.everyMinutes(5)
.create();
}
/**
* Cleans up the project (stop triggers, form submission, etc.)
*/
function cleanup_() {
let formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
if (!formUrl) {
return;
}
ScriptApp.getProjectTriggers().forEach(function(trigger) {
ScriptApp.deleteTrigger(trigger);
});
FormApp.openByUrl(formUrl)
.deleteAllResponses()
.setAcceptingResponses(false);
}
/**
* Handles new form submissions to trigger the workflow.
*
* @param {Object} event - Form submit event
*/
function onFormSubmit_(event) {
let response = mapResponse_(event.response);
sendNewEquipmentRequestEmail_(response);
let equipmentDetails = Utilities.formatString('%s\n%s\n%s',
response['Laptop'],
response['Desktop'],
response['Monitor']);
let row = ['New',
'',
response['Due date'],
response['Employee name'],
response['Desk location'],
equipmentDetails,
response['email']];
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName('Pending requests');
sheet.appendRow(row);
}
/**
* Sweeps completed and cancelled requests, notifying the requestors and archiving them
* to the completed sheet.
*
* @param {Object} event
*/
function processCompletedItems_() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let pending = ss.getSheetByName('Pending requests');
let completed = ss.getSheetByName('Completed requests');
let rows = pending.getDataRange().getValues();
for (let i = rows.length; i >= 2; i--) {
let row = rows[i -1];
let status = row[0];
if (status === 'Completed' || status == 'Cancelled') {
pending.deleteRow(i);
completed.appendRow(row);
console.log("Deleted row: " + i);
sendEquipmentRequestCompletedEmail_({
'Employee name': row[3],
'Desk location': row[4],
'email': row[6],
});
}
};
}
/**
* Sends an email notification that a new equipment request has been submitted.
*
* @param {Object} request - Request details
*/
function sendNewEquipmentRequestEmail_(request) {
let template = HtmlService.createTemplateFromFile('new-equipment-request.html');
template.request = request;
template.sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
let msg = template.evaluate();
MailApp.sendEmail({
to: REQUEST_NOTIFICATION_EMAIL,
subject: 'New equipment request',
htmlBody: msg.getContent(),
});
}
/**
* Sends an email notifying the requestor that the request is complete.
*
* @param {Object} request - Request details
*/
function sendEquipmentRequestCompletedEmail_(request) {
let template = HtmlService.createTemplateFromFile('request-complete.html');
template.request = request;
let msg = template.evaluate();
MailApp.sendEmail({
to: request.email,
subject: 'Equipment request completed',
htmlBody: msg.getContent(),
});
}
/**
* Converts a form response to an object keyed by the item titles. Allows easier
* access to response values.
*
* @param {FormResponse} response
* @return {Object} Form values keyed by question title
*/
function mapResponse_(response) {
let initialValue = {
email: response.getRespondentEmail(),
timestamp: response.getTimestamp(),
};
return response.getItemResponses().reduce(function(obj, itemResponse) {
let key = itemResponse.getItem().getTitle();
obj[key] = itemResponse.getResponse();
return obj;
}, initialValue);
}
new-equipment-request.html
贡献者
此示例由 Google 维护，并由 Google 开发者专家提供帮助。
后续步骤
