구글 스프레드시트로 캘린더 이벤트 등록하기
연초가 되어서 내년 계획를 잡던중 회원들의 생년월일을 구글 캘린더에 등록해야 할 일이 생겼습니다. 약 50명의 회원인데.. 일일이 구글 캘린더에 등록을 하려니 "내가 지금 뭘 하고 있는건가??" 라는 자괴감이 들어서 어떻게 편하게 등록할 방법이 없나 찾던중 구글의 쉬트에 작성한 내용을 한번에 구글 캘린더에 등록할 수 있는 방법을 찾아 냈습니다. 저는 약 2일에 걸쳐 프로그램을 해서 현재 그나마 정상적인 매크로 코딩을 완료하게 되었습니다. 저와 같은 필요를 느낀신 분들은 금방 사용할 수 있게 코드와 설명을 붙혀보도록 하겠습니다.
구글 캘린더 문서 준비
먼저 구글 시트의 생일이 등록되어 질 캘린더를 생성합니다.
- 본인의 캘린더 중 등록을 원하는 캘린더 중 좌측의
...부분을 클릭합니다. - 메뉴 중 설정 및 공유 메뉴를 선택합니다.
설정화면 중 캘린더 통합 > 캘린더 ID를 복사하여 준비합니다. 이제 이 캘린더에 생일 일정 이벤트가 등록될 예정입니다.
구글시트 문서 준비
이제 구글 시트 문서를 준비합니다. 문서는 성명, 생년월일, 내용, 캘린더 등록 항목이 나오면 됩니다.
이제 구글 시트의 메뉴항목에서 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 기능중 트리거기능을 활용하면 됩니다.
- 좌측메뉴 중 트리거를 선택합니다.
- 트리거 추가 메뉴를 클릭합니다.
트리거는 몇가지 옵션이 있는데.. 위의 그림처럼 설정하면 해당 구글시트가 열릴 경우 onOpenBirth() 함수가 실행되서 상단 메뉴영역에 버튼이 나오게 됩니다.
그리고 그 메뉴를 누르면 캘린더에 등록하는 함수를 실행할 수 있습니다.
Comments
여기서 그대로 내용을 도둑질했네요
혹시 동일인이실까요?