구글 스프레드 시트 자동화(app script, slack)

매일 특정 시간에 약을 복용하고 스프레드 시트에 기록해둔다고 해보자. 복용을 깜빡하고 기록을 남기지 않았을 때나 복용은 했는데 기록을 남기지 않은 경우들이 생길 수 있다. 이 때, 복용 시간에서 2시간 뒤, 5시간 뒤에 각각 체크하여 약 복용을 슬랙으로 리마인드 해주는 기능을 만들어보자.

스프레드시트에는 다음과 같이 기록되어 있을 것이다.

요구사항을 정리하면
11시에 A약 복용을 목표로 하고 있다.
13시, 16시가 될 때마다 체크한다.
오늘 날짜로 로우를 찾고 "A약 복용시간"의 칼럼을 찾는다. 찾아진 로우와 칼럼을 이용해 셀을 찾는다. 셀이 비었는지 확인한다. 
비었으면 슬랙 알림을 보낸다. 

해결과정
우선 구글 스프레드시트를 사용하고 있으니 바로 연동할 수 있는 App script를 이용한다.

 

hello world를 출력하는 코드를 적고 "실행" 버튼을 누르면 실행 로그에 hello world가 나오는 것이 보인다.

 

코드를 myFunction 내부에 입력하고 실행을 누르는 방식으로 개발을 진행할 수 있다.

매일 13시 16시 마다 코드를 실행하는 방법

우리는 "13시, 16시가 될 때마다 체크한다." 를 필요로 하는데 App script에서 매일 특정 시간대마다 코드를 실행하도록 해주는 기능을 제공한다. 즉, "매일 13시~14시 사이에 내가 작성한 코드를 실행시켜줘" 를 할 수 있다.

위와 같이 좌측에 알람시계처럼 생긴 아이콘을 누르면 아래와 같은 화면으로 이동한다.

여기서 "+트리거 추가" 를 누른 후에 1~3 순서대로 선택한다.
1.이벤트 소스 선택 -> 시간 기반
2.트리거 기반 시간 유형 선택 -> 일 단위 타이머
3.시간 선택 오후1시~오후 2시 사이

위 방법대로 하면 쉽고 빠르게 매일 13시마다 내가 작성한 코드를 실행할 수 있다. 엄밀하게 13시 정각에는 동작하지는 않는데, App script에서 시간 범위 안에 실행을 해주기 때문에 약간의 오차를 허용하게 된다. 일단 약 복용을 리마인드 차원에서 하는거니 나쁘지는 않다.

오늘 날짜로 로우를 찾고 "A약 복용시간"의 칼럼을 찾는다. 찾아진 로우와 칼럼을 이용해 셀을 찾는다. 셀이 비었는지 확인한다. 

각각의 동작을 함수로 구현하기 위하여 함수를 먼저 작성해보자. 한글로 설명했으니 한글을 사용하여 코드를 작성하였다. const, 함수에 대해서 모르시는 분들은 chatgpt나 검색을 통해 이해해볼 수 있다. 우측에 "오늘날짜의로우찾기()"는 오늘 날짜에 해당하는 셀을 찾아서 로우(행)을 알려달라고 하는 요청으로 봐주시면 된다. 즉, 누군가에게 "오늘 날짜 로우가 몇이야?"라고 물어보는 행위인 것이다. 이 결과 로우를 다시 돌려주게 되는데, 그 로우를 나중에 쓰려고 "const 오늘날짜의로우" 라는 것을 써서 잠시 저장해두는 것이다.

const 오늘날짜의로우 = 오늘날짜의로우찾기();
const A약복용시간의칼럼 = A약복용시간의칼럼찾기();
const 셀이비었나요 = 셀이비었는지확인(오늘날짜의로우, A약복용시간의칼럼);

이제 각각 요청하는 실제 함수(로우찾기, 칼럼찾기, 비었는지확인)를 작성하면 셀이 비었는지 확인하는 것까지 완료하게 된다.

비었으면 슬랙 알림을 보낸다. 

슬랙 알림을 보내기 위해서는 웹훅 url을 필요로 한다. 이를 위해서 슬랙 가입 및 채널 생성, 약간의 설정을 해주어야 하는데 간단하다. 구글링 등 검색을 통해서 슬랙 설정에 대한 포스팅을 참고하시면 된다. 제가 참고한 글도 첨부: https://blog.naver.com/segasas/223311008126

Slack Webhook 연동

 

이제 웹훅 url도 알았으니 비었으면 메시지를 보내주기만 하면 끝난다. 최종적으로 myFunction에 작성된 코드는 다음과 같을 것이다.

const 오늘날짜의로우 = 오늘날짜의로우찾기();
const A약복용시간의칼럼 = A약복용시간의칼럼찾기();
const 셀이비었나요 = 셀이비었는지확인(오늘날짜의로우, A약복용시간의칼럼);
if(셀이비었나요) {
  슬랙메시지보내기();
}

 

작성 완료된 코드를 첨부(참고로 필자는 "24년 5월 16일(목)" 과 같은 글자 형식을 사용했다.)
가독성이나 유지보수를 고려하지는 않고 최대한 빠르게 동작만 하도록 작업하였다. chatgpt의 도움을 받아 작성하였기에 가독성이나 유지보수에 적합한 부분은 떨어질 수 있으니, 참고하시면 좋을 거 같다.

function myFunction() {

  const 오늘날짜의로우 = 오늘날짜의로우찾기();
  const A약복용시간칼럼 = A약복용시간의칼럼찾기();
  const 비었나요 = 셀이비었는지확인(오늘날짜의로우, A약복용시간칼럼);
  if(비었나요){
    슬랙알림보내기();
  }
}

function 슬랙알림보내기() {
  var url = "https://hooks.slack.com/services/..."; // 슬랙 웹훅 URL
  
  var message = {
    "text": "약 복용을 확인해주세요. 💊"
  };

  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(message)
  };
  
  UrlFetchApp.fetch(url, options);
}

function 셀이비었는지확인(오늘날짜의로우, A약복용시간칼럼) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange(오늘날짜의로우숫자, A약복용시간칼럼숫자);
  var value = cell.getValue();
  
  if (value === "" || value === null) {
    Logger.log("셀은 비어 있습니다.");
    return true;
  } else {
    Logger.log("셀에 값이 있습니다.");
    return false;
  }
}

function 오늘날짜의로우찾기(){
    var today = new Date();
  var year = today.getFullYear().toString().slice(-2); // 현재 연도의 뒤의 두 자리 숫자
  var month = today.getMonth() + 1; // 현재 월 (0부터 시작하기 때문에 1을 더함)
  var day = today.getDate(); // 현재 일
  var dayOfWeek = ['일', '월', '화', '수', '목', '금', '토'][today.getDay()]; // 요일 배열

  var formattedDate = year + '년 ' + month + '월 ' + day + '일(' + dayOfWeek + ')';

  var searchTerm = formattedDate; // 찾고자 하는 날짜의 형식
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.createTextFinder(searchTerm).findAll();

  if (range.length > 0) {
    var firstCell = range[0];
    return firstCell.getRow();
  } else {
    Logger.log("오늘 날짜를 포함하는 셀을 찾을 수 없습니다.");
    return null;
  }
}

function A약복용시간의칼럼찾기(){
  var searchTerm = "A약복용시간"; // 찾고자 하는 날짜의 형식
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.createTextFinder(searchTerm).findAll();

  if (range.length > 0) {
    var firstCell = range[0];
    return firstCell.getColumn();
  } else {
    Logger.log("A약복용시간 글자를 포함하는 셀을 찾을 수 없습니다.");
    return null;
  }
}