管理新员工设备申请

编码水平：初级
时长：10 分钟
项目类型：使用事件驱动型触发器和时间驱动型触发器实现自动化

目标

了解此解决方案的功能。
了解 Apps 脚本服务在此解决方案中的功能。
设置脚本。
运行脚本。

关于此解决方案

新员工通常需要 IT 部门提供系统访问权限和设备。为了管理这些请求，您可以使用 Google 表单创建一个表单，让用户指明员工需要的访问权限和设备。IT 部门完成请求并更新其状态后，请求者会收到电子邮件通知。

用于新员工设备申请的 Google 表单。

运作方式

该脚本会创建一个设备请求表单。您可以在示例脚本的代码中自定义表单上的项。当有人提交表单时，该脚本会向指定的请求联系人发送邮件通知。电子表格中的请求状态更改为“已完成”后，该脚本会向提交表单的人员发送确认电子邮件。

Apps 脚本服务

此解决方案使用以下服务：

表单服务：创建 IT 请求表单。
电子表格服务：检查请求表单是否已存在，以减少重复。根据需要将表单响应移至待处理 和已完成 工作表，从而管理表单响应。
邮件服务：创建并发送请求和完成通知电子邮件。
脚本服务：创建触发器。一个触发器在提交表单时运行，另一个触发器每 5 分钟运行一次，以检查请求的状态是否标记为“已完成”。

前提条件

如需使用此示例，您需要满足以下前提条件：

一个 Google 账号（Google Workspace 账号可能需要管理员批准）。
一个可访问互联网的网络浏览器。

设置脚本

请完成以下步骤来设置脚本。

创建 Apps 脚本项目

点击以下按钮，复制管理员工设备请求 电子表格。此解决方案的 Apps 脚本项目已附加到该电子表格：

复制聊天机器人
依次点击扩展程序 > Apps 脚本。
在 REQUEST_NOTIFICATION_EMAIL 变量旁边，将示例电子邮件地址替换为您的电子邮件地址。
点击“保存”图标。

设置电子表格

返回到电子表格，然后依次点击设备请求 > 设置。您可能需要刷新页面才能显示此自定义菜单。
根据提示为脚本授权。 <<../_snippets/oauth.md>>
再次依次点击设备请求 > 设置。

运行脚本

依次点击工具 > 管理表单 > 前往实时表单。
填写并提交表单。
查看您的电子邮件，了解有关设备请求的通知。
返回到电子表格，然后在待处理的请求 工作表上，将请求的状态更改为已完成 。
在 5 分钟内，该脚本会发送另一封电子邮件，通知您请求已完成。该脚本会将请求从待处理的请求 工作表移至已完成的请求 工作表。

查看代码

如需查看此解决方案的 Apps 脚本代码，请点击 查看源代码：

查看源代码

Code.gs

solutions/automations/equipment-requests/Code.js

在 GitHub 上查看

// 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_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (ss.getFormUrl()) {
    const msg = "Form already exists. Unlink the form and try again.";
    SpreadsheetApp.getUi().alert(msg);
    return;
  }
  const 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.
  for (const sheet of ss.getSheets()) {
    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_() {
  const formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  if (!formUrl) {
    return;
  }
  for (const trigger of ScriptApp.getProjectTriggers()) {
    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) {
  const response = mapResponse_(event.response);
  sendNewEquipmentRequestEmail_(response);
  const equipmentDetails = Utilities.formatString(
    "%s\n%s\n%s",
    response.Laptop,
    response.Desktop,
    response.Monitor,
  );
  const row = [
    "New",
    "",
    response["Due date"],
    response["Employee name"],
    response["Desk location"],
    equipmentDetails,
    response.email,
  ];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const 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_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pending = ss.getSheetByName("Pending requests");
  const completed = ss.getSheetByName("Completed requests");
  const rows = pending.getDataRange().getValues();
  for (let i = rows.length; i >= 2; i--) {
    const row = rows[i - 1];
    const 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) {
  const template = HtmlService.createTemplateFromFile(
    "new-equipment-request.html",
  );
  template.request = request;
  template.sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  const 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) {
  const template = HtmlService.createTemplateFromFile("request-complete.html");
  template.request = request;
  const 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) {
  const initialValue = {
    email: response.getRespondentEmail(),
    timestamp: response.getTimestamp(),
  };
  return response.getItemResponses().reduce((obj, itemResponse) => {
    const key = itemResponse.getItem().getTitle();
    obj[key] = itemResponse.getResponse();
    return obj;
  }, initialValue);
}

</section>
<section>
  <h3>new-equipment-request.html</h3>

solutions/automations/equipment-requests/new-equipment-request.html

在 GitHub 上查看

<!DOCTYPE html>
<!--
 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

      http://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.
-->

<html>
  <body>
    <p>
    A new equipment request has been made by <?= request.email ?>.
    </p>

    <p>
    Employee name: <?= request['Employee name'] ?><br/>
    Desk location name: <?= request['Desk location'] ?><br/>
    Due date: <?= request['Due date'] ?><br/>
    Laptop model: <?= request['Laptop'] ?><br/>
    Desktop model: <?= request['Desktop'] ?><br/>
    Monitor(s): <?= request['Monitor'] ?><br/>
    </p>

    See <a href="<?= sheetUrl ?>">the spreadsheet</a> to take or assign this item.
  </body>
</html>

</section>
<section>
  <h3>request-complete.html</h3>

solutions/automations/equipment-requests/request-complete.html

在 GitHub 上查看

<!DOCTYPE html>
<!--
 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

      http://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.
-->

<html>
  <body>
    <p>
    An equipment request has been completed.
    </p>

    <p>
    Employee name: <?= request['Employee name'] ?><br/>
    Desk location name: <?= request['Desk location'] ?><br/>
    </p>
  </body>
</html>

</section>

贡献者

此示例由 Google 在 Google 开发者专家的帮助下维护。

管理新员工设备申请 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

目标

关于此解决方案

运作方式

Apps 脚本服务

前提条件

设置脚本

创建 Apps 脚本项目

设置电子表格

运行脚本

查看代码

查看源代码

Code.gs

贡献者

后续步骤

管理新员工设备申请