[공모주] 구글 스프레드시트의 AppsScript로 날짜 데이터를 구글 캘린더에 연동하기

2024. 6. 17. 08:00ㆍ01. Projects/반복작업 자동화

728x90
반응형

안녕하세요. 해콩입니다.

 

최근에 공모주 관련된 워크플로우를 조금이나마 자동화하면서 공부한 내용을 공유하고자 합니다.

 

구글 스프레드시트와 구글 캘린더 연동하기

작업의 배경과 결과 는 제가 운영하는 네이버 블로그에 잘 적어뒀습니다.

 

간단히 설명하면

공모주 청약을 할 때에 제일 중요한 일정을 어떻게 관리할지 고민하던 중

구글 스프레드시트와 구글 캘린더를 연동해서 쓰면 제일 간편한 방법이 아닐까 생각하다가

둘을 연동해서 작업하기로 했다는 내용입니다.

 

그리고 이 작업을 위해서는 구글에서 만든 Apps Script를 이용해야 했습니다.

자바스크립트는 조금 다뤄봤는데, 이건 처음 보는 언어기도 하고, 배우기엔 배보다 배꼽이 큰 느낌이라

바로 ChatGPT를 이용해서 작업을 시작했습니다.

 

Google Apps Script

사실 작업할 때에는 그냥 바로 원하는 코드를 작성해 달라고 했지만, 그래도 이름이 비슷한 자바스크립트랑 뭐가 다른지는 알고 가면 좋을 것 같아서 ChatGPT 한테 물어봤습니다.

Apps Script 와 JavaScript 비교

 

Apps Script도 기본 언어는 자바스크립트지만 구글과 관련된 여러 기능들이 추가된 언어라고 보면 됩니다.

 

필요한 기능 

제가 필요했던 기능은 아래와 같습니다.

  • 청약일, 환불일, 상장일 정보를 조건부로 캘린더에 등록
  • 청약일, 환불일, 상장일 정보를 조건부로 캘린더에서 삭제
  • 등록된 일정 정보들 모두 삭제

아래부터는 작성된 각각의 기능 함수를 설명하겠습니다. 

 

청약일, 환불일, 상장일 정보를 조건부로 캘린더에 등록

먼저, 바로 가져다 쓰셔도 되는 함수 코드 먼저 공유드립니다.

function addEventsToSpecificCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendarId = 'your-calendar-id@group.calendar.google.com'; // 자신의 캘린더 ID로 변경
  var calendar = CalendarApp.getCalendarById(calendarId);
  var lastRow = sheet.getLastRow();
  var today = new Date();
  today.setHours(0, 0, 0, 0); // 오늘 날짜의 시간을 00:00:00으로 설정

  for (var i = 2; i <= lastRow; i++) { // 첫 번째 행은 헤더로 가정
    var itemName = sheet.getRange(i, 1).getValue(); // 종목명
    var broker = sheet.getRange(i, 2).getValue(); // 증권사
    var applicationDate = sheet.getRange(i, 3).getValue(); // 청약일
    var issuePrice = sheet.getRange(i, 4).getValue(); // 공모가
    var minimumQuantity = sheet.getRange(i, 5).getValue(); // 최소 수량
    var refundDate = sheet.getRange(i, 8).getValue(); // 환불일
    var allocationQuantity = sheet.getRange(i, 10).getValue(); // 배정 수량
    var listingDate = sheet.getRange(i, 11).getValue(); // 상장일
    var applicationEventId = sheet.getRange(i, 14).getValue(); // 청약일 이벤트 ID
    var refundEventId = sheet.getRange(i, 15).getValue(); // 환불일 이벤트 ID
    var listingEventId = sheet.getRange(i, 16).getValue(); // 상장일 이벤트 ID

    // 청약일 이벤트 추가
    if (applicationDate && !applicationEventId) {
      var appDate = new Date(applicationDate);
      if (appDate >= today) { // 청약일이 오늘 또는 미래일 경우
        var startTime = new Date(applicationDate);
        startTime.setHours(10, 0); // 오전 10시로 설정
        var endTime = new Date(applicationDate);
        endTime.setHours(16, 0); // 오후 4시로 설정

        var event = calendar.createEvent(
          '청약일: ' + itemName,
          startTime,
          endTime,
          {description: '증권사: ' + broker + '\n공모가: ' + issuePrice + '\n최소 수량: ' + minimumQuantity}
        );
        sheet.getRange(i, 14).setValue(event.getId()); // 이벤트 ID 저장
      }
    }

    // 환불일 이벤트 추가
    if (refundDate && !refundEventId) {
      var refundDateObj = new Date(refundDate);
      if (refundDateObj >= today) { // 환불일이 오늘 또는 미래일 경우
        var startTime = new Date(refundDate);
        startTime.setHours(9, 30); // 오전 9시 30분으로 설정
        var endTime = new Date(refundDate);
        endTime.setHours(15, 0); // 오후 3시로 설정

        var event = calendar.createEvent(
          '환불일: ' + itemName,
          startTime,
          endTime,
          {description: '증권사: ' + broker + '\n공모가: ' + issuePrice + '\n최소 수량: ' + minimumQuantity}
        );
        sheet.getRange(i, 15).setValue(event.getId()); // 이벤트 ID 저장
      }
    }

    // 상장일 이벤트 추가
    if (listingDate && !listingEventId && allocationQuantity && allocationQuantity > 0) {
      var listingDateObj = new Date(listingDate);
      if (listingDateObj >= today) { // 상장일이 오늘 또는 미래일 경우
        var startTime = new Date(listingDate);
        startTime.setHours(9, 0); // 오전 9시로 설정
        var endTime = new Date(listingDate);
        endTime.setHours(15, 0); // 오후 3시로 설정

        var event = calendar.createEvent(
          '상장일: ' + itemName,
          startTime,
          endTime,
          {description: '증권사: ' + broker + '\n공모가: ' + issuePrice + '\n최소 수량: ' + minimumQuantity + '\n배정 수량: ' + allocationQuantity}
        );
        sheet.getRange(i, 16).setValue(event.getId()); // 이벤트 ID 저장
      }
    }
  }
}

 

여기서 캘린더 연동과 관련된 부분만 중복을 제외하고 뽑아보면 아래와 코드와 같습니다.

function addEventsToSpecificCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendarId = 'your-calendar-id@group.calendar.google.com'; // 자신의 캘린더 ID로 변경
  var calendar = CalendarApp.getCalendarById(calendarId);
  var lastRow = sheet.getLastRow();
  var today = new Date();
  today.setHours(0, 0, 0, 0); // 오늘 날짜의 시간을 00:00:00으로 설정
	...
    // 청약일 이벤트 추가
    if (applicationDate && !applicationEventId) {
      var appDate = new Date(applicationDate);
      if (appDate >= today) { // 청약일이 오늘 또는 미래일 경우
        var startTime = new Date(applicationDate);
        startTime.setHours(10, 0); // 오전 10시로 설정
        var endTime = new Date(applicationDate);
        endTime.setHours(16, 0); // 오후 4시로 설정

        var event = calendar.createEvent(
          '청약일: ' + itemName,
          startTime,
          endTime,
          {description: '증권사: ' + broker + '\n공모가: ' + issuePrice + '\n최소 수량: ' + minimumQuantity}
        );
        sheet.getRange(i, 14).setValue(event.getId()); // 이벤트 ID 저장
      }
    }
	...
}

 

calendarID를 이용하여 calendar 객체를 만들고, 아래에서 해당 객체의 createEvent 메서드로 일정을 캘린더에 생성합니다.

그리고 해당 생성 이벤트에 대한 ID를 저장하여 일정 생성 이벤트를 관리할 수 있게 합니다.

 

해당 메서드는 아래와 같은 인자들을 이용하여 사용할 수 있습니다.

// To ChatGPT-4o: calendar 객체의 createEvent 메소드에 대해서 간단하게 알려줘
var calendar = CalendarApp.getCalendarById('your-calendar-id@group.calendar.google.com');
calendar.createEvent(
	'이벤트 제목', 
    new Date(시작 시간), 
    new Date(종료 시간), 
    {description: '이벤트 설명', location: '이벤트 위치'}
);

 

조건부라고 쓰긴 했지만, 사실 조건은 그냥 원하는 대로 적기만 하면 되는터라 별도의 설명은 하지 않겠습니다.

 

캘린더 ID를 확인하는 방법

그럼 연동에서 가장 핵심이 되는 부분은 캘린더 ID를 가져오는 것입니다.

 

이 방법을 구글 캘린더 웹 페이지 기준으로 확인하는 방법을 설명드리겠습니다.

 

먼저, 구글 캘린더로 가서 우측 상단의 톱니바퀴를 눌러 설정 창으로 들어갑니다.

캘린더 설정창

 

 

설정으로 들어가면 자신이 관리하고 있는 캘린더 리스트를 왼쪽에서 확인할 수 있습니다.

해당 캘린더를 누르고 "캘린더 설정" 메뉴를 누릅니다.

그리고 쭉 스크롤을 내려보면 아래 사진처럼 "캘린더 통합"이라는 영역을 확인할 수 있습니다.

캘린더 ID 확인하는 위치

 

여기서 @group.calendar.google.com 앞의 부분이 해당 캘린더의 ID에 해당하는 부분입니다.

해당 정보를 위의 함수에 있는 "calendarID" 변수에 있는 "your-calendar-id" 부분에 넣어주시면 됩니다.

 

위의 방법은 아래에 이어질 삭제 부분에도 동일하게 적용할 수 있습니다. 

 

청약일, 환불일, 상장일 정보를 조건부로 캘린더에서 삭제

위와 마찬가지로 등록한 일정 정보를 캘린더에서 삭제하는 함수를 공유드리겠습니다.

function deleteEventsFromSpecificCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendarId = 'your-calendar-id@group.calendar.google.com'; // 자신의 캘린더 ID로 변경
  var calendar = CalendarApp.getCalendarById(calendarId);
  var lastRow = sheet.getLastRow();
  var today = new Date();
  today.setHours(0, 0, 0, 0); // 오늘 날짜의 시간을 00:00:00으로 설정

  for (var i = 2; i <= lastRow; i++) { // 첫 번째 행은 헤더로 가정
    var applicationEventId = sheet.getRange(i, 14).getValue(); // 청약일 이벤트 ID
    var refundEventId = sheet.getRange(i, 15).getValue(); // 환불일 이벤트 ID
    var listingEventId = sheet.getRange(i, 16).getValue(); // 상장일 이벤트 ID
    var applicationDate = sheet.getRange(i, 3).getValue(); // 청약일
    var refundDate = sheet.getRange(i, 8).getValue(); // 환불일
    var listingDate = sheet.getRange(i, 11).getValue(); // 상장일
    var allocationQuantity = sheet.getRange(i, 10).getValue(); // 배정 수량

    // 청약일 이벤트 삭제
    if (applicationEventId && applicationDate) {
      var appDate = new Date(applicationDate);
      if ((today - appDate) / (1000 * 60 * 60 * 24) >= 2) { // 청약일이 2일 이상 지난 경우
        var event = calendar.getEventById(applicationEventId);
        if (event) {
          event.deleteEvent();
          sheet.getRange(i, 14).setValue(''); // 이벤트 ID 초기화
        }
      }
    }

    // 환불일 이벤트 삭제
    if (refundEventId && refundDate) {
      var refundDateObj = new Date(refundDate);
      if ((today - refundDateObj) / (1000 * 60 * 60 * 24) >= 2) { // 환불일이 2일 이상 지난 경우
        var event = calendar.getEventById(refundEventId);
        if (event) {
          event.deleteEvent();
          sheet.getRange(i, 15).setValue(''); // 이벤트 ID 초기화
        }
      }
    }

    // 상장일 이벤트 삭제
    if (listingEventId && listingDate) {
      var listingDateObj = new Date(listingDate);
      if (((today - listingDateObj) / (1000 * 60 * 60 * 24) >= 2) || !allocationQuantity || allocationQuantity == 0) { // 상장일이 2일 이상 지난 경우 또는 배정 수량이 없는 경우
        var event = calendar.getEventById(listingEventId);
        if (event) {
          event.deleteEvent();
          sheet.getRange(i, 16).setValue(''); // 이벤트 ID 초기화
        }
      }
    }
  }
}

 

역시 위와 마찬가지로 일정 정보를 삭제하는데 필요한 코드만 추려서 보면 아래와 같습니다.

function deleteEventsFromSpecificCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendarId = 'your-calendar-id@group.calendar.google.com'; // 자신의 캘린더 ID로 변경
  var calendar = CalendarApp.getCalendarById(calendarId);
  var lastRow = sheet.getLastRow();
  var today = new Date();
  today.setHours(0, 0, 0, 0); // 오늘 날짜의 시간을 00:00:00으로 설정

	...
        var event = calendar.getEventById(applicationEventId);
        if (event) {
          event.deleteEvent();
          sheet.getRange(i, 14).setValue(''); // 이벤트 ID 초기화
        }
      }
    }
	...
}

 

캘린더에 일정을 등록할 때와 마찬가지로 calendar 객체를 생성합니다.

다음으로 이전에 일정을 만들 때 저장해 뒀던 eventID를 이용해서 해당 일정 정보를 event 객체에 가져옵니다.

마지막으로 해당 event 객체의 deleteEvent 메서드를 이용하여 해당 event를 제거하면 됩니다.

 

역시 마찬가지로 자신이 원하는 조건을 이용해 해당 함수의 실행 여부를 결정하면 됩니다.

 

등록된 일정 정보를 모두 삭제

이 함수는 사실 위에서 소개한 삭제함수에서 몇 가지 조건만을 제거한 함수입니다.

따라서, 바로 가져다 쓰셔도 괜찮을 함수입니다.

function forceDeleteEventsFromSpecificCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendarId = 'your-calendar-id@group.calendar.google.com'; // 자신의 캘린더 ID로 변경
  var calendar = CalendarApp.getCalendarById(calendarId);
  var lastRow = sheet.getLastRow();
  var today = new Date();
  today.setHours(0, 0, 0, 0); // 오늘 날짜의 시간을 00:00:00으로 설정

  for (var i = 2; i <= lastRow; i++) { // 첫 번째 행은 헤더로 가정
    var applicationEventId = sheet.getRange(i, 14).getValue(); // 청약일 이벤트 ID
    var refundEventId = sheet.getRange(i, 15).getValue(); // 환불일 이벤트 ID
    var listingEventId = sheet.getRange(i, 16).getValue(); // 상장일 이벤트 ID
    var applicationDate = sheet.getRange(i, 3).getValue(); // 청약일
    var refundDate = sheet.getRange(i, 8).getValue(); // 환불일
    var listingDate = sheet.getRange(i, 11).getValue(); // 상장일
    var allocationQuantity = sheet.getRange(i, 10).getValue(); // 배정 수량

    // 청약일 이벤트 삭제
    if (applicationEventId && applicationDate) {
      var appDate = new Date(applicationDate);
      var event = calendar.getEventById(applicationEventId);
      if (event) {
        event.deleteEvent();
        sheet.getRange(i, 14).setValue(''); // 이벤트 ID 초기화
      }
    }

    // 환불일 이벤트 삭제
    if (refundEventId && refundDate) {
      var refundDateObj = new Date(refundDate);
      var event = calendar.getEventById(refundEventId);
      if (event) {
        event.deleteEvent();
        sheet.getRange(i, 15).setValue(''); // 이벤트 ID 초기화
      }
    }

    // 상장일 이벤트 삭제
    if (listingEventId && listingDate) {
      var listingDateObj = new Date(listingDate);
      var event = calendar.getEventById(listingEventId);
      if (event) {
        event.deleteEvent();
        sheet.getRange(i, 16).setValue(''); // 이벤트 ID 초기화
      }
    }
  }
}

 

 

마치며

이번에 공모주 관련 워크플로우를 자동화하면서 chatGPT를 이용해

구글 스프레드시트와 구글 캘린더를 AppsScript를 이용해서 연동하는 방법을 공부했습니다.

 

해당 내용은 정말 여러 가지 방식으로 활용가능할 것 같습니다.

앞으로 스프레드시트를 바라보는 관점이 많이 달라질 것 같습니다.

 

긴 포스팅 봐주셔서 감사합니다. 

반응형
저작자표시 비영리 변경금지

'01. Projects > 반복작업 자동화' 카테고리의 다른 글

[메시지 자동화] 텔레그램 봇에 메시지 보내는 방법  (0) 2024.06.25
[아이폰 단축어] 출퇴근에 알뜰교통카드 열어주는 자동화 만들기  (2) 2023.07.14

관련글

댓글 0

티스토리툴바