JS / 以Google表單為基礎結合日曆與試算表，用GAS建置簡易教室借用系統-試算表觸發

JS / 以Google表單為基礎結合日曆與試算表，用GAS建置簡易教室借用系統-試算表觸發

Post Views: 41

接下來進入到審核階段

工作表的架構

A-G欄是表單資料產生的

H欄是自訂用來記錄跟程式判斷是否進行寫入日曆的依據

I-J欄是程式寫出的資訊

 

第1步，透過表單回覆資料的工作表，連結到GAS

 

第2步，編輯GAS程式碼

總共有1個主程式+4個功能函數+1個內建的函數onOpen()

onOpen()函數顧名思義，當試算表打開時會觸發

這裡用來執行模組4-更新資料驗證、在工作表增加自訂選單

模組4的程式碼是我額外加的

這裡要補充說明在前面提到的：AI搞錯的地方

假設要取得B2的資料，流程會是

1.先取得整個工作表的資料，會是個陣列

2.取得陣列的第2筆資料，陣列序號是從0開始，所以是陣列[1]

3.再取得第2筆資料的第2筆資料，所以是陣列[1][1]

AI在一開始的參數設定，就是用陣列序號的方式

例如：CLASSROOM_NAME: 1, //  B 欄 借用教室名稱

所以在後面要抓取資料的時候

const dataRange = sheet.getDataRange(); //取得工作表資料範圍
const allData = dataRange.getValues();     //取得範圍內的資料
const row = allData[i];                                   //取得指定的列數所有的資料

再來就是取得特定欄位的資料，假如要取得的是B欄，程式碼應該是row[1]，也就是下面的

const classroomName = row[CONFIG.COL_INDEX.CLASSROOM_NAME];

但是AI的程式碼一開始是

const classroomName = row[CONFIG.COL_INDEX.CLASSROOM_NAME-1];

所以會對應不到正確欄位

到了後面，要寫入試算表資料時，會用到getRange()函數

裡面的參數，序號是從1開始

所以假如要在J欄寫入資料，應該是寫入第10欄，CONFIG.COL_INDEX.MESSAGE是9，所以要+1

sheet.getRange(rowNumber, CONFIG.COL_INDEX.MESSAGE + 1).setValue('要寫入的內容');

這裡AI卻是

sheet.getRange(rowNumber, CONFIG.COL_INDEX.MESSAGE ).setValue('要寫入的內容');

這樣會寫到前一個欄位

 

模組4的程式碼也是問Gemini，給的程式碼整體架構是正確的

但是其中一個設定"拒絕輸入清單以外的值"的函數方法 ，AI是用"setAllowInvalidInput()"

程式碼跑到這裡就報錯，經過查詢官方文件，應該是 "setAllowInvalid()"

 

整體程式碼如下

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
 
// ====================================================================
//                             全域設定 (請務必調整)
// ====================================================================

const CONFIG = {
  SHEET_NAME: "表單回應 1", // 表單回應工作表名稱

  // 請根據試算表欄位順序 (從 1 開始) 決定索引 (陣列從 0 開始)
  COL_INDEX: {
    CLASSROOM_NAME: 1, // 假設在 B 欄

    // 由於表單將日期與時間拆分，請確認這四個欄位的索引
    START_DATE: 2,     // 假設在 C 欄 (開始日期)
    START_TIME: 3,     // 假設在 D 欄 (開始時間)
    END_DATE: 4,       // 假設在 E 欄 (結束日期)
    END_TIME: 5,       // 假設在 F 欄 (結束時間)

    USER_EMAIL: 6,     // 假設在 G 欄
    STATUS: 7,         // 假設在 H 欄 (手動更改為 Approved)
    EVENT_ID: 8,       // 假設在 I 欄 (腳本寫入)
    MESSAGE: 9         // 假設在 J 欄 (腳本寫入)
  }
};

// 教室名稱與對應 Google 日曆 ID 的映射 (請務必替換)
const CALENDAR_MAP = {
  'Room A - 101': '****************************************************************@group.calendar.google.com',
  'Room B - 203': '****************************************************************@group.calendar.google.com',
  // 如果有更多教室，請在此處添加
};

// ====================================================================
//                             步驟 1: 建立自訂選單
// ====================================================================

/**
 * 試算表開啟時自動執行，建立一個自訂選單按鈕、更新/建立 資料驗證 //模組4。
 */
function onOpen() {
  //更新/建立 資料驗證 //模組4
  createDropdownValidation();
  
  //建立選單
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🛠 教室借用審核')
    .addItem('✅ 更新行事曆 (處理已批准請求)', 'processApprovedBookings')
    .addToUi();
  Logger.log('自訂選單已建立。');
}

// ====================================================================
//                             步驟 2: 處理已批准請求的主邏輯
// ====================================================================

/**
 * 掃描試算表，處理所有狀態為 'Approved' 且尚未寫入日曆的請求。
 * 由管理員點擊自訂選單按鈕觸發。
 */
function processApprovedBookings() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) {
    Browser.msgBox('錯誤', `找不到工作表：${CONFIG.SHEET_NAME}。請檢查設定。`, Browser.Buttons.OK);
    return;
  }

  const dataRange = sheet.getDataRange();
  const allData = dataRange.getValues();

  let processedCount = 0;

  // 從第二列開始處理 (跳過標頭)
  for (let i = 1; i < allData.length; i++) {
    const row = allData[i];
    const rowNumber = i + 1; // 實際的列號

    // 陣列索引
    const status = row[CONFIG.COL_INDEX.STATUS];
    const eventId = row[CONFIG.COL_INDEX.EVENT_ID];

    // ****** 除錯輸出：查看腳本讀到的值 ******
    //Logger.log(`--- Row ${rowNumber} ---`);
    //Logger.log(row[7])
    //Logger.log(row[CONFIG.COL_INDEX.STATUS])

    // 檢查狀態是否為 'Approved' 且 'Calendar Event ID' 欄位為空
    if (status === 'Approved' && (!eventId || eventId === '')) {

      const classroomName = row[CONFIG.COL_INDEX.CLASSROOM_NAME];
      const userEmail = row[CONFIG.COL_INDEX.USER_EMAIL];

      // 取得日期和時間的原始值
      const startDate = row[CONFIG.COL_INDEX.START_DATE];
      const startTimeVal = row[CONFIG.COL_INDEX.START_TIME];
      const endDate = row[CONFIG.COL_INDEX.END_DATE];
      const endTimeVal = row[CONFIG.COL_INDEX.END_TIME];

      // 合併為完整的 Date 物件  // 模組1
      const combinedStartTime = combineDateAndTime(startDate, startTimeVal);
      const combinedEndTime = combineDateAndTime(endDate, endTimeVal);

      if (!combinedStartTime || !combinedEndTime) {
        // 合併失敗，標記錯誤
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.STATUS + 1).setValue('Error');
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.MESSAGE + 1).setValue('日期或時間格式錯誤，無法解析。');
        sendNotificationEmail(userEmail, title, null, 'error', '日期時間格式錯誤，請聯繫管理員。');
        continue;
      }

      // 執行檢查並寫入行事曆的邏輯  // 模組2
      const title = `已核准借用: ${classroomName}`;
      const result = createCalendarEvent(classroomName, combinedStartTime, combinedEndTime, title, userEmail);

      // 更新試算表
      if (result.success) {
        // 成功寫入
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.STATUS + 1).setValue('Completed');
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.EVENT_ID + 1).setValue(result.id);
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.MESSAGE + 1).setValue('Success');
        sendNotificationEmail(userEmail, title, result.id, 'success');
        processedCount++;
      } else {
        // 寫入失敗 (因邏輯錯誤或日曆衝突)
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.STATUS + 1).setValue('Error');
        sheet.getRange(rowNumber, CONFIG.COL_INDEX.MESSAGE + 1).setValue(`處理失敗: ${result.message}`);
        sendNotificationEmail(userEmail, title, null, 'error', result.message);  // 模組3
        processedCount++;
      }
    }
  }

  // 執行完成後給管理員的提示
  Browser.msgBox('日曆更新完成', `總共處理了 ${processedCount} 個已批准的請求。請查看試算表中的狀態欄位。`, Browser.Buttons.OK);
}

// ====================================================================
//                             模組1: 日期時間合併輔助函式
// ====================================================================

/**
 * 將 Google Sheet 中的日期物件和時間值合併成一個完整的 Date 物件。
 */
function combineDateAndTime(datePart, timePart) {
  try {
    const date = new Date(datePart);
    if (isNaN(date.getTime())) return null; // 無效日期

    let timeStr = '';

    if (typeof timePart === 'string') {
      timeStr = timePart;
    } else if (timePart instanceof Date) {
      // 如果是 Date 物件 (通常來自 Sheet 的時間欄位)，格式化為 HH:mm:ss
      const timeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
      timeStr = Utilities.formatDate(timePart, timeZone, 'HH:mm:ss');
    } else {
      return null;
    }

    const year = date.getFullYear();
    const month = date.getMonth();
    const day = date.getDate();

    const timeParts = timeStr.split(':');
    const hours = parseInt(timeParts[0]);
    const minutes = parseInt(timeParts[1]);
    const seconds = parseInt(timeParts[2] || 0);

    // 組合新的 Date 物件 (使用本地時間設定)
    return new Date(year, month, day, hours, minutes, seconds);

  } catch (e) {
    Logger.log('合併日期時間時發生錯誤: ' + e);
    return null;
  }
}

// ====================================================================
//                             模組2: 檢查衝突與寫入日曆
// ====================================================================

/**
 * 執行所有邏輯檢查 (時間順序、跨天、日曆衝突) 並寫入日曆。
 */
function createCalendarEvent(classroomName, startTime, endTime, title, userEmail) {
  const calendarId = CALENDAR_MAP[classroomName];
  if (!calendarId) return { success: false, message: '錯誤：無效的教室名稱或日曆ID未設定。' };

  // 1. 【檢查點：時間邏輯】開始時間不能晚於或等於結束時間
  if (startTime.getTime() >= endTime.getTime()) {
    return { success: false, message: '邏輯錯誤：開始時間晚於或等於結束時間。' };
  }

  // 2. 【檢查點：跨天邏輯】不能跨天 (確保年/月/日相同)
  const timeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  const startDateStr = Utilities.formatDate(startTime, timeZone, 'yyyy-MM-dd');
  const endDateStr = Utilities.formatDate(endTime, timeZone, 'yyyy-MM-dd');

  if (startDateStr !== endDateStr) {
    return { success: false, message: '邏輯錯誤：借用時間不能跨越日期。' };
  }

  // 3. 【檢查點：日曆衝突】
  const calendar = CalendarApp.getCalendarById(calendarId);
  try {
    const conflictingEvents = calendar.getEvents(startTime, endTime);
    if (conflictingEvents.length > 0) {
      const conflictTitle = conflictingEvents[0].getTitle();
      return { success: false, message: `日曆衝突：該時段已被預訂 (${conflictTitle})。` };
    }
  } catch (e) {
    return { success: false, message: `日曆讀取失敗：請檢查日曆ID和權限。錯誤: ${e}` };
  }

  // 4. 【通過所有檢查，執行寫入】
  try {
    const newEvent = calendar.createEvent(
      title,
      startTime,
      endTime,
      {
        description: `教室借用系統核准\n借用人信箱: ${userEmail}`,
        sendInvites: false,
        guests: userEmail
      }
    );
    return { success: true, id: newEvent.getId() };
  } catch (e) {
    return { success: false, message: `日曆寫入失敗：發生未預期的錯誤。錯誤: ${e}` };
  }
}


// ====================================================================
//                             模組3: 通知郵件函式
// ====================================================================

/**
 * 發送電子郵件通知借用者審核結果。
 */
function sendNotificationEmail(recipient, title, eventId, status, errorMessage) {
  let subject;
  let body;

  if (status === 'success') {
    subject = `✅ 借用申請已核准：${title}`;
    body = `親愛的借用者：\n\n您的教室借用申請已由管理員核准並登錄至行事曆。\n\n`
      + `活動標題：${title}\n`
      + `活動 ID：${eventId}\n\n`
      + `請查看您的 Google 日曆以確認活動細節。\n\n`
      + `此為系統自動發送，請勿直接回覆。`;
  } else if (status === 'error') {
    subject = `❌ 借用申請處理失敗：${title}`;
    body = `親愛的借用者：\n\n您的教室借用申請日期時間發生錯誤，無法完成預訂。\n\n`
      + `失敗原因：${errorMessage || '未知的系統錯誤。'}\n`
      + `請聯繫管理員處理。`;
  }

  if (subject && body) {
    try {
      MailApp.sendEmail(recipient, subject, body);
      Logger.log(`成功發送通知信給 ${recipient}`);
    } catch (e) {
      Logger.log(`發送通知信失敗給 ${recipient}: ${e}`);
    }
  }
}

// ====================================================================
//                             模組4: 更新/建立 資料驗證
// ====================================================================
function createDropdownValidation() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) {
    Browser.msgBox('錯誤', `找不到工作表：${CONFIG.SHEET_NAME}。請檢查設定。`, Browser.Buttons.OK);
    return;
  }

  const dataRange = sheet.getDataRange();
  const allData = dataRange.getValues();
  const lastRow = allData.length;
  Logger.log(lastRow);
  // 1. 定義下拉式選單的選項
  var items = ['Approved', 'Completed', 'Pending', 'Cancel'];

  // 2. 建立資料驗證規則
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(items) // 要求值必須在定義的清單中
    .setAllowInvalid(false) // 設定為拒絕輸入清單以外的值
    .setHelpText("請從清單中選擇一個有效的選項。") // 設定說明文字
    .build();

  // 3. 將規則應用到指定的範圍（例如 A2:A10）
  sheet.getRange(2, 8, lastRow - 1).setDataValidation(rule);

  Logger.log("資料驗證規則已設定完成。");
}

 

第3步，手動執行審核

如果資料無誤，可以借用，在相對應的H欄，選擇"Approved"

然後從選單中執行程序

 

這樣應該就會發出審核通過信件了

 

行事曆上也會有這個活動

Tags: