구글 스프레드시트로 캘린더 이벤트 등록하기

연초가 되어서 내년 계획를 잡던중 회원들의 생년월일을 구글 캘린더에 등록해야 할 일이 생겼습니다. 약 50명의 회원인데.. 일일이 구글 캘린더에 등록을 하려니 "내가 지금 뭘 하고 있는건가??" 라는 자괴감이 들어서 어떻게 편하게 등록할 방법이 없나 찾던중 구글의 쉬트에 작성한 내용을 한번에 구글 캘린더에 등록할 수 있는 방법을 찾아 냈습니다. 저는 약 2일에 걸쳐 프로그램을 해서 현재 그나마 정상적인 매크로 코딩을 완료하게 되었습니다. 저와 같은 필요를 느낀신 분들은 금방 사용할 수 있게 코드와 설명을 붙혀보도록 하겠습니다.

구글 캘린더 문서 준비

먼저 구글 시트의 생일이 등록되어 질 캘린더를 생성합니다.

https://calendar.google.com

1. 본인의 캘린더 중 등록을 원하는 캘린더 중 좌측의 ... 부분을 클릭합니다.
2. 메뉴 중 설정 및 공유 메뉴를 선택합니다.

설정화면 중 캘린더 통합 > 캘린더 ID를 복사하여 준비합니다. 이제 이 캘린더에 생일 일정 이벤트가 등록될 예정입니다.

구글시트 문서 준비

이제 구글 시트 문서를 준비합니다. 문서는 성명, 생년월일, 내용, 캘린더 등록 항목이 나오면 됩니다.

https://drive.google.com

이제 구글 시트의 메뉴항목에서 Apps Script를 작성해야 합니다.

메뉴중 확장 프로그램을 선택합니다. Apps Script메뉴를 클릭합니다.

그러면 이렇게 광활한 코딩칸이 나오게 됩니다. ^^

Apps Script 작성하기

Apps Script는 거의 javascript와 동일한 방식으로 구글의 앱들을 API형태로 코딩을 하여 컨트롤을 할 수 있게 해줍니다. 옛날 앱이나 프로그램에 비한다면 엄청난 자유도가 주어지게 됩니다.

우상단에 ? 버튼을 누르면 관련 문서를 볼 수 있습니다.
https://developers.google.com/apps-script/

이곳에 아래의 코드를 입력합니다.

먼저 전체 코드입니다.

function goBirthCreate() {
    /***************************************************************
     * SheetTabName : 스프래드시트의 하단에 있는 Sheet tab의 이름
     * Header ~~ : 스프래드시트의 Header명을 입력하면 해당 셀을 구글 캘린더에 등록함
     * startRow : 처음 데이터가 시작하는 표의 시작점(row)
     * startColumn : 처음 데이터가 시작하는 표의 시작점(column)
     * calendarId : 등록하려는 캘린더에서 찾아서 작성함
     * kindWord : 캘린더에 등록할 때 제목부분에 공통으로 들어갈 머릿말
     * magicWord : 캘린더에 등록할때 내용부분에 공통으로 들어가는 구분 단어임(캘린더이벤트 삭제할 때 필요하기 때문에 꼭 필요함)
     * registYear : 캘린더에 등록할 연도
     * alarm :  캘린더에 같이 등록할 알람(리마인더) 분 (분단위 숫자)
     * startRow : 처음 데이터가 시작하는 시작점(row)
     ***************************************************************/
    const SheetTabName = "회원";
    // const HeaderKind = "주최";
    const HeaderTitle = "성명";
    const HeaderStartTime = "생년월일";
    // const HeaderEndTime = "종료일";
    const HeaderDescription = "휴대폰번호";
    const HeaderEtc = "비고";
    const HeaderIsYes = "캘린더등록";
    const startRow = 4;
    const startColumn = 1;
    const calendarId = "sunrlqu4quk3qi2rdhtjvhoh10@group.calendar.google.com";
    const kindWord = "[3남생일]";
    const magicWord = "[구글시트_생일]";
    const registYear = "2022";
    const alarm1 = "10080"; // 첫번째 알람
    const alarm2 = "500"; // 두번째 알람
    /***************************************************************/
    // var spreadsheet = SpreadsheetApp.getActiveSheet();
    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 colHeaderKind = spreadsheet.createTextFinder(HeaderKind).findNext().getColumnIndex() -1;
    const colHeaderTitle = spreadsheet.createTextFinder(HeaderTitle).findNext().getColumnIndex() - 1;
    const colHeaderStartTime = spreadsheet.createTextFinder(HeaderStartTime).findNext().getColumnIndex() - 1;
    // const colHeaderEndTime = spreadsheet.createTextFinder(HeaderEndTime).findNext().getColumnIndex() -1;
    const colHeaderEndTime = colHeaderStartTime;
    const colHeaderDescription = spreadsheet.createTextFinder(HeaderDescription).findNext().getColumnIndex() - 1;
    const colHeaderEtc = spreadsheet.createTextFinder(HeaderEtc).findNext().getColumnIndex() - 1;
    const colHeaderIsYes = spreadsheet.createTextFinder(HeaderIsYes).findNext().getColumnIndex() - 1;
    //////////////////////////////////////////////////////////////////

    for (x = 0; x < count.length; x++) {
        /********************************************* */
        if (x === 15) Utilities.sleep(2 * 1000); // 한꺼번에 많은 캘린더를 등록하면 오류가 발생함
        /********************************************* */
        const shift = count[x];
        const isYes = shift[colHeaderIsYes];
        // const kind = shift[colHeaderKind];
        const title = shift[colHeaderTitle];
        const description = shift[colHeaderDescription] ? shift[colHeaderDescription] : "";
        const etc = shift[colHeaderEtc] ? "\n" + shift[colHeaderEtc] : "";
        const titleSum = kindWord + " " + title;
        const descriptionSum = magicWord + " " + 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);
        //  var startTime = startT.setDate(startT.getDate() + 1);
        //  var startTimeNew = new Date(startTime);
        /***********************************************************************************************
         * endTime은 캘린더에 등록할 때 startTime과 동일함
         ***********************************************************************************************/
        // EST시간을 KOR시간으로 치환 시작
        const endCurr = new Date(shift[colHeaderEndTime]);
        const endUtc = endCurr.getTime() + endCurr.getTimezoneOffset() * 60 * 1000;
        const endT = new Date(endUtc + KR_TIME_DIFF);
        // EST시간을 KOR시간으로 치환  끝
        const endTimeMonth = endT.getMonth();
        const endTimeDay = endT.getDate();
        const endCalendarTime = new Date(registYear, endTimeMonth, endTimeDay);
        // var endTime = endT.setDate(endT.getDate() + 1);
        // var endTimeNew = new Date(endTime);
        /***********************************************************************************************/

        if (isYes === "Y") {
            const events = eventCal.getEventsForDay(startCalendarTime, { search: magicWord });
            for (y = 0; y < events.length; y++) {
                events[y].deleteEvent();
            }
            spreadsheet.getRange(Number(startRow + x), endColumn).setValue("N");
        } else if (isYes === "N") {
            const event = {
                description: descriptionSum,
                guests: "",
            };
            if (titleSum !== null && titleSum !== "") {
                //eventCal.createEvent(titleSum, startCalendarTime, endCalendarTime, event);
                //eventCal.createEvent(titleSum, startCalendarTime, endCalendarTime);
                eventCal.createAllDayEvent(titleSum, startCalendarTime, event).addPopupReminder(alarm1).addPopupReminder(alarm2);

                spreadsheet.getRange(Number(startRow + x), endColumn).setValue("Y");
            }
        }
    }
}

function onOpenBirth() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu("캘린더동기화").addItem("행사계획 업데이트", "goEventPlanCreate").addItem("회원생일 업데이트", "goBirthCreate").addToUi();
}

코드 설명

코드의 상단 부분을 본인에게 맞게 설정합니다.

const SheetTabName = "회원";
const HeaderTitle = "성명";
const HeaderStartTime = "생년월일";
const HeaderDescription = "휴대폰번호";
const HeaderEtc = "비고";
const HeaderIsYes = "캘린더등록";
const startRow = 4;
const startColumn = 1;

• SheetTabName : 구글 시트중 하단에 있는 탭메뉴의 이름을 작성합니다. 그러면 그 시트를 참조하게 됩니다.
• HeaderTitle : 표의 헤더 이름을 작성하면 해당 위치를 참조해서 이벤트 제목으로 활용하게 됩니다.
• HeaderStartTime : 표의 헤더 이름을 작성하면 해당 위치를 참조해서 이벤트 날짜로 활용하게 됩니다.
• HeaderDescription : 표의 헤더 이름을 작성하면 해당 위치를 참조해서 이벤트의 내용으로 활용하게 됩니다.
• HeaderEtc : 기타 내용을 등록합니다.
• HeaderIsYes : 이 Row가 캘린더에 등록이 되었는지 되어 있지 않은지 표시해 줍니다. 또한 [Y/N] 정보를 참조해서 캘린더의 이벤트를 제거하거나 추가를 해 줍니다.
• startRow : 표에서 실제 데이터가 나오는 row 번호를 입력합니다.
• startColumn : 표에서 실제 데이터가 나오는 column 번호를 입력합니다.

const calendarId = "sunreke03904rdkdkdkdkdkddkdkdkddar.google.com";
const kindWord = "[3남생일]";
const magicWord = "[구글시트_생일]";
const registYear = "2022";
const alarm1 = "10080"; // 첫번째 알람
const alarm2 = "500"; // 두번째 알람

• calendarId : 아까 확인했던 캘린더의 고유 ID를 여기에 작성합니다.
• kindWord : 키워드는 캘린더 이벤트의 Title 앞에 항상 붙혀주어서 표시를 해줍니다.
• magicWord : 매직워드는 갤린더 이벤트의 Description 앞쪽에 항상 붙혀 줍니다. 이 매직워드를 통해 나중에 일괄삭제 시에도 사용되기 때문에 꼭 구분된 키워드를 작성해 주는 것이 좋습니다.
• registYear : 캘린더에 등록하고자 하는 년도를 작성합니다. (생일의 년도를 사용하게 된다면 아주 먼 날짜에 이벤트가 등록되기 때문에...)
• alarm1 : 첫번째 알람시간을 입력합니다. 분단위 입니다.
• alarm2 : 두번째 알람시간입니다. 분단위 입니다.

이코드에 작성된 기능은 goBirthCreate() 과 onOpenBirth() 2개 입니다.

첫번째 goBirthCreate()는 캘린더 이벤트를 등록하는 코드이고, onOpenBirth()는 구글시트 메뉴에 새로운 메뉴를 나오게 하는 코드입니다.

function onOpenBirth() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu("캘린더동기화").addItem("회원생일 업데이트", "goBirthCreate").addToUi();
}

onOpenBirth()를 실행시키면 위와 같이 캘린더동기화 > 회원생일 업데이트 메뉴가 나타나게 됩니다.

트리거 등록하기

이제 마지막으로 이 코드를 실행하는 방법을 설정해야 하는데, 그것은 같은 Apps Script 기능중 트리거기능을 활용하면 됩니다.

1. 좌측메뉴 중 트리거를 선택합니다.
2. 트리거 추가 메뉴를 클릭합니다.

트리거는 몇가지 옵션이 있는데.. 위의 그림처럼 설정하면 해당 구글시트가 열릴 경우 onOpenBirth() 함수가 실행되서 상단 메뉴영역에 버튼이 나오게 됩니다. 그리고 그 메뉴를 누르면 캘린더에 등록하는 함수를 실행할 수 있습니다.