학교 업무 자동화 - 야간자율학습 출석체크 자동화

학교마다 다르지만, 많은 학생들의 니즈가 있다면 야간자율학습을 하게 될 가능성이 높다.

그래서 우리 학교는 아직 야간자율학습이 남아있다.

야자의 이름은 독서삼도.

이때까지 야간자율학습의 출석 체크를 수기로 해왔다.

자습실로 들어가기 전에 자리 그림이 그려진 출석부에다가 자신의 학번, 이름을 적으면 자율학습 담당 교사가 그 출석부를 가지고 자습실을 돌면서 학생들의 이름을 확인해야 했다.

그래서 야자 전에 출석부 앞에서 아이들이 줄을 서서 자신의 이름을 적곤 했다.

학기말에는 야간자율학습에 참여한 학생의 통계도 필요했다.

이 모든게 수기이다.

그래서 한번 전산화 시켜보기로 했고, 한 학기 동안 성공적으로 운영했다.

미리 완성된 설문지와 스프레드시트를 링크 걸어 둔다.

1. 아이디어

야자 출첵에 있어서 가장 중요한 핵심들을 뽑아 보았다.

• 쉽고 간편한 방법으로 출첵이 가능해야 한다.
• 제출 후 수정이 가능해야 한다.
• 담당 교사의 확인이 필요하다.

위의 세가지를 만족시키는 방안은 구글 설문지의 쿼리 스트링을 qr코드 형태로 만들어 배포하는 것이라 생각했다.

각 자리마다 쿼리스트링을 포함한 설문 조사 qr코드를 만들어 붙여놓으면 학생들이 그 qr을 찍고 설문지를 제출하는 방식이다.

이렇게 취합된 데이터를 스프레드시트의 Apps Script를 이용해 특정 시간에 날짜와 함께 한줄씩 추가하는 것이다.

이렇게 하면 야자 담당교사도 모바일에서 출석부에 내용을 추가하거나 수정할 수 있다.

또한 학기말에 학생의 참여도를 취합할 수 있다.

이제 해당 과정으로 세팅을 진행해보자.

2. 야자를 위한 설문지와 스프레드시트 만들기

구글 설문지에서 내용은 자리번호, 학번, 이름이 전부이다.

입력하는 내용은 최대한 간결해야 한다.

그리고 오른쪽 위에 보면 미리 채워진 링크 가져오기가 있다.

해당 링크로 들어간다.

우리 학교에서 야자는 자리가 지정되어 있지 않기 때문에 학번과 이름은 비워두고 자리번호만 미리 채워둘 예정이다.

링크 복사를 누르면 아래와 같은 주소를 얻을 수 있다.

https://docs.google.com/forms/d/e/1FAIpQLScXgTn9UK5HETv3fCipGiRMl-et9IezB1OSZp2r9-Nj8b54Fg/viewform?usp=pp_url&entry.1738725719=23&entry.7330080=20366&entry.1354978010=%EC%BA%A5%EA%B1%B0%EB%A3%A8

여기서 viewform? 뒷부분이 쿼리이다.

쿼리스트링은 물음표 뒤에 key와 value를 쌍으로 전달한다.

나는 자리번호를 23번, 학번은 20366, 이름은 캥거루로 주소를 생성해보았다.

https://docs.google.com/forms/d/e/1FAIpQLScXgTn9UK5HETv3fCipGiRMl-et9IezB1OSZp2r9-Nj8b54Fg/viewform?usp=pp_url
&entry.1738725719=23 // 자리번호
&entry.7330080=20366 // 학번
&entry.1354978010=%EC%BA%A5%EA%B1%B0%EB%A3%A8 // 이름

따라서 우리가 생성해야 하는 주소는 아래와 같다.

해당 주소로 접속하면

https://docs.google.com/forms/d/e/1FAIpQLScXgTn9UK5HETv3fCipGiRMl-et9IezB1OSZp2r9-Nj8b54Fg/viewform?usp=pp_url&entry.1738725719=23

이제 해당 설문지의 스프레드시트를 생성하고 시트를 작성해보자.

3. 스프레드시트 설정하기

먼저 시트에 들어가면 설문지 응답 시트가 있다.

여기는 설문응답이 시간순으로 쌓이게 된다.

중간출석부에서는 설문의 응답을 정재하는 역할을 한다.

여기가 가장 중요하다.

먼저 query 함수를 이용해 타임스탬프가 해당일인 데이터를 가져온다.

=QUERY('설문지 응답 시트1'!$A:$D,"SELECT B,C,D WHERE A<DATE'"&YEAR('자기주도학습실'!C1)&"-"&MONTH('자기주도학습실'!C1)&"-"&DAY('자기주도학습실'!C1)+1&"' AND A>=DATE'"&YEAR('자기주도학습실'!C1)&"-"&MONTH('자기주도학습실'!C1)&"-"&DAY('자기주도학습실'!C1)&"' ORDER BY A",1)

이렇게 가져왔을 때 몇가지 문제점을 생각해 볼 수 있다.

• 학생이 자리를 바꾸면서 서로 다른 자리에 이름이 중복될 경우
• 하나의 자리에 두명의 학생이 중복되는 경우

그래서 unique 함수와 xlookup 함수로 이를 해결했다.

먼저 "학번", "이름" 열에서는 =UNIQUE(F:G) 로 유니크한 값을 가져온다.

그리고 "앉은자리" 탭에서는 =XLOOKUP(J2,F:F,E:E,,,-1) 으로 해당 학번의 제일 마지막에 제출된 값만 가져온다.

그리고 최종적으로 자리번호에 대한 앉은 학생을 결정해 준다.

=XLOOKUP($A2,$I:$I,J:J,"",,-1) 로 제출된 자리들 중 가장 마지막 값을 가져온다.

이렇게 하면 데이터 정제는 끝이다.

이제 자리 번호에 대한 학번과 이름으로 여러 시트를 제작하면 된다.

자기주도학습실 시트는 교사들에게 보이기 위한 시트이다.

스프레드시트 함수인 today() 를 이용해 오늘의 날짜를 표시한다.

해당 자리에 맞는 좌석 테이블을 하나 작성하고, 이 값을 중심으로 자리에 맞는 학생들의 학번, 이름을 중간출석부에서 불러온다.

조건부 스타일을 이용해 앉은 자리는 초록색으로 변경해준다.

마지막으로 출결 통계와 체크를 위한 시트이다.

야간자율학습에 참여하는 학생들의 통계를 위한 것이다.

야자에 2/3 이상을 참석하면 생활기록부에 적어줄 수 있다.

여기서 Apps Script 설정이 필요하다.

4. Apps Script 설정

최상단의 메뉴에서 확장 프로그램으로 들어가면 Apps Script라는 탭이 있다.

여기로 들어가면 시트에 대한 명령을 javascript 행태로 줄 수 있다.

엑셀에서 프로그램을 만들기 위해서는 비주얼 베이직을 배워야 하는 반면, 스프레드시트에서는 javascript만으로 원하는 기능을 구현할 수 있으니 정말 편했다.

코드를 입력하는 곳에 해당 코드를 입력한다.

함수명은 addRowDaily로 했는데 뭐든지 상관 없다.

자세한 설명은 주석으로 대신한다.

function addRowDaily() {
  // 시트를 호출할 객체를 생성
  let checkSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("출석통계");
  let data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("중간출석부");
 
  // 열의 인덱스로 이용할 날짜 정보
  let date = new Date()
  let month = date.getMonth() + 1;
  let day = date.getDate();
  let time = date.getHours();

  // 중간에 삽입할 열과 가장 마지막 행을 구함.
  let lastColumn = 3
  let rowRange = checkSheet.getLastRow()

  // 중간에 열 삽입. 열의 이름은 월, 일, 시간이며 모두 체크박스 형태
  checkSheet.insertColumnAfter(lastColumn-1);
  checkSheet.getRange(1, lastColumn).setValue(`${month}월 ${day}일 ${time}시`);
  checkSheet.getRange(2, lastColumn, rowRange-1, 1).insertCheckboxes();

  // 학생들의 이름이 있는 행정보를 map으로 저장. 학번이 key, 행번호가 value.
  let studentMap = new Map();
  checkSheet.getRange("A1:B6").getValues().forEach((row, idx) => {
    studentMap.set(row[0], idx+1)
  });

  // 출석이 있는 행을 돌면서 값이 있는 행의 학번을 가져옴. map에서 학번을 키로 값을 찾아 해당 행에 [true]를 입력함.
  let checkedStudents = data.getRange("A2:C64").getValues();
  checkedStudents.forEach((rows) => {
    let seatNumbers = rows[0];
    let studentIdNumber = rows[1];
    let studentName = rows[2];
    if (studentIdNumber ===""){
      return
    };
    checkSheet.getRange(studentMap.get(studentIdNumber), lastColumn).setValue([true]);
  })
}

// 셀 수정을 감시함. 출석 통계 셀의 A11 셀이 변경될 경우 함수 실행
function onEdit(e){
  let rg = e.range;
  if (rg.getA1Notation() === "A11" && rg.isChecked() && rg.getSheet().getName() === "출석통계"){
    addRowDaily();
    rg.uncheck();
  };
}

맨 마지막에는 함수 실행을 위한 트리거를 지정했다.

왜 굳이 체크박스를 버튼으로 쓰는지 알고 싶다면 stackoverflow를 참고하길 바란다.

이제 테스트를 누르면 아래와 같은 이상한 창이 뜬다.

권한검토를 눌러주자.

확인되지 않은 앱이라는 경고가 나오는데, 어차피 내가 짠 코드니까 따로 검사는 필요없다.

고급설정으로 들어가 제일 아래쪽의 링크를 눌러준다.

그리고 원래의 시트로 들어가면 아래와 같이 하나의 행이 추가된 것을 볼 수 있다.

A11 셀에 트리거를 지정했으니 이 셀에 함수 실행을 위한 체크박스를 하나 만들어준다.

그리고 체크박스를 한번 클릭해보자.

체크박스를 클릭하면 함수가 실행된 후 다시 체크가 해제되는 것을 볼 수 있다.

일단 대충 틀은 만들어졌으니 qr코드를 만들어보자.

5. qr코드 만들기

qr코드는 python의 qrcode를 이용해자.

라이브러리가 없다면 pip를 이용해 qrcode를 설치해준다.

pip install qrcode

qrcode의 사용법은 무척 직관적이고 단순하다.

make와 save면 qr코드가 뚝딱이다.

import qrcode

checkqr = qrcode.make('https://naver.com')
checkqr.save('./qr.png')

이 라이브러리를 알게된 뒤로 파이썬으로만 qr코드를 만들고 있다.

어쨌든 우리는 자리 번호에 대한 qr코드를 만들것이므로 for문을 돌면서 qr코드를 만들면 된다.

위에서 입력했던 주소를 활용하자.

import qrcode

for i in range(1,44):
    url = f"https://docs.google.com/forms/d/e/1FAIpQLScXgTn9UK5HETv3fCipGiRMl-et9IezB1OSZp2r9-Nj8b54Fg/viewform?usp=pp_url&entry.1738725719={i}"
    qr = qrcode.make(url)
    qr.save(f"./qr/{i}번.png")

이렇게 하면 내가 원하는 자리번호까지의 qr코드가 제작된다.

그런데 이걸 또 이용하려면 양식을 갖춰야 한다.

qr코드를 어떻게 활용하는지, 출석체크를 어떻게 하는지 등에 대한 설명이 함께 있어야 한다는 것.

이것은 또 pywin32를 이용해 해결했다.

이전과 마찬가지로 자리 번호, 그림이 들어갈 자리에 미리 누름틀을 만들어 놓는다.

그리고 해당 코드를 실행한다.

import win32com.client as win32
import os
import time

hwpdir = os.getcwd()
hwpfiles = hwpdir + "/2.hwpx"

maxnum = 43 # 원하는 자리번호까지

hwp = win32.gencache.EnsureDispatch('HWPFrame.HwpObject')
hwp.RegisterModule("FilePathCheckDLL", "FilePathCheckerModule")
hwp.Open(hwpfiles)
field_list = [i for i in hwp.GetFieldList().split('\x02')] 
time.sleep(0.5)
hwp.MovePos(3)
hwp.Run('SelectAll')
hwp.Run('Copy')
hwp.MovePos(3)

for i in range(0,maxnum):
    hwp.MovePos(3)
    hwp.Run('Paste')
    hwp.MovePos(3)
    
for page in range(0,maxnum):
    hwp.PutFieldText(f'자리번호{{{{{page}}}}}', page+1)
    hwp.MoveToField(f'qr코드{{{{{page}}}}}')
    hwp.InsertPicture(f'{hwpdir}/2qr/{page+1}번.png', True, 1, False, False, 0, 60, 60)
   al
hwp.SaveAs(hwpfiles+".hwpx")
hwp.Quit()

이렇게 하면 내가 원하는 번호까지의 좌석에 붙일 한글 문서가 한번에 완성된다.

그냥 A4 용지에 뽑으면 크기가 너무 크니, 페이지 분할을 이용해 인쇄하면 된다.

6. 사용 후기

해당 방식을 4월에 고안해 7월까지 잘 썼다.

설문 결과가 739명이나 있어서 동아리 학생들과 함께 야자 참여학생의 추이와 선호좌석 통계를 내어보기도 했다.

2학기 때 사용하지 않은 이유는 1~3학년의 야간자율학습이 통합되면서 명단에 변경이 생겨서이다.

또한 내가 업무 담당자가 아니기에, 다른 학년이 통합 운영하는 업무에 개입하기도 쉽지 않았다.

내년에는 양식을 조금 더 이쁘게 만들어서 업무 담당자에게 권해볼 생각이다.

올해도 잘 사용했으니 내년에도 이용할 확률이 높을 듯 하다.

학교의 많은 부분이 자동화되길 기대해본다.