Google 스프레드시트로 구글캘린더에 일정 연동하는 방법
저는 구글 제품을 많이 사용하는 편입니다. 제 주력 캘린더도 Google 캘린더 고요. 이번에 모임의 임원을 맡게 되면서 회원들의 생일을 캘린더에 등록해야 할 일이 생겼어요. 그냥 하나하나 등록을 하는 도중 "내가 지금 뭐하고 있나.." 라는 자괴감이 들기 시작했어요.
구글 시트에 있는 날짜 정보(생일)을 한 번에 쉽게 일괄 등록할 수는 없을까라는 생각이 뇌리를 스쳤습니다. 그래서 찾아봤더니.. 약간의 매크로 프로그램을 작성하면 가능할 것 같더라고요.
그래서 열심히 개발을 해봤습니다.
1시간이면 등록할 것을 8시간 걸려서 프로그램을 짜 봤어요.
결과적으로는 더 비효율적이었네요. ㅠㅠ
그러나...
나에게는 비효율 적이었지만 이코드를 공개하면 다른 사람에게는 큰 도움이 될 수 있겠구나 생각을 하고 코드를 공개해 보려고 합니다.
준비물
준비물은 Google 스프레드시트, Google 캘린더만 있으면 돼요. 당연히 무료고요.
Google 캘린더
먼저 Google 캘린더를 만들거나 사용하고 있는 캘린더를 준비합니다.
- 적용하기 원하는 캘린더의 우측의
...를 클릭하고 설정 및 공유를 선택합니다.
- 캘린더 ID를 잘 기억해 놓습니다. 나중에 이 ID를 활용할 예정입니다.
Google 스프레드시트
Google 스프레드시트로 명단과 생일을 작성합니다.
▲ 위와 같이 작성을 하면 되고 중요한 사항은..
생년월일이 구글 시트의 날짜 형식에 맞아야 합니다. 그리고 갤린더등록, 캘린더상태의 항목은 필수로 있어야 합니다.
- 캘린더등록 : 캘린더에 등록할지 제거할지를 표시 (ADD / DEL)
- 캘린더상태 : 현재 캘린더에 해당 항목이 적용되었는지 확인 (Y / ' ')
매크로 프로그램 작성하기
기본적인 준비는 끝났습니다. 이제부터 Apps Script를 제작하고 트리거를 등록하면 됩니다.
Apps Script 작성하기
Apps Script는 구글 제품에 대해 javascript 문법으로 프로그램을 할 수 있게 만들어진 프로그램 언어입니다. 이 스크립트를 활용하여 매크로를 상세하게 제작할 수 있습니다.
- 캘린더의 상단 메뉴에서
확장 프로그램을 선택합니다. Apps Script를 선택합니다.
- 먼저 임의의 스크립트 이름을 작성합니다.
- 5개의 메뉴 중 2번째에 있는 편집기를 선택합니다.
- 기본으로 있는 함수을 사용해도 되지만 +를 눌러 새로운 함수를 만들어도 됩니다.
- 이름을 변경하여 특정 이름을 작성합니다.(기본 함수 이름을 사용해도 돼요) 이제 이 함수에 프로그램을 작성하면 되고 제가 작성한 코드를 복사 적용하시면 돼요.
전체 코드
/****************************************************************************************************
* 회원 생일을 캘린더에 자동등록하는 기능
****************************************************************************************************/
function goBirthCreate() {
/*****************************************************************************************************
************** 사용자가 등록해야 할 부분 *******************************************************
*****************************************************************************************************
* SheetTabName : 스프래드시트의 하단에 있는 Sheet tab의 이름
* Header ~~ : 스프래드시트의 Header명을 입력하면 해당 셀을 구글 캘린더에 등록함
* startRow : 처음 데이터가 시작하는 표의 시작점(row)
* startColumn : 처음 데이터가 시작하는 표의 시작점(column)
* calendarId : 등록하려는 캘린더에서 찾아서 작성함
* titlePrefaceWord : 캘린더에 등록할 때 제목부분에 공통으로 들어갈 머릿말
* descPrefaceWord : 캘린더에 등록할때 내용부분에 공통으로 들어가는 구분 단어임(캘린더이벤트 삭제할 때 필요하기 때문에 꼭 필요함)
* registYear : 캘린더에 등록할 연도
* alarm : 캘린더에 같이 등록할 알람(리마인더) 분 (분단위 숫자)
*****************************************************************************************************/
const SheetTabName = "회원생일";
const HeaderTitle = "성명";
const HeaderStartTime = "생년월일";
const HeaderDescription = "";
const HeaderEtc = "";
const HeaderRegYN = "캘린더등록";
const HeaderRegState = "캘린더상태";
const startRow = 3;
const startColumn = 2;
const calendarId = "su***************************lendar.google.com";
const titlePrefaceWord = "[테스트생일]";
const descPrefaceWord = "[구글시트_생일]";
const registYear = "2022";
const alarm1 = "10080"; // 첫번째 알람(분단위 숫자)
const alarm2 = "500"; // 두번째 알람(분단위 숫자)
/****************************************************************************************************/
/****************************************************************************************************/
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetTabName);
const eventCal = CalendarApp.getCalendarById(calendarId);
const endRow = spreadsheet.getLastRow();
const endColumn = spreadsheet.getLastColumn();
const count = spreadsheet.getRange(startRow, startColumn, endRow, endColumn).getValues(); // getRange(row, column, numRows, numColumns)
////////////////////////////////////////////////////////////////////////////////////////////////////
const colHeaderStartTime = spreadsheet.createTextFinder(HeaderStartTime).findNext().getColumnIndex() - startColumn;
const colHeaderTitle = HeaderTitle ? spreadsheet.createTextFinder(HeaderTitle).findNext().getColumnIndex() - startColumn : "";
const colHeaderDescription = HeaderDescription ? spreadsheet.createTextFinder(HeaderDescription).findNext().getColumnIndex() - startColumn : "";
const colHeaderEtc = HeaderEtc ? spreadsheet.createTextFinder(HeaderEtc).findNext().getColumnIndex() - startColumn : "";
const colHeaderRegYN = spreadsheet.createTextFinder(HeaderRegYN).findNext().getColumnIndex() - startColumn;
const colHeaderRegState = spreadsheet.createTextFinder(HeaderRegState).findNext().getColumnIndex() - startColumn;
/////////////////////////////////////////////////////////////////////////////////////////////////////
for (x = 0; x < count.length; x++) {
/**********************************************************************************************/
if (x === 15) Utilities.sleep(2 * 1000); // 한꺼번에 많은 캘린더를 등록하면 오류가 발생함
/**********************************************************************************************/
const shift = count[x];
const regYes = shift[colHeaderRegYN];
const title = shift[colHeaderTitle];
const description = shift[colHeaderDescription] ? shift[colHeaderDescription] : "";
const etc = shift[colHeaderEtc] ? "\n" + shift[colHeaderEtc] : "";
const titleSum = titlePrefaceWord + " " + title;
const descriptionSum = descPrefaceWord + " " + description + etc;
/***********************************************************************************************/
/***********************************************************************************************
* startTime은 캘린더에 등록할 년도는 금년 또는 내년이기 때문에 생일에서 년도는 빼고 지정한 년도로 교체해서 등록처리함
***********************************************************************************************/
// EST시간을 KOR시간으로 치환 시작
const KR_TIME_DIFF = 9 * 60 * 60 * 1000;
const startCurr = new Date(shift[colHeaderStartTime]);
const startUtc = startCurr.getTime() + startCurr.getTimezoneOffset() * 60 * 1000;
const startT = new Date(startUtc + KR_TIME_DIFF);
// EST시간을 KOR시간으로 치환 끝
const startTimeMonth = startT.getMonth();
const startTimeDay = startT.getDate();
const startCalendarTime = new Date(registYear, startTimeMonth, startTimeDay);
/***********************************************************************************************/
if (regYes === "DEL" || regYes === "del" || regYes === "D") {
const events = eventCal.getEventsForDay(startCalendarTime, { search: descPrefaceWord });
for (y = 0; y < events.length; y++) {
events[y].deleteEvent();
}
spreadsheet.getRange(Number(startRow + x), colHeaderRegYN + startColumn).setValue("");
spreadsheet.getRange(Number(startRow + x), colHeaderRegState + startColumn).setValue("");
} else if (regYes === "ADD" || regYes === "add" || regYes === "A") {
const event = {
description: descriptionSum,
guests: "",
};
if (titleSum !== null && titleSum !== "") {
const events = eventCal.getEventsForDay(startCalendarTime, { search: descPrefaceWord });
for (y = 0; y < events.length; y++) {
events[y].deleteEvent();
}
eventCal.createAllDayEvent(titleSum, startCalendarTime, event).addPopupReminder(alarm1).addPopupReminder(alarm2);
spreadsheet.getRange(Number(startRow + x), colHeaderRegYN + startColumn).setValue("");
spreadsheet.getRange(Number(startRow + x), colHeaderRegState + startColumn).setValue("Y");
}
}
}
}
function onOpenBirth() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("캘린더동기화").addItem("회원생일 업데이트", "goBirthCreate").addToUi();
}
부분 코드
코드는 크게 2개의 함수로 이루어져 있습니다.
function goBirthCreate() {}
function onOpenBirth() {}
goBirthCreate(): 생일을 적용하는 코드onOpenBirth(): 구글 시트에 적용 버튼이 나오게 하는 코드
코드 설정 영역
상단부분은 설정 부분입니다.
/*******************************************************************/
const SheetTabName = "회원생일"; // 스프래드시트의 하단에 있는 Tab의 이름
const HeaderTitle = "성명"; // [표의 Header 이름] 캘린더의 제목으로 등록될 항목
const HeaderStartTime = "생년월일"; // [표의 Header 이름] 캘린더의 등록될 날짜
const HeaderDescription = ""; // [표의 Header 이름] 캘린더의 내용으로 등록될 항목
const HeaderEtc = ""; // [표의 Header 이름] 캘린더의 내용을 등록될 항목
const HeaderRegYN = "캘린더등록"; // [표의 Header 이름] 캘린더에 등록할지 삭제할지 설정 (ADD/DEL)
const HeaderRegState = "캘린더상태"; // [표의 Header 이름] 현재 캘린더에 등록이 되어있는지 없는지 표시
const startRow = 3; // 실제 데이터가 시작하는 줄 번호
const startColumn = 2; // 실제 데이터가 시작되는 칸 번호
const calendarId = "sunrl******************************dar.google.com"; // 캘린더ID
const titlePrefaceWord = "[테스트생일]"; // 캘린더 제목에 등록할 말머리
const descPrefaceWord = "[구글시트_생일]"; // 캘린더 내용에 등록할 말머리
const registYear = "2022"; // 캘린더에 등록할 연도 설정
const alarm1 = "10080"; // 캘린더에 같이 등록할 첫번째 알람(분단위 숫자)
const alarm2 = "500"; // 캘린더에 같이 등록할 두번째 알람(분단위 숫자)
/******************************************************************/
트리거 등록하기
코드를 등록하고 설정했다면 이제 이 코드가 특정 상황에서 적용될 수 있게 트리거 코드를 작성하고 적용해야 합니다.
트리거 적용을 위한 코드
function onOpenBirth() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("캘린더동기화").addItem("회원생일 업데이트", "goBirthCreate").addItem("회원생일 업데이트 2", "code").addToUi();
}
위와 같이 코드를 작성하면 아래와 같이 상단 메뉴에 버튼이 생성됩니다. 그리고 이 버튼을 클릭하면 프로그램이 실행돼서 캘린더에 반영이 됩니다.
.addItem()을 붙여주면 계속 아래로 메뉴가 추가됩니다.
- 캘린더동기화 버튼이 보입니다.
- 회원생일 업데이트 버트이 보입니다.
트리거 반영하기
이제는 특정 이벤트가 발생할 때 onOpenBirth()가 실행되는 트리거를 등록하면 됩니다.
- 좌측 메뉴 중
트리거메뉴를 선택합니다. 트리거 추가버튼을 클릭하여 새로운 트리거를 생성합니다.- 실행할 함수로 우리가 만든
onOpenBirth를 선택합니다. - Head로 선택하면 됩니다.
스프레트시트에서 발생하는 이벤트를 반영하겠다는 것입니다.- 스프레트시트가
열릴 시이 함수를 실행하겠다는 뜻입니다. - 트리거가 실패하면
즉시 알림입니다. 이제 해당 스프레드시트를 열게 되면 자동으로 상단 메뉴에 "캘린더동기화" 버튼이 보이게 됩니다. 생일을 추가하거나 삭제하거나 할 때 해당 버튼을 누르기만 하면 반영이 됩니다.
테스트해보기
실행해 보면 잘 반영되는 것을 볼 수 있습니다.
Comments