구글 스프레드 시트에 있는 내용을 구글 켈린더로 자동 연동 시켜보자!

필자는 혹시나 해서 검색해봤다

 

구글 스프레스 시트를 활용해 일정 정보를 구글 켈린더로 보내기를 검색하니 몇개의 게시물과 유튭이 있긴하다.

하지만 뭔가 답답함이 있다 

 

내가 좀더 이해를 하고 소개 해보고자 세팅을 시작 했다.

오 생각보다 쉽겠다는 생각이 들었지만.. 쉽게 결과가 나오지 않았고 하루 종일 해서 결과를 얻었다. ㅎㅎㅎㅎㅎ

 

이제 쉽게 켈린더 쉽게 연동해보자.!!  먼저 전체 흐름도 부터 알려줄게요 

 

1 STEP !!! 기능 알아보기!!

 

스프레드 시트에도  엑셀 파일처럼 VBS처럼 스크립트 할수 있다!!!

아래 보이는 확정 프로그램의 Apps Script이다. 여기서 구굴 API를 이용해서 엑셀에서 발생하는 내용을 구글 켈린더로 내용을 던질수 있다..

 

<>모양을 클릭하면 스크립트가 짤수 있는 공간이 나온다 아무것도 없으면 프로젝트 명을 "스프레스시트일정 자동등록삭제"입력하고 스크립트를 작성해야한다.  프로젝트명은 달라도 문제 없어요 

 

함수를 만들고 실행해서 내용을 보거나 디버그를 할때는 아래 처럼 원하는 줄에서 마우스를 클릭하면 디버그 포인트가 찍힌다 해당 포인트에서 원하는 데이터가 들어 오는지 볼수있다.

 

 

전체 개발이 완료 되면 이제 함수를 트리거에 등록만 하면된다.

함수명에 해당하는 트리거를 등록하되 스프리트에서 변경시로 세팅하면 자동으로 인지해서 함수를 수행한다.

 

 

 

켈린더를 하나 추가 해서 테스트 하기 바람니다.

저는 이사일정 공유로 만들었고 해당 켈린더를 만들고 구독되게 설정하고 켈린터ID를 가지고 스크립트에 넣어서 작성합니다. 수행 한번하면 아마도 개인정보 어쩌고 그냥 다 확인 누름니다 ㅎㅎ.. 자기 아이디로 할꺼니깐요 ㅎㅎ

 

 

 

2 STEP  !!! 필수 항목 알아보기 !!!!

 

이제 구글 켈린더에 내용을 보내기 위해서 스프레드시트를 만들어보자 일단 필수로 필요한 부분은 아래와 같다.

1.일정등록/삭제 checkbox : 채크 박스를 채크 하면 일정이 등록되고 해제 하면 삭제 하도록 할겁니다.

2. 켈런터 타이틀(Title)  : 켈린터로 보낼 타이블로 쓸만한 컬럼 제목을 만든다.

3. 약속시간(StartDate) : 일정등록시 시작날짜와 시간 분이 필요하다 

4. 종료시간(EndDate) : 일정등록시 종료 날짜를 위해서 날짜 시간 분이 필요하다

5. 설명 : 일정에 설명에 들어갈 내용을 작성하기 위한 컬럼 필요

 

저는 요즘 이사를 준비 중이다 그래서 여러부동산에서 일정을 받아서 정리하고 그것을 켈린더에 등록하기 위해 만들었습니다.

위 내용을 바탕으로 아래와 같이 시트를 구성했다.  

 

해당 시트명을 매물로 지정했다.

 

 

이제 모든 기본세팅이 끝났다.

이제 Apps Scripts에서 함수만 만들면 끝이다.!

 

함수에 대한 설명 잠시 해드릴게요 

일단 시트에서 첫줄의 행의 열이름을 찾는 함수를 이용해서 직관적으로 쉽게 열을 찾기 위해서 해당 함수를 활용했습니다.

해당 함수만 있으면 이제 첫줄에 있는 모든 단어를 찾을수 있습니다. 해당 함수는 Apps Scripts의 맨처음이나 맨 아래아 위치는 상과없으니 일단 복붙하세요.

 

function getColumnIndexByName(sheet, columnName) {
  // 시트의 첫 번째 행을 가져옴
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  // 헤더 행에서 열 이름 찾기
  const columnIndex = headers.indexOf(columnName);
  
  if (columnIndex === -1) {
    throw new Error(`Column "${columnName}" not found`);
  }
  
  // Apps Script는 1부터 시작하므로 +1을 해줌
  return columnIndex + 1;
}

 

 

메인 함수를 소개 합니다 아래 소스는 ChatGPT에게 질문을 겁나해서 빼온 소스이고 그리고  https://developers.google.com/apps-script/reference/calendar/calendar?hl=ko 해당 링크를 통해서 이해를 하면서 짜집기를 하면서 만들었습니다.

 

function logSheetData() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName("매물"); // 시트명이 없다면 Sheet일겁니다.

  if (!sheet) {
    Logger.log("매물 시트를 찾을 수 없습니다.");
    return;
  }

  // 열 인덱스 찾기
  const titleColumn = getColumnIndexByName(sheet, "켈린더타이틀");  // E열
  const dateColumn = getColumnIndexByName(sheet, "약속시간");     // F열
  const timeColumn = getColumnIndexByName(sheet, "종료시간");     // G열
  const descColumn = getColumnIndexByName(sheet, "설명");         // J열
  const checkboxColumn = 1;  // A열 (체크박스)
  const eventIdColumn = 12;  // L열 (이벤트 ID)
  // 이벤트ID는 켈린터를 생성하고 삭제 할때 사용하니 참고하세요 스프레드시트에서 지우면안되요 위치는 필요에따라 수정하세요

  const lastRow = sheet.getLastRow();
  
  // E, F, G, J열의 데이터 범위
  const dataRange = sheet.getRange(2, titleColumn, lastRow - 1, 5);  //타이틀 컬럼 데이터범위입니다 지금은 5개 밖에 없어서 5입니다 필요에따라 증가 시키세요
  const checkboxRange = sheet.getRange(2, checkboxColumn, lastRow - 1, 1);  // A열 체크박스 상태
  const eventIdRange = sheet.getRange(2, eventIdColumn, lastRow - 1, 1);    // L열 이벤트 ID
  
  //위에서 가져온데이더를 저장합니다
  const data = dataRange.getValues();                      
  const checkboxes = checkboxRange.getValues();
  const eventIds = eventIdRange.getValues();

  // 구글 캘린더 가져오기 구글 켈린더의 CALENDAR_ID를 여기다 넣어주세요 
  const CALENDAR_ID = "ce069개인정보 처리 ㅋㅋㅋㅋㅋ13@group.calendar.google.com";
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);

//위에서 저장한 data 부분을 row단위로 값을 넣습니다.(반복문)

  data.forEach((row, index) => {
    const title = row[0];         // E열 (켈린더타이틀)
    const sdate = row[1];         // F열 (약속 날짜)
    const eDate = row[2];         // G열 (종료 시간)
    const description = row[4];   // J열 (설명) J열이 4인 이유는 H,I열을 건너 뛰고 난 다음이 J열이기 때문에4이다 
    const isChecked = checkboxes[index][0];  // A열 체크박스 값
    const eventId = eventIds[index][0];      // L열에 저장된 이벤트 ID

    Logger.log(`startdate: ${sdate}, endDate: ${eDate}, Description: ${description}`);
 
    // 날짜와 시간 포맷 설정 (null 검사)
    const formattedsDate = sdate ? Utilities.formatDate(new Date(sdate), "Asia/Seoul", "yyyy-MM-dd HH:mm") : null;
    const formattedeDate = eDate ? Utilities.formatDate(new Date(eDate), "Asia/Seoul", "yyyy-MM-dd HH:mm") : null;

    if (!formattedsDate || !formattedeDate) return;

    const startDate = new Date(formattedsDate);
    const endDate = new Date(formattedeDate);
    
    // 함수가 수행되면 아래내용으로 checkbox의 상태에 따라 켈린러에 이벤트를 등록 하고 삭제 합니다. 
    // 만질건 이벤트 ID가 기록되는것만 위치를 지정하세요 
    if (isChecked) {
      if (!eventId) {
        const event = calendar.createEvent(title, startDate, endDate, { description: description });
        sheet.getRange(index + 2, eventIdColumn).setValue(event.getId());  
        // index+2는 첫줄뺴고 다음열 부터 이벤트컬럼위이를 의미 합니다. 
        Logger.log(`Row ${index + 2} - Created event: ${title}, startdate: ${formattedsDate}, endDate: ${formattedeDate}, Description: ${description}`);
      }
    } else {
      if (eventId) {
        try {
          const event = calendar.getEventById(eventId);
          if (event) {
            event.deleteEvent();
            sheet.getRange(index + 2, eventIdColumn).clearContent();
            Logger.log(`Row ${index + 2} - Deleted event: ${title}, startdate: ${formattedsDate}, endDate: ${formattedeDate}`);
          }
        } catch (e) {
          Logger.log(`Row ${index + 2} - Failed to delete event: ${title}. Error: ${e}`);
        }
      }
    }
  });
}

 

 

 

 

전체 코드를 합처서 저장을 하고 디버그 옆에 함수를 logSheetData를 선택 후 필수 정보를 스프레드 시트에 작성을 하고

실행을 눌러 보면 달력으로 쭉 들어 가는데... 빠르게 들어 가지 않으니 안나온다고 여러번 하면 중복으로 들어 갈수 있으니 

오류 없다면 대기해서 점검해보세요 아니면 디버그 하면서 데이터를 확인해 가면서 하는것도 좋습니다.

 

 

저장된 스프리드시트의 내용으로 checkbox를 채크를 하고 나서 실행을 누르면 이렇게  켈린더에 저장이 됨니다.

 

 

이제 테스트가 끝났다면..  트리거 등록을 하시면됨니다. 오른쪽 아래 트리거 추가를 클릭해서 트리거를 적용한다

이제 끝났어요 테스트 해보세요 ..  

모르는게 있다면 댓글 달아 주시면 제가 아는선에서 알려드리겠습니다

 

날짜와 시간이 분리된 컬럼을 활용하는건 조금만 응용하시면됨니다 ^^ 

간단한 소스만 공개함

function addEventsToCalendar() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("일정"); // 시트 이름 수정
  const calendarId = 'ceㅋㅋㅋㅋㅋㅋ개인정보5713@group.calendar.google.com'; // 캘린더 ID 입력
  const calendar = CalendarApp.getCalendarById(calendarId);
  
  const data = sheet.getDataRange().getValues();
  data.shift(); // 헤더 제거
  
  Logger.log(data);


  data.forEach(row => {
    const date = row[0]; // 날짜가 있는 컬럼 (오직 날짜 정보만)
    const time = row[1]; // 시간이 있는 컬럼 (오직 시간 정보만 사용)

    // 새로운 Date 객체에 시간 정보만 설정하기
    const fullDateTime = new Date(date); // date에서 날짜 정보만 사용
    fullDateTime.setHours(time.getHours()); // time에서 시간 정보만 설정
    fullDateTime.setMinutes(time.getMinutes()); // time에서 분 정보만 설정
    fullDateTime.setSeconds(0); // 초 정보 초기화 (필요한 경우)

	  Logger.log(fullDateTime);
    const endDateTime = new Date(fullDateTime);
	  endDateTime.setHours(fullDateTime.getHours() + 1); // 1시간 뒤로 설정


    const eventTitle = row[2]; // 이벤트 제목
    const eventDesc = row[3]; // 이벤트 설명 (선택 사항)
    
    // GMT+09:00 형식으로 시간 포맷하기
    const formattedDateTime = Utilities.formatDate(fullDateTime, "GMT+09:00", "yyyy-MM-dd HH:mm:ss");

    // 로그에 출력하기
    Logger.log("추가할 이벤트:");
    Logger.log("제목: " + eventTitle);
    Logger.log("일정 (GMT+09:00): " + formattedDateTime);
    Logger.log("설명: " + eventDesc);
    
    // 구글 캘린더에 이벤트 추가
	  calendar.createEvent(eventTitle, fullDateTime, endDateTime, {description: eventDesc});

  });
}

[추가팁]

 

날짜나 시간이 이상하게 표시될때.. 아래 설정을 확인 해보세요  파일>설정> 시간대가 seoul인지 확인 바람니다.