IT이야기/Google프로그램

Google 스프레드시트로 구글캘린더에 일정 연동하는 방법

어쩌다보니여기까지~

2022. 2. 9.

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

테스트해보기

실행해 보면 잘 반영되는 것을 볼 수 있습니다.

아래에 IT이야기/Google프로그램의 더 많은 정보에 링크를 걸어놨습니다.

Google Sheets 시작하기 바로가기 클릭!Google Workspace 무료 사용 방법 바로가기 클릭![Gmail] 원치않는 발송자의 메일 스팸처리하고 모두 지우기 바로가기 클릭!구글 크롬을 초기화 하는 방법 바로가기 클릭!