此页面由 Cloud Translation API 翻译。

管理新员工设备申请

编码级别：新手
时长：10 分钟
项目类型：采用事件驱动型触发器和时间驱动型触发器的自动化操作

目标

了解解决方案的用途。
了解 Apps 脚本服务在此解决方案中执行的操作。
设置脚本。
运行脚本。

关于此解决方案

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

设备申请表单的屏幕截图

运作方式

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

Apps 脚本服务

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

表单服务 - 为 IT 请求创建表单。
电子表格服务 - 检查请求表单是否已存在，以减少重复情况。根据需要将表单回复移至待处理和已完成工作表来管理表单回复。
邮件服务 - 创建并发送请求和完成通知电子邮件。
脚本服务 - 创建触发器。其中一个在提交表单时触发，另一个会在五分钟后触发，以检查请求的状态是否标记为“已完成”。

前提条件

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

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

设置脚本

创建 Apps 脚本项目

点击以下按钮创建管理员工设备请求电子表格的副本。此解决方案的 Apps 脚本项目已附加到该电子表格中。
复制
点击扩展程序 > Apps 脚本。
在 REQUEST_NOTIFICATION_EMAIL 变量旁边，将示例电子邮件地址替换为您的电子邮件地址。
点击“保存”。

设置电子表格

返回电子表格，然后依次点击设备请求 > 设置。您可能需要刷新页面才能看到此自定义菜单。
出现提示时，为脚本授权。如果 OAuth 同意屏幕显示 This app is not verify 警告，请依次选择 Advanced > Go to {Project Name} (unsafe)，以继续操作。
再次点击设备请求 > 设置。

运行脚本

依次点击 Tools（工具）> Manage form（管理表单）> Go to live form（转到在线表单）。
填写并提交表单。
请查看您的电子邮件，获取关于设备申请的通知。
返回电子表格，在待处理的请求工作表上，将请求的状态更改为已完成。
在 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_() {
  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

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>

request-complete.html

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>

贡献者

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

管理新员工设备申请

目标

关于此解决方案

运作方式

Apps 脚本服务

前提条件

设置脚本

创建 Apps 脚本项目

设置电子表格

运行脚本

查看代码

查看源代码

Code.gs

new-equipment-request.html

request-complete.html

贡献者

后续步骤