엑셀 자동화 - 구글 스프레드시트 매크로로 데일리 플래너 만들기

 

+ 2022.07.30.
글 하단에 시트제작 강의안내 및 쿠폰코드 추가해두었습니다.

 

---

 

일정관리 플랫폼 유랑자로서 작년에도 이리저리 떠돌다 하반기에는 구글 스프레드시트를 위클리 플래너로 활용했었다. 월요일부터 일요일까지 한눈에 볼 수 있고 스크롤해서 전주랑 다음 주 일정을 볼 수 있어서 좋았다. 역시 뭔가 아쉬웠지만 나름대로 쓸 만했다. 이따금씩 반복적인 복붙이 필요했지만 신경 쓰일 정도는 아니었다.

 

올해 초, 우연히 이지영 스타강사님의 '30분 시간관리법'에 대해 듣고 흥미를 갖게 되었다. 사전기록은 최대한 러프하게 하고, 30분 단위로 시간을 어디에 썼는지 사후기록을 해서 낭비되는 시간을 점검하고 또 최소화할 수 있는 시간관리법이다. 내 일상에는 30분보다 호흡이 짧은 일들이 많아서 20분으로 적용해보고 싶었다. '20분 시간관리법'을 적용하려면 위클리 가 아닌 데일리 플래너가 필요했다.

30분 시간관리법

 

처음엔 '손'으로 적어야겠다 싶었다. 30분 단위로 사후기록할 때 칸을 채우는 맛을 살리기 위해서 고심 끝에  아이패드 굿노트 서식을 구매했다. 문제가 있었다. 하이퍼링크가 잘 들어가 있었지만 과목별로 학습계획을 먼슬리, 위클리에 옮겨적고 이걸 다시 데일리로 옮겨 적는 수동 작업은 너무 번거로웠다. 감성보다 귀차니즘이 더 강력했다...

 

결국 다시 구글 스프레드시트로 돌아왔다. 원래 쓰던 위클리는 간소화하고, 데일리 시트를 매일 생성해서 쓰기로 했다. 효율성이 낮다는 이유로 스프레드시트로 다시 넘어온건데 매일 시트를 수동으로 복제하고 있을 수는 없었다. 그렇게 매크로에 입문했다.

 

작년에 만든 위클리                                                                                                     올해 새로 만든 데일리 양식

 

 

 

 

 

 

우선은 구현하고 싶은의 목록을 작성하고 하나씩 구현해 나갔다.

 

앱스 스크립트에서는 '매크로기록'이라는 기능을 제공한다.  [도구-매크로-매크로기록] 기능으로 시트 이동이나 복사 입력과 같이 원하는 동작을 직접 해보면 스크립트가 알아서 작성된다. 앱스 스크립트에서 사용하는 cell, range, sheet, spreadsheet 등의 작업 단위가 낯설었지만 매크로 기록 덕분에 금방 구현할 수 있었다. 매크로 기록을 하고나서 [도구-스크립트 편집기]에 들어가서 cell, range 부분만 변수로 바꾸어주면 된다.

 

1. 오래된 시트 자동으로 숨기기

시트 메뉴탭을 간결하게 유지할 수 있도록 7일이 지난 시트는 숨겨준다.

const hideOldSheets = () => {
  const ss = SpreadsheetApp.getActive();
  const today = getDateStr(new Date());
  const allSheets = ss.getSheets();
  const EXPIRATION = 7;

  for (let sheet of allSheets) {
    let sheetName = sheet.getSheetName();

    if (Number.isInteger(+sheetName) && today - sheetName >= EXPIRATION) {
      sheet.hideSheet();
    }
  }
};

 

 

2. 오늘의 시트 자동으로 만들기

지정 템플릿을 복제해서 오늘의 시트를 생성한다.

   - 시트 제목은 오늘 날짜로 한다.

   - 시트 순서를 맨 앞으로 옮긴다.

   - 시트 본문 헤더에 오늘 날짜로 적어준다.

const createSheetForToday = () => {
  const ss = SpreadsheetApp.getActive();
  const today = getDateStr(new Date());

  ss.setActiveSheet(ss.getSheetByName(TEMPLATE_SHEET), true);
  ss.duplicateActiveSheet();
  
  // 시트 이름 설정
  ss.setActiveSheet(ss.getSheetByName(`${TEMPLATE_SHEET}의 사본`), true);
  try {
    ss.getActiveSheet().setName(today);
  } catch (e) {
    ss.deleteActiveSheet();
  }
  
  // 시트 정렬
  ss.moveActiveSheet(1);
  
  // 본문 헤더에 오늘 날짜 입력
  ss.getRange('B1')
    .activate()
    .setValue(TIME_VALUE_2021 + +today);
  ss.getActiveRangeList().setNumberFormat('m" / "d" ("ddd")"');
};

 

 

3. 오늘 할 일 자동으로 가져오기

위클리에서 오늘 날짜에 해당하는 할 일을 카테고리 별로 가져온다.

const updateTasksFromWeeklyToDaily = () => {
  const ss = SpreadsheetApp.getActive();
  const today = getDateStr(new Date());
  const weeklyCells = getWeeklyCells();

  Logger.log(`주간계획 시트에서 ${weeklyCells} 부분을 복사합니다`);

  try {
    ss.setActiveSheet(ss.getSheetByName(today), true);
  } catch (e) {
    createSheetForToday();
    ss.setActiveSheet(ss.getSheetByName(today), true);
  }

  // 복사 붙여넣기
  for (let i = 0; i < CATEGORY_NUM; i++) {
    ss.getRange(DAILY_TASK_CELLS[i]).activate();
    ss.getRange(`${WEEKLY_SHEET}!${weeklyCells[i]}`).copyTo(
      ss.getActiveRange(),
      SpreadsheetApp.CopyPasteType.PASTE_VALUES,
      false
    );
  }
  inserCheckbox();
};

 

할 일이 있다면 체크박스를 생성해준다.

function inserCheckbox() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getActiveSheet();

  for (let cell of DAILY_TASK_CELLS) {
    sheet.getRange(cell).activate();
    for (let i = 0; i < MAX_TASK_NUM; i++) {
      let cell = sheet.getCurrentCell();
      if (!cell.isBlank()) {
        cell
          .offset(0, 1)
          .activate()
          .insertCheckboxes()
          .offset(1, -1)
          .activate();
      }
    }
  }
}

 

매크로 테스트 화면 예시

 

 

4. 사후기록 자동으로 꾸미기

사후기록 불릿 포인트를 카테고리별로 색칠해준다.

const colorBulletPoint = () => {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getActiveSheet();
  const taskContents = DAILY_TASK_CELLS.map((cell) =>
    sheet
      .getRange(`${cell}:${cell[0]}${+cell.slice(1) + MAX_TASK_NUM}`)
      .getValues()
      .join('♥︎')
      .split('♥︎')
      .filter((v) => v)
  );
  let cell = DAILY_TASK_CELLS[1];
  Logger.log(
    sheet.getRange(`${cell}:${cell[0]}${+cell[1] + MAX_TASK_NUM}`).getValues()
  );
  Logger.log(taskContents);

  for (let cell of DAILY_LOG_CELLS) {
    sheet.getRange(cell).activate();
    for (let i = 0; i < TIMETABLE_LEN; i++) {
      let cell = sheet.getCurrentCell();
      let taskDone = cell.getValue();
      let isColoured = false;

      if (taskDone === '') {
        cell
          .offset(0, -1)
          .activate()
          .setFontColor(DEFAULT_COLOR)
          .offset(1, 1)
          .activate();
        continue;
      }
      for (let j = 0; j < taskContents.length; j++) {
        if (taskContents[j].includes(taskDone)) {
          cell
            .offset(0, -1)
            .activate()
            .setFontColor(PALETTE[j])
            .offset(1, 1)
            .activate();
          isColoured = true;
          break;
        }
      }
      if (isColoured === false) {
        cell
          .offset(0, -1)
          .activate()
          .setFontColor(DEFAULT_COLOR)
          .offset(1, 1)
          .activate();
      }
    }
  }
};

매크로 테스트 화면 예시

 

 

 

5. 매일 아침 자동으로 실행하기

필요한 함수를 엮어 아침에 자동으로 실행되도록 설정해서 사용하고 있다. 지정된 시간에 자동으로 실행하도록 하는 건 코드를 작성하지 않고 할 수 있다. 스크립트 편집기 메뉴에서 트리거를 생성하면 된다. 트리거 옵션이 세분화되어있어서 업무에 활용하기 정말 좋아 보였다. 원하는만큼 커스터마이즈할 수 있으니 생각나는 기능들을 하나씩 덧붙여 나가도 좋을 것 같다. 

const prepareNewDay = () => {
  hideOldSheets();
  createSheetForToday();
  updateTasksFromWeeklyToDaily();
};

 

 

 

+ 상수로 사용한 전역변수

const CATEGORY_NUM = 6;
const EXTRA_CATEGORY_NUM = 3;
const MAX_TASK_NUM = 5;
const TIMETABLE_LEN = 6;
const ROW_OFFSET = 2;
const ALPHABETS = new Array(26).fill().map((v, i) => String.fromCharCode(65 + i));
const TIME_VALUE_2021 = 44196;
const WEEKLY_SHEET = '주간계획';
const TEMPLATE_SHEET = '템플릿'
const DEFAULT_COLOR = '#efefef';
const PALETTE = ['#5fadff', '#91c7ff', '#c8e3ff', '#9061ff', '#af90f9', '#dfd2ff', '#ff6cbe', '#ff9bd3', '#ffcfea'];
const DAILY_TASK_CELLS = ['L5', 'L13', 'L21', 'Q5', 'Q13', 'Q21', 'V5', 'V13', 'V21'];
const DAILY_LOG_CELLS = ['D5','F5','H5','D13','F13','H13','D21','F21','H21'];

 

 

 

유튜버 중년코딩님 앱스스크립트

 

Class Sheet  |  Apps Script  |  Google Developers

`집콕 스터디` 시대…`1타 강사`들이 전하는 `혼자 공부하는 법`

 

 

+ 강의 선출시 안내

잇따른 요청에 따라 시트 제작과정을 담은 강의를 출시했습니다. 많은 관심 주셔서 감사드립니다.

7월 현재, 절반의 영상이 준비된 상태로 선출시하였고 쿠폰코드 사용가능합니다.
(하단 쿠폰코드를 적용하셔야 최저가로 구입하실 수 있습니다.)

부족한 강의이지만 도움되셨으면 좋겠습니다.
감사합니다.

• 쿠폰코드: DAY_BY_DAY
• 강의링크: https://www.udemy.com/course/efficient-macro/?couponCode=DAY_BY_DAY

[엑셀 자동화] 매크로로 일정관리 자동화 시트 만들기: Google SpreadSheet/AppsScript