程式設計程度：初學者
時間長度：10 分鐘
專案類型：使用事件導向觸發條件和時間導向觸發條件的自動化動作
目標
- 瞭解解決方案的功能。
- 瞭解 Apps Script 服務在解決方案中的作用。
- 設定指令碼。
- 執行指令碼。
認識這項解決方案
新進員工通常需要 IT 部門提供的系統存取權和設備。如要管理這些要求，您可以使用 Google 表單建立表單，讓使用者指出員工需要哪些存取權和裝置。IT 完成要求並更新狀態後，要求者就會收到電子郵件通知。
運作方式
指令碼會建立設備申請表單。您可以在範例指令碼的程式碼中自訂表單上的項目。使用者提交表單後，指令碼會將電子郵件通知傳送給要求的指定聯絡人。試算表中的申請狀態變更為「已完成」後，指令碼就會傳送確認電子郵件給提交表單的使用者。
Apps Script 服務
本解決方案會使用下列服務：
- 表單服務：建立 IT 要求的表單。
- 試算表服務：檢查要求表單是否已存在，以減少重複項目。視需要將表單回覆移至「待處理」和「已完成」工作表，以便管理表單回覆。
- 郵件服務：建立並傳送要求和完成通知電子郵件。
- 指令碼服務：建立觸發條件。其中一個會在提交表單時觸發，另一個則會每五分鐘觸發一次，以便檢查要求的狀態是否標示為「已完成」。
必要條件
如要使用這個範例，您必須具備下列先決條件：
- Google 帳戶 (Google Workspace 帳戶可能需要管理員核准)。
- 可連上網際網路的網路瀏覽器。
設定指令碼
建立 Apps Script 專案
- 點選下方按鈕，複製「管理員工設備申請」試算表。這個解決方案的 Apps Script 專案已附加到試算表中。
「建立副本」
- 依序按一下「Extensions」>「Apps Script」。
- 在
REQUEST_NOTIFICATION_EMAIL 變數旁，將範例電子郵件地址替換為您的電子郵件地址。
- 按一下「儲存」圖示
。
設定試算表
- 返回試算表，然後依序點選「設備申請」>「設定」。您可能需要重新整理頁面，才能顯示這個自訂選單。
出現提示時，請授權執行指令碼。如果 OAuth 同意畫面顯示「This app isn't verified」警告，請依序選取「Advanced」「Go to {Project Name} (unsafe)」(前往「{Project Name}」(不安全))。
依序按一下「設備申請」>「設定」。
執行指令碼
- 依序點選「工具」>「管理表單」>「前往表單直播頁面」。
- 填寫並提交表單。
- 請查看電子郵件，瞭解設備申請的通知。
- 返回試算表，然後在「待處理的要求」工作表中，將要求狀態變更為「已完成」。
- 在 5 分鐘內，指令碼會傳送另一封電子郵件，通知您要求已完成。指令碼會將要求從「待處理的要求」工作表移至「已完成的要求」工作表。
查看程式碼
如要查看這個解決方案的 Apps Script 程式碼，請按一下下方的「查看原始碼」：
查看原始碼
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 開發人員專家提供協助。
後續步驟
