[G Suite] 구글 설문지로 받은 응답, 자동 정렬시키기(feat. 매크로)

원격수업이 장기화 되면서 교사들은 수업 콘텐츠를 제작하고 업로드 한 다음 학생들이 수업에 잘 참여했는지 확인해 보는 방식으로 하루를 살고 있습니다. 

단지 출석률과 진도율만으로는 학생들이 수업에 제대로 참여했는지 확인이 어렵기 때문에, 몇몇 선생님들은 과제를 제시한다든가 퀴즈를 통해 형성평가를 하고 있죠.

퀴즈나 설문지 작성에는 또 구글 설문지가 딱이지 않습니까? ^^

그래서 준비했습니다. 

 

 

구글 설문지를 학생들에게 배포하면 응답 시간 순으로 스프레드시트에 자료가 작성됩니다. 

그런데 선생님들은 제출된 자료를 반별 혹은 번호순으로 자료를 정렬해서 보고 싶을 겁니다. 

그럴 때 우리는 자동으로 자료를 정렬시켜주는 구글 스프레드시트 매크로와 JavaScript 트리거를 사용할 수 있습니다.

 

1. 준비하기(구글 설문지 배포 및 응답 받기)

자동으로 자료를 정렬시키려면 우선 자료가 있어야겠죠?

저는 저희 학년을 대상으로 하는 자료를 만드느라 학년과 반을 선택하는 설문지를 제작했고 이미 자료를 받았습니다.

 

설문지 작성하기와 배포한 설문지

배포한 설문지로 응답 받은 구글 스프레드시트

설문지를 작성하고 배포하는 일은 이미 다 하셨으리라 믿습니다. 아직 안 하신 분들은 아래 글을 읽고 그것부터 해 주세요.

[G Suite][Google][설문지] Google 설문지 만들기

[G Suite][Google][설문지] Google 설문지 배포하기

 

2. 반별로 색깔 다르게 표시하기(조건부 서식)

응답받은 자료를 반별로 색생을 달리하여 구분지으려고 합니다. 

이때 필요한 명령은 조건부 서식입니다.

 

 

반을 묻는 물음에 응답한 열(위의 경우는 B열)을 선택한 후 마우스 우클릭을 합니다. 

아래쪽에 나오는 [조건부 서식]을 클릭해 줍니다.

시트 오른쪽으로 조건부 서식 규칙창이 생깁니다.

① 범위에 적용 : B열을 선택했기 때문에 자동으로 지정돼 있습니다. 만약 지정을 잘못 했다면 여기서 다시 지정해도 됩니다.

② 형식 규칙 : '옵션'으로 [텍스트에 포함]을 선택합니다. 그리고 반 이름을 한 가지 씁니다. 저는 '1반'을 썼습니다.

③ 서식 지정 스타일 : 셀에 서식을 지정해 줍니다. 저는 셀을 배경을 색으로 지정했습니다. 

④ 완료 : 조건부 서식 지정이 끝났으면 '완료'를 클릭해 줍니다.

제가 속한 학년은 반이 5반이기 때문에 조건부 서식을 5개 지정했습니다. 1반부터 5반까지.

여러분들도 소속된 반 개수에 따라 조건부 서식을 추가해 주세요. 

만약 반 이름이 숫자가 아닌 명사로 돼 있어서 상관 없습니다. [형식 규칙]에서 그에 해당하는 텍스트를 적어주면 그만입니다.

 

3. 반복되는 작업 매크로 지정하기(반, 번호순 정렬, 가운데 정렬 등)

윗부분까지는 한 번만 작업해 주면 구글 스프레드시트가 조건에 따라 알아서 색칠을 해 주는 작업이었습니다.

이제부터는 설문지를 받은 사람들이 계속 응답을 할 때마다 바꿔줘야 하는 부분이라 매번 하려면 여간 귀찮은 일이 아니죠. 

1~5반 순으로 반별로 정렬도 해야 되고, 반 안에서는 번호순으로 나열되야 누가 제출을 했는지 안 했는지 찾기 쉬울 겁니다. 또 문서가 보기 좋으려면 가운데 정렬도 해 줘야 할 거고요.

뭐, 물론 모든 학생이 다 제출된 다음 마지막에 해도 되는 작업이지만 언제 다 제출할지 누가 알까요? 

교실에서도 신청서를 제출하면 번호순으로 정리하든지 낸 사람, 안 낸 사람을 표시하지 않습니까? 그런 작업이 필요한 겁니다.

그만큼 중요하고 우리를 편하게 만들어 주는 작업입니다. 잘 따라해 주세요.

매크로 기록을 시작하겠습니다.

 

메뉴에서 [도구]-[매크로]-[매크로 기록]을 찾아서 선택합니다.

그러면 위와 같이 스프레트시트 창 가운데 하단에 [새 매크로 기록 중...]이라는 팝업이 뜰 겁니다.

이제부터 우리가 클릭하고, 쓰고, 했던 내용들이 기록되어 나중에 지정된 이름만 클릭하면 그 작업을 반복할 수 있게 됩니다. 그게 [매크로]입니다.

일단 저는 반별 그리고 번호순 정렬만 해 보겠습니다.

스프레드시트의 A:J까지 내용이 들어가 있는 모든 열을 드래그해서 영역을 지정해 줍니다.

메뉴 [데이터]-[범위 정렬]을 선택합니다.

 

범위 정렬을 지정하는 팝업이 뜨면 이제 몇 가지 옵션을 설정해 줍니다.

① 데이터에 머리글 행이 있습니다. 를 체크 선택합니다. 머리글은 1행에 있는 질문들이기 때문에 정렬 범위에 포함시키기 않게 하려고 선택하는 것입니다.

② 정렬 기준 [3학년 몇 반입니까?] : 첫 번째 기준을 정하는 것입니다. 첫번째 옵션은 '타임스탬프'였지만 우리는 시간으로 정렬할 것이 아니고, 반을 기준으로 먼저 정렬을 할 것이기 때문에 [3학년 몇 반입니까?]를 선택합니다. 그리고 A→Z로 정렬하는 오름차순으로 정렬을 선택합니다.

③ 정렬 기준 [교실에서 몇 번입니까?] :  두 번째 정렬 기준입니다. 반별로 순서대로 정렬이 된 후에 다시 반 안에서 번호대로 정렬을 하기 위해 선택하는 옵션입니다. 방법은 ②와 동일합니다.

④ 정렬 : 완료가 됐으면 [정렬]를 선택합니다.

 

다른 매크로 작업이 필요 없다면 이제 매크로를 저정해야 합니다. 

스프레드시트 하단에서 계속 우리를 기다리고 있던 [저장] 버튼을 클릭해 줍니다.

 

매크로는 위에서 설명드린 바와 같이 반복되는 작업을 하나의 명렬어로 압축시켜 놓은 함수입니다. 

그렇기 때문에 이름을 지정해 줘야 합니다. 

저는 "반번호정렬"이란 이름으로 지정했습니다.

단축키도 만들 수 있는데, 선택사항입니다. 

 

다시 [도구]-[매크로]로 이동해 보면 아까까지 없었던 [반번호정렬]이란 명령어가 하나 생긴 걸 볼 수 있습니다.

우리가 만든 매크로 명령어입니다.

이제 저 버튼만 누르면, 아까 했었던 범위 정렬에서 반, 번호 순으로 정렬을 일일이 하지 않아도 됩니다.

100여명 혹은 전교생이 다 참여하는 설문을 정리한다고 했을 때, 응답하는 시간이 모두 제각각일 겁니다.

그 때 우리 반은 몇 명이나 작성했는지 확인하려고 수동으로 반, 번호 정렬을 했는데 바로 또 새로운 응답이 추가 되면 정렬을 다시 해야 되는 번거로움이 있습니다. 만약 이 외에도 여러 가지 반복 작업이 추가 된다면 더더욱 귀찮아지죠.

하지만 우리가 만든 매크로 명령어만 클릭하면 모든 과정을 한 번에 끝낼 수 있습니다.

 

4. 스크립트 편집기-트리거(진정한 자동화)

여기서 만족하면 반만 자동인 겁니다. 

왜냐하면 명령어를 일일이 클릭해 줘야 하기 때문입니다. 

여러 명령어를 거치지 않아도 되긴 하지만 어쨌든 수동으로 우리가 명령을 해야 하기 때문에 귀찮음이 약간은 남아 있습니다.

그럴 때 활용할 수 있는 것이 JavaScript 기반의 구글 스크립트 편집기와 트리거입니다.

메뉴 [도구]-[스크립트 편집기]를 선택합니다.

 

왠지 알 수 없는, 괜히 복잡해 보이는, 그래서 실수하면 두려울 것 같은 명령문이 나옵니다.

저도 JavaScript 1도 모릅니다.

절대 우리는 이걸 건드리지 않을 겁니다.

단, 이게 뭔지는 알고 가야겠죠?

여기 있는 복잡한 영문 명령어는 우리가 만든 매크로 명령어입니다.

EXCEL VBA처럼 구글 스프레드시트에서 실행되는 Script 명령어입니다.

 

우리는 여기서 위에 아이콘 메뉴 중 시계 모양을 선택할 겁니다. 

마우스를 갖다 대면 "현재 프로젝트의 트리거"라는 설명이 나옵니다. 클릭해 주세요.

 

트리거 설정창이 나옵니다.

만약 설정창이 안 나온다면 오른쪽 하단의 파란색 버튼 [트리거 추가]를 클릭하면 됩니다.

 

트리거(Trigger)란  총 방아쇠란 뜻입니다. 

말 그대로 방아쇠를 당겨 매크로를 실행시킨다는 의미죠. 

 

여기서 선택할 건 별로 없습니다. 

우리는 응답자가 설문지에 응답을 하고 제출을 했을 때, 스프레드시트가 자동으로 정렬되길 바라는 겁니다.

그렇기 때문에 [이벤트 유형 선택]에서 "양식 제출 시"를 선택하고 [저장]을 누릅니다.

 

완료가 되면 위와 같이 트리거 설정이 추가되었다고 나옵니다.

이제 우리는 학생들이 응답을 제출할 때마다 자동으로 반, 번호 순으로 정렬된 구글 스프레드시트를 볼 수 있습니다.

참 편리하죠?

 

여기까지의 과정을 영상으로 정리해 봤습니다.

혹시 글과 이미지로 이해가 안 가신 분들은 영상으로 확인해 보셔도 좋을 것 같습니다.

---

과정이 조금 복잡했을 수 있습니다.

하지만 대부분의 일이 그렇듯 몇 번 반복하다보면 별 거 없습니다. 

트리거 설정을 시간 단위로도 바꿀 수 있지만, 양식 제출 시마다 설정해 주는 것이 더 효율적인 것 같았습니다.

시간으로 설정해 두면 아무 작업도 없을 때도 매크로가 작동을 해서 스프레드시트를 무겁게 만드는 느낌이랄까?

 

오늘도 이 글도 도움이 되셨길 바랍니다.