진급반이나 배정반 전체명단 공개하지 않고 자신것만 검색해서 알아내기

스마트러닝/기타유용한 팁

진급반이나 배정반 전체명단 공개하지 않고 자신것만 검색해서 알아내기 - 구글스프레드 시트 이용

민서아빠(과학사랑) 2020. 2. 28. 19:03

이곳 내용은 보안상 문제가 있습니다. 더이상 사용하지 마세요

이곳에 있는 프로그램은 개발자 모드에서 원본 스프레드시트 링크 주소가 나타나는 보안상 문제가 있습니다. 따라서 공부용으로만 사용하시고

* 아래 링크에 있는 이동준샘이 만든 방법을 이용해도 됩니다.

그럼 보완도 완벽하고, 작동도 어디에서나 잘 됩니다. 아래 링크에 들어가면 이동준 선생님이 동영상으로 자세하게 설명해 놓았습니다.

들어가서 따라해 봤는데 정말 좋습니다.

https://javalab.org/class/?fbclid=IwAR1FQCq02p0Oiw2X5F-z1eD7oSdQNNUYHxxJFiNJC7TmkAMnFRippHkYJD4

------------

* 김민우샘이 아이디어를 주셔서 더 간단하게 만들수 있게 되었고
이상훈샘이 아이디어를 주셔서 구글사이트 도구를 이용해서 거의 완벽하게 검색할 수 있게 되었습니다.

그런데 구글 사이트도구에서 명단이 노출되는 보안상의 문제가 발견되었습니다.

<구글스프레드 시트 이용하는 방법>

학교에서 진급반을 공개할때 개인정보 보호 때문에 전체 명단을 공개하기 어려운 경우가 있다.

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

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

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

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

1. 일단 명단이 들어있는 배정반명단 구글스프레트 화일을 비공개로 만들어 둔다. 화일에는 배정반 명단(이름,생년월일,배정반)을 넣어 놓는다. 상황에 따라 다른 내용을 넣어도 된다.

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

(*단 생년월일은 셀을 선택하고 서식메뉴로 들어가서 숫자를 - 일반텍스트로 변경해 주어야 한다. 그렇지 않고 숫자로 인식되면 앞에 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명이 더 접속해 있다는 것이다. 아이콘이 없다면 나 혼자 접속해 있다는 것을 알 수 있다.

보기에는 복잡해 보이지만, 간단하게 만들 수 있다.
우선 위에 내용 보면서 조금씩 시도해 보기 바란다.

<추가 - 구글 사이트도구 이용해서 배정반 검색하는 방법>

* 이긍정 샘이 구글사이트 도구를 이용하는 경우 명단이 노출되는 문제점을 찾아서 알려 주셨네요.
문제점을 다 해결한 줄 알았는데, 구글 사이트의 경우 소스보기를 통해 접근하면, 전체 명단과 내용이 노출 될 수 있습니다.
현재로서는 완벽하게 보안이 되질 않는 문제점이 생겼습니다.
더 공부해 봐야 겠습니다.
당분간 이 방법은 완벽한 보안이 되지 않으므로 권장하지 않습니다.

이상훈 선생님께서 구글사이트를 이용해서 구글스프레드시트의 문제점을 해결하는 방법을 제시해 주셨네요. 초보자에게는 조금어려울 수 있지만 한번 도전해 보셔도 좋을 듯 합니다.

https://www.notion.so/947b0910465646b1a5444cccf5500943

구글 사이트 도구 이용해서 검색하는 방법은 우선 아래 게시된 주소에 들어가서 테스트 해보기 바란다.

https://sites.google.com/view/sciencelove2/

이름 : 김민서, 생년월일 : 080301

아래는 이상훈 선생님의 설명서를 참고로 해서 조금 더 쉽게 다시 만들어 본 설명서 입니다.

구글 사이트로 검색창을 만들면, 구글스프레드시트로 검색창을 만들었을 때 동시에 사용하지 못하는 문제와 보안 문제를 해결할 수 있다.
구글사이트 만드는 방법도 아주 간단하다. 한번 도전해 보기 바란다.

위 설명에서 배정반명단만 그대로 사용하면 된다. 배정반 검색은 구글스프레드시트로 만들필요 없이 아래 방법대로 구글사이트로 만들면 된다. 이미 위 방법대로 배정반을 검색하게 만들었다면 구글사이트 도구로도 만들어 보자.

1. 우선 구글드라이브에 가서 새로만들기-더보기-구글사이트 도구를 선택한다.

2. 제목을 적당하게 적고, 우측에 삽입 – 삽입을 누르고 삽입창이 열리면 소스코드를 선택한다.

3. 소스코드 입력창에 아래 스크립트의 일부 내용을 수정해서 그대로 붙여넣기 하면 된다.

4. 그리고 게시를 눌러 구글사이트 주소를 만든 다음 주소를 학생들에게 공개해 주면 된다.

생각보다 상당히 간단하다.

<스크립트 삽입하는 방법>

삽입할 스크립트 : 아래 스크립트 내용중에 빨간색 부분을 적당하게 수정한 다음 복사해서 소스코드 스크립트 삽입 부분에 붙여 넣기 하면 된다.

1. 수정해야 할 부분은 미리 만들어 둔 배정반 검색 구글스프레드 시트를 보기 전용으로 공유하고, 구글스프레드 시트 주소창에서 빨간색으로 표시된 부분만 복사해서 아래 스크립트에 바꿔치기 하면 된다.

2. 이름과 생년월일을 학교 특성에 맞게 생성된 기초명단 상황에 따라 성명과 학번등으로 바꿔도 된다. 그럼 검색화면에 글자가 나온다.

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Document</title>
    <script language=javascript>
        function getPost() {
            var search = document.frmSubmit.txt1.value;
            var search2 = document.frmSubmit.txt2.value;
            if (search == '' || search == null || search2 == '' || search2 == null) {
//alert(" 값을 입력해주세요 ");

var url1; url1="https://img1.daumcdn.net/thumb/R1280x0/?scode=mtistory2&fname=http%3A%2F%2Fcfile23.uf.tistory.com%2Fimage%2F99D93C4A600E83FE2780FB" window.open(url1, "", "width=400,height=227,left=385");

            } else {
                var url;
search = encodeURI(search);
                url = "https://docs.google.com/spreadsheets/u/0/d/1mvGNY2dYRbN0mDuV0C3bfakJQPhYKNAB4EcMUZ0gl5E/gviz/tq?tqx=out:html&tq=SELECT+*+WHERE+A+=+'" + search + "'+AND+B+=+'" + search2 + "'";
                window.open(url, "", "width=400,height=400,left=600");
            }
        }
    </script>
</head>
<body>
    <form name=frmSubmit method="POST">
        이름<br>
        <input type=text name=txt1>
        <p>
        생년월일<br>
        <input type=text name=txt2>
        <p>
        <input type=button name=btn1 value="검색" target="_blank" onClick="getPost()">
    </form>
</body>
</html>

3. 완성되면 미리보기로 제대로 나오는지 확인하고, 이상이 없으면 게시를 눌러 구글사이트를 웹상에 게시하면 된다.
미리보기를 눌러서 살펴보고 다시 편집 상태로 돌아오려면 좌측상단 메뉴에 뒤로가기를 눌러야 한다. 그냥 닫기를 누르면 편집화면이 사라져 버린다.

4. 미리보기를 눌러 충분히 확인한 다음 마음에 들면 게시를 눌러 마음에 드는 주소를 적고 사이트 도구를 완성하면 된다.

그런 다음 아래처럼 주소를 학생들에게 제공하면 된다. 주소는 앞부분은 똑같고, 맨 뒷 부분(sciencelove2)만 웹에 게시할 때 적은 주소가 된다.

아래 게시된 주소에 들어가서 테스트 해보기 바란다.

https://sites.google.com/view/sciencelove2/

이름 : 김민서, 생년월일 : 123456

완성이 되면 구글사이트 삽입에서 우측에 있는 축소할 수 있는 텍스트나 그림등 다양한 자료를 이용해서 화면을 멋지게 꾸며 보자.

<주의사항>

1. 배정반명단 스프레드시트는 보기전용으로 공유해야 한다.

구글사이트에서 소스보기를 하면 배정반명단 스프레드 시트 주소를 알아 낼 수 있다. 그래서 배정반 명단을 보기 전용으로 공유할때 A열에서 C열까지 숨기기를 해서 배정반명단으로 접근해도 내용을 볼 수 없게 해야 한다.

2. 그리고 다운로드하지 못하게 공유설정에서 다운로드 권한을 해제해 놓아야 한다.

그렇지 않으면 스프레드시트를 다운로드 해서 엑셀에서 열어 보면 전체명단을 확인할 수 있다. 반드시 공유설정에서 다운로드 권한을 해제해 놓아야 안전하게 개인정보를 보호할 수 있다.

3. 스마트폰에서는 브라우저에 따라 제대로 작동하지 않는 경우가 있다. 컴퓨터에서 검색해야 정확한 결과가 나온다는 말을 추가해 주도록 하자. 어떤 경우에는 구글 반배정 스프레드 시트가 통채로 열리는 경우도 종종 발생한다. 따라서 위에 설명한 것처럼 중요한 정보는 열감추기 해서 볼 수 없도록 해야 한다. 그리고 D열 쯤에 이 화면이 보이는 학생은 컴퓨터로 접속해서 다시 확인하라는 안내 문구를 적어 주면 좋다.

그리고 컴퓨터에서도 크롬으로 접속하면 잘 보이는데, 익스플러로 접속하면 안 보이는 경우가 있다. 가능하면 크롬으로 접속해서 확인하도록 안내해 주자.

4. 게시를 한 구글사이트를 다시 수정하는 경우에는 수정을 하고 나면 반드시 다시 게시를 해 주어야, 공개된 주소에 수정사항이 반영된다.

5. 배정반 명단 셀 속성이 다른 경우에도 제대로 나타나지 않는 경우가 있다. 만약 이메일 주소가 안나오거나, 검색이 잘 안되면 아래 링크에 있는 배정반 명단을 사본복사해서 사용해 보기 바란다. 일단 복사가 되면 그대로 링크를 걸어서 제대로 되나 확인해 보고, 제대로 되면 명단을 추가하면 된다. 이메일주소와 같은 텍스트가 나오게 하고 싶으면 비고 란에 입력하면 된다.

docs.google.com/spreadsheets/d/1mvGNY2dYRbN0mDuV0C3bfakJQPhYKNAB4EcMUZ0gl5E/copy

저작자표시