🟡 구글 캘린더에 있는 데이터를 스프레드 시트로 옮겨서 관리하고 싶어요 🙋🏽♀️
저희 회사는 근태관리 솔루션을 따로 쓰고 있지 않고, 구글 캘린더에 서로 휴가를 공유하고 있는 상태였습니다. 이때 HR팀에서 요청이 들어온 게, "구글 캘린더에 있는 데이터를 스프레드 시트로 옮길 수 있나요?"
저도 처음 해보는 일이라, 좀 찾아보고 답변을 준다고 하고 나서 찾아보니 가능했습니다. 구글에서 제공하는 Apps Script라는 기능을 통해서 구글 워크스페이스에 있는 앱끼리 연동이 가능했습니다. 하지만 개발 언어를 사용해야 하므로 비개발자들에게는 어려울 것 같아 이 글을 쓰게 됐습니다.
🟡 Apps Script란?
구글 워크스페이스의 애플리케이션(스프레드시트, 캘린더, 등)을 자동화하고 확장하는 데 사용하는 플랫폼입니다.예를 들어 구글 캘린더의 데이터를 스프레드 시트에 옮거나, 반대로 스프레드 시트의 데이터를 구글 캘린더에 주입도 가능합니다.
🟡 구글 캘린더 -> 구글 스프레드 시트
🟣 작업 순서
1. 캘린더 권한 설정
2. 데이터를 갖고 오려고 하는 캘린더의 "iCal 형식의 공개 주소"
3. Apps Script에 코드 넣기
4. 실행하기
🟣 1. 캘린더 권한 설정
- 좌측 하단에 다른 캘린더 옆에 "+" 버튼 클릭 -> 캘린더 구독" 버튼 클릭
- "내 캘린더의 설정" -> 원하는 캘린더 선택 -> "일정의 액세스 권한" -> 공개 사용 설정 (체크✅)
🟣 2. 데이터를 갖고 오려고 하는 캘린더의 "iCal 형식의 공개 주소"
- "캘린더 통합" -> iCal 형식의 공개 주소 복사
🟣 3. Apps Script에 코드 넣기
- 데이터를 넣고자 하는 스프레드 시트 열기 -> 확장 프로그램 -> Apps Script
- Code.gs에 아래 코드를 복사 붙여 넣기 합니다.
❗️icalUrl에는 2번에서 복사한 주소를 넣어줘야 합니다.❗️
function importICalToSheet() {
var icalUrl = ''; // 올바른 iCal 공개 주소로 변경하세요
var response;
try {
response = UrlFetchApp.fetch(icalUrl, {muteHttpExceptions: true});
} catch (e) {
Logger.log('Error fetching iCal data: ' + e.message);
return;
}
var icalData = response.getContentText();
// iCal 데이터를 파싱하는 함수 호출
var events = parseICalData(icalData);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// 헤더 작성
sheet.getRange('A1').setValue('Title');
sheet.getRange('B1').setValue('Name');
sheet.getRange('C1').setValue('Start Time');
sheet.getRange('D1').setValue('End Time');
sheet.getRange('E1').setValue('Description');
sheet.getRange('F1').setValue('Time Diff');
// 이벤트 데이터를 스프레드시트에 입력
var rowIndex = 2; // 데이터를 작성할 시작 행 번호
for (var i = 0; i < events.length; i++) {
var event = events[i];
// 캘린더 제목중에서 '휴가'라는 글씨가 들어간 데이터만 시트에 입력
if(event.summary && event.summary.includes('휴가')) {
var title = event.summary;
var name = extractTextInsideBrackets(title) ?? title;
var startTime = formatDateTime(new Date(event.start));
var endTime = formatDateTime(new Date(event.end));
var description = event.description || '';
var duration = calculateDuration(new Date(event.start), new Date(event.end));
// 데이터 작성
sheet.getRange(rowIndex, 1).setValue(title);
sheet.getRange(rowIndex, 2).setValue(name);
sheet.getRange(rowIndex, 3).setValue(startTime);
sheet.getRange(rowIndex, 4).setValue(endTime);
sheet.getRange(rowIndex, 5).setValue(description);
sheet.getRange(rowIndex, 6).setValue(calculateLeave(duration));
// 다음 행으로 이동
rowIndex++;
}
}
}
// iCal 데이터를 파싱하는 함수
function parseICalData(data) {
var events = [];
var currentEvent = null;
var lines = data.split(/\r?\n/);
for (var i = 0; i < lines.length; i++) {
var line = lines[i];
if (line.startsWith('BEGIN:VEVENT')) {
currentEvent = {};
} else if (line.startsWith('END:VEVENT')) {
events.push(currentEvent);
currentEvent = null;
} else if (currentEvent) {
var parts = line.split(':');
var key = parts[0].trim();
var value = parts.slice(1).join(':').trim();
if (key === 'SUMMARY') {
currentEvent.summary = value;
} else if (key === 'DTSTART' || key === 'DTSTART;VALUE=DATE') {
currentEvent.start = parseICalDate(value);
} else if(key === 'DTEND' || key === 'DTEND;VALUE=DATE'){
currentEvent.end = parseICalDate(value);
}else if (key === 'DESCRIPTION') {
currentEvent.description = value;
}
}
}
return events;
}
// iCal 날짜 문자열을 파싱하는 함수
function parseICalDate(value) {
if (value.length === 8) { // YYYYMMDD 형식
return value.replace(/(\d{4})(\d{2})(\d{2})/, '$1-$2-$3');
} else if (value.length === 16) { // YYYYMMDDTHHmmssZ 형식
return value.replace(/(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})Z/, '$1-$2-$3T$4:$5:$6Z');
}
return value; // 예상하지 못한 형식의 경우 원래 값 반환
}
// '[]' 안의 텍스트를 추출하는 함수
function extractTextInsideBrackets(summary) {
var match = summary.match(/\[([^\]]+)\]/);
return match ? match[1] : '';
}
// 날짜와 시간을 포맷팅하는 함수
function formatDateTime(date) {
var formattedDate = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');
return formattedDate;
}
// 두 시간의 차이를 계산하는 함수
function calculateDuration(startTime, endTime) {
var diff = (endTime - startTime) / (1000 * 60 * 60); // 밀리초를 시간으로 변환
return diff.toFixed(2); // 소수점 두 자리까지 반올림
}
// 휴가 시간을 계산하는 함수
function calculateLeave(duration) {
var hours = parseFloat(duration);
if (hours < 2) {
return 0;
} else if (hours < 4) {
return 0.25;
} else if (hours < 8) {
return 0.5;
} else if (hours <= 24) {
return 1;
} else if (hours > 24) {
return hours / 24;
}
}
🟣 4. 실행하기
- icalUrl 값을 넣어주고, 실행 버튼을 눌러 줍니다.
- 정상적으로 실행이 되면 스프레드 시트에 위 사진처럼 데이터가 들어갑니다.
🟡 실행 코드 설명 (참고)
- 캘린더에서 데이터 가져오기
var icalUrl = ''; // 올바른 iCal 공개 주소로 변경하세요
var response;
// 캘린더에서 데이터 가져 오기. 실패하면 샐행 종료하고, 오류 로그 보이도록 함.
try {
response = UrlFetchApp.fetch(icalUrl, {muteHttpExceptions: true});
} catch (e) {
Logger.log('Error fetching iCal data: ' + e.message);
return;
}
var icalData = response.getContentText();
// 캘린더 데이터를 파싱하는 함수 호출
var events = parseICalData(icalData);
....
// 캘린더 데이터를 파싱하는 함수
function parseICalData(data) {
var events = [];
var currentEvent = null;
var lines = data.split(/\r?\n/);
for (var i = 0; i < lines.length; i++) {
var line = lines[i];
if (line.startsWith('BEGIN:VEVENT')) {
currentEvent = {};
} else if (line.startsWith('END:VEVENT')) {
events.push(currentEvent);
currentEvent = null;
} else if (currentEvent) {
var parts = line.split(':');
var key = parts[0].trim();
var value = parts.slice(1).join(':').trim();
if (key === 'SUMMARY') {
currentEvent.summary = value;
} else if (key === 'DTSTART' || key === 'DTSTART;VALUE=DATE') {
currentEvent.start = parseICalDate(value);
} else if(key === 'DTEND' || key === 'DTEND;VALUE=DATE'){
currentEvent.end = parseICalDate(value);
}else if (key === 'DESCRIPTION') {
currentEvent.description = value;
}
}
}
return events;
}
- 스프레드 시트 제목행 만들기
// 캘린더 데이터를 넣을 시트의 정보를 가져온다.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// 시트의 제목행을 만든다
sheet.getRange('A1').setValue('Title');
sheet.getRange('B1').setValue('Name');
sheet.getRange('C1').setValue('휴가 시작 시간');
sheet.getRange('D1').setValue('휴가 종료 시간');
sheet.getRange('E1').setValue('Description');
sheet.getRange('F1').setValue('사용 연차 개수');
// 만약 '비고'라는 항목을 추가 하고 싶으면 아래 코드도 추가 작성하면 됩니다.
// 이때 G1에 '비고'라는 글씨가 들어가게 됩니다.
// sheet.getRange('G1').setValue('비고');
- 스프레스 시트 데이터 넣기
// 이벤트 데이터를 스프레드시트에 입력
var rowIndex = 2; // 데이터를 작성할 시작 행 번호
for (var i = 0; i < events.length; i++) {
var event = events[i];
if(event.summary && event.summary.includes('휴가')) {
var title = event.summary;
var name = extractTextInsideBrackets(title) ?? title;
var startTime = formatDateTime(new Date(event.start));
var endTime = formatDateTime(new Date(event.end));
var description = event.description || '';
var duration = calculateDuration(new Date(event.start), new Date(event.end));
// 데이터 작성
sheet.getRange(rowIndex, 1).setValue(title);
sheet.getRange(rowIndex, 2).setValue(name);
sheet.getRange(rowIndex, 3).setValue(startTime);
sheet.getRange(rowIndex, 4).setValue(endTime);
sheet.getRange(rowIndex, 5).setValue(description);
sheet.getRange(rowIndex, 6).setValue(calculateLeave(duration));
// 다음 행으로 이동
rowIndex++;
}
}
}
// 만약 캘린더의 모든 데이터를 갖고 오고 싶으면 아래 코드로 수정하면 됩니다.
var rowIndex = 2; // 데이터를 작성할 시작 행 번호
for (var i = 0; i < events.length; i++) {
var event = events[i];
var title = event.summary;
var name = extractTextInsideBrackets(title) ?? title;
var startTime = formatDateTime(new Date(event.start));
var endTime = formatDateTime(new Date(event.end));
var description = event.description || '';
var duration = calculateDuration(new Date(event.start), new Date(event.end));
// 데이터 작성
sheet.getRange(rowIndex, 1).setValue(title);
sheet.getRange(rowIndex, 2).setValue(name);
sheet.getRange(rowIndex, 3).setValue(startTime);
sheet.getRange(rowIndex, 4).setValue(endTime);
sheet.getRange(rowIndex, 5).setValue(description);
sheet.getRange(rowIndex, 6).setValue(calculateLeave(duration));
// 다음 행으로 이동
rowIndex++;
}
}
❗️이런 오류가 발생한다면?❗️
1. 오류 발생은 안 하는데, 시트에 데이터가 안 들어가요!
- 공개 사용 설정 체크를 하지 않으면 데이터를 못 갖고 옵니다.
📝 정리
1. 데이터를 갖고 오고 싶은 캘린더의 권한 설정
2. 캘린더의 "iCal 형식의 공개 주소" 복사하기
3. Apps Script에 코드 넣기
4. 실행하기
5. 확인하기
긴 글 읽어주셔서 감사합니다 :)
틀린 내용이 있거나, 덧붙일 내용이 있다면 언제든지 댓글 달아주세요!
제 글이 조금이나마 읽으신 분들에게 도움이 되도록 노력하겠습니다
다음 편에 봬요~
728x90
반응형