반응형
저는 구글 제품을 많이 사용하는 편입니다. 제 주력 캘린더도 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로 선택하면 됩니다.
스프레트시트에서 발생하는 이벤트를 반영하겠다는 것입니다.- 스프레트시트가
열릴 시이 함수를 실행하겠다는 뜻입니다. - 트리거가 실패하면
즉시 알림입니다. 이제 해당 스프레드시트를 열게 되면 자동으로 상단 메뉴에 "캘린더동기화" 버튼이 보이게 됩니다. 생일을 추가하거나 삭제하거나 할 때 해당 버튼을 누르기만 하면 반영이 됩니다.
테스트해보기
실행해 보면 잘 반영되는 것을 볼 수 있습니다.
728x90
0
