버스 예약 시스템(구글 스프레드시트+앱스스크립트+구글 AI 스튜디오 활용) [https://docs.google.com/spreadsheets/d/1Wwcfo3IkgWuBawupXvJgw9sSac5e3BxCtiTFQWEew8U/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1Wwcfo3IkgWuBawupXvJgw9sSac5e3BxCtiTFQWEew8U/edit?usp=sharing) * 제작 배경 : 구글에 설문으로 버스 신청을 예약하면 자동으로 탑승자 목록이 체크리스트 형식으로 생성되고, 탑승자 버스 탑승시 체크리스트를 체크하면 탑승자 이름과 탑승자수, 미탑승자 이름과 미탑승자수가 자동으로 생성된다. * 제작 포인트 : 구글 스프레드 시트에 3개의 탭을 만든다.(탑승현황, 신청현황, 탑승자목록) 이후, 앱스스크립트로 아래 해당 코드를 입력후 저장하고, 앱스스크립트의 트리거를 3개 생성후 저장-실행하여 테스트한다. * 배포 방법 : 구글 설문 주소를 탑승자에게 배포한다. 위의 해당 시트로 탑승자를 파악한다. ``` ### 1. 통합 앱스크립트 전체 코드 **주의:** 코드 중간에 있는 `'여기에_구글_설문지_편집_주소를_입력하세요'` 부분을 실제 본인의 설문지 주소로 반드시 수정해야 합니다. ```javascript /** * 전체 통합 관리 함수 */ // 1. 설문 응답 시 & 데이터 변경 시 실행되는 명단 동기화 함수 function syncPassengerList() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sourceSheet = ss.getSheetByName("설문응답"); const targetSheet = ss.getSheetByName("탑승자명단"); if (!sourceSheet || !targetSheet) return; // [기능 1] 설문응답 탭 중복 하이라이트 (이름 또는 연락처 중복 시 노란색) highlightDuplicates(sourceSheet); // 데이터 가져오기 const sourceValues = sourceSheet.getDataRange().getValues(); if (sourceValues.length < 2) { if (targetSheet.getLastRow() > 1) { targetSheet.deleteRows(2, targetSheet.getLastRow() - 1); } updateDashboard(targetSheet); return; } const responses = sourceValues.slice(1); // [기능 2] 유니크 명단 필터링 (이름 OR 연락처 중복 1개만 인정) const uniqueResponses = []; const seenNames = new Set(); const seenInfos = new Set(); const validKeys = new Set(); responses.forEach(row => { const name = String(row[1]).trim(); const info = String(row[2]).trim(); const timestamp = row[0]; const combinedKey = name + "|" + info; if (name && info && !seenNames.has(name) && !seenInfos.has(info)) { seenNames.add(name); seenInfos.add(info); validKeys.add(combinedKey); uniqueResponses.push({key: combinedKey, name: name, info: info, time: timestamp}); } }); // [기능 3] 삭제 연동 (설문응답에서 지워지면 명단에서도 삭제) let targetData = targetSheet.getDataRange().getValues(); for (let i = targetData.length - 1; i >= 1; i--) { const tName = String(targetData[i][1]).trim(); const tInfo = String(targetData[i][2]).trim(); const tKey = tName + "|" + tInfo; if (!validKeys.has(tKey)) { targetSheet.deleteRow(i + 1); } } // [기능 4] 추가 연동 (새로운 응답자만 추가) targetData = targetSheet.getDataRange().getValues(); const currentTargetKeys = new Set(targetData.slice(1).map(r => String(r[1]).trim() + "|" + String(r[2]).trim())); uniqueResponses.forEach(res => { if (!currentTargetKeys.has(res.key)) { const nextRow = targetSheet.getLastRow() + 1; targetSheet.getRange(nextRow, 1).insertCheckboxes(); targetSheet.getRange(nextRow, 1).setValue(false); targetSheet.getRange(nextRow, 2).setValue(res.name); targetSheet.getRange(nextRow, 3).setValue(res.info); targetSheet.getRange(nextRow, 4).setValue(res.time); } }); updateDashboard(targetSheet); } // 2. 세 번째 탭(이름목록)의 내용을 설문지 드롭다운으로 전송하는 함수 function updateFormItemList() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const nameListSheet = ss.getSheetByName("이름목록"); if (!nameListSheet) return; const lastRow = nameListSheet.getLastRow(); if (lastRow < 2) return; const names = nameListSheet.getRange(2, 1, lastRow - 1, 1).getValues() .map(row => row[0]) .filter(name => name !== ""); // 중요: 아래 URL을 본인의 설문지 편집 URL로 바꾸세요 const formUrl = '여기에_구글_설문지_편집_주소를_입력하세요'; try { const form = FormApp.openByUrl(formUrl); const items = form.getItems(); let nameItem; for (let i = 0; i < items.length; i++) { if (items[i].getTitle() === "이름") { // 설문지 질문 제목이 "이름"이어야 함 if (items[i].getType() === FormApp.ItemType.LIST) nameItem = items[i].asListItem(); else if (items[i].getType() === FormApp.ItemType.MULTIPLE_CHOICE) nameItem = items[i].asMultipleChoiceItem(); break; } } if (nameItem) { nameItem.setChoiceValues(names); console.log("설문지 목록 업데이트 완료"); } } catch(e) { console.log("URL이 잘못되었거나 권한이 없습니다."); } } /** * 보조 함수들 */ function updateDashboard(sheet) { sheet.getRange("E2").setFormula("=COUNTIF(A2:A, TRUE)"); sheet.getRange("F2").setFormula("=COUNTA(B2:B)"); sheet.getRange("G2").setFormula('=IFERROR(TEXTJOIN(", ", TRUE, FILTER(B2:B, A2:A = FALSE, B2:B <> "")), "")'); } function highlightDuplicates(sheet) { const range = sheet.getDataRange(); const values = range.getValues(); if (values.length <= 1) return; sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).setBackground(null); const nameCounts = {}; const infoCounts = {}; for (let i = 1; i < values.length; i++) { const name = values[i][1].toString().trim(); const info = values[i][2].toString().trim(); if (name) nameCounts[name] = (nameCounts[name] || 0) + 1; if (info) infoCounts[info] = (infoCounts[info] || 0) + 1; } for (let i = 1; i < values.length; i++) { const name = values[i][1].toString().trim(); const info = values[i][2].toString().trim(); if (nameCounts[name] > 1 || infoCounts[info] > 1) { sheet.getRange(i + 1, 1, 1, sheet.getLastColumn()).setBackground("#FFF2CC"); } } } ``` ### 반드시 설정해야 할 트리거 (3개) 코드를 저장한 후, 왼쪽 **시계 아이콘(트리거)** 메뉴에서 아래 3개의 트리거를 각각 추가해야 모든 기능이 유기적으로 작동합니다. 1. **설문 응답 시 명단 추가** * 함수: `syncPassengerList` / 이벤트 소스: 스프레드시트 / 이벤트 유형: **양식 제출 시** 2. **응답 삭제 시 명단 동기화** * 함수: `syncPassengerList` / 이벤트 소스: 스프레드시트 / 이벤트 유형: **변경 시** (On change) 3. **이름목록 수정 시 설문지 반영** * 함수: `updateFormItemList` / 이벤트 소스: 스프레드시트 / 이벤트 유형: **수정 시** (On edit) ``` ```