编码级别：初级
时长：10 分钟（
）
项目类型：采用
事件驱动型触发器
以及一个时间驱动型触发器
目标
- 了解此解决方案的用途。
- 了解 Apps 脚本服务在
解决方案。
- 设置脚本。
- 运行脚本。
关于此解决方案
新员工通常需要 IT 部门的访问权限和设备。如何管理
那么您可以使用 Google 表单创建一个表单，供用户
什么访问权限
员工所需的各种设备当 IT 团队完成请求并更新其
请求者会收到电子邮件通知。
工作原理
脚本创建设备请求表单。您可以自定义
表单（位于示例脚本代码中）。当某人
提交表单时，脚本会向指定时间点发送电子邮件通知
联系权限。待电子表格中的请求状态变为
“已完成”脚本会向
提交表单。
Apps 脚本服务
此解决方案使用以下服务：
- 表单服务 - 创建表单
处理 IT 请求。
- 电子表格服务 - 检查确认
为减少重复项，请求表单已存在。管理表单
根据需要将回复移至待处理和已完成工作表。
- 邮件服务 - 创建并发送请求
和完成通知电子邮件
- 脚本服务 - 创建触发器。一个
在提交表单时触发，另一个则每五分钟触发一次
请求的状态标记为“已完成”
前提条件
如需使用此示例，您需要满足以下前提条件：
- Google 账号（Google Workspace 账号可能
需要管理员批准）。
- 可以访问互联网的网络浏览器。
设置脚本
创建 Apps 脚本项目
- 点击以下按钮，复制管理员工设备
请求电子表格。此 API 的 Apps 脚本项目
解决方案。
复制
- 点击附加信息 >
Apps 脚本。
- 在
REQUEST_NOTIFICATION_EMAIL 变量旁边，替换示例电子邮件
。
- 点击“保存”
。
设置电子表格
- 返回电子表格，然后点击设备请求。
> 设置。您可能需要刷新
显示此自定义菜单的页面。
出现提示时，为脚本授权。
如果 OAuth 同意屏幕显示以下警告：“此应用未经验证”，
选择高级 > 以继续
前往“{Project Name}”（不安全）。
点击设备请求。
> 重新设置。
运行脚本
- 点击 Tools（工具）> Manage form（管理表单）。
> 转到实时表单。
- 填写并提交表单。
- 查收电子邮件，查看关于设备申请的通知。
- 返回电子表格，在待处理的请求工作表中，将
状态显示为 Completed。
- 在 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 开发者专家的帮助下进行维护。
后续步骤
