[GAS] 구글 캘린더 시간 관리 예제 분석

목적

목적

구글 캘린더에 기록한 이벤트를 구글 앱스 스크립트로 가져와서 이벤트별로 사용한 시간을 분석하는 방법을 이해합니다.

샘플 준비 하기

구글 앱스 스크립트 샘플 사이트에서는 훌륭한 샘플들을 제공하고 있습니다.

Automations 카테고리 하위에 Time Management > Record time and activities in Calendar and Sheets 에서 캘린더 시간 분석 샘플을 자신의 계정으로 복사할 수 있습니다.

샘플 분석

샘플은 구글 시트와 시트에 연결된 앱스 스크립트로 구성됩니다.

구글 시트는 3개의 탭으로 구성되어 있습니다.

캘린더의 정보를 연동해서 가져오는 Hours 탭

Hours 탭의 데이터를 가공해서 보여주는 Dashboard 탭

Hours 탭에서 데이터 확인으로 입력하는 목록을 관리하는 Categories 탭

앱스 스크립트는 2개의 파일로 구성되어 있습니다.

메인 코드가 작성된 Code.gs

사이드바의 HTML과 서브 코드가 작성된 Page.html

그럼 이제 앱스 스크립트를 중심으로 샘플을 분석하겠습니다.

많은 경우 코드의 주석을 참고해주세요.

샘플을 복사한 뒤에 스프레드시트 설정에서 시간대를 바꿔주는 것을 잊지마세요. 바꿔주지 않으면 원하는 시간대로 기록되지 않습니다.

메뉴 및 사이드바

다음 코드는 메뉴 및 사이드바를 실행을 구성하는 코드입니다.

// 구글 시트가 열릴 때 메뉴를 생성합니다. 
const onOpen = () => {
  SpreadsheetApp.getUi()
    .createMenu('myTime') // 메뉴는 myTime 이라는 이름으로 생성합니다. 
    .addItem('Sync calendar events', 'run') // run 함수를 아이템으로 지정합니다. 
    .addItem('Settings', 'settings') // settings 함수를 아이템으로 지정합니다. 
    .addToUi();
};

//메뉴에서 Settings 를 누르면 실행되는 settings 함수 입니다. 
const settings = () => {
  const html = HtmlService.createHtmlOutputFromFile('Page')
    .setTitle('Settings');

  SpreadsheetApp.getUi().showSidebar(html);
	//Page.html 을 불러와서 제목을 Settings라고 쓰고, 사이드바로 표시합니다.
};
JavaScript
복사

메뉴에서 Settings 를 실행하면 아래와 같이 사이드바가 표시됩니다.

사이드바의 실행 및 기본 세팅 로드

사이드바의 소스인 Page.html 에서는 HTML 로 보여지는 UI를 정의하고, 자동으로 또는 사용자의 액션에 따라 스크립트를 실행합니다.

사이드바가 실행되면, HTML에 따라서 UI가 생성됩니다.

다음은 HTML 부분입니다.

<!DOCTYPE html>
<html>
<!--Head 부분에서는 Style 을 정의합니다.-->
<head>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <style>
        #main {
            display: none
        }

        #categories-as-item-title {
            display: none
        }

        #show_title_warning {
            display: none
        }

        #show_description_warning {
            display: none
        }

        .red {
            color: red;
        }

        .branding-below {
            bottom: 56px;
            top: 0;
        }

        input[type=number] {
            width: 50px;
            height: 15px;
        }
    </style>
</head>

<!--Body 에서는 사이드바에 표시되는 내용을 정의합니다. -->
<body>
    <div class="sidebar branding-below" id="wait">
        Please wait...
    </div>
    <div class="sidebar branding-below" id="main">
        <div class="block" id="checks">
            <b>Synchronise calendars</b>
            <div>
                <span class="error" id="calendar-message"></span>
            </div>
        </div>

        <div class="block">
            <b>Synchronisation period</b>
            <br>Synchronise from the last <input type="number" name="sync-from" id="sync-from"> days
            <br>Synchronise up to the coming <input type="number" name="sync-to" id="sync-to"> days
        </div>

        <div class="block">
            <b>Update the calendar items</b><br>
            <input type="checkbox" id="is-update-calendar-item-title">
            <label for="is-update-calendar-item-title">Overwrite the calendar item title</label>
            <span class="secondary" id="show_title_warning">The calendar title will be overwritten with the values in
                title
                column of the sheet</span>
        </div>
        <div id="categories-as-item-title">
            <input type="checkbox" id="is-use-categories-as-item-title">
            <label for="is-use-categories-as-item-title">Use categories as the calendar item title</label>
        </div>
        <div class="block">
            <input type="checkbox" id="is-update-calendar-item-description">
            <label for="is-update-calendar-item-description">Overwrite the calendar item description</label>
            <span class="secondary" id="show_description_warning">The calendar description will be overwritten with the
                values in description column of the sheet</span>
        </div>
        <div class="block">
				<!--Save 버튼을 누르면 saveSettings 함수를 실행시킵니다. -->
            <button class="blue" onClick="saveSettings()">Save</button>
        </div>
        <div class="block"> 
				<!-- 스크립트 실행과 관련해서 오류와 성공 메시지를 출력하는 부분입니다. -->
            <span class="error" id="generic-error"></span>
            <span class="gray" id="generic-message"></span>
        </div>

    </div>
    <div class="sidebar bottom">
        <span class="gray">
            myTime v1.2.0</span>
    </div>
</body>
<script>
<!--Script 부분은 아래에 별도로 빼서 설명합니다. -->
</script>
</html>
HTML
복사

UI가 그려진 다음에는 가장 먼저 getSettings() 를 실행하여 초기 값을 세팅합니다.

만약 처음 실행하는 경우라면, getSettings()에서 정보가 없는 것으로 처리되게 되어 있고, 기존에 저장한 정보가 있다면, 해당 정보를 불러오도록 되어 있습니다.

getSettings()의 성공 여부에 따라 성공 또는 실패 핸들러를 동작시킵니다.

이해를 돕기 위해서 Page.html 과 Code.gs 에서 코드를 발췌하여 하나로 표시하였습니다.

/* 이하 Page.html */

/*Code.gs의 getSettings를 실행합니다.
성공하는 경우에는 getSettings의 결과를 onSuccessGetSettings 에 파라미터로 넘겨줍니다.
실패하는 경우에는 onFailure 를 실행합니다. 
*/ 
google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccessGetSettings)
  .getSettings();

/* 이하 Code.gs */
// Page.html 에서 호출하여 실행하는 getSettings() 입니다. 
const getSettings = () => {
  const settings = {};
  
  // 저장된 캘린더 세팅(savedCalendarSettings)
	// Properties Key가 calendar 를 찾아서 Value를 리턴하는데, 값이 없다면 [] 로 설정합니다.
  const savedCalendarSettings = JSON.parse(PropertiesService.getScriptProperties().getProperty('calendar') || '[]');
  
  // Primary Calendar 를 찾아서 정의하면서 캘린더의 ID도 설정합니다.  
  const primaryCalendar = CalendarApp.getAllCalendars()
    .filter((cal) => cal.isMyPrimaryCalendar())
    .map((cal) => ({
      name: 'Primary calendar',
      id: cal.getId()
    }));
    
  // Primary Calender 가 아닌 캘린더를 정의하면서 캘린더의 ID도 설정합니다. 
  const secundaryCalendars = CalendarApp.getAllCalendars()
    .filter((cal) => cal.isOwnedByMe() && !cal.isMyPrimaryCalendar())
    .map((cal) => ({
      name: cal.getName(),
      id: cal.getId()
    }));

  // 현재 사용가능한 캘린더(availableCalendars)로 Primary Calendar 와 Secondary Calendar를 하나로 합쳐서 정의합니다.
  const availableCalendars = primaryCalendar.concat(secundaryCalendars);
  
  // 기존에 저장된 캘린더 정보와 현재 사용가능한 캘린더 정보의 ID를 비교해서 존재하지 않으면
	// unavailebleCalendars 에 푸시합니다. 
  const unavailebleCalendars = [];
  savedCalendarSettings.forEach((savedCalendarSetting) => {
    if (!availableCalendars.find((availableCalendar) => availableCalendar.id === savedCalendarSetting.id)) {
      unavailebleCalendars.push(savedCalendarSetting);
    }
  });
 
  // availableCalendars 와 savedCalendarSettings의 캘린더 ID를 비교해서, 
	// 일치하면 availableCalendar.sync 값을 true로 설정합니다.

  const calendarSettings = availableCalendars.map((availableCalendar) => {
    if (savedCalendarSettings.find((savedCalendar) => savedCalendar.id === availableCalendar.id)) {
      availableCalendar.sync = true;

    }
    return availableCalendar;
  });

  // 위에서 생성한 현재 사용가능한 캘린더 정보를 setting.calendarSettings 에 지정합니다.
  settings.calendarSettings = calendarSettings;

	// 각종 정보를 저장합니다.
  const savedFrom = PropertiesService.getScriptProperties().getProperty('syncFrom');
  settings.syncFrom = savedFrom;
 
  const savedTo = PropertiesService.getScriptProperties().getProperty('syncTo');
  settings.syncTo = savedTo;

  const savedIsUpdateTitle = PropertiesService.getScriptProperties().getProperty('isUpdateTitle') === 'true';
  settings.isUpdateCalendarItemTitle = savedIsUpdateTitle;

  const savedIsUseCategoriesAsCalendarItemTitle = PropertiesService.getScriptProperties().getProperty('isUseCategoriesAsCalendarItemTitle') === 'true';
  settings.isUseCategoriesAsCalendarItemTitle = savedIsUseCategoriesAsCalendarItemTitle;

  const savedIsUpdateDescription = PropertiesService.getScriptProperties().getProperty('isUpdateDescription') === 'true';
  settings.isUpdateCalendarItemDescription = savedIsUpdateDescription;
  
	//결과로 settings 객체를 리턴합니다. 
  return settings;
};

/* 이하 다시 Page.html */
/* getSettings의 성공, 실패 여부에 따라 각 함수가 실행됩니다. */
// 실패 핸들러입니다. 
const onFailure = (error) => {
  console.debug(error);
  document.getElementById('generic-error').innerHTML = error.message;
	//실패한 경우 오류의 메시지를 사이드바에 표시합니다.
}

// Code.gs의 getSettings의 실행이 성공한 경우에 실행하는 성공 핸들러입니다.
// 캘린더의 정보를 불러와서 Synchronise calendars 하위에 목록으로 추가합니다. 
// 여러 개의 캘린더가 있는 경우에 여러 캘린더가 보이게 됩니다. 
const onSuccessGetSettings = (settings) => {
  console.debug(settings);

  settings.calendarSettings.forEach((calendar, index) => {
      const div = document.createElement('div');

      const check = document.createElement('input');
      check.className = 'calendar-check';
      check.className = 'calendar-check red';
      check.type = 'checkbox';
      check.id = 'calendar' + index;
      check.value = (calendar.id);
      check.name = (calendar.name);
      check.checked = (calendar.sync);

      const label = document.createElement('label')
      label.htmlFor = "calendar" + index;
      label.appendChild(document.createTextNode(calendar.name));
      if (index == 0) {
          label.className = 'red';
      }

      div.appendChild(check);
      div.appendChild(label);

      document.getElementById('checks').appendChild(div);
  });

  document.getElementById('sync-from').value = settings.syncFrom || 31;
  document.getElementById('sync-to').value = settings.syncTo || 31;
  document.getElementById('is-update-calendar-item-title').checked = settings.isUpdateCalendarItemTitle;

  if (settings.isUpdateCalendarItemTitle) {
      document.getElementById('categories-as-item-title').style.display = "block";
      document.getElementById('is-use-categories-as-item-title').checked = settings.isUseCategoriesAsCalendarItemTitle;
      document.getElementById('show_title_warning').style.display = "block";
  }

  if (settings.isUpdateCalendarItemDescription) {
      document.getElementById('is-update-calendar-item-description').checked = settings.isUpdateCalendarItemDescription;
      document.getElementById('show_description_warning').style.display = "block";
  }
  document.getElementById('wait').style.display = "none";
  document.getElementById('main').style.display = "block";
}
JavaScript
복사

사용자 액션 및 세팅 저장

이제 Page.html 의 다른 코드는 사용자의 액션과 관련되어 있습니다.

체크박스, 연동을 시작하고 종료하는 기간(숫자)의 설정과 Save 버튼이 사용자가 할 수 있는 액션입니다.

/* 이하 Page.html*/

/* 첫 번째 부분은 body에서 Overwrite the calendar item title (캘린더 아이템 제목을 덮어쓰기)를
체크하거나 체크 해제했을 때 경고문구와 추가 옵션을 표시하거나 숨기는 스크립트입니다. 
*/
document.getElementById('is-update-calendar-item-title').addEventListener('change', (event) => {
  if (event.target.checked) { //체크박스에 체크가 되면
      document.getElementById('categories-as-item-title').style.display = "block";
			//id가 categories-as-item-title 인 div 를 표시합니다. 
			//Use categories as the calendar item title (카테고리를 캘린더 아이템의 제목으로 사용)
			//메뉴가 표시되며 체크할 수 있도록 됩니다.
      document.getElementById('show_title_warning').style.display = "block";
			//캘린더 아이템 제목을 덮어쓰기의 경고 문구를 표시합니다. 
  } else { //체크박스가 해제되면
      document.getElementById('categories-as-item-title').style.display = "none";
			//id가 categories-as-item-title 인 div 를 숨깁니다. 
      document.getElementById('is-use-categories-as-item-title').checked = false;
			//추가로 표시되었던 Use categories as the calendar item title의 체크박스를 해제합니다.
      document.getElementById('show_title_warning').style.display = "none";
			//캘린더 아이템 제목을 덮어쓰기의 경고 문구를 숨깁니다.
  }
})

/* 두 번째 부분은 Overwrite the calendar item description (캘린더 아이템 설명을 덮어쓰기)를
체크하거나 체크 해제했을 때 경고문구를 표시하거나 숨기는 스크립트입니다.
*/
document.getElementById('is-update-calendar-item-description').addEventListener('change', (event) => {
  if (event.target.checked) {
      document.getElementById('show_description_warning').style.display = "block";
			//체크되면 경고 문구를 표시합니다. 
  } else {
      document.getElementById('show_description_warning').style.display = "none";
			//체크 해제되면 경고 문구를 숨깁니다. 
  }
})


// 세팅 저장이 성공하는 경우
const onSuccessSaveSettings = (msg) => {
  console.debug(msg);
  document.getElementById('generic-message').innerHTML = msg;
}

/* Save 버튼을 누르는 경우 세팅을 저장하는 스크립트를 실행합니다.
*/
// save the settings
const saveSettings = () => {
	//사이드바의 generic-message를 공백으로 처리합니다. 
  document.getElementById('generic-message').innerHTML = ''; 
	//사이드바에 보여지는 캘린더들의 목록을 가져와서 체크된 것만 calendarSettings 에 저장합니다.
  const checks = document.getElementsByClassName('calendar-check');
  const calendarSettings = [];
  for (let check of checks) {
      if (!check.checked) {
          continue;
      }
      calendarSettings.push({
          name: check.name,
          id: check.value,
          sync: check.checked
      });
  }

	// 다른 세팅을 저장합니다. 
  const settings = {};
  settings.calendarSettings = calendarSettings;
  settings.syncFrom = document.getElementById('sync-from').value;
  settings.syncTo = document.getElementById('sync-to').value;
  settings.isUpdateCalendarItemTitle = document.getElementById('is-update-calendar-item-title').checked;
  if (settings.isUpdateCalendarItemTitle) {
      settings.isUseCategoriesAsCalendarItemTitle = document.getElementById('is-use-categories-as-item-title').checked;
  }
  if (!settings.isUpdateCalendarItemTitle) {
      settings.isUseCategoriesAsCalendarItemTitle = false;
  }

  settings.isUpdateCalendarItemDescription = document.getElementById('is-update-calendar-item-description').checked;
  console.debug(settings)

	//Code.gs 의 saveSettings() 에 settings 객체를 넘겨주어 실행시키고 성공, 실패여부에 따라 핸들러를 실행합니다. 
  google.script.run
      .withFailureHandler(onFailure)
      .withSuccessHandler(onSuccessSaveSettings)
      .saveSettings(settings);
}

/* 이하 Code.gs*/
const saveSettings = (settings) => {
  PropertiesService.getScriptProperties().setProperty('calendar', JSON.stringify(settings.calendarSettings));
  PropertiesService.getScriptProperties().setProperty('syncFrom', settings.syncFrom);
  PropertiesService.getScriptProperties().setProperty('syncTo', settings.syncTo);
  PropertiesService.getScriptProperties().setProperty('isUpdateTitle', settings.isUpdateCalendarItemTitle);
  PropertiesService.getScriptProperties().setProperty('isUseCategoriesAsCalendarItemTitle', settings.isUseCategoriesAsCalendarItemTitle);
  PropertiesService.getScriptProperties().setProperty('isUpdateDescription', settings.isUpdateCalendarItemDescription);
  return 'Settings saved';
};

/* 이하 Page.html*/
// Code.gs 의 saveSettings(settings)가 성공하면 Settings saved라는 메시지를 사이드바에 표시합니다. 
const onSuccessSaveSettings = (msg) => {
        console.debug(msg);
        document.getElementById('generic-message').innerHTML = msg;
    }
JavaScript
복사

캘린더 이벤트 연동

이제 사이드바에서 기본 세팅은 완료되었습니다.

이제 메뉴에서 Sync calendar events를 실행시킵니다. Sync calendar events는 run 함수를 실행하도록 되어 있습니다.

run 함수는 myTime 함수에게 파라미터로 현재 구글 시트의 ID를 전달하여 실행하도록 되어 있습니다.

myTime 함수는 파라미터를 전달받아 기본 상수들을 정의하고 하위의 run() 메서드를 실행합니다.

myTime(par).run() 이 실행되면 먼저, 캘린더를 연동하는 작업이 진행되고, 마지막에 formatSheet 함수를 실행하여 마무리합니다.

// run 이 실행되면, myTime(par).run() 이 이어서 실행됩니다. 
// par (파라미터)로는 현재 구글 시트의 ID를 전달합니다. 
const run = () => {
  'use strict';
  myTime({
    mainSpreadsheetId: SpreadsheetApp.getActiveSpreadsheet().getId(),
  }).run();
};

const myTime = (par) => {
  'use strict';

  const formatSheet = () => {
   // 아래에서 상세히 다룸
  };

  function run() {
     // 아래에서 상세히 다룸
  }

	// 먼저 필요한 상수들을 모두 정의 합니다. 
  const mainSpreadSheetId = par.mainSpreadsheetId;
  const mainSpreadsheet = SpreadsheetApp.openById(mainSpreadSheetId);
  const hourSheet = mainSpreadsheet.getSheetByName('Hours');
  const categoriesSheet = mainSpreadsheet.getSheetByName('Categories');
  const settings = getSettings();

  const syncStartDate = new Date();
  syncStartDate.setDate(syncStartDate.getDate() - Number(settings.syncFrom));
  
  const syncEndDate = new Date();
  syncEndDate.setDate(syncEndDate.getDate() + Number(settings.syncTo));
  
  const isUpdateCalendarItemTitle = settings.isUpdateCalendarItemTitle;
  const isUseCategoriesAsCalendarItemTitle = settings.isUseCategoriesAsCalendarItemTitle;
  const isUpdateCalendarItemDescription = settings.isUpdateCalendarItemDescription;

  const startTimeColumn = 3;
  const endTimeColumn = 4;
  const creatorsColumn = 6;
  const originalTitleColumn = 7;
  const originalDescriptionColumn = 8;
  const clientColumn = 9;
  const projectColumn = 10;
  const taskColumn = 11;
  const titleColumn = 12;
  const descriptionColumn = 13;
  const guestListColumn = 14;
  const locationColumn = 15;

  return Object.freeze({
    run: run,
  });
};
JavaScript
복사

핵심이 되는 myTime(par).run()을 먼저 살펴보겠습니다.

여러 변수들이 정의되어 있지만, 중요한 액션은 다음의 3가지입니다.

메인 프로세스가 every로 실행되면서 하위의 프로세스들이 진행되어 캘린더의 데이터를 구글 시트에 추가, 업데이트, 삭제합니다.

// 1. 메인 프로세스
settings.calendarSettings.filter((calenderSetting) => calenderSetting.sync === true).every(processCalendar);

// 2. 메인 프로세스 하위에 이어서 진행되는 프로세스: 캘린더 데이터 추가 또는 업데이트
events.every(handleEvent);

// 2번이 완료된 후에 진행하는 마지막 프로세스: 캘린더 데이터 삭제
existingEvents.every((event, index) => {
        if (event[0] !== calendarId) {
          return true;
        };

        if (eventsLookup[event[1]]) {
          return true;
        }
        
        if (event[3] < syncStartDate) {
          return true;
        }

        hourSheet.getRange(index + 2, 1, 1, 20).clear();
        return true;
      });
JavaScript
복사

상세하게 분석하면 다음과 같습니다.

function run() {
    console.log('Started processing hours.');

    const processCalendar = (setting) => {
			//구글 시트를 먼저 플러시합니다.
      SpreadsheetApp.flush();

      // 싱크가 되는 캘린더의 정보를 정의합니다. 
      const calendarName = setting.name;
      const calendarId = setting.id;

      console.log(`processing ${calendarName} with the id ${calendarId} from ${syncStartDate} to ${syncEndDate}`);

      // 캘린더를 ID로 불러옵니다.
      const calendar = CalendarApp.getCalendarById(calendarId);

      // 싱크 일자만큼 이벤트를 가져옵니다.
      const events = calendar.getEvents(syncStartDate, syncEndDate);
			// eventsLookup을 정의합니다. {'eventId1' = event1, 'eventId2' = event2 ...} 
      const eventsLookup = events.reduce((jsn, event) => {
        jsn[event.getId()] = event;
        return jsn;
      }, {});

      // Hours 탭에 존재하는 이벤트를 정의합니다. 
			// slice(1)로 제목 행은 삭제합니다. 
      const existingEvents = hourSheet.getDataRange().getValues().slice(1);

			// existingEventsLookUp 을 eventsLookup과 동일하게 정의합니다. 
			// 단, A열의 calendarId 와 주어진 Id가 같은 것만 추출합니다.
      const existingEventsLookUp = existingEvents.reduce((jsn, row, index) => {
        if (row[0] !== calendarId) {
          return jsn;
        }
        jsn[row[1]] = {
          event: row,
          row: index + 2 //index 는 0부터 시작하고, 제목행을 삭제 했기 때문에 +2로 행번호를 정의합니다.
        };
        return jsn;
      }, {});

      // 캘린더에서 가져온 이벤트를 every로 프로세스합니다.
      const handleEvent = (event) => {
        const eventId = event.getId();

        // existingEventsLookUp에 eventId가 Key로 존재하지 않으면 새로운 이벤트입니다.
        if (!existingEventsLookUp[eventId]) {
					//새로운 행을 추가합니다. 
          hourSheet.appendRow([
            calendarId,
            eventId,
            event.getStartTime(),
            event.getEndTime(),
            calendarName,
            event.getCreators().join(','),
            event.getTitle(),
            event.getDescription(),
            event.getTag('Client') || 'tbd',
            event.getTag('Project') || 'tbd',
            event.getTag('Task') || 'tbd',
            (isUpdateCalendarItemTitle) ? '' : event.getTitle(),
            (isUpdateCalendarItemDescription) ? '' : event.getDescription(),
            event.getGuestList().map((guest) => guest.getEmail()).join(','),
            event.getLocation(),
            undefined,
            undefined,
            undefined,
            undefined
          ]);
          return true;
        }

        // 존재하는 이벤트라면 정의합니다.
        const exisitingEvent = existingEventsLookUp[eventId].event;
        const exisitingEventRow = existingEventsLookUp[eventId].row;
				
				// 기존에 입력된 정보랑 같은지 확인해서 다르다면 새로운 정보를 입력합니다. 
        if (event.getStartTime() - exisitingEvent[startTimeColumn - 1] !== 0) {
          hourSheet.getRange(exisitingEventRow, startTimeColumn).setValue(event.getStartTime());
        }

        if (event.getEndTime() - exisitingEvent[endTimeColumn - 1] !== 0) {
          hourSheet.getRange(exisitingEventRow, endTimeColumn).setValue(event.getEndTime());
        }

        if (event.getCreators().join(',') !== exisitingEvent[creatorsColumn - 1]) {
          hourSheet.getRange(exisitingEventRow, creatorsColumn).setValue(event.getCreators()[0]);
        }

        if (event.getGuestList().map((guest) => guest.getEmail()).join(',') !== exisitingEvent[guestListColumn - 1]) {
          hourSheet.getRange(exisitingEventRow, guestListColumn).setValue(event.getGuestList().map((guest) => guest.getEmail()).join(','));
        }

        if (event.getLocation() !== exisitingEvent[locationColumn - 1]) {
          hourSheet.getRange(exisitingEventRow, locationColumn).setValue(event.getLocation());
        }

        if(event.getTitle() !== exisitingEvent[titleColumn - 1]) {
          if(!isUpdateCalendarItemTitle) {
            hourSheet.getRange(exisitingEventRow, titleColumn).setValue(event.getTitle());
          }
          if(isUpdateCalendarItemTitle) {
            event.setTitle(exisitingEvent[titleColumn - 1]);
          }
        }
       
        if(event.getDescription() !== exisitingEvent[descriptionColumn - 1]) { 
          if(!isUpdateCalendarItemDescription) {
            hourSheet.getRange(exisitingEventRow, descriptionColumn).setValue(event.getDescription());
          }
          if(isUpdateCalendarItemDescription) {
            event.setDescription(exisitingEvent[descriptionColumn - 1]);
          }
        }

        return true;
      };

      /* 새로운 이벤트인지 기존에 입력된 이벤트인지를 체크하고, 
			새로운 이벤트면 추가하고, 기존에 입력된 이벤트이면 데이터를 업데이트 합니다. 
			*/
      events.every(handleEvent);

      // 연동한 캘린더에 없어진 이벤트는 삭제합니다. 
      existingEvents.every((event, index) => {
        if (event[0] !== calendarId) {
          return true;
        };

        if (eventsLookup[event[1]]) {
          return true;
        }
        
        if (event[3] < syncStartDate) {
          return true;
        }
				// 위의 조건에 하나도 해당하지 않으면 삭제
				// 캘린더Id 가 같고, 불러온 캘린더 이벤트에는 없고, syncStartDate 보다 Finish가 큰 경우
        hourSheet.getRange(index + 2, 1, 1, 20).clear();
        return true;
      });

			// 모든 프로세스가 진행되고 나면 true를 리턴
      return true;
    };

    // 캘린더의 싱크가 true로 되어 있는 캘린더만 골라서 processCalendar를 실행시킵니다.
    settings.calendarSettings.filter((calenderSetting) => calenderSetting.sync === true).every(processCalendar);

    formatSheet();
    SpreadsheetApp.setActiveSheet(hourSheet);

    console.log('Finished processing hours.');
  }
JavaScript
복사

다음은 formatSheet 를 살펴보겠습니다.

데이터가 모두 입력된 후에 텍스트 및 시간을 자동으로 계산하는 함수를 입력하는 스크립트입니다.

const formatSheet = () => {
    // Hours 탭을 C열 기준으로 내림차순 정렬합니다.  
    hourSheet.sort(3, false);

    // Hours 탭의 A,B 열을 숨김처리합니다. (1번 열에서 2개)
    hourSheet.hideColumns(1, 2);

    // 데이터가 없는 행을 삭제합니다.
    if (hourSheet.getLastRow() > 1 && hourSheet.getLastRow() < hourSheet.getMaxRows()) {
      hourSheet.deleteRows(hourSheet.getLastRow() + 1, hourSheet.getMaxRows() - hourSheet.getLastRow());
    }

    // Customer 열에 대한 데이터 확인을 생성합니다. 
    let rule = SpreadsheetApp.newDataValidation()
      .requireValueInRange(categoriesSheet.getRange('A2:A'), true)
      .setAllowInvalid(true)
      .build();
    hourSheet.getRange('I2:I').setDataValidation(rule);

    // Project 열에 대한 데이터 확인을 생성합니다. 
    rule = SpreadsheetApp.newDataValidation()
      .requireValueInRange(categoriesSheet.getRange('B2:B'), true)
      .setAllowInvalid(true)
      .build();
    hourSheet.getRange('J2:J').setDataValidation(rule);

    // Task 열에 대한 데이터 확인을 생성합니다. 
    rule = SpreadsheetApp.newDataValidation()
      .requireValueInRange(categoriesSheet.getRange('C2:C'), true)
      .setAllowInvalid(true)
      .build();
    hourSheet.getRange('K2:K').setDataValidation(rule);

		//settings에서 가져온 isUseCategoriesAsCalendarItemTitle 정보가 true 이면, L열에 함수를 입력합니다. 
    if(isUseCategoriesAsCalendarItemTitle) {
			//Customer, Project, Task 열 중 하나라도 tbd 이면 공백으로 처리하고, 
			//모두 입력되면 세 열을 Concat 해서 표시
      hourSheet.getRange('L2:L').setFormulaR1C1('IF(OR(R[0]C[-3]="tbd";R[0]C[-2]="tbd";R[0]C[-1]="tbd");""; CONCATENATE(R[0]C[-3];"|";R[0]C[-2];"|";R[0]C[-1];"|"))');
    }
    // 시간, 월, 주를 계산하는 수식을 추가
    hourSheet.getRange('P2:P').setFormulaR1C1('=IF(R[0]C[-12]="";"";R[0]C[-12]-R[0]C[-13])');
    hourSheet.getRange('Q2:Q').setFormulaR1C1('=IF(R[0]C[-13]="";"";month(R[0]C[-13]))');
    hourSheet.getRange('R2:R').setFormulaR1C1('=IF(R[0]C[-14]="";"";WEEKNUM(R[0]C[-14];2))');
    hourSheet.getRange('S2:S').setFormulaR1C1('=R[0]C[-3]');
  };
JavaScript
복사

대시보드

Hours 탭의 데이터를 구글 쿼리로 가져와서 표시해줍니다.

A1 셀의 날짜를 변경하면 그에 해당하는 데이터들이 보여지게 됩니다.

Customer, Project, Task 를 캘린더에서 적절히 입력한다면 대시보드로 효과적일 것 같습니다.

[GAS] 구글 캘린더 시간 관리 예제 분석

목적

샘플 준비 하기

샘플 분석

메뉴 및 사이드바

사이드바의 실행 및 기본 세팅 로드

사용자 액션 및 세팅 저장

캘린더 이벤트 연동

대시보드

관련 포스팅