![TrPak [시간Time, 공간Space, 사람Human)] 중등 사회과 교수학습 교육포털](https://www.trpak.kr/data/logo/logo_1763375261.png){kind=logo}
버스 예약 시스템(구글 스프레드시트+앱스스크립트+구글 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)
```
```
콘텐츠를 불러오는 중..
댓글목록
등록된 댓글이 없습니다.