[Google Apps Script] 스프레드시트와 캘린더 연동

dyomi·2023년 9월 22일

구글 앱스 스크립트(Google Apps Script)란?

구글에서 개발한 스크립팅 플랫폼으로 구글 메일, 구글 드라이브, 구글 문서, 구글 스프레드 시트 등 구글 서비스가 통하는 구글 워크스페이스에서 반복적인 매크로 기능이나, 간단한 업부를 수행하는 기능을 개발할 수 있으며, 자바스크립트에 기반을 두고 있다.

캘린더 준비

연동할 캘린더 생성 후, 해당 캘린더 설정 및 공유 화면으로 이동
하단 캘린더 ID 기억해두기!

앱스 스크립트 생성

캘린더와 연동 할 스프레드시트 상단 메뉴에서 확장프로그램 > Apps Script
스크립트 파일 생성

전체 코드

function createCalendar() {
	
	//스프래드시트의 하단에 있는 Sheet tab의 이름
    const SheetTabName = "시트1";        
	  
	//필요한 데이터의 스프래드시트 Header명
	const HeaderTitle = "Name";
    const HeaderTime = "Upcoming Interview Time";  
    const HeaderStartTime = "Upcoming Interview Date";
    const HeaderDescription = "Status";
    const DescriptionProject = "Project";
    const DescriptionClient = "Client";
    const CalId = "id";                 // 캘린더이벤트 고유값
    const HeaderDel = "isdel";          // 삭제여부 확인
    const HeaderRegState = "calendar";  // 업데이트항목 확인

	//처음 데이터가 시작하는 표의 시작점(row)
    const startRow = 5;
	//처음 데이터가 시작하는 표의 시작점(column)
    const startColumn = 1;
	//등록할 캘린더의 ID값 (상단 설명 참고)
    const calendarId = "59496b1aaa********생략********";

	/***************************************************************************************/
	
    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)

	//해당 header의 스프레드시트 ColumnIndex
    const colHeaderStartTime = spreadsheet.createTextFinder(HeaderStartTime).findNext().getColumnIndex() - startColumn;
    const colHeaderTitle = HeaderTitle ? spreadsheet.createTextFinder(HeaderTitle).findNext().getColumnIndex() - startColumn : "";
    const colHeaderTime = HeaderTime ? spreadsheet.createTextFinder(HeaderTime).findNext().getColumnIndex() - startColumn : "";
    const colHeaderDescription = HeaderDescription ? spreadsheet.createTextFinder(HeaderDescription).findNext().getColumnIndex() - startColumn : "";
    const colDescriptionProject = DescriptionProject ? spreadsheet.createTextFinder(DescriptionProject).findNext().getColumnIndex() - startColumn : "";
    const colDescriptionClient = DescriptionClient ? spreadsheet.createTextFinder(DescriptionClient).findNext().getColumnIndex() - startColumn : "";
    const colCalId = CalId ? spreadsheet.createTextFinder(CalId).findNext().getColumnIndex() - startColumn : "";
    const colHeaderDel = HeaderDel ? spreadsheet.createTextFinder(HeaderDel).findNext().getColumnIndex() - startColumn : "";
    const colHeaderState = HeaderRegState ? spreadsheet.createTextFinder(HeaderRegState).findNext().getColumnIndex() - startColumn : "";
    
	const KR_TIME_DIFF = 9 * 60 * 60 * 1000;
    
	//시트 Row 수 만큼 반복
    for (x = 0; x < count.length; x++) {

		// 한꺼번에 많은 캘린더를 등록하면 오류가 발생함
        if (x === 15) Utilities.sleep(2 * 1000); 

        const shift = count[x]; // 시트 row (하단 설명 참고)
				
		//한국 시간은 UTC보다 9시간 빠름
        const startTime = new Date(shift[colHeaderTime]);
        const startTimeUtc = startTime.getTime() + startTime.getTimezoneOffset() * 60 * 1000;
        const startTimeKor = new Date(startTimeUtc + KR_TIME_DIFF);
        const time = startTimeKor.toTimeString().split(':')[0] + ":" + startTimeKor.toTimeString().split(':')[1];
				
		// var data = shift[가져오고 싶은 header ColumnIndex];

        const name = shift[colHeaderTitle] ? shift[colHeaderTitle] : "";
        const headerDescription = shift[colHeaderDescription] ? shift[colHeaderDescription] : "";
        const descriptionProject = shift[colDescriptionProject] ? shift[colDescriptionProject] : "";
        const descriptionClient = shift[colDescriptionClient] ? shift[colDescriptionClient] : "";
        // 캘린더 해당 이벤트 고유값 (캘린더 내용 수정 시 필요)
		const calId = shift[colCalId] ? shift[colCalId] : "";
        // 캘린더 이벤트 삭제 여부
		const isdel = shift[colHeaderDel] ? shift[colHeaderDel] : "";
		// 캘린더 이벤트 등록 상태
        const state = shift[colHeaderState] ? shift[colHeaderState] : "";

        const titleSum = name + "(" + time + ")" + " - " + headerDescription;
        const descriptionSum = "Client : " + " " + descriptionClient + "\n" + "Project : " + " " + descriptionProject;

        // EST시간을 KOR시간으로 치환 시작
        const startCurr = new Date(shift[colHeaderStartTime]);
        const startUtc = startCurr.getTime() + startCurr.getTimezoneOffset() * 60 * 1000;
        const startT = new Date(startUtc + KR_TIME_DIFF);

		// 캘린더에 등록할 이벤트 내용
        const event = {
            description: descriptionSum
        };
	
	  	// 캘린더 등록 상태 "Y"가 아니라면, 등록
      	if (state != "Y" && !isNaN(startCurr) && name !== "") {

          if(calId !== null && calId !== "") {
			// 기존 캘린터 이벤트 id값 삭제
            eventCal.getEventById(calId).deleteEvent();
          }

          let newEvent = eventCal.createAllDayEvent(titleSum, startT, event);

          spreadsheet.getRange(Number(startRow + x), colHeaderState + startColumn).setValue("Y"); // 등록 상태 "Y"로 업데이트
          spreadsheet.getRange(Number(startRow + x), colCalId + startColumn).setValue(newEvent.getId()); // 새로 등록한 이벤트 Id값 입력
      }
			
		// iddel 체크 후, 이벤트 삭제
      	if (isdel === "DEL" || isdel === "del" || isdel === "D" || isdel === "d") {
            const events = eventCal.getEventsForDay(startT, { search: titleSum });
            for (y = 0; y < events.length; y++) {
                events[y].deleteEvent();
            }

            spreadsheet.deleteRow(Number(startRow + x));
      }

    }
}

반복문에 const shift = count[x]; 는 스프레드시트의 한 줄의 데이터를 가르킴. console.log(shift)로 찍었을때와 실제 시트의 데이터 사진 참고!

해당 코드 적용한 시트

결과 화면

시트에서 함수 실행 시키기

앱스 스크립트 코드 추가

// 스프레드시트에 버튼 생성
function onOpenSchedule() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu("캘린더동기화").addItem("업데이트", "createCalendar").addToUi();
}