const SPACE_SHEETS = {
spaces: '공간목록',
reservations: '예약내역',
usage: '이용기록',
inquiries: '시설문의',
spaceSummary: '공간요약',
timeSummary: '시간대요약'
};
const SPACE_LIST = [
['SPACE-01', '스터디룸 A', '스터디룸', 6, '3층', '운영중'],
['SPACE-02', '스터디룸 B', '스터디룸', 8, '3층', '운영중'],
['SPACE-03', '소회의실', '회의실', 10, '2층', '운영중'],
['SPACE-04', '대회의실', '회의실', 24, '2층', '운영중'],
['SPACE-05', 'ICT 교육실', '교육장', 30, '3층', '운영중'],
['SPACE-06', '세미나실', '교육장', 40, '1층', '운영중'],
['SPACE-07', '미디어실', '특수공간', 12, '4층', '운영중'],
['SPACE-08', '프로젝트룸', '특수공간', 16, '4층', '운영중']
];
const SPACE_PURPOSES = ['팀 프로젝트', '학습 모임', '부서 회의', '교육 진행', '발표 연습', '상담'];
const SPACE_USER_TYPES = ['대학생', '교직원', '일반참여자'];
const SPACE_INQUIRY_TYPES = ['장비', '청결', '냉난방', '출입', '예약변경', '기타'];
const SPACE_WEEKDAYS = ['일', '월', '화', '수', '목', '금', '토'];
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('공간 예약 캡스톤')
.addItem('1. 예약 샘플 320건 만들기', 'createSpaceCapstoneData')
.addItem('2. 운영요약 다시 계산', 'rebuildSpaceSummaries')
.addItem('3. 데이터 연결 검사', 'checkSpaceDataLinks')
.addSeparator()
.addItem('4. 선택 문의 처리완료', 'completeSelectedSpaceInquiry')
.addToUi();
}
function createSpaceCapstoneData() {
const ui = SpreadsheetApp.getUi();
const answer = ui.alert(
'예약 샘플 만들기',
'공간 예약 캡스톤의 6개 시트 기존 내용을 지우고 합성 데이터로 다시 만듭니다.',
ui.ButtonSet.OK_CANCEL
);
if (answer !== ui.Button.OK) return;
const random = makeSpaceRandom(240713);
const reservations = [];
const usageRows = [];
for (let index = 0; index < 320; index += 1) {
const reservationId = 'RSV-' + String(index + 1).padStart(4, '0');
const space = SPACE_LIST[Math.floor(random() * SPACE_LIST.length)];
const dayOffset = index % 60;
const date = new Date(2026, 6, 1 + dayOffset);
const startHour = 9 + (index * 2) % 9;
const duration = index % 4 === 0 ? 2 : 1;
const people = Math.max(2, Math.min(space[3], 2 + Math.floor(random() * space[3])));
const status = index % 19 === 0 ? '취소' : index % 13 === 0 ? '대기' : '승인';
const isNoShow = status === '승인' && index % 7 === 0;
const actualStatus = status !== '승인' ? '해당없음' : isNoShow ? '노쇼' : '이용완료';
reservations.push([
reservationId,
formatSpaceDate(date),
space[0],
space[1],
String(startHour).padStart(2, '0') + ':00',
String(startHour + duration).padStart(2, '0') + ':00',
'USER-' + String(1 + index % 80).padStart(3, '0'),
SPACE_USER_TYPES[index % SPACE_USER_TYPES.length],
SPACE_PURPOSES[index % SPACE_PURPOSES.length],
people,
status
]);
usageRows.push([
reservationId,
actualStatus,
actualStatus === '이용완료' ? String(startHour).padStart(2, '0') + ':' + (index % 3) * 5 : '',
actualStatus === '이용완료' ? String(startHour + duration).padStart(2, '0') + ':00' : '',
actualStatus === '이용완료' ? Math.max(1, people - index % 3) : 0,
actualStatus === '노쇼' ? '예약시간 30분 후 미입실' : ''
]);
}
const inquiries = [];
for (let index = 0; index < 64; index += 1) {
const inquiryId = 'INQ-' + String(index + 1).padStart(3, '0');
const space = SPACE_LIST[index % SPACE_LIST.length];
const type = SPACE_INQUIRY_TYPES[index % SPACE_INQUIRY_TYPES.length];
const statusSeed = (index * 5 + 3) % 11;
const status = statusSeed < 2 ? '접수' : statusSeed < 4 ? '처리중' : '완료';
const priority = type === '출입' || type === '장비' ? '높음' : '보통';
inquiries.push([
inquiryId,
formatSpaceDate(new Date(2026, 6, 1 + index % 45)),
space[0],
space[1],
type,
makeSpaceInquiryText(type, space[1]),
priority,
status,
status === '완료' ? formatSpaceDate(new Date(2026, 6, 2 + index % 45)) : '',
status === '완료' ? '현장 확인 및 조치 완료' : ''
]);
}
writeSpaceSheet(SPACE_SHEETS.spaces, ['공간ID', '공간명', '공간유형', '정원', '위치', '운영상태'], SPACE_LIST);
writeSpaceSheet(
SPACE_SHEETS.reservations,
['예약ID', '예약일', '공간ID', '공간명', '시작시간', '종료시간', '신청자ID', '이용자유형', '이용목적', '예약인원', '예약상태'],
reservations
);
writeSpaceSheet(
SPACE_SHEETS.usage,
['예약ID', '이용상태', '입실시간', '퇴실시간', '실제인원', '이용메모'],
usageRows
);
writeSpaceSheet(
SPACE_SHEETS.inquiries,
['문의ID', '접수일', '공간ID', '공간명', '문의유형', '문의내용', '우선순위', '처리상태', '처리일', '처리메모'],
inquiries
);
rebuildSpaceSummaries(false);
ui.alert('공간 8개, 예약 320건, 이용기록 320건, 시설문의 64건을 만들었습니다.');
}
function rebuildSpaceSummaries(showAlert) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const reservationSheet = ss.getSheetByName(SPACE_SHEETS.reservations);
const usageSheet = ss.getSheetByName(SPACE_SHEETS.usage);
const inquirySheet = ss.getSheetByName(SPACE_SHEETS.inquiries);
if (!reservationSheet || !usageSheet || !inquirySheet) {
SpreadsheetApp.getUi().alert('먼저 예약 샘플 데이터를 만들어 주세요.');
return;
}
const reservations = reservationSheet.getRange(2, 1, reservationSheet.getLastRow() - 1, 11).getValues();
const usageRows = usageSheet.getRange(2, 1, usageSheet.getLastRow() - 1, 6).getValues();
const inquiries = inquirySheet.getRange(2, 1, inquirySheet.getLastRow() - 1, 10).getValues();
const usageByReservation = {};
usageRows.forEach(function(row) { usageByReservation[String(row[0])] = String(row[1]); });
const spaceStats = {};
SPACE_LIST.forEach(function(space) {
spaceStats[space[0]] = { approved: 0, used: 0, noShow: 0, people: 0, openInquiries: 0 };
});
const timeStats = {};
reservations.forEach(function(row) {
const reservationId = String(row[0]);
const spaceId = String(row[2]);
const status = String(row[10]);
if (!spaceStats[spaceId]) return;
if (status === '승인') spaceStats[spaceId].approved += 1;
if (usageByReservation[reservationId] === '이용완료') {
spaceStats[spaceId].used += 1;
spaceStats[spaceId].people += Number(row[9]) || 0;
}
if (usageByReservation[reservationId] === '노쇼') spaceStats[spaceId].noShow += 1;
const rawDate = row[1];
const date = rawDate instanceof Date ? rawDate : new Date(String(rawDate) + 'T00:00:00');
const weekday = SPACE_WEEKDAYS[date.getDay()];
const timeKey = weekday + '|' + String(row[4]);
if (!timeStats[timeKey]) timeStats[timeKey] = { weekday: weekday, hour: String(row[4]), count: 0, people: 0 };
if (status === '승인') {
timeStats[timeKey].count += 1;
timeStats[timeKey].people += Number(row[9]) || 0;
}
});
inquiries.forEach(function(row) {
const spaceId = String(row[2]);
const status = String(row[7]);
if (spaceStats[spaceId] && status !== '완료') spaceStats[spaceId].openInquiries += 1;
});
const spaceSummaryRows = SPACE_LIST.map(function(space) {
const stat = spaceStats[space[0]];
const usageRate = stat.approved ? stat.used / stat.approved : 0;
const noShowRate = stat.approved ? stat.noShow / stat.approved : 0;
const status = stat.openInquiries >= 3 || noShowRate >= 0.15 ? '확인필요' : '정상';
return [
space[0], space[1], space[2], space[3], space[4],
stat.approved, stat.used, stat.noShow, usageRate, noShowRate,
stat.people, stat.openInquiries, status
];
});
const timeSummaryRows = Object.values(timeStats)
.sort(function(a, b) { return a.weekday.localeCompare(b.weekday) || a.hour.localeCompare(b.hour); })
.map(function(item) { return [item.weekday, item.hour, item.count, item.people]; });
writeSpaceSheet(
SPACE_SHEETS.spaceSummary,
['공간ID', '공간명', '공간유형', '정원', '위치', '승인예약', '실제이용', '노쇼', '이용률', '노쇼율', '예약인원합계', '미처리문의', '운영상태'],
spaceSummaryRows
);
writeSpaceSheet(
SPACE_SHEETS.timeSummary,
['요일', '시간대', '승인예약건수', '예약인원합계'],
timeSummaryRows
);
formatSpaceSummarySheets();
if (showAlert !== false) SpreadsheetApp.getUi().alert('공간요약과 시간대요약을 최신 값으로 계산했습니다.');
}
function checkSpaceDataLinks() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const spaceSheet = ss.getSheetByName(SPACE_SHEETS.spaces);
const reservationSheet = ss.getSheetByName(SPACE_SHEETS.reservations);
const usageSheet = ss.getSheetByName(SPACE_SHEETS.usage);
const inquirySheet = ss.getSheetByName(SPACE_SHEETS.inquiries);
if (!spaceSheet || !reservationSheet || !usageSheet || !inquirySheet) {
SpreadsheetApp.getUi().alert('먼저 예약 샘플 데이터를 만들어 주세요.');
return;
}
const spaceIds = new Set(spaceSheet.getRange(2, 1, spaceSheet.getLastRow() - 1, 1).getValues().flat().map(String));
const reservations = reservationSheet.getRange(2, 1, reservationSheet.getLastRow() - 1, 11).getValues();
const reservationIds = new Set(reservations.map(function(row) { return String(row[0]); }));
const usageRows = usageSheet.getRange(2, 1, usageSheet.getLastRow() - 1, 6).getValues();
const inquiries = inquirySheet.getRange(2, 1, inquirySheet.getLastRow() - 1, 10).getValues();
let unknownReservationSpace = 0;
let unknownUsageReservation = 0;
let unknownInquirySpace = 0;
reservations.forEach(function(row) { if (!spaceIds.has(String(row[2]))) unknownReservationSpace += 1; });
usageRows.forEach(function(row) { if (!reservationIds.has(String(row[0]))) unknownUsageReservation += 1; });
inquiries.forEach(function(row) { if (!spaceIds.has(String(row[2]))) unknownInquirySpace += 1; });
SpreadsheetApp.getUi().alert(
'연결 검사 결과\n' +
'- 공간이 없는 예약: ' + unknownReservationSpace + '건\n' +
'- 예약이 없는 이용기록: ' + unknownUsageReservation + '건\n' +
'- 공간이 없는 문의: ' + unknownInquirySpace + '건'
);
}
function completeSelectedSpaceInquiry() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
if (sheet.getName() !== SPACE_SHEETS.inquiries) {
SpreadsheetApp.getUi().alert('시설문의 시트에서 처리할 행을 선택해 주세요.');
return;
}
const row = sheet.getActiveRange().getRow();
if (row < 2) {
SpreadsheetApp.getUi().alert('헤더가 아닌 문의 행을 선택해 주세요.');
return;
}
const inquiryId = String(sheet.getRange(row, 1).getValue());
const ui = SpreadsheetApp.getUi();
const answer = ui.alert(inquiryId + ' 처리완료', '선택한 합성 문의를 처리완료로 변경할까요?', ui.ButtonSet.OK_CANCEL);
if (answer !== ui.Button.OK) return;
sheet.getRange(row, 8).setValue('완료');
sheet.getRange(row, 9).setValue(formatSpaceDate(new Date()));
sheet.getRange(row, 10).setValue('대시보드 실습에서 처리완료');
rebuildSpaceSummaries(false);
ui.alert(inquiryId + '의 상태를 완료로 변경하고 요약을 갱신했습니다.');
}
function writeSpaceSheet(name, headers, rows) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(name) || ss.insertSheet(name);
sheet.clear();
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
if (rows.length) sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
sheet.getRange(1, 1, 1, headers.length)
.setBackground('#1d4ed8')
.setFontColor('white')
.setFontWeight('bold');
sheet.setFrozenRows(1);
sheet.getDataRange().setWrap(true);
sheet.autoResizeColumns(1, headers.length);
}
function formatSpaceSummarySheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const summary = ss.getSheetByName(SPACE_SHEETS.spaceSummary);
if (summary && summary.getLastRow() > 1) {
summary.getRange(2, 9, summary.getLastRow() - 1, 2).setNumberFormat('0.0%');
}
}
function makeSpaceInquiryText(type, spaceName) {
const texts = {
'장비': spaceName + '의 화면 연결 장비를 확인해 주세요.',
'청결': spaceName + ' 이용 전 정리가 필요합니다.',
'냉난방': spaceName + ' 실내 온도를 확인해 주세요.',
'출입': spaceName + ' 출입 방법을 안내해 주세요.',
'예약변경': spaceName + ' 예약 시간을 변경하고 싶습니다.',
'기타': spaceName + ' 이용 관련 확인 요청입니다.'
};
return texts[type];
}
function formatSpaceDate(date) {
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}
function makeSpaceRandom(seed) {
let value = seed % 2147483647;
return function() {
value = value * 16807 % 2147483647;
return (value - 1) / 2147483646;
};
}총 306줄 · 펼치기를 누르면 전체 코드를 볼 수 있습니다.