* 이전에 제안했던 방법이 명단이 누출될 수 있는 보안문제가 발생해서 , 보안문제를 해결해서 다시 올려 놓습니다. 이젠 명단유출되는 일이 없을 겁니다. 김민우샘이 아이디어를 주셔서 더 간단하게 만들수 있게 되었네요

학교에서 진급반을 공개할때 개인정보 보호 때문에 전체 명단을 공개하기 어려운 경우가 있다.
합격여부라든가 교과성적을 확인한다 거나, 그밖에 예민한 사항을 본인의 이름과 개인정보만을 이용해서 확인하게 하는 방법을 만들어 보았다. 2020년 3월1일까지 공개되었던 방법에 문제가 있어 3월2일에 다시 수정해서 올려 놓는다..

일단 아래 링크 검색창에 들어가서 배정반을 검색해 보자.
https://docs.google.com/spreadsheets/d/1JlNSwMEnlutg_LiuJIQxVGw5qDWOvZ6Z0G2PjTT4SXc/

우선 구글스프레드 시트의 공유 기능을 이용해서 전체명단(명단 스프레드시트를 비공개로 따로 만들어 놓음)을 공개하지 않고 자신의 이름과 개인정보만을 이용해서 진급반을 알아낼 수 있다.

방법은 간단하다. 데이타베이스나 서버 사용하지 않아도 된다. 구글스프레드 시트 화일을 2개 만들어 사용하면 된다.


1.  일단 명단이 들어있는 배정반명단 구글스프레트 화일을 비공개로 만들어 둔다.  화일에는 배정반 명단(이름,배정반,생년월일)을 넣어 놓는다. 상황에 따라 다른 내용을 넣어도 된다.
(*단 생년월일은 셀을 선택하고 서식메뉴로 들어가서 숫자를 - 일반텍스트로 변경해 주어야 한다. 그렇지 않고 숫자로 인식되면 앞에 0이 나오지 않는다)

2. 검색할 수 있는 배정반검색 구글스프레드 시트 화일을 한개 더 만든다.

3. 배정반검색 화일에 QUERY 함수를 사용하여 이름을 검색하면 나머지 정보가 나오도록 만든다.
   (단, 동시에 여러명이 접속할 수 있으니 검색창을 2개 이상(3개정도)을 만들어 놓을 필요가 있다. 다른 사람이 검색하고 있으면 같은 셀에 입력할 수 없는 경우가 생길 수 있기 때문이다.)

4. 시트보호를 해서 다른 사람이 양식을 수정 할 수 없게 한다. 단 이름입력하는 칸은 셀제외를 해서 입력이 가능하도록 해야 한다.

5. 완성이 되면 배정반검색 구글스프레드 시트를 공유한다(링크공유 - 고급 - 링크가 있는 모든 사용자 - 수정가능 하도록)

아래 링크는 그렇게 해서 만들어 놓은 배정반검색 창이다. 링크를 타고 들어가서 마음대로 검색해 봐도 된다. 이름만 입력하게 할 수도 있고, 이름과 생년월일을 동시에 입력하게 할 수도 있다.

https://docs.google.com/spreadsheets/d/1AszSYNz9NWNLOKpaT4SBYYWKi86O9qHXH2nSrZQXiXQ/

이곳에 사용된 QUERY 함수를 간단하게 설명 드리면
QUERY(데이터, 쿼리, 헤더) 로 이루어져 있습니다.

데이터(검색할 데이터범위) 쿼리(검색 조건에 따라 해당 데이터 출력) 헤더(true면 제목줄 출력)

=QUERY(IMPORTRANGE("1mvGNY2dYRbN0mDuV0C3bfakJQPhYKNAB4EcMUZ0gl5E","A1:C8"), "SELECT Col2,Col3 WHERE Col1='"&A3&"'", true) 를 해석해 보면

1. 데이터  ( IMPORTRANGE("1mvGNY2dYRbN0mDuV0C3bfakJQPhYKNAB4EcMUZ0gl5E","A1:C8")) 배정반명단 화일 첫번째 시트 A1에서 C8 범위에 있는 데이터 사용 

배정반 명단 화일 주소는 배정반명단 화일에 가서 주소창에 주소를 아래 그림처럼 알아내면 된다.
그래서 알아낸 배정반명단 화일 주소 "1mvGNY2dYRbN0mDuV0C3bfakJQPhYKNAB4EcMUZ0gl5E"


2. 쿼리 (SELECT Col2,Col3 WHERE Col1='"&A3&"') 배정반명단 화일 Col1(A열)에서  배정반검색화일 A3(이름셀)에 입력된 이름과 같은 데이터가 있으면 그 데이터의 Col2(B열)과 Col3(C열)의 내용을 가져와 출력 - 명단화일 A열에는 이름이 B열에는 배정반이 C열에는 생일이 입력되어 있음


3. 헤더(true) - 출력할때 제목줄도 함께 출력할지 여부, 이곳에서는 제목줄도 함께 출력

따라서 노란색 이름칸에 김민서 라고 입력해서 검색하면 
=QUERY(IMPORTRANGE("1mvGNY2dYRbN0mDuV0C3bfakJQPhYKNAB4EcMUZ0gl5E","A1:C8"), "SELECT Col2,Col3 WHERE Col1='김민서', true)  함수가 작동하게 되고
그럼 배정반명단 화일 A열(이름열)에서 김민서 라는 이름이 있으면 김민서 이름 옆에 B열(배정반)과 C열(생일) 을 가져와 출력하게 된다.

배정반검색 화일 C13열에 제시해 놓은 것처럼 2개의 조건(이름,생년월일)을 입력받아 배정반을 알려 줄 수도 있다.

* 만약 처음에 수식을 입력하고 실행이 되지 않으면 아래 그림처럼 엑세스 허용을 한번 해 주어야 한다. 그럼 그 다음부터 자동으로 연결된다.

100% 만족스럽지는 않지만 구글스프레드 시트를 이용하면  데이터베이스나 서버를 사용하지 않고도 홈페이지에 링크 주소를 제공해서 간단하게 자신의 합격여부나 진급반을 검색하게 할 수 있다.

만드는 방법이 포함된 구글스프레드 시트 주소도 공개한다 참고하기 바란다

https://docs.google.com/spreadsheets/d/1rLxvt9numkfQPsciBE20aua2q9iMXb2A364ytzb_ReU/

* 한가지 문제점은 동시에 접속해 있는 상황에서는 접속한 다른사람이 내가 입력하는 것을 볼 수 있다는 점이다. 따라서 완벽한 보안은 불가능하다.
다른사람이 접속해 있는지 확인하는 방법은 내가 접속했을때 우즉 상단 로그인 옆에 현재접속해 있는 사람이 있는지 확인할 수 있다. 4개의 아이콘이 떠 있다면 나 외에 4명이 더 접속해 있다는 것이다. 아이콘이 없다면 나 혼자 접속해 있다는 것을 알 수 있다.

 

Posted by 민서아빠(과학사랑)

댓글을 달아 주세요

  1. 2020.03.03 00:22  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  2. 김혜경 2020.03.03 00:59  댓글주소  수정/삭제  댓글쓰기

    감사합니다.과학교사로서항상반성하고배우게됩니다.^^
    계속제가가진명단으로실습해도안되서보니..제명단에학생들생년월일에해당하는내용이숫자로되어있어텍스트로변경하니오류없이검색됩니다.이게원인인가조금의심스럽네요..
    궁금증이또있습니다.
    만약동시에2명의학생들이들어왔고,각각다른셀에서입력한다고가정할때, 2명학생간에는서로의정보를볼수있는건가요?^^

    • 민서아빠(과학사랑) 2020.03.03 12:10 신고  댓글주소  수정/삭제

      맞습니다. 숫자를 일반텍스트로 바꿔 주어야 합니다.
      그리고 공동작업이기 때문에 동시에 접속해 있는 학생들은 다른 학생이 입력해서 나오는 결과를 다 볼 수 있습니다.
      그래서 너무 민감한 사항은 올리면 안 될 것 같아요. ㅠㅠ

  3. 김혜경 2020.03.03 21:39  댓글주소  수정/삭제  댓글쓰기

    학생들평가결과를쉽게알려줄수있는방법이없나고민했는데...아직까진답이없네요ㅠ
    감사합니다.^^

    • 민서아빠(과학사랑) 2020.03.03 22:50 신고  댓글주소  수정/삭제

      서버와 데이타베이스를 사용하면 좋은데 그건 일상적인 방법이 아니라 아쉬운대로 구글스프레드시트를 사용했습니다. 다른 사람이 들어와 있으면 누가 들어와 있는지 알 수 있습니다. 접속해 있는 사람이 셀을 누르고 있으면 표시가 되거든요. 다른사람이 누르고 있지 않을때 점수를 확인하게 하면 됩니다. 어쨌거나 완벽한 보안은 쉽지 않습니다. ^^

  4. chris 2020.03.04 04:51  댓글주소  수정/삭제  댓글쓰기

    대단하시네요..어찌 이런걸 다......

  5. 이현주 2020.03.05 22:03  댓글주소  수정/삭제  댓글쓰기

    엑셀에서는 쿼리 함수를 지원하지 않드라고요...
    엑셀에서 구현하는 방법은 없을까... vlookup 함수로 하다가..
    동명이인 때문에 손을 내려놨네요... ^^:;;
    혹시, 엑세스에서 될까?? 문득 스치고 지나갑니다만... 흑 어려워요...

    우야동동 자료 고맙습니다.

  6. 2020.03.18 22:25  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • 민서아빠(과학사랑) 2020.03.20 09:04 신고  댓글주소  수정/삭제

      그건 아마 결과물이 여러개가 나오는데 아래쪽 무언가 다른 출력물과 겹쳐 나와서 그럴겁니다. 빈 시트에 다른것 다 지우고 결과창 하나만 만들어서 다시 시도해 보시기 바랍니다