程式設計層級：新手
持續時間：10 分鐘
專案類型：使用事件導向觸發條件和時間導向觸發條件進行自動化作業
目標
- 瞭解解決方案的功能。
- 瞭解 Apps Script 服務在解決方案中的功能。
- 設定指令碼。
- 執行指令碼。
認識這項解決方案
新員工通常需要 IT 人員存取系統和設備。如要管理這些要求，您可以使用 Google 表單建立表單，供使用者指出員工所需的存取權和裝置。IT 完成要求並更新狀態後，要求者會收到電子郵件通知。
運作方式
這段指令碼會建立設備申請表單。您可以自訂指令碼程式碼範例中表單上的項目。有人提交表單時，指令碼會傳送電子郵件通知給指定聯絡窗口。在試算表中的要求狀態變更為「已完成」後，指令碼就會傳送確認電子郵件給提交表單的使用者。
Apps Script 服務
這項解決方案使用下列服務：
- 表單服務：建立 IT 要求表單。
- 試算表服務 - 檢查是否已有要求表單，以減少重複項目。視需要將回應移至「Pending」和「Completed」(已完成) 工作表，管理表單回應。
- 郵件服務：建立並傳送要求和完成通知電子郵件。
- 指令碼服務：建立觸發條件。一個人提交表單時，另一個會觸發一次，檢查要求的狀態為「Completed」(已完成)。
必要條件
如要使用這個範例，您必須具備以下必要條件：
- Google 帳戶 (Google Workspace 帳戶可能需要取得管理員核准)。
- 可連上網際網路的網路瀏覽器。
設定指令碼
建立 Apps Script 專案
- 按一下下方按鈕，建立「Manage employee Equipment requests」(管理員工設備要求) 試算表的副本。這個解決方案的 Apps Script 專案已附加到試算表。
建立副本
- 依序按一下「擴充功能」>「Apps Script」。
- 在
REQUEST_NOTIFICATION_EMAIL 變數旁邊，將範例電子郵件替換成您的電子郵件。
- 按一下「儲存」
。
設定試算表
- 返回試算表，然後依序點選「Equipment requests」>「Set up」。您可能需要重新整理頁面，系統才會顯示這個自訂選單。
出現提示訊息時，請授權執行指令碼。如果 OAuth 同意畫面顯示警告「This app has not verification」(這個應用程式尚未驗證)，請依序選取「Advanced」(進階) >「Go to {Project Name} (unsafe)」 (前往 {Project Name} (不安全))。
再次依序點選「Equipment requests」>「Set up」。
執行指令碼
- 依序點選「工具」>「管理表單」>「前往即時表單」。
- 填寫並提交表單。
- 請查看電子郵件，取得設備申請的相關通知。
- 返回試算表，在「待處理的要求」工作表上，將要求的狀態變更為「已完成」。
- 指令碼會在 5 分鐘內再次傳送電子郵件，通知您要求已完成。指令碼會將要求從「Pending requests」(待處理的要求) 工作表移至「Completed requests」(已完成要求) 工作表。
查看程式碼
如要查看這項解決方案的 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 Developers 專家的協助下維護。
後續步驟
