這段要貼在哪裡：先在自己的工作資料夾建立同名相對位置，再複製內容。
var SHEET_NAMES_ = Object.freeze({
products: "Products",
orders: "Orders",
contacts: "Contacts"
});
var HEADERS_ = Object.freeze({
Products: Object.freeze(["productId", "name", "image", "price", "salePrice", "active"]),
Orders: Object.freeze(["orderNo", "submissionId", "timestamp", "buyer", "gender", "phone", "address", "email", "subtotal", "shipping", "total", "status", "itemsJson"]),
Contacts: Object.freeze(["contactNo", "submissionId", "timestamp", "name", "gender", "phone", "email", "message", "status"])
});
var TEST_SPREADSHEET_PROPERTY_ = "TEST_SPREADSHEET_ID";
var TIME_ZONE_ = "Asia/Taipei";
var FREE_SHIPPING_THRESHOLD_ = 2000;
var SHIPPING_FEE_ = 120;
var ORDER_STATUS_ = "待確認";
var CONTACT_STATUS_ = "未處理";
var ALLOWED_GENDERS_ = Object.freeze(["女", "男", "其他", "不透露"]);
function doGet(e) {
try {
var action = normalizeText_(e && e.parameter ? e.parameter.action : "");
if (action !== "listProducts") {
fail_("UNKNOWN_ACTION", "不支援的讀取動作");
}
var catalog = loadProductCatalog_(getTestSpreadsheet_());
var products = catalog.products.filter(function (product) {
return product.active;
}).map(function (product) {
return {
productId: product.productId,
name: product.name,
image: product.image,
price: product.price,
salePrice: product.salePrice
};
});
return jsonResponse_({
ok: true,
data: { products: products },
message: "商品載入成功"
});
} catch (error) {
return errorResponse_(error);
}
}
function doPost(e) {
try {
var request = parsePostRequest_(e);
if (request.action === "createOrder") {
return jsonResponse_(createOrder_(request.payload));
}
if (request.action === "createContact") {
return jsonResponse_(createContact_(request.payload));
}
fail_("UNKNOWN_ACTION", "不支援的寫入動作");
} catch (error) {
return errorResponse_(error);
}
}
function parsePostRequest_(e) {
var parameters = e && e.parameter ? e.parameter : {};
var action = normalizeText_(parameters.action);
var rawPayload = typeof parameters.payload === "string" ? parameters.payload : "";
if (!action) fail_("MISSING_ACTION", "缺少 action");
if (!rawPayload) fail_("MISSING_PAYLOAD", "缺少 payload");
if (rawPayload.length > 30000) fail_("PAYLOAD_TOO_LARGE", "payload 超過允許長度");
var payload;
try {
payload = JSON.parse(rawPayload);
} catch (error) {
fail_("INVALID_JSON", "payload 必須是有效 JSON");
}
if (!payload || typeof payload !== "object" || Array.isArray(payload)) {
fail_("INVALID_PAYLOAD", "payload 必須是 JSON object");
}
if (normalizeText_(parameters.website) || normalizeText_(payload.website)) {
fail_("HONEYPOT", "表單驗證失敗");
}
return { action: action, payload: payload };
}
function createOrder_(payload) {
var input = validateOrderInput_(payload);
var spreadsheet = getTestSpreadsheet_();
var ordersSheet = requireSheet_(spreadsheet, SHEET_NAMES_.orders);
var lock = LockService.getScriptLock();
if (!lock.tryLock(10000)) fail_("SERVER_BUSY", "系統忙碌中，請稍後使用相同 submissionId 重試");
try {
var existing = findBySubmissionId_(ordersSheet, input.submissionId);
if (existing) {
return {
ok: true,
data: {
duplicate: true,
orderNo: String(existing[0]),
subtotal: Number(existing[8]),
shipping: Number(existing[9]),
total: Number(existing[10]),
status: String(existing[11]),
items: parseStoredOrderItems_(existing[12])
},
message: "此訂單已接收，未重複寫入"
};
}
var catalog = loadProductCatalog_(spreadsheet);
var pricing = calculateAuthoritativeOrder_(input.items, catalog.byId);
var now = new Date();
var orderNo = makeNumber_("ORD", now);
ordersSheet.appendRow([
orderNo,
input.submissionId,
now.toISOString(),
safeSheetText_(input.buyer),
input.gender,
safeSheetText_(input.phone),
safeSheetText_(input.address),
safeSheetText_(input.email),
pricing.subtotal,
pricing.shipping,
pricing.total,
ORDER_STATUS_,
JSON.stringify(pricing.items)
]);
return {
ok: true,
data: {
duplicate: false,
orderNo: orderNo,
subtotal: pricing.subtotal,
shipping: pricing.shipping,
total: pricing.total,
status: ORDER_STATUS_,
items: pricing.items
},
message: "測試訂單已建立"
};
} finally {
lock.releaseLock();
}
}
function createContact_(payload) {
var input = validateContactInput_(payload);
var spreadsheet = getTestSpreadsheet_();
var contactsSheet = requireSheet_(spreadsheet, SHEET_NAMES_.contacts);
var lock = LockService.getScriptLock();
if (!lock.tryLock(10000)) fail_("SERVER_BUSY", "系統忙碌中，請稍後使用相同 submissionId 重試");
try {
var existing = findBySubmissionId_(contactsSheet, input.submissionId);
if (existing) {
return {
ok: true,
data: {
duplicate: true,
contactNo: String(existing[0]),
status: String(existing[8])
},
message: "此聯絡訊息已接收，未重複寫入"
};
}
var now = new Date();
var contactNo = makeNumber_("CON", now);
contactsSheet.appendRow([
contactNo,
input.submissionId,
now.toISOString(),
safeSheetText_(input.name),
input.gender,
safeSheetText_(input.phone),
safeSheetText_(input.email),
safeSheetText_(input.message),
CONTACT_STATUS_
]);
return {
ok: true,
data: {
duplicate: false,
contactNo: contactNo,
status: CONTACT_STATUS_
},
message: "測試聯絡訊息已建立"
};
} finally {
lock.releaseLock();
}
}
function validateOrderInput_(payload) {
var submissionId = requiredText_(payload, "submissionId", 8, 100);
validateSubmissionId_(submissionId);
return {
submissionId: submissionId,
buyer: requiredText_(payload, "buyer", 2, 80),
gender: validateGender_(requiredText_(payload, "gender", 1, 10)),
phone: validatePhone_(requiredText_(payload, "phone", 8, 24)),
address: requiredText_(payload, "address", 5, 200),
email: validateEmail_(requiredText_(payload, "email", 5, 160)),
items: validateItems_(payload.items)
};
}
function validateContactInput_(payload) {
var submissionId = requiredText_(payload, "submissionId", 8, 100);
validateSubmissionId_(submissionId);
return {
submissionId: submissionId,
name: requiredText_(payload, "name", 2, 80),
gender: validateGender_(requiredText_(payload, "gender", 1, 10)),
phone: validatePhone_(requiredText_(payload, "phone", 8, 24)),
email: validateEmail_(requiredText_(payload, "email", 5, 160)),
message: requiredText_(payload, "message", 5, 2000)
};
}
function validateItems_(items) {
if (!Array.isArray(items) || items.length < 1 || items.length > 50) {
fail_("INVALID_ITEMS", "商品項目數必須介於 1 到 50");
}
var quantities = {};
var order = [];
items.forEach(function (item) {
if (!item || typeof item !== "object" || Array.isArray(item)) {
fail_("INVALID_ITEM", "每筆商品必須是 object");
}
var productId = requiredText_(item, "productId", 1, 64);
if (!/^[A-Za-z0-9][A-Za-z0-9_-]{0,63}$/.test(productId)) {
fail_("INVALID_PRODUCT_ID", "商品編號格式錯誤");
}
if (!Number.isInteger(item.qty) || item.qty < 1 || item.qty > 20) {
fail_("INVALID_QTY", "商品數量必須是 1 到 20 的整數");
}
if (!Object.prototype.hasOwnProperty.call(quantities, productId)) order.push(productId);
quantities[productId] = (quantities[productId] || 0) + item.qty;
if (quantities[productId] > 20) {
fail_("INVALID_QTY", "同一商品合計數量不得超過 20");
}
});
return order.map(function (productId) {
return { productId: productId, qty: quantities[productId] };
});
}
function calculateAuthoritativeOrder_(items, productsById) {
var subtotal = 0;
var snapshots = items.map(function (item) {
var product = productsById[item.productId];
if (!product) fail_("UNKNOWN_PRODUCT", "訂單包含不存在的商品");
if (!product.active) fail_("PRODUCT_INACTIVE", "訂單包含已停售的商品");
var unitPrice = product.salePrice === null ? product.price : product.salePrice;
var lineTotal = unitPrice * item.qty;
if (!Number.isSafeInteger(lineTotal)) fail_("TOTAL_OUT_OF_RANGE", "商品金額超出允許範圍");
subtotal += lineTotal;
if (!Number.isSafeInteger(subtotal) || subtotal > 10000000) {
fail_("TOTAL_OUT_OF_RANGE", "訂單金額超出允許範圍");
}
return {
productId: product.productId,
name: product.name,
image: product.image,
price: product.price,
salePrice: product.salePrice,
unitPrice: unitPrice,
qty: item.qty,
lineTotal: lineTotal
};
});
var shipping = subtotal >= FREE_SHIPPING_THRESHOLD_ ? 0 : SHIPPING_FEE_;
return {
items: snapshots,
subtotal: subtotal,
shipping: shipping,
total: subtotal + shipping
};
}
function loadProductCatalog_(spreadsheet) {
var sheet = requireSheet_(spreadsheet, SHEET_NAMES_.products);
var lastRow = sheet.getLastRow();
var rows = lastRow < 2 ? [] : sheet.getRange(2, 1, lastRow - 1, HEADERS_.Products.length).getValues();
var products = [];
var byId = {};
rows.forEach(function (row, index) {
if (row.every(function (value) { return normalizeText_(value) === ""; })) return;
var rowNumber = index + 2;
var productId = normalizeText_(row[0]);
var name = normalizeText_(row[1]);
var image = normalizeText_(row[2]);
if (!/^[A-Za-z0-9][A-Za-z0-9_-]{0,63}$/.test(productId)) {
fail_("PRODUCT_CONFIG_ERROR", "Products 第 " + rowNumber + " 列 productId 格式錯誤");
}
if (!name || name.length > 120) {
fail_("PRODUCT_CONFIG_ERROR", "Products 第 " + rowNumber + " 列 name 格式錯誤");
}
if (Object.prototype.hasOwnProperty.call(byId, productId)) {
fail_("PRODUCT_CONFIG_ERROR", "Products 有重複 productId");
}
var price = productMoney_(row[3], false, "price", rowNumber);
var salePrice = productMoney_(row[4], true, "salePrice", rowNumber);
if (salePrice !== null && salePrice >= price) {
fail_("PRODUCT_CONFIG_ERROR", "salePrice 必須低於 price");
}
var product = {
productId: productId,
name: name,
image: image,
price: price,
salePrice: salePrice,
active: activeValue_(row[5])
};
products.push(product);
byId[productId] = product;
});
return { products: products, byId: byId };
}
function productMoney_(value, allowBlank, field, rowNumber) {
if (allowBlank && normalizeText_(value) === "") return null;
var amount = typeof value === "number" ? value : Number(normalizeText_(value));
if (!Number.isSafeInteger(amount) || amount <= 0 || amount > 1000000) {
fail_("PRODUCT_CONFIG_ERROR", "Products 第 " + rowNumber + " 列 " + field + " 必須是 1 到 1000000 的整數");
}
return amount;
}
function activeValue_(value) {
if (value === true || value === 1) return true;
var normalized = normalizeText_(value).toLowerCase();
return normalized === "true" || normalized === "1" || normalized === "yes" || normalized === "是";
}
function requiredText_(source, field, minLength, maxLength) {
if (!source || typeof source[field] !== "string") {
fail_("VALIDATION_ERROR", field + " 必須是文字");
}
var value = source[field].trim();
if (value.length < minLength || value.length > maxLength) {
fail_("VALIDATION_ERROR", field + " 長度必須介於 " + minLength + " 到 " + maxLength);
}
return value;
}
function validateSubmissionId_(value) {
if (!/^[A-Za-z0-9][A-Za-z0-9_-]{7,99}$/.test(value)) {
fail_("INVALID_SUBMISSION_ID", "submissionId 格式錯誤");
}
return value;
}
function validateGender_(value) {
if (ALLOWED_GENDERS_.indexOf(value) === -1) {
fail_("INVALID_GENDER", "gender 必須是女、男、其他或不透露");
}
return value;
}
function validatePhone_(value) {
var digits = value.replace(/\D/g, "");
if (!/^[0-9+()# .-]+$/.test(value) || digits.length < 8 || digits.length > 15) {
fail_("INVALID_PHONE", "phone 格式錯誤");
}
return value;
}
function validateEmail_(value) {
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value)) {
fail_("INVALID_EMAIL", "email 格式錯誤");
}
return value.toLowerCase();
}
function getTestSpreadsheet_() {
var spreadsheetId = normalizeText_(PropertiesService.getScriptProperties().getProperty(TEST_SPREADSHEET_PROPERTY_));
if (!spreadsheetId) {
fail_("CONFIG_ERROR", "尚未設定測試試算表");
}
try {
return SpreadsheetApp.openById(spreadsheetId);
} catch (error) {
fail_("CONFIG_ERROR", "無法開啟測試試算表");
}
}
function requireSheet_(spreadsheet, sheetName) {
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) fail_("SCHEMA_ERROR", "缺少 " + sheetName + " 工作表");
var expected = HEADERS_[sheetName];
var actual = sheet.getRange(1, 1, 1, expected.length).getDisplayValues()[0].map(normalizeText_);
if (sheet.getLastColumn() !== expected.length || JSON.stringify(actual) !== JSON.stringify(expected)) {
fail_("SCHEMA_ERROR", sheetName + " 表頭不符合契約");
}
return sheet;
}
function findBySubmissionId_(sheet, submissionId) {
var lastRow = sheet.getLastRow();
if (lastRow < 2) return null;
var values = sheet.getRange(2, 2, lastRow - 1, 1).getDisplayValues();
for (var index = 0; index < values.length; index += 1) {
if (normalizeText_(values[index][0]) === submissionId) {
return sheet.getRange(index + 2, 1, 1, sheet.getLastColumn()).getValues()[0];
}
}
return null;
}
function parseStoredOrderItems_(rawValue) {
var parsed;
try {
parsed = JSON.parse(String(rawValue || "[]"));
} catch (error) {
return [];
}
if (!Array.isArray(parsed) || parsed.length > 50) return [];
var safeItems = [];
for (var index = 0; index < parsed.length; index += 1) {
var item = parsed[index];
if (!item || typeof item !== "object" || Array.isArray(item)) return [];
var productId = normalizeText_(item.productId);
var name = normalizeText_(item.name);
var image = normalizeText_(item.image);
var price = Number(item.price);
var salePrice = item.salePrice === null || normalizeText_(item.salePrice) === "" ? null : Number(item.salePrice);
var unitPrice = Number(item.unitPrice);
var qty = Number(item.qty);
var lineTotal = Number(item.lineTotal);
var salePriceValid = salePrice === null || (Number.isSafeInteger(salePrice) && salePrice > 0 && salePrice < price);
if (!/^[A-Za-z0-9][A-Za-z0-9_-]{0,63}$/.test(productId)
|| !name
|| !Number.isSafeInteger(price) || price <= 0
|| !salePriceValid
|| !Number.isSafeInteger(unitPrice) || unitPrice <= 0
|| !Number.isInteger(qty) || qty < 1 || qty > 20
|| !Number.isSafeInteger(lineTotal) || lineTotal !== unitPrice * qty) {
return [];
}
safeItems.push({
productId: productId,
name: name,
image: image,
price: price,
salePrice: salePrice,
unitPrice: unitPrice,
qty: qty,
lineTotal: lineTotal
});
}
return safeItems;
}
function makeNumber_(prefix, date) {
var day = Utilities.formatDate(date, TIME_ZONE_, "yyyyMMdd");
var random = Utilities.getUuid().replace(/-/g, "").slice(0, 10).toUpperCase();
return prefix + "-" + day + "-" + random;
}
function safeSheetText_(value) {
var text = String(value);
return /^[=+-@]/.test(text) ? "'" + text : text;
}
function normalizeText_(value) {
return value === null || value === undefined ? "" : String(value).trim();
}
function fail_(code, message) {
var error = new Error(message);
error.apiCode = code;
throw error;
}
function errorResponse_(error) {
console.error(error && error.stack ? error.stack : error);
var known = error && error.apiCode;
return jsonResponse_({
ok: false,
message: known ? error.message : "伺服器處理失敗",
error: known ? error.apiCode : "INTERNAL_ERROR"
});
}
function jsonResponse_(payload) {
return ContentService.createTextOutput(JSON.stringify(payload))
.setMimeType(ContentService.MimeType.JSON);
}
function setupTestSheets() {
var spreadsheet = getTestSpreadsheet_();
Object.keys(HEADERS_).forEach(function (sheetName) {
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) sheet = spreadsheet.insertSheet(sheetName);
if (sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, HEADERS_[sheetName].length).setValues([HEADERS_[sheetName]]);
sheet.setFrozenRows(1);
}
requireSheet_(spreadsheet, sheetName);
});
return "測試工作表已建立並通過表頭驗證";
}
