小小商家一點靈使用 Google 試算表當作存放訂單和顧客資料的資料庫，以及管理商品、訂單、顧客的後台介面。
我利用 Google Apps Script 程式，實作試算表的互動功能，並且讓他能夠自動呼叫後端計算顧客的 RFM 和 CAI，對顧客進行分群。
什麼是 Google Apps Script？
Google Apps Script（簡稱 GAS）是 Google 提供的雲端腳本平台，讓你可以用 JavaScript 為 Google Workspace 的各種服務（試算表、文件、Gmail 等）撰寫自動化程式。
如果你用過 Excel 的 VBA，Google Apps Script 就是 Google 試算表版的 VBA，但更強大、更現代。它們都能讓試算表擁有程式邏輯，但 Google Apps Script 有幾個優勢：
語言更現代：語法使用 JavaScript 而非 VBA。
雲端執行：不需要開著電腦就能自動執行。
跨平台：任何裝置都能使用。
整合外部服務：可以輕鬆呼叫外部 API。
Google Apps Script 能做什麼？
操作試算表
// 讀取資料
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange('A2:D10').getValues();
// 寫入資料
sheet.getRange('E2').setValue('已處理');呼叫外部 API
var response = UrlFetchApp.fetch('https://api.example.com/data', {
method: 'POST',
headers: {'Authorization': 'Bearer token'},
payload: JSON.stringify({key: 'value'})
});建立自訂選單
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('顧客分析')
.addItem('計算 RFM', 'calculateRFM')
.addToUi();
}設定定時執行
// 每天早上 9 點自動執行
ScriptApp.newTrigger('myFunction')
.timeBased()
.atHour(9)
.everyDays(1)
.create();為什麼用試算表當資料庫？
雖然你如果去問專業的工程師，他可能會反對把 Google 試算表當作資料庫使用，原因不外乎是效能和穩定性。
但我這次畢業專題的核心，是要為微型商家提供一個低成本的解決方案，那麼 Google 試算表就是一個很方便又容易上手的現成工具。
優點
完全免費，不需要租用資料庫伺服器。
本身就是視覺化介面，商家可以直接查看編輯資料。
快速開發，不需要設計資料庫結構。
自動備份，支援多人協作。
缺點
效能有限（適合小規模應用）。
缺乏關聯式資料庫的進階功能。
不適合高頻率讀寫。
Google Apps Script 有每日和每次執行時數限制。
對於畢業專題和小型商家來說，試算表的優點遠大於缺點。如果未來要商業化，再遷移到真正的資料庫也不遲。
小小商家一點靈那靈動的後台
小小商家一點靈的試算表主要包含以下工作表：
商品類別：管理商品分類。
商品：管理商品資訊（名稱、價格、庫存、狀態）。
顧客：儲存顧客資料（ID、姓名、生日、電話、Email）。
訂單：記錄所有訂單。
RFM：儲存 RFM 分析結果。
CAI：儲存 CAI 分析結果。
設定：存放 API Token 等設定。
我的 Google Apps Script 專案架構
為了讓試算表靈動起來，因此我讓它身體形成 X 型寫了一系列 Google Apps Script 來操作它的每個部分。
GAS 程式碼檔案結構：
gas/
├── main.gs # 主程式入口
├── settings.gs # 讀取設定工作表
├── do_get.gs # 處理 GET 請求
├── do_post.gs # 處理 POST 請求
├── on_edit.gs # 編輯觸發器
├── fetch_calculate.gs # 呼叫後端計算 RFM/CAI
├── send_sidebar.gs # LINE 訊息推播功能
├── installable_triggers.gs # 安裝觸發器
├── utils.gs # 工具函數
├── unique.gs # 唯一性檢查
├── customer_validator.gs # 顧客資料驗證
├── product_validator.gs # 商品資料驗證
├── order_validator.gs # 訂單資料驗證
├── test.gs # 測試程式
├── SendSidebar.html # 發信側欄介面
└── appsscript.json # Apps Script 設定檔其中 test.gs 不太重要，它只是我用來測試某個函數是否能正常運作用的。
appsscript.json：專案的設定檔
appsscript.json 是 Google Apps Script 專案的設定檔，定義了專案的執行環境和權限。
{
"timeZone": "Asia/Taipei",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"webapp": {
"executeAs": "USER_DEPLOYING",
"access": "ANYONE_ANONYMOUS"
},
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/script.container.ui",
"https://www.googleapis.com/auth/script.send_mail"
]
}timeZone：設定為台北時區，確保時間相關的計算（如觸發器執行時間）是用台灣時間。
runtimeVersion：使用 V8 引擎，這是較新的 JavaScript 執行環境，支援現代 JavaScript 語法（如箭頭函數、解構賦值等）。
webapp：Web 應用程式設定。
executeAs: "USER_DEPLOYING"：以部署者的身份執行，這樣才能存取試算表。access: "ANYONE_ANONYMOUS"：允許任何人（不需登入）存取這個 Web 應用程式，這樣前端 LIFF App 才能呼叫doGet()和doPost()。
oauthScopes：定義需要的 Google API 權限。
spreadsheets：讀寫試算表的權限。script.external_request：呼叫外部 API 的權限（用來呼叫後端計算 RFM/CAI）。script.container.ui：顯示使用者介面的權限（用來顯示自訂選單和側欄）。script.send_mail：發送 Email 的權限（雖然目前主要用 LINE 推播，但保留了 Email 功能）。
這些權限在第一次執行程式時，Google 會要求使用者授權。
主程式入口：main.gs
Google Apps Script 其實沒有程式入口的概念，但我還是寫了一個 main.gs，在裡面定義了各工作表的參照，並建立自訂選單。
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const categorySheet = spreadsheet.getSheetByName('商品類別');
const productSheet = spreadsheet.getSheetByName('商品');
const customerSheet = spreadsheet.getSheetByName('顧客');
const orderSheet = spreadsheet.getSheetByName('訂單');
const rfmSheet = spreadsheet.getSheetByName('RFM');
const caiSheet = spreadsheet.getSheetByName('CAI');
const settingsSheet = spreadsheet.getSheetByName('設定');
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('行銷')
.addItem('發信側欄', 'showSendSidebar')
.addToUi();
}工作表參照
我先定義了工作表的全域變數，這樣在其他檔案中就可以直接使用 customerSheetorderSheet 等直接呼叫，不需要每次都重新取得工作表。
onOpen() 函數
onOpen() 是一個特殊函數，會在每次打開試算表時自動執行。我用它來建立自訂選單「行銷」，裡面有「發信側欄」這個選項。
當商家點選「發信側欄」時，會執行 showSendSidebar() 函數，顯示一個側邊欄讓商家選擇要推播訊息給哪些顧客分群。
這樣商家就不需要懂程式碼，只要在試算表介面上點選選單就能使用功能。
設定表
目前「設定」工作表中只有一項設定，就是 token。
為了避免這個試算表的 Google Apps Script 被亂 call 消耗每日執行時數，或著是後端的計算 RFM、CAI 等功能被濫用，我特別設計了 token 驗證機制。只有在 Google 試算表和後端設定同一個 token 時，它們才互相拿得到資料。
這麼設計還有一個好處，就是未來我可以將系統改進成一個後端對好幾個不同的試算表（商家），這個 token 就可以讓後端用來區分資料是來自哪個專案，回傳或接收對應的資料。
商品管理
商品管理功能分為「商品類別管理」和「商品管理」。
商品類別
在「商品類別」表裡面，我們可以定義商品類別、描述（應該是要顯示在點餐頁面，但目前沒有做），並且可以用下拉式選單選擇該類別的商品要不要上架。如果選「否」，則該類別的所有商品都不會顯示在點餐頁。
商品類別的資料驗證
當商家在「商品類別」工作表編輯資料時onEdit() 觸發器會自動執行驗證：
case '商品類別':
if (col === 1 && row > 1) {
// 確保類別名稱唯一
if (!value) return;
if (!isUniqueInColumn(sheet, 1, value, row)) {
e.range.setValue(e.oldValue || '');
SpreadsheetApp.getUi().alert('錯誤', '此商品類別名稱已存在，請輸入不同的名稱！', SpreadsheetApp.getUi().ButtonSet.OK);
return;
}
} else if (col === 3 && row > 1) {
// 類別名稱不為空時，才能更改是否上架
categoryName = sheet.getRange(row, 1).getValue();
if (!categoryName) {
e.range.setValue(e.oldValue || '');
SpreadsheetApp.getUi().alert('錯誤', '類別名稱不能為空，請先輸入類別名稱！', SpreadsheetApp.getUi().ButtonSet.OK);
return;
}
}
break;驗證邏輯：
類別名稱唯一性：當編輯類別名稱（第 1 欄）時，會檢查這個名稱是否已存在。如果重複，就恢復原值並顯示警告。
必須先有名稱：如果類別名稱是空的，就不能設定「是否上架」（第 3 欄）。
唯一性檢查函數
isUniqueInColumn() 函數（定義在 unique.gs）負責檢查某個值在欄位中是否唯一：
function isUniqueInColumn(sheet, column, value, excludeRow = null) {
const lastRow = sheet.getLastRow();
if (lastRow < 2) return true;
const range = sheet.getRange(2, column, lastRow - 1);
const values = range.getValues();
for (let i = 0; i < values.length; i++) {
const currentRow = i + 2;
if (excludeRow && currentRow === excludeRow) {
continue; // 跳過當前編輯的列
}
if (values[i][0] === value) {
return false; // 找到重複值
}
}
return true;
}這個函數會：
取得指定欄位的所有資料。
逐行檢查是否有重複值。
排除當前正在編輯的那一列（因為自己跟自己比一定會重複）。
回傳
true表示唯一false表示重複。
商品
在「商品」工作表，可以定義每一項商品，用下拉式選單選擇它屬於哪個類別（選項會自動根據商品類別表產生，避免手誤填錯的問題），設定價格、庫存、狀態，也可以加上商品說明（會顯示在點餐頁）。
商品的狀態可設為「有現貨」、「暫時無法供貨」、「已售完」、「下架」四種狀態。商品的狀態會根據價格和庫存自動限制。例如當庫存為 0 時，商品狀態就不能設為「有現貨」，只能選「下架」、「已售完」或「暫時無法供貨」。這些邏輯都是用 Google Apps Script 實作的。
商品的資料驗證
商品的驗證邏輯更複雜，因為要確保商品名稱唯一、狀態合理、以及庫存與狀態的一致性：
case '商品':
if (col === 1 && row > 1) {
// 確保商品名稱唯一
if (!value) return;
if (!isUniqueInColumn(sheet, 1, value, row)) {
e.range.setValue(e.oldValue || '');
SpreadsheetApp.getUi().alert('錯誤', '此商品名稱已存在，請輸入不同的名稱！', SpreadsheetApp.getUi().ButtonSet.OK);
return;
}
// 新增商品時自動設定初始值
if (!e.oldValue) {
sheet.getRange(row, 4).setValue(0); // 庫存設為 0
sheet.getRange(row, 5).setValue('下架'); // 狀態設為「下架」
}
} else if (col === 5 && row > 1) {
// 驗證商品狀態的合理性
const price = sheet.getRange(row, 3).getValue();
const stock = sheet.getRange(row, 4).getValue();
const status = value;
const allowedStatuses = ['下架', '已售完', '暫時無法供貨'];
// 價格 <= 0 時，只允許特定狀態
if (price <= 0 && !allowedStatuses.includes(status)) {
const recoveryValue = e.oldValue && allowedStatuses.includes(e.oldValue) ? e.oldValue : '';
e.range.setValue(recoveryValue);
SpreadsheetApp.getUi().alert('錯誤', '未設定價格或價格小於等於0時，商品狀態只能為「下架」、「已售完」或「暫時無法供貨」！', SpreadsheetApp.getUi().ButtonSet.OK);
return;
}
// 庫存 <= 0 時，不能設為「有現貨」
if (status === '有現貨' && stock <= 0) {
e.range.setValue(e.oldValue || '');
SpreadsheetApp.getUi().alert('錯誤', '庫存小於等於0時，商品狀態不能設為「有現貨」！', SpreadsheetApp.getUi().ButtonSet.OK);
return;
}
}
break;驗證邏輯：
商品名稱唯一性：確保商品名稱不重複。
自動設定初始值：新增商品時，自動將庫存設為 0、狀態設為「下架」。
價格驗證：價格 ≤ 0 時，狀態只能是「下架」、「已售完」或「暫時無法供貨」。
庫存驗證：庫存 ≤ 0 時，狀態不能設為「有現貨」。
這些驗證確保試算表中的資料符合商業邏輯，避免出現「庫存為 0 但顯示有現貨」這種矛盾的情況。
訂單管理
後台當然也要有辦法管理訂單，不然實用性就會大打折扣，並且 RFM 和 CAI 也都需要訂單資料才能計算。
訂單管理表列出了每一份訂單，當顧客在點餐頁面下單後，它就會出現在這裡。可以看到每一份訂單的商品內容、顧客姓名、下單時間、總金額、顧客寫的備註，商家自己也可以加上筆記（顧客看不到）。
當訂單開始製作時，商家可以用下拉式選單來改變訂單狀態。訂單狀態共分為「待處理」、「進行中」、「已完成」、「取消」，只有已完成的訂單才會列入 RFM 和 CAI 的計算。
新增訂單的處理流程
當顧客在點餐頁面下單時，會發送 POST 請求到 GAS 的 doPost() 函數，由 addOrder() 處理：
function addOrder(data) {
const order = data.order;
const sheet = orderSheet;
// 驗證所有產品是否存在，並計算總金額
const validationResult = validateProductsAndCalculateTotal(order.products);
if (!validationResult.isValid) {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: validationResult.errorMessage,
})
).setMimeType(ContentService.MimeType.JSON);
}
// 驗證顧客是否存在
const customerValidation = checkCustomerExists(order.customer_id);
if (!customerValidation.exists) {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: customerValidation.errorMessage,
})
).setMimeType(ContentService.MimeType.JSON);
}
const totalAmount = validationResult.totalAmount;
// 計算新的訂單 ID（所有訂單 ID 的最大值 + 1）
let maxId = 0;
const lastRow = sheet.getLastRow();
if (lastRow > 1) {
const idRange = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
for (let i = 0; i < idRange.length; i++) {
const currentId = parseInt(idRange[i][0]);
if (!isNaN(currentId) && currentId > maxId) {
maxId = currentId;
}
}
}
const newOrderId = maxId + 1;
// 在第二列插入新訂單
sheet.insertRowAfter(1);
const newRow = 2;
// 將產品格式化為 "產品名*數量" 的形式
const products = order.products
.map((item) => ${item.product}*${item.quantity})
.join(", ");
// 設置訂單資料
sheet.getRange(newRow, 1, 1, 3).setValues([[newOrderId, products, order.customer_id]]);
const orderData = [
"待處理", // 訂單狀態
new Date(), // 訂單時間
totalAmount, // 訂單總金額
order.customer_note // 顧客備註
];
sheet.getRange(newRow, 5, 1, 4).setValues([orderData]);
return ContentService.createTextOutput(
JSON.stringify({ status: "success", orderId: newOrderId })
).setMimeType(ContentService.MimeType.JSON);
}處理流程：
驗證商品：檢查訂單中的所有商品是否存在，並計算總金額。
驗證顧客：檢查顧客 ID 是否存在。
產生訂單 ID：找出目前最大的訂單 ID，新訂單 ID = 最大 ID + 1。
插入新訂單：在第二行插入新訂單（最新的訂單會顯示在最上方）。
設定初始狀態：新訂單的狀態預設為「待處理」。
商品驗證與總金額計算
validateProductsAndCalculateTotal() 函數（定義在 product_validator.gs）負責驗證商品並計算總金額：
function validateProductsAndCalculateTotal(products) {
// 檢查產品清單是否為空
if (!products || !Array.isArray(products) || products.length === 0) {
return {
isValid: false,
totalAmount: 0,
errorMessage: '產品清單不能為空'
};
}
// 一次性讀取所有產品資料（提升效能）
const productData = getProductDataRange();
let totalAmount = 0;
for (const productItem of products) {
const productName = productItem.product;
const quantity = productItem.quantity;
// 檢查產品名稱
if (!productName || typeof productName !== 'string' || productName.trim() === '') {
return {
isValid: false,
totalAmount: 0,
errorMessage: '產品名稱不能為空'
};
}
// 檢查數量
if (!quantity || typeof quantity !== 'number' || quantity <= 0) {
return {
isValid: false,
totalAmount: 0,
errorMessage: 數量必須是正數: ${productName}
};
}
// 在產品資料中查找產品
const productInfo = findProductInData(productName, productData);
if (!productInfo.exists) {
return {
isValid: false,
totalAmount: 0,
errorMessage: 找不到產品: ${productName}
};
}
// 計算小計
totalAmount += productInfo.price * quantity;
}
return {
isValid: true,
totalAmount: totalAmount,
errorMessage: null
};
}驗證邏輯：
檢查清單：確保訂單至少有一項商品。
檢查商品名稱：每個商品的名稱不能為空。
檢查數量：數量必須是正數。
檢查商品存在性：確認商品在「商品」工作表中存在。
計算總金額：將每個商品的價格 × 數量累加。
效能優化：
為了避免在驗證每個商品時都重複讀取試算表（這會非常慢），我先用 getProductDataRange() 一次性讀取所有商品資料，然後在記憶體中查找。這樣即使訂單有 10 項商品，也只需要讀取試算表一次。
訂單查詢函數
checkOrderExists() 和 findOrderRow() 函數（定義在 order_validator.gs）用於檢查訂單是否存在和找到訂單所在的列：
function checkOrderExists(orderId) {
const orders = orderSheet.getDataRange().getValues();
for (let i = 1; i < orders.length; i++) {
if (orders[i][0] === orderId) {
return true;
}
}
return false;
}
function findOrderRow(orderId) {
const orders = orderSheet.getDataRange().getValues();
for (let i = 1; i < orders.length; i++) {
if (orders[i][0] === orderId) {
return i + 1; // 返回實際的行數
}
}
return -1; // 找不到訂單
}這些函數在更新訂單狀態時會用到，確保只更新存在的訂單。
顧客管理
顧客管理是整個系統的重頭戲，顧客資料主要是由 LIFF App 使用者註冊後自動新增，商家可以在這裡為顧客加上附註（顧客不會看到），對於辨識奧客非常有幫助。
這個表還會列出每位顧客的 RFM、CAI 分群和象限分群（CAI 和 F），可以用於制定針對不同群體的行銷活動。
「RFM會員類型對照表」有寫每一種會員類型代表什麼意思，以及行銷活動方向上的建議。這部分我是用 GPT 做的。
顧客資料的新增與驗證
當前端 LIFF App 中有新顧客註冊時，會經由後端發送 POST 請求到 GAS 的 doPost() 函數，由 addCustomer() 處理：
function addCustomer(data) {
const customer = data.customer;
const sheet = customerSheet;
// 檢查顧客ID是否已存在
const existingCustomer = checkCustomerExists(customer.id);
if (existingCustomer.exists) {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: 顧客ID ${customer.id} 已存在,
})
).setMimeType(ContentService.MimeType.JSON);
}
// 檢查顧客姓名是否為空
if (!customer.name || customer.name.trim() === "") {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: "顧客姓名不能為空",
})
).setMimeType(ContentService.MimeType.JSON);
}
// 檢查生日格式（YYYY-MM-DD）
if (customer.birthday && !/^\d{4}-\d{2}-\d{2}$/.test(customer.birthday)) {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: "顧客生日格式不正確，應為 YYYY-MM-DD",
})
).setMimeType(ContentService.MimeType.JSON);
}
// 檢查電話格式（數字）
if (customer.phone && !/^\d+$/.test(customer.phone)) {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: "顧客電話格式不正確，應為數字",
})
).setMimeType(ContentService.MimeType.JSON);
}
// 檢查 email 格式
if (customer.email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(customer.email)) {
return ContentService.createTextOutput(
JSON.stringify({
status: "error",
message: "顧客電子郵件格式不正確",
})
).setMimeType(ContentService.MimeType.JSON);
}
// 驗證通過，新增到試算表
sheet.appendRow([
customer.id,
customer.name,
customer.birthday,
customer.phone,
customer.email,
customer.note || ''
]);
return ContentService.createTextOutput(
JSON.stringify({
status: "success",
message: "顧客新增成功"
})
).setMimeType(ContentService.MimeType.JSON);
}驗證邏輯：
ID 唯一性：確保顧客 ID 不重複。
必填欄位：顧客姓名不能為空。
生日格式：必須是
YYYY-MM-DD格式。電話格式：只能包含數字。
Email 格式：必須符合 Email 格式規範。
檢查顧客是否存在
checkCustomerExists() 函數（定義在 customer_validator.gs）負責檢查顧客 ID 是否已存在：
function checkCustomerExists(customerId) {
if (!customerId || customerId.trim() === '') {
return {
exists: false,
customerRow: null,
customerInfo: null,
errorMessage: '顧客ID不能為空'
};
}
// 在 A 欄查找顧客 ID
const lastRow = customerSheet.getLastRow();
const customerIds = customerSheet.getRange(1, 1, lastRow, 1).getValues();
let customerRow = null;
for (let i = 0; i < customerIds.length; i++) {
if (customerIds[i][0] && customerIds[i][0].toString().trim() === customerId.toString().trim()) {
customerRow = i + 1;
break;
}
}
if (!customerRow) {
return {
exists: false,
customerRow: null,
customerInfo: null,
errorMessage: 找不到顧客ID: ${customerId}
};
}
// 取得顧客完整資訊
const customerData = customerSheet.getRange(customerRow, 1, 1, 6).getValues()[0];
const customerInfo = {
id: customerData[0],
name: customerData[1],
birthday: customerData[2],
phone: customerData[3],
email: customerData[4],
note: customerData[5]
};
return {
exists: true,
customerRow: customerRow,
customerInfo: customerInfo,
errorMessage: null
};
}這個函數不只檢查顧客是否存在，還會回傳顧客的完整資訊和所在列數，方便後續的操作（如更新顧客資料）。
RFM 的計算與分群
我的試算表裡面有一個 RFM 工作表，上面列出每一位有下過單的顧客的 R、F、M 分數、加總的分數、RFM 分群（將 R 分數、F 分數、M 分數連起來），以及最後判定的會員類型。
會員類型的判定公式如下：
XLOOKUP(VALUE(F2),'RFM會員類型對照表'!A:A,'RFM會員類型對照表'!B:B,"",0)他會直接比對我在「RFM 會員類型對照表」中的定義。為了各位方便，我這裡再放一次：
為了分群方便，我將 RFM 常用的五等均分法改成二等均分法，R、F、M 各只分為兩群，總共 8 種會員類型。
因為在這個系統中，我們的會員數量會不斷改變，如果我們的分群方式也跟著變動，會導致策略制定困難。並且假如我們真的有 5x5x5=125 個區隔，商家也難以制定出 125 個不同的行銷策略。
自動計算 RFM
當商家在訂單工作表手動編輯時（例如更改訂單狀態），試算表會自動觸發 RFM 和 CAI 的計算。這是透過「可安裝觸發器」實作的。
function installableTriggerOnEdit(e) {
const sheet = e.source.getActiveSheet();
const sheetName = sheet.getName();
switch (sheetName) {
case "訂單":
// 執行 RFM、CAI 等計算並存儲結果
doAllCalculationsAndStoreResults();
break;
}
}可安裝觸發器跟一般的 onEdit() 觸發器不同，它可以呼叫外部 API。因為我們需要呼叫後端的 /api/calculate 來計算 RFM，所以必須用這種觸發器。
要使用可安裝觸發器，需要在 Apps Script 編輯器的「觸發器」選單中手動建立，設定當試算表被編輯時執行 installableTriggerOnEdit 函數。
一個有趣的設計細節
你可能會好奇：為什麼前端 LIFF App 新增訂單時，不會馬上觸發 RFM 計算？
這是因為可安裝觸發器只會在「手動編輯試算表」時啟動，不會在透過程式碼（doPost()）寫入資料時觸發。
不過這樣設計其實很合理：
新訂單的狀態預設是「待處理」，根本不會被列入 RFM 計算（只計算「已完成」的訂單）
等商家把訂單狀態改成「已完成」，這時候才會觸發重新計算
避免每次下單都觸發計算，省下不少 API 呼叫次數和執行時間
從試算表到後端的完整流程
doAllCalculationsAndStoreResults() 是整個計算流程的起點：
function doAllCalculationsAndStoreResults() {
const { rfm: rfmData, cai: caiData } = fetchCalculate(orderSheet);
// 將 RFM 結果寫入試算表
setValuesToSheet(rfmSheet, rfmData);
// 將 CAI 結果寫入試算表
setValuesToSheet(caiSheet, caiData);
}接著 fetchCalculate() 負責準備資料並呼叫後端：
function fetchCalculate(orderDataSheet) {
const orderData = getValuesOfSheet(orderDataSheet);
const statusColIndex = orderData[0].indexOf("狀態");
// 只保留「已完成」的訂單
const completedOrderData = orderData.filter((row, index) => {
if (index === 0) return true; // 保留標題列
return row[statusColIndex] === "已完成";
});
// 建立計算請求
const requests = [
CalculateRequest(
completedOrderData,
{
customerIDColName: "顧客 ID",
tradingDayColName: "下單時間",
amountColName: "總金額",
},
"all" // 同時計算 RFM 和 CAI
)
];
// 發送請求到後端
const response = UrlFetchApp.fetchAll(requests);
// 檢查回應
for (let i = 0; i < response.length; i++) {
if (response[i].getResponseCode() !== 200) {
throw new Error("Calculation request failed: " + response[i].getContentText());
}
}
return {
rfm: JSON.parse(response[0].getContentText()).RFM,
cai: JSON.parse(response[0].getContentText()).CAI,
};
}這段程式做了幾件重要的事：
篩選出「已完成」的訂單（未完成或取消的訂單不納入分析）。
用
getValuesOfSheet()把試算表的日期轉成yyyy/MM/dd HH:mm:ss格式，符合後端 Insyra 的要求。用
type: "all"讓後端同時平行計算 RFM 和 CAI，省時間。
CalculateRequest() 負責組裝 API 請求：
function CalculateRequest(orderData, config, type) {
return {
url: calculateBackendUrl + "/" + type,
method: "POST",
headers: {
"Content-Type": "application/json",
Authorization: "Bearer " + settings.token,
},
payload: JSON.stringify({ data: orderData, config: config }),
};
}這個請求會發送到後端的 /api/calculate/all，並帶著 Bearer Token 認證。
日期格式的魔法
試算表的日期格式跟程式需要的格式常常不一樣，所以我寫了 getValuesOfSheet() 來自動轉換：
function getValuesOfSheet(sheet) {
const values = sheet.getDataRange().getValues();
// 遍歷所有儲存格
for (let r = 0; r < values.length; r++) {
for (let c = 0; c < values[r].length; c++) {
const cell = values[r][c];
// 如果是 Date 物件，轉換成 "yyyy/MM/dd HH:mm:ss" 格式
if (cell instanceof Date) {
values[r][c] = formatDateToYYYYMMDD_HHMMSS(cell);
}
}
}
return values;
}
function formatDateToYYYYMMDD_HHMMSS(input) {
const d = input instanceof Date ? input : new Date(input);
const pad = (n) => ("0" + n).slice(-2);
return (
d.getFullYear() + "/" +
pad(d.getMonth() + 1) + "/" +
pad(d.getDate()) + " " +
pad(d.getHours()) + ":" +
pad(d.getMinutes()) + ":" +
pad(d.getSeconds())
);
}這樣訂單的「下單時間」就能正確傳給後端了。
把結果寫回試算表
setValuesToSheet() 負責把後端回傳的結果寫入試算表：
function setValuesToSheet(sheet, values) {
const range = sheet.getRange(1, 1, values.length, values[0].length);
range.setValues(values);
Logger.log("Set values to sheet: " + sheet.getName());
}這裡有個小技巧：用 setValues() 一次寫入整個陣列，而不是用迴圈一個一個 setValue()。試算表的 I/O 操作很耗時，所以要盡量減少操作次數，這樣做可以快很多。
完整流程回顧
當商家把訂單狀態從「待處理」改成「已完成」時，整個流程是這樣的：
觸發器偵測到訂單工作表被編輯。
讀取所有訂單資料，日期自動轉換格式。
篩選出「已完成」的訂單。
把資料打包成 JSON，發送 POST 請求到後端
/api/calculate/all。後端用 Insyra 平行計算 RFM 和 CAI（詳見畢業專題小札記04）。
接收後端回傳的二維陣列結果。
分別寫入 RFM 和 CAI 工作表。
試算表的 XLOOKUP 公式自動判定會員類型。
商家只要動動手指改個狀態，後面這一整串就自動跑完了！
CAI 的計算與分群
CAI 表紀錄每位有下過單的顧客的 MLE（平均購買間隔時間）、WMLE（加權的平均購買間隔時間）、CAI，以及購買行為趨勢。
MLE、WMLE 和 CAI 都是後端 Insyra 算的，購買行為趨勢則由下列公式計算：
IF(D2="", "", IF(D2<(0-STDEVA(D:D)), "沉寂", IF(D2>(0+STDEVA(D:D)), "活躍", "固定")))依據上次介紹的 CAI 定義，CAI < 0 代表顧客購買行為趨於沉寂、CAI = 0 為固定、CAI > 0 是活躍。但是實務上，很少有 CAI 剛好等於 0 的情況。因此我的作法是：負一個標準差到正一個標準差之間都算是固定。
這個公式的邏輯：
如果 CAI 是空的，就不判定。
計算所有顧客 CAI 的標準差。
CAI < -1σ：沉寂（購買頻率持續下降）。
-1σ ≤ CAI ≤ 1σ：固定（購買頻率穩定）。
CAI > 1σ：活躍（購買頻率持續上升）。
自動計算 CAI
CAI 跟 RFM 是一起計算的，當訂單狀態更新時doAllCalculationsAndStoreResults() 會同時呼叫後端計算兩者：
function doAllCalculationsAndStoreResults() {
const { rfm: rfmData, cai: caiData } = fetchCalculate(orderSheet);
// 將 RFM 結果寫入試算表
setValuesToSheet(rfmSheet, rfmData);
// 將 CAI 結果寫入試算表
setValuesToSheet(caiSheet, caiData);
}在 fetchCalculate() 中，我們用 type: "all" 讓後端平行計算 RFM 和 CAI：
const requests = [
CalculateRequest(
completedOrderData,
{
customerIDColName: "顧客 ID",
tradingDayColName: "下單時間",
amountColName: "總金額", // CAI 不需要金額，但 RFM 需要
},
"all" // 同時計算 RFM 和 CAI
)
];後端收到請求後，會同時執行 mkt.RFM() 和 mkt.CAI()，並把結果一起回傳：
{
"RFM": [[...]],
"CAI": [[...]]
}CAI 的特殊要求
CAI 跟 RFM 有個重要差異：CAI 需要至少 4 筆交易紀錄才能計算。
因為 CAI 要計算購買間隔的變化趨勢，需要至少 3 個間隔，而 3 個間隔需要 4 個時間點。如果顧客只下過 1、2、3 次單，是算不出趨勢的。
後端 Insyra 在計算 CAI 時會自動處理這個問題：
交易次數 < 4：不會出現在 CAI 表中。
交易次數 ≥ 4：正常計算 CAI。
與 RFM 的互補
RFM 和 CAI 各有優勢：
RFM：看過去的累積表現（誰是好顧客）。
CAI：看未來的趨勢（誰會變成好顧客、誰正在流失）。
舉個例子：
某顧客 RFM 分數很高（重要價值客戶），但 CAI < 0（沉寂）→ 這位顧客可能正在流失，要趕快挽回
某顧客 RFM 分數普通，但 CAI > 0（活躍）→ 這位顧客有潛力，值得培養
商家可以結合兩者來制定更精準的行銷策略！
發信功能
作為一個 CRM 系統，「發信功能」是最核心的應用場景之一。商家可以根據 RFM 或 CAI 分群，選擇性地推播訊息給特定類型的顧客。
還記得在 main.gs 中，我們建立了一個自訂選單「行銷」→「發信側欄」嗎？當商家點選這個選項時，會開啟一個側邊欄，讓商家輕鬆完成分群推播。
發信側欄的介面設計
這個側邊欄是用 HTML 寫的（SendSidebar.html），包含以下元素：
分群依據：下拉式選單，可選擇「RFM會員類型」、「CAI購買行為趨勢」或「象限分類」。
選擇分群：多選框，列出該分群依據下的所有群體。
發送方式：選擇 Email 或 LINE。
主旨：Email 專用（LINE 不需要主旨）。
訊息內容：可點擊插入變數，如顧客姓名、Email、電話、今天日期、現在時間。
發送按鈕：觸發推播。
個人化訊息
側邊欄提供了快速插入變數的按鈕，在目前的設計中，商家可以在訊息中使用：
{{customerName}}：顧客姓名{{customerEmail}}：顧客 Email{{customerPhone}}：顧客電話
舉例來說，訊息內容可以寫成：
{{customerName}} 您好！
感謝您一直以來的支持，我們推出新產品優惠活動，特別為您保留專屬優惠名額。
活動時間：即日起至本週日
優惠內容：全館 9 折
期待您的光臨！當推播給顧客「王小明」時，實際收到的訊息會是：
王小明 您好！
感謝您一直以來的支持，我們推出新產品優惠活動，特別為您保留專屬優惠名額。
活動時間：即日起至本週日
優惠內容：全館 9 折
期待您的光臨！分群推播的流程
當商家點選「發送」按鈕時sendMessages() 函數會執行以下流程：
function sendMessages(payload) {
const { fieldName, groups, channel, subject, message } = payload;
// 找到對應的分群欄位
const header = customerSheet.getRange(1, 1, 1, customerSheet.getLastColumn()).getValues()[0];
const colIndex = header.indexOf(fieldName);
if (colIndex === -1) {
return { status: 'error', message: 找不到欄位：${fieldName} };
}
// 取得所有顧客資料
const rows = customerSheet.getRange(2, 1, Math.max(0, customerSheet.getLastRow() - 1), customerSheet.getLastColumn()).getValues();
const results = [];
rows.forEach((row, i) => {
const value = row[colIndex];
// 只處理符合選定分群的顧客
if (value && groups.indexOf(String(value)) !== -1) {
const customer = {
id: row[0],
name: row[1],
birthday: row[2],
phone: row[3],
email: row[4],
recipient: row[0] // LINE 收件者（通常是顧客 ID）
};
// 展開訊息模板
const personalizedMessage = expandTemplate(message, customer);
if (channel === 'email') {
// 發送 Email
if (!customer.email) {
results.push({ id: customer.id, name: customer.name, status: 'skipped', reason: '無電子郵件' });
return;
}
try {
MailApp.sendEmail({
to: customer.email,
subject: subject || '行銷訊息',
htmlBody: personalizedMessage
});
results.push({ id: customer.id, name: customer.name, status: 'sent' });
} catch (e) {
results.push({ id: customer.id, name: customer.name, status: 'error', reason: e.message });
}
} else if (channel === 'line') {
// 呼叫後端推播 LINE 訊息
const backendToken = settings.token;
if (!backendToken) {
results.push({ id: customer.id, name: customer.name, status: 'skipped', reason: '未設定 token' });
return;
}
try {
const resp = UrlFetchApp.fetch(lineBackendUrl, {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + backendToken },
payload: JSON.stringify({
userId: String(customer.recipient),
message: personalizedMessage
})
});
const code = resp.getResponseCode();
if (code >= 200 && code < 300) {
results.push({ id: customer.id, name: customer.name, status: 'sent' });
} else {
results.push({ id: customer.id, name: customer.name, status: 'error', reason: HTTP ${code} });
}
} catch (e) {
results.push({ id: customer.id, name: customer.name, status: 'error', reason: e.message });
}
}
}
});
return { status: 'success', results };
}流程說明：
找到分群欄位在顧客表的位置。
讀取所有顧客資料。
篩選出符合選定分群的顧客。
對每位顧客：
展開訊息模板，替換變數為真實資料。
根據發送方式（Email 或 LINE）推播訊息。
記錄發送結果。
訊息模板展開
expandTemplate() 函數負責將模板中的變數替換成真實資料：
function expandTemplate(tpl, customer) {
if (!tpl) return '';
let res = tpl;
res = res.replace(/{{\s*customerName\s*}}/gi, customer.name || '');
res = res.replace(/{{\s*customerEmail\s*}}/gi, customer.email || '');
res = res.replace(/{{\s*customerPhone\s*}}/gi, customer.phone || '');
return res;
}這個函數用正規表達式找出所有的變數（如 {{customerName}}），然後替換成對應的顧客資料。
LINE 推播的實作
LINE 推播跟 Email 不同，Google Apps Script 沒有內建的 LINE 發送功能，所以我們需要呼叫後端去打 LINE Messaging API：
const resp = UrlFetchApp.fetch(lineBackendUrl, {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + backendToken },
payload: JSON.stringify({
userId: String(customer.recipient),
message: personalizedMessage
})
});這個請求會發送到後端的 /api/line/message，後端再呼叫 LINE Messaging API 推播訊息給顧客。
發信紀錄
每次推播後，系統會自動記錄在「發信紀錄」工作表中：
function appendSendLog(time, channel, field, groupValue, customer, subject, message, status, note) {
const sheetName = '發信紀錄';
let sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
// 如果工作表不存在，就建立一個
sheet = spreadsheet.insertSheet(sheetName);
sheet.getRange(1, 1, 1, 9).setValues([
['時間', '通道', '分群欄位', '分群值', '顧客ID', '顧客姓名', '收件/目標', '狀態', '備註']
]);
}
const target = channel === 'email' ? (customer.email || '') : (customer.id || '');
sheet.appendRow([time, channel, field, groupValue, customer.id, customer.name, target, status, note || '']);
}發信紀錄包含：
時間：發送時間。
通道：Email 或 LINE。
分群欄位：用哪個欄位分群（如「RFM會員類型」）。
分群值：目標分群（如「重要價值客戶」）。
顧客 ID、姓名：收件人資訊。
收件/目標：Email 地址或 LINE ID。
狀態：sent（成功）、error（失敗）、skipped（跳過）。
備註：錯誤訊息或其他說明。
這樣商家就能追蹤每次推播的成效，也能避免重複發送。
實際應用場景
場景一：喚醒沉睡顧客
分群依據選「CAI購買行為趨勢」，選擇「沉寂」群體，發送訊息：
{{customerName}} 好久不見！
我們推出專屬回饋活動，只要本週內下單，即享 85 折優惠！
期待您的再次光臨 ❤️場景二：感謝忠實顧客
分群依據選「RFM會員類型」，選擇「重要價值客戶」和「重要發展客戶」，發送訊息：
親愛的 {{customerName}}，
感謝您長期以來的支持！
作為我們的 VIP 會員，我們特別為您準備了專屬優惠券，請查收您的 Email 信箱。
再次感謝您的支持！場景三：新品推薦給活躍顧客
分群依據選「CAI購買行為趨勢」，選擇「活躍」群體，發送訊息：
Hi {{customerName}}！
我們剛到貨一批新商品，想第一時間跟您分享！
點選連結查看：[商品連結]
限量供應，售完為止！總結
透過 Google Apps Script，我把 Google 試算表變成了一個功能完整的 CRM 後台：
✅ 商品管理（類別、庫存、價格、狀態）。
✅ 訂單管理（新增、更新、狀態追蹤）。
✅ 顧客管理（資料驗證、分群標籤）。
✅ 自動計算 RFM 和 CAI（呼叫後端 API）。
✅ 分群推播訊息（Email 和 LINE）。
✅ 發信紀錄（追蹤推播成效）。
小型商家不需要購買貴森森的 CRM 系統，就可以直接在試算表上管理資料，非常直觀易用。
雖然試算表有效能限制，但對於小型商家（幾百到幾千筆資料）來說完全足夠。如果未來要擴展，也可以輕鬆遷移到真正的資料庫，因為後端已經做好了分層架構。