雙層整合版完整 GS
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('GAS 行政流工具')
.addItem('① 建立飲料訂購表單', '建立飲料訂購表單')
.addItem('①-1 產生 15 筆模擬訂購資料', '產生模擬訂購資料')
.addItem('② 建立飲料訂購工作台', '建立飲料訂購工作台')
.addItem('③ 整理訂購明細', '整理訂購明細')
.addItem('④ 統計品項數量', '統計品項數量')
.addItem('⑤ 產生群組通知文字', '產生群組通知文字')
.addSeparator()
.addItem('⑥ 建立通用行政工作台與欄位對照', '建立通用行政工作台與欄位對照')
.addItem('⑦ 整理通用明細', '整理通用明細')
.addItem('⑧ 統計分類數量', '統計分類數量')
.addItem('⑨ 產生通用通知文字', '產生通用通知文字')
.addItem('⑩ 產生行政情境範例對照', '產生行政情境範例對照')
.addToUi();
}
function 建立飲料訂購表單() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ui = SpreadsheetApp.getUi();
let settingSheet = ss.getSheetByName('系統設定');
if (!settingSheet) {
settingSheet = ss.insertSheet('系統設定');
}
if (settingSheet.getLastRow() === 0) {
settingSheet.getRange(1, 1, 1, 2).setValues([['key', 'value']]);
}
const settings = {};
const settingRows = {};
settingSheet.getDataRange().getValues().slice(1).forEach((row, index) => {
const key = String(row[0] || '').trim();
const value = row[1];
if (key) {
settingRows[key] = index + 2;
if (value !== '') settings[key] = value;
}
});
let existingForm = null;
if (settings['表單ID']) {
try {
existingForm = FormApp.openById(String(settings['表單ID']).trim());
} catch (error) {
existingForm = null;
}
}
if (!existingForm && settings['表單編輯網址']) {
try {
existingForm = FormApp.openByUrl(String(settings['表單編輯網址']).trim());
} catch (error) {
existingForm = null;
}
}
if (!existingForm && settings['表單填寫網址']) {
try {
existingForm = FormApp.openByUrl(String(settings['表單填寫網址']).trim());
} catch (error) {
existingForm = null;
}
}
if (existingForm) {
const result = ui.alert(
'已存在飲料訂購表單',
'系統設定中已經有有效的表單資料。是否仍要建立一份新的表單？',
ui.ButtonSet.YES_NO
);
if (result !== ui.Button.YES) {
ui.alert('已取消建立新表單。');
return;
}
}
const form = FormApp.create('飲料訂購表單');
form.setDescription('請填寫飲料訂購資料。這份表單用來練習行政收件：收件、整理、統計與通知。');
form.addTextItem().setTitle('姓名').setRequired(true);
form.addListItem().setTitle('飲料品項').setChoiceValues(['紅茶', '綠茶', '奶茶', '拿鐵']).setRequired(true);
form.addMultipleChoiceItem().setTitle('甜度').setChoiceValues(['正常糖', '半糖', '微糖', '無糖']).setRequired(true);
form.addMultipleChoiceItem().setTitle('冰塊').setChoiceValues(['正常冰', '少冰', '微冰', '去冰']).setRequired(true);
form.addListItem().setTitle('數量').setChoiceValues(['1', '2', '3', '4', '5']).setRequired(true);
form.addParagraphTextItem().setTitle('備註').setRequired(false);
form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
const updates = {
'流程名稱': settings['流程名稱'] || '飲料訂購行政流',
'通知模式': settings['通知模式'] || '群組通知',
'通知標題': settings['通知標題'] || '飲料訂購行政流',
'店家名稱': settings['店家名稱'] || '今天飲料店',
'收單時間': settings['收單時間'] || '今天 11:00',
'截止時間': settings['截止時間'] || settings['收單時間'] || '今天 11:00',
'表單ID': form.getId(),
'表單編輯網址': form.getEditUrl(),
'表單填寫網址': form.getPublishedUrl(),
'回應試算表ID': ss.getId()
};
Object.entries(updates).forEach(([key, value]) => {
if (settingRows[key]) {
settingSheet.getRange(settingRows[key], 2).setValue(value);
} else {
settingSheet.appendRow([key, value]);
}
});
ui.alert('飲料訂購表單已建立完成！\n\n填寫網址：\n' + form.getPublishedUrl());
}
function 產生模擬訂購資料() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let responseSheet = 尋找表單回應工作表();
if (!responseSheet) {
const excludedNames = new Set([
'訂購明細',
'品項統計',
'通知文字',
'系統設定',
'整理明細',
'分類統計',
'欄位對照',
'行政情境',
'行政情境範例對照'
]);
responseSheet = ss.getSheets().find(sheet => {
if (excludedNames.has(sheet.getName()) || sheet.getLastColumn() < 1) return false;
const candidateHeaders = sheet
.getRange(1, 1, 1, sheet.getLastColumn())
.getDisplayValues()[0]
.map(value => String(value).trim());
const name = sheet.getName();
const hasCommonResponseName =
name.startsWith('表單回應') ||
name.startsWith('表單回覆') ||
name.startsWith('Form Responses');
return candidateHeaders.includes('時間戳記') || hasCommonResponseName;
}) || null;
}
if (!responseSheet) {
responseSheet = ss.insertSheet('表單回應 1');
}
const expectedHeaders = ['時間戳記', '姓名', '飲料品項', '甜度', '冰塊', '數量', '備註'];
const currentHeaders = responseSheet
.getRange(1, 1, 1, expectedHeaders.length)
.getDisplayValues()[0]
.map(value => String(value).trim());
const headerIsValid = expectedHeaders.every(
(header, index) => currentHeaders[index] === header
);
if (!headerIsValid) {
if (responseSheet.getLastRow() > 0) {
throw new Error(
'找到的表單回應工作表欄位與課程預期不一致，請確認第一列是否為：時間戳記、姓名、飲料品項、甜度、冰塊、數量、備註。'
);
}
responseSheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
responseSheet.getRange(1, 1, 1, expectedHeaders.length).setFontWeight('bold');
responseSheet.setFrozenRows(1);
}
const mockRows = [
['王小明', '紅茶', '正常糖', '少冰', 1, ''],
['陳小華', '奶茶', '半糖', '去冰', 2, '加珍珠'],
['林品安', '綠茶', '微糖', '微冰', 1, ''],
['黃子晴', '拿鐵', '無糖', '少冰', 1, '不要吸管'],
['張育誠', '紅茶', '半糖', '正常冰', 3, ''],
['李佳蓉', '奶茶', '微糖', '少冰', 1, ''],
['吳柏翰', '綠茶', '無糖', '去冰', 2, ''],
['劉怡君', '拿鐵', '正常糖', '正常冰', 1, '加冰塊'],
['蔡承恩', '紅茶', '微糖', '去冰', 1, ''],
['楊雅婷', '奶茶', '半糖', '微冰', 2, '少甜一點'],
['周冠宇', '綠茶', '正常糖', '少冰', 1, ''],
['鄭羽庭', '拿鐵', '半糖', '去冰', 1, ''],
['許哲維', '紅茶', '無糖', '微冰', 2, ''],
['洪詩涵', '奶茶', '正常糖', '少冰', 1, '加椰果'],
['郭家豪', '綠茶', '微糖', '正常冰', 3, '']
];
const baseTime = new Date();
const output = mockRows.map((row, index) => {
const timestamp = new Date(baseTime.getTime() + index * 60 * 1000);
return [timestamp, ...row];
});
const maxRows = responseSheet.getMaxRows();
if (maxRows > 1) {
responseSheet.getRange(2, 1, maxRows - 1, expectedHeaders.length).clearContent();
}
responseSheet.getRange(2, 1, output.length, expectedHeaders.length).setValues(output);
responseSheet.autoResizeColumns(1, expectedHeaders.length);
SpreadsheetApp.getUi().alert('已產生 15 筆模擬訂購資料，可以繼續執行整理訂購明細。');
}
function 建立飲料訂購工作台() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ui = SpreadsheetApp.getUi();
const existingSettings = {};
const settingRows = {};
const settingSheet = ss.getSheetByName('系統設定');
if (settingSheet) {
settingSheet.getDataRange().getValues().slice(1).forEach((row, index) => {
const key = String(row[0] || '').trim();
const value = row[1];
if (key) {
settingRows[key] = index + 2;
if (value !== '') existingSettings[key] = value;
}
});
}
const outputSheetNames = ['訂購明細', '品項統計', '通知文字'];
const hasOutputData = outputSheetNames.some(name => {
const sheet = ss.getSheetByName(name);
return sheet && sheet.getLastRow() > 1;
});
if (hasOutputData) {
const result = ui.alert(
'重新建立工作台',
'這個操作會清空訂購明細、品項統計與通知文字，但不會刪除原始表單回應資料。適合課程重新操作或重設使用。是否繼續？',
ui.ButtonSet.YES_NO
);
if (result !== ui.Button.YES) return;
}
const sheetConfigs = [
{ name: '訂購明細', headers: ['序號', '填寫時間', '姓名', '飲料品項', '甜度', '冰塊', '數量', '備註'] },
{ name: '品項統計', headers: ['飲料品項', '杯數'] },
{ name: '通知文字', headers: ['產生時間', '通知內容'] },
{ name: '系統設定', headers: ['key', 'value'] }
];
sheetConfigs.forEach(config => {
let sheet = ss.getSheetByName(config.name);
if (!sheet) sheet = ss.insertSheet(config.name);
if (config.name === '系統設定') {
if (sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, config.headers.length).setValues([config.headers]);
sheet.getRange(1, 1, 1, config.headers.length).setFontWeight('bold');
sheet.setFrozenRows(1);
}
} else {
sheet.clear();
sheet.getRange(1, 1, 1, config.headers.length).setValues([config.headers]);
sheet.getRange(1, 1, 1, config.headers.length).setFontWeight('bold');
sheet.setFrozenRows(1);
}
});
const updatedSettingSheet = ss.getSheetByName('系統設定');
const defaultSettings = {
'流程名稱': existingSettings['流程名稱'] || '飲料訂購行政流',
'通知模式': existingSettings['通知模式'] || '群組通知',
'通知標題': existingSettings['通知標題'] || '飲料訂購行政流',
'店家名稱': existingSettings['店家名稱'] || '今天飲料店',
'收單時間': existingSettings['收單時間'] || existingSettings['截止時間'] || '今天 11:00',
'截止時間': existingSettings['截止時間'] || existingSettings['收單時間'] || '今天 11:00',
'表單ID': existingSettings['表單ID'] || '',
'表單編輯網址': existingSettings['表單編輯網址'] || '',
'表單填寫網址': existingSettings['表單填寫網址'] || '',
'回應試算表ID': existingSettings['回應試算表ID'] || ss.getId()
};
Object.entries(defaultSettings).forEach(([key, value]) => {
if (settingRows[key]) {
updatedSettingSheet.getRange(settingRows[key], 2).setValue(value);
} else {
updatedSettingSheet.appendRow([key, value]);
}
});
ss.getSheets().forEach(sheet => sheet.autoResizeColumns(1, Math.max(1, sheet.getLastColumn())));
ui.alert('飲料訂購工作台建立完成！');
}
function 整理訂購明細() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const responseSheet = 尋找表單回應工作表();
const detailSheet = ss.getSheetByName('訂購明細');
if (!responseSheet) {
SpreadsheetApp.getUi().alert('找不到表單回應工作表，請先建立表單並送出測試資料。');
return;
}
if (!detailSheet) {
SpreadsheetApp.getUi().alert('找不到「訂購明細」，請先執行建立飲料訂購工作台。');
return;
}
const data = responseSheet.getDataRange().getValues();
if (data.length <= 1) {
SpreadsheetApp.getUi().alert('目前沒有表單回應資料，請先填寫幾筆測試資料。');
return;
}
const rows = data.slice(1);
const output = rows.map((row, index) => {
const rawQuantity = row[5];
const quantity = Number(rawQuantity);
if (
rawQuantity === '' ||
rawQuantity === null ||
rawQuantity === undefined ||
Number.isNaN(quantity) ||
quantity <= 0
) {
throw new Error(`表單回應第 ${index + 2} 列的數量不正確：${rawQuantity}`);
}
return [
index + 1,
row[0],
row[1],
row[2],
row[3],
row[4],
quantity,
row[6] || ''
];
});
detailSheet.getRange(2, 1, detailSheet.getMaxRows() - 1, 8).clearContent();
if (output.length) {
detailSheet.getRange(2, 1, output.length, output[0].length).setValues(output);
}
SpreadsheetApp.getUi().alert('訂購明細整理完成！');
}
function 統計品項數量() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const detailSheet = ss.getSheetByName('訂購明細');
const statSheet = ss.getSheetByName('品項統計');
if (!detailSheet || !statSheet) {
SpreadsheetApp.getUi().alert('找不到訂購明細或品項統計，請先建立飲料訂購工作台。');
return;
}
const data = detailSheet.getDataRange().getValues();
if (data.length <= 1) {
SpreadsheetApp.getUi().alert('訂購明細沒有資料，請先整理訂購明細。');
return;
}
const itemOrder = ['紅茶', '綠茶', '奶茶', '拿鐵'];
const totals = new Map(itemOrder.map(item => [item, 0]));
data.slice(1).forEach((row, index) => {
const item = String(row[3] || '').trim();
if (!item) return;
const quantity = Number(row[6]);
if (Number.isNaN(quantity)) {
throw new Error(`訂購明細第 ${index + 2} 列的數量無法加總：${row[6]}`);
}
totals.set(item, (totals.get(item) || 0) + quantity);
});
const output = Array.from(totals.entries()).map(([item, quantity]) => [item, quantity]);
const total = output.reduce((sum, row) => sum + row[1], 0);
output.push(['總計', total]);
statSheet.getRange(2, 1, statSheet.getMaxRows() - 1, 2).clearContent();
if (output.length) {
statSheet.getRange(2, 1, output.length, 2).setValues(output);
}
SpreadsheetApp.getUi().alert('品項統計完成！');
}
function 產生群組通知文字() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const detailSheet = ss.getSheetByName('訂購明細');
const statSheet = ss.getSheetByName('品項統計');
const noticeSheet = ss.getSheetByName('通知文字');
if (!detailSheet || !statSheet || !noticeSheet) {
SpreadsheetApp.getUi().alert('找不到訂購明細、品項統計或通知文字，請先建立飲料訂購工作台。');
return;
}
const settings = {};
const settingSheet = ss.getSheetByName('系統設定');
if (settingSheet) {
settingSheet.getDataRange().getValues().slice(1).forEach(row => {
const key = String(row[0] || '').trim();
const value = row[1];
if (key && value !== '') settings[key] = value;
});
}
const processName = settings['流程名稱'] || '飲料訂購行政流';
const noticeTitle = settings['通知標題'] || processName;
const storeName = settings['店家名稱'] || '今天飲料店';
const closeTime = settings['收單時間'] || settings['截止時間'] || '今天 11:00';
const detailData = detailSheet.getDataRange().getValues();
const statData = statSheet.getDataRange().getValues();
if (detailData.length <= 1) {
SpreadsheetApp.getUi().alert('訂購明細沒有資料，請先整理訂購明細。');
return;
}
const statLines = statData.slice(1)
.filter(row => row[0])
.map(row => `${row[0]}：${row[1]}`);
const detailLines = detailData.slice(1)
.filter(row => row[2])
.map(row => {
const note = row[7] ? `（${row[7]}）` : '';
return `${row[2]}：${row[3]}／${row[4]}／${row[5]}／${row[6]} 杯${note}`;
});
if (!statLines.length || !detailLines.length) {
SpreadsheetApp.getUi().alert('目前沒有可輸出的統計或訂購明細，請先完成整理與統計。');
return;
}
const message = [
`【${noticeTitle}】`,
`店家名稱：${storeName}`,
`收單時間：${closeTime}`,
'',
'品項統計：',
...statLines,
'',
'訂購明細：',
...detailLines,
'',
'確認提醒：請確認姓名、飲料、甜度、冰塊與數量是否正確。'
].join('\n');
noticeSheet.getRange(2, 1, noticeSheet.getMaxRows() - 1, 2).clearContent();
noticeSheet.getRange(2, 1, 1, 2).setValues([[new Date(), message]]);
SpreadsheetApp.getUi().alert(`「${processName}」群組通知文字已產生於「通知文字」。`);
}
function 建立通用行政工作台與欄位對照() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ui = SpreadsheetApp.getUi();
const existingSettings = {};
const settingRows = {};
const settingSheet = ss.getSheetByName('系統設定');
if (settingSheet) {
settingSheet.getDataRange().getValues().slice(1).forEach((row, index) => {
const key = String(row[0] || '').trim();
const value = row[1];
if (key) {
settingRows[key] = index + 2;
if (value !== '') existingSettings[key] = value;
}
});
}
const outputSheetNames = ['整理明細', '分類統計', '通知文字', '欄位對照'];
const hasOutputData = outputSheetNames.some(name => {
const sheet = ss.getSheetByName(name);
return sheet && sheet.getLastRow() > 1;
});
if (hasOutputData) {
const result = ui.alert(
'重新建立通用工作台',
'這個操作會清空整理明細、分類統計、通知文字與欄位對照，但不會刪除原始表單回應資料。適合課程重新操作或重設使用。是否繼續？',
ui.ButtonSet.YES_NO
);
if (result !== ui.Button.YES) return;
}
const sheetConfigs = [
{ name: '整理明細', headers: ['序號', '填寫時間', 'name', 'category', 'option1', 'option2', 'quantity', 'note'] },
{ name: '分類統計', headers: ['分類', '數量'] },
{ name: '通知文字', headers: ['產生時間', '通知內容'] },
{ name: '系統設定', headers: ['key', 'value'] },
{ name: '欄位對照', headers: ['後端角色', '表單欄位名稱', '是否必要', '說明'] }
];
sheetConfigs.forEach(config => {
let sheet = ss.getSheetByName(config.name);
if (!sheet) sheet = ss.insertSheet(config.name);
if (config.name === '系統設定') {
if (sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, config.headers.length).setValues([config.headers]);
sheet.getRange(1, 1, 1, config.headers.length).setFontWeight('bold');
sheet.setFrozenRows(1);
}
} else {
sheet.clear();
sheet.getRange(1, 1, 1, config.headers.length).setValues([config.headers]);
sheet.getRange(1, 1, 1, config.headers.length).setFontWeight('bold');
sheet.setFrozenRows(1);
}
});
const updatedSettingSheet = ss.getSheetByName('系統設定');
const defaultSettings = {
'流程名稱': existingSettings['流程名稱'] || '飲料訂購行政流',
'通知模式': existingSettings['通知模式'] || '群組通知',
'通知標題': existingSettings['通知標題'] || '飲料訂購行政流',
'店家名稱': existingSettings['店家名稱'] || '今天飲料店',
'收單時間': existingSettings['收單時間'] || existingSettings['截止時間'] || '今天 11:00',
'截止時間': existingSettings['截止時間'] || existingSettings['收單時間'] || '今天 11:00',
'表單ID': existingSettings['表單ID'] || '',
'表單編輯網址': existingSettings['表單編輯網址'] || '',
'表單填寫網址': existingSettings['表單填寫網址'] || '',
'回應試算表ID': existingSettings['回應試算表ID'] || ss.getId()
};
Object.entries(defaultSettings).forEach(([key, value]) => {
if (settingRows[key]) {
updatedSettingSheet.getRange(settingRows[key], 2).setValue(value);
} else {
updatedSettingSheet.appendRow([key, value]);
}
});
ss.getSheetByName('欄位對照').getRange(2, 1, 6, 4).setValues([
['name', '姓名', '是', '訂購者、報名者或申請人'],
['category', '飲料品項', '是', '要統計或分類的主要欄位'],
['option1', '甜度', '否', '補充條件一'],
['option2', '冰塊', '否', '補充條件二'],
['quantity', '數量', '是', '可加總的數字欄位'],
['note', '備註', '否', '補充說明、特殊需求或附件連結']
]);
ss.getSheets().forEach(sheet => sheet.autoResizeColumns(1, Math.max(1, sheet.getLastColumn())));
ui.alert('通用行政工作台與欄位對照建立完成！');
}
function 整理通用明細() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const responseSheet = 尋找表單回應工作表();
const detailSheet = ss.getSheetByName('整理明細');
if (!responseSheet) {
SpreadsheetApp.getUi().alert('找不到表單回應工作表，請先建立表單並送出測試資料。');
return;
}
if (!detailSheet) {
SpreadsheetApp.getUi().alert('找不到「整理明細」，請先執行建立通用行政工作台與欄位對照。');
return;
}
const data = responseSheet.getDataRange().getValues();
if (data.length <= 1) {
SpreadsheetApp.getUi().alert('目前沒有表單回應資料，請先填寫幾筆測試資料。');
return;
}
const headers = data[0];
const headerIndex = 建立欄位索引(headers);
const fieldMap = 取得欄位對照();
const rows = data.slice(1);
try {
const output = rows.map((row, index) => {
const rawQuantity = 依角色取值(row, headerIndex, fieldMap, 'quantity');
const quantity = Number(rawQuantity);
if (
rawQuantity === '' ||
rawQuantity === null ||
rawQuantity === undefined ||
Number.isNaN(quantity) ||
quantity <= 0
) {
throw new Error(`表單回應第 ${index + 2} 列的數量不正確：${rawQuantity}`);
}
return [
index + 1,
row[0],
依角色取值(row, headerIndex, fieldMap, 'name'),
依角色取值(row, headerIndex, fieldMap, 'category'),
依角色取值(row, headerIndex, fieldMap, 'option1'),
依角色取值(row, headerIndex, fieldMap, 'option2'),
quantity,
依角色取值(row, headerIndex, fieldMap, 'note')
];
});
detailSheet.getRange(2, 1, detailSheet.getMaxRows() - 1, 8).clearContent();
detailSheet.getRange(2, 1, output.length, output[0].length).setValues(output);
SpreadsheetApp.getUi().alert('通用明細整理完成！');
} catch (error) {
SpreadsheetApp.getUi().alert(error.message);
throw error;
}
}
function 統計分類數量() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const detailSheet = ss.getSheetByName('整理明細');
const statSheet = ss.getSheetByName('分類統計');
if (!detailSheet || !statSheet) {
SpreadsheetApp.getUi().alert('找不到整理明細或分類統計，請先建立通用行政工作台與欄位對照。');
return;
}
const data = detailSheet.getDataRange().getValues();
if (data.length <= 1) {
SpreadsheetApp.getUi().alert('整理明細沒有資料，請先整理通用明細。');
return;
}
const headers = data[0].map(header => String(header || '').trim());
const categoryIndex = headers.indexOf('category');
const quantityIndex = headers.indexOf('quantity');
if (categoryIndex === -1 || quantityIndex === -1) {
SpreadsheetApp.getUi().alert('整理明細缺少 category 或 quantity 欄位。');
return;
}
const totals = new Map();
data.slice(1).forEach((row, index) => {
const category = String(row[categoryIndex] || '').trim();
if (!category) return;
const quantity = Number(row[quantityIndex]);
if (Number.isNaN(quantity)) {
throw new Error(`整理明細第 ${index + 2} 列的 quantity 無法加總：${row[quantityIndex]}`);
}
totals.set(category, (totals.get(category) || 0) + quantity);
});
const output = Array.from(totals.entries()).map(([category, quantity]) => [category, quantity]);
const total = output.reduce((sum, row) => sum + row[1], 0);
output.push(['總計', total]);
statSheet.getRange(2, 1, statSheet.getMaxRows() - 1, 2).clearContent();
if (output.length) {
statSheet.getRange(2, 1, output.length, 2).setValues(output);
}
SpreadsheetApp.getUi().alert('分類統計完成！');
}
function 產生通用通知文字() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const detailSheet = ss.getSheetByName('整理明細');
const statSheet = ss.getSheetByName('分類統計');
const noticeSheet = ss.getSheetByName('通知文字');
if (!detailSheet || !statSheet || !noticeSheet) {
SpreadsheetApp.getUi().alert('找不到整理明細、分類統計或通知文字，請先建立通用行政工作台與欄位對照。');
return;
}
const settings = 取得系統設定();
const processName = settings['流程名稱'] || '飲料訂購行政流';
const noticeTitle = settings['通知標題'] || processName;
const deadline = settings['截止時間'] || '今天 11:00';
const detailData = detailSheet.getDataRange().getValues();
const statData = statSheet.getDataRange().getValues();
if (detailData.length <= 1) {
SpreadsheetApp.getUi().alert('整理明細沒有資料，請先整理通用明細。');
return;
}
const detailHeaders = detailData[0].map(header => String(header || '').trim());
const idx = role => detailHeaders.indexOf(role);
const statLines = statData.slice(1)
.filter(row => row[0])
.map(row => `${row[0]}：${row[1]}`);
const detailLines = detailData.slice(1)
.filter(row => row[idx('name')])
.map(row => {
const note = row[idx('note')] ? `（${row[idx('note')]}）` : '';
return `${row[idx('name')]}：${row[idx('category')]}／${row[idx('option1')]}／${row[idx('option2')]}／${row[idx('quantity')]}${note}`;
});
if (!statLines.length || !detailLines.length) {
SpreadsheetApp.getUi().alert('目前沒有可輸出的分類統計或整理明細，請先完成整理與統計。');
return;
}
const message = [
`【${noticeTitle}】`,
`截止時間：${deadline}`,
'',
'分類統計：',
...statLines,
'',
'明細：',
...detailLines,
'',
'請確認以上資料是否正確。'
].join('\n');
noticeSheet.getRange(2, 1, noticeSheet.getMaxRows() - 1, 2).clearContent();
noticeSheet.getRange(2, 1, 1, 2).setValues([[new Date(), message]]);
SpreadsheetApp.getUi().alert(`「${processName}」通知文字已產生於「通知文字」。`);
}
function 產生行政情境範例對照() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName('行政情境範例對照');
if (sheet && sheet.getLastRow() > 1) {
const ui = SpreadsheetApp.getUi();
const result = ui.alert(
'重新建立行政情境範例對照',
'這個操作會清空目前的行政情境範例對照。是否繼續？',
ui.ButtonSet.YES_NO
);
if (result !== ui.Button.YES) return;
}
if (!sheet) sheet = ss.insertSheet('行政情境範例對照');
sheet.clear();
const rows = [
['行政情境', 'name', 'category', 'option1', 'option2', 'quantity', 'note'],
['飲料訂購', '姓名', '飲料品項', '甜度', '冰塊', '數量', '備註'],
['社團報名', '學生姓名', '社團志願', '年級', '班級', '報名人數', '特殊需求'],
['研習報名', '姓名', '研習場次', '服務單位', '職稱', '報名人數', '研習需求'],
['設備借用', '借用人', '設備名稱', '借用日期', '歸還日期', '借用數量', '用途說明'],
['成果收件', '繳交人', '成果類型', '班級', '繳交狀態', '件數', '附件連結'],
['值勤調查', '姓名', '值勤時段', '可支援日期', '任務類型', '可支援人數', '備註']
];
sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
sheet.getRange(1, 1, 1, rows[0].length).setFontWeight('bold');
sheet.setFrozenRows(1);
sheet.autoResizeColumns(1, rows[0].length);
SpreadsheetApp.getUi().alert('行政情境範例對照已建立完成！');
}
function 建立欄位索引(headers) {
const headerIndex = {};
headers.forEach((header, index) => {
const name = String(header || '').trim();
if (name) headerIndex[name] = index;
});
return headerIndex;
}
function 取得欄位對照() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('欄位對照');
if (!sheet) throw new Error('找不到「欄位對照」，請先建立通用行政工作台與欄位對照。');
const data = sheet.getDataRange().getValues();
const fieldMap = {};
data.slice(1).forEach(row => {
const role = String(row[0] || '').trim();
const fieldName = String(row[1] || '').trim();
const required = String(row[2] || '').trim() === '是';
const description = String(row[3] || '').trim();
if (role) fieldMap[role] = { fieldName, required, description };
});
return fieldMap;
}
function 依角色取值(row, headerIndex, fieldMap, role) {
const requiredRoles = ['name', 'category', 'quantity'];
const config = fieldMap[role];
if (!config) {
if (requiredRoles.includes(role)) throw new Error(`欄位對照缺少必要角色：${role}`);
return '';
}
const fieldName = String(config.fieldName || '').trim();
if (!fieldName) {
if (config.required) throw new Error(`必要角色 ${role} 沒有設定表單欄位名稱。`);
return '';
}
const index = headerIndex[fieldName];
if (index === undefined) {
if (config.required) throw new Error(`必要欄位不存在：欄位對照中的「${fieldName}」找不到對應的表單欄位。`);
return '';
}
const value = row[index];
return value === null || value === undefined ? '' : value;
}
function 取得系統設定() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('系統設定');
const settings = {};
if (!sheet) return settings;
const data = sheet.getDataRange().getValues();
data.slice(1).forEach(row => {
const key = String(row[0] || '').trim();
const value = row[1];
if (key && value !== '') settings[key] = value;
});
return settings;
}
function 尋找表單回應工作表() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const excludedNames = new Set([
'訂購明細',
'品項統計',
'通知文字',
'系統設定',
'整理明細',
'分類統計',
'欄位對照',
'行政情境',
'行政情境範例對照'
]);
const mappedRequiredHeaders = [];
const fieldMapSheet = ss.getSheetByName('欄位對照');
if (fieldMapSheet && fieldMapSheet.getLastRow() > 1) {
const requiredRoles = new Set(['name', 'category', 'quantity']);
fieldMapSheet.getDataRange().getDisplayValues().slice(1).forEach(row => {
const role = String(row[0] || '').trim();
const fieldName = String(row[1] || '').trim();
if (requiredRoles.has(role) && fieldName) mappedRequiredHeaders.push(fieldName);
});
}
return ss.getSheets().find(sheet => {
const name = sheet.getName();
if (excludedNames.has(name)) return false;
if (sheet.getLastColumn() < 1) return false;
const headers = sheet
.getRange(1, 1, 1, sheet.getLastColumn())
.getDisplayValues()[0]
.map(value => String(value).trim());
const matchesFixedDrinkOrder = (
headers.includes('時間戳記') &&
headers.includes('姓名') &&
headers.includes('飲料品項')
);
const matchesFieldMap = (
headers.includes('時間戳記') &&
mappedRequiredHeaders.length === 3 &&
mappedRequiredHeaders.every(header => headers.includes(header))
);
return matchesFixedDrinkOrder || matchesFieldMap;
}) || null;
}