Google Apps Script를 통해 SpreadSheets와 Calendar 데이터 동기화 하기

2020년 6월 기준

스프레드시트 - 설문지 폼 - 캘린더가 자동으로 동기화되며 데이터를 업데이트 하던 기능이 유료화되었다.

이에 구글에서 제공하는 Google Apps Script를 통해 비슷한 기능을 구현해보게 됐다.

 

 

 

먼저 위와같은 형태의 테스트 데이터를 만들었다.

 

스크립트 작성에 앞서 동기화될 캘린더의 ID를 알아야한다.

캘린더 추가 - 추가된 캘린더 우측 점점점 클릭 - 설정 및 공유 클릭

 

 

 

 

 

스크롤 아래로 쭉~내리다보면 ID가 보인다.

 

 

 

복사해서 스프레드시트 아무칸에나 입력.

나같은 경우 K1에 입력했다.

 

 

그리고 스프레드시트에서 [도구] - [스크립트 편집기]를 클릭해 화면이동을 한다.

그럼 총 세개의 화면을 보게된다 스프레드시트 - 캘린더 - 스크립트 편집기

 

 

 

스크립트 편집기의 초기화면이다.

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

 

function goCreate() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  
  var calendarId = spreadsheet.getRange('K1').getValue();

  var eventCal = CalendarApp.getCalendarById(calendarId);

  var lr = spreadsheet.getLastRow();

  var count = spreadsheet.getRange('A2:I'+lr+'').getValues();
  
  for (x=0; x<count.length; x++) {
    
    var shift = count[x];
    var isYes = shift[8];
    
    if(isYes === 'N'){
      var summary = shift[2] + '-' + shift[1];
      var startTime = shift[4];
      var endTime = shift[5];
      var guests = shift[1];
      var description = shift[6];
      //var location = shift[5];
  
      var event = {
        'description': description,
        'guests': ''
      }
      
      if(summary !== null && summary !== ''){
        //eventCal.createEvent(summary, new Date(startTime), new Date(endTime), event);
        //eventCal.createEvent(summary, startTime, endTime);
        eventCal.createAllDayEvent(summary, new Date(startTime));
      }
    }
  }
  spreadsheet.getRange('I2:I'+lr+'').setValue('Y');
}

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  
  ui.createMenu("Sync to Cal")
  .addItem('Create Events Now', 'goCreate')
  .addToUi();
}

 

저장을 하고 상단에 보이는 ▶버튼을 이용해 Run한다.

 

 

성공하면 캘린더에 등록이 되고 스프레드시트의 [등록]열에 N이라고 되어있던 값이 Y로 바뀐다.

 

 

 

 

 

위 코드에서 아래보이는 onOpen()이라는 함수를 호출하게 되면 스프레드시트 상단에 메뉴가 새로 생기게 된다.

그 메뉴를 이용해 쉽게 goCreate()함수를 호출할 수 있게 만들어 준다.

 

 

이걸로 호출 ㄱㄱ

 

 

 

물론 이 마저도 수동이기때문에 불편하다.

 

그래서 트리거를 등록해 1시간마다 동작하게 만들었다

 

https://script.gooe.com/u/0/home

Apps Script – Google Apps Script

위 링크로 접속해서 좌측메뉴의 내 프로젝트 클릭 - {아까만든거} 클릭 - 우측 프로젝트 세부정보 - 트리거 클릭

 

 

 

새 트리거 추가 - 원하는 환경으로 세팅후 저장

 

 

 

 

 

이제 내 휴가가 캘린더에 등록되지 않아 짤리는 일은 없게 되었다.