엑셀을(스프레드시트) 데이터베이스로 활용하기 - follow(6)

해당 포스트의 제목을 무엇으로 해야 이 프로젝트가 필요한 사람들이 쉽게 찾을 수 있을 지에 대한 살짝의 고민 결과 엑셀을 데이터베이스로 활용하기 라는 제목을 붙였다.

 

해당 프로젝트는 실제로 엑셀 그 자체를 데이터베이스로 활용하기 위해 만든 프로젝트라고 해도 과언이 아니다. 물론 데이터베이스를 구축하고 SQL을 사용하여 해당 프로젝트를 손쉽게 운용할 수 있다. 하지만 그렇게 하지 않은 이유는 2가지였다.

 

- 데이터 양이 적음.

  절대적인 데이터의 크기가 크지 않았다.

- 잦은 엑셀화

  해당 데이터들은 이미 엑셀화 되어있었고, 또한 매번 엑셀 파일을 만들어야 했다. 따라서 만약 데이터베이스를 사용한다면 매번 엑셀파일화 시켜주어야 했을 것이다.(간단하다 하지만 실 사용자는 프로그램을 잘 다루지 못한다는 점이 컸다.) 그럴 바에는 직접 엑셀을 가져다가 그 속에 데이터를 참조하고, 또 그 엑셀파일에 직접 기입하는 것은 어떨까라는 생각에서 시작한 프로젝트이다.

 

실제로 블로그에 포스팅하지는 않겠지만 엑셀파일 내부에는 많은 함수들과 여러 다양한 내용들이 함께 들어가있다. 즉, 정해진 엑셀파일의 양식에서 특정 셀을 참조하거나 수정해야 하는 상황에서 만든 프로젝트이다.

 

이전 포스팅에서는 스프레드시트의 웹앱배포, 스크립트를 사용한 셀 읽기와 쓰기에 대해 자세하게 다뤘다. 해당 내용이 필요한 사람들은 이전 게시물들을 보기 바란다.

 

이번 포스팅에서는 스크립트를 활용한 문자열 넘기기에 대해 말하고자 한다. 사실 어거지로 한 방법이고, 좀더 깔끔한 내용이 분명 있을 텐데 모르겠다... 찾아도 나오지 않아 우회하는 방법을 사용하였다.

 

follow 사이트 내부에 특정 방식으로 접근하면 다음과 같은 화면이 존재한다.

 (해당 프로젝트는 교회 출석을 기입하기 위해 만든 프로젝트이다.)

해당 창은 개인의 이름이 선택된 상태이다. 즉, 개인정보가 식별되어있다.

그 상태에서 글을 입력 후 쓰기 버튼을 누르면 문자열이 전달된다. 해당 화면의 HTML코드는 다음과 같다.

 

<div id="pray" class="container">
    <div class="form-group">
      <!-- 기도제목을 적는 <div> -->
      <!-- <label>: 폼의 양식에 이름을 붙이는 태그, for를 이용해서 label의 for 값과 id값이 같으면 연결, 주로 연결된 양식에 입력할 때 쓰거나 체크 할떄 씀 => label 이름과 id 값이 같으면 label를 클릭해도 id로 연결됨 -->
      <label for="comment">기도제목(번호를 매겨주세요. 바로 기도편지로 작성됩니다.)</label>
      <span style="color:#aaa;" id="counter">(0 / 최대 200자)</span>
      <!-- textarea: 실제 작성하는 곳 -->
      <textarea class="form-control" rows="8" id="comment" placeholder="ex. 1. 기도제목"></textarea>
      <br>
       <!-- 쓰기 버튼 가운데 정렬 -->
       <div class="text-center">
          <button type="button" class="btn btn-success write" id="write" name="write">쓰기</button>
      </div>
    </div>
   </div>

 

그리고 자바스크립트 단의 ready 안에 다음과 같은 코드를 삽입하여 글자수 제한을 두었다.

 

 $('.form-control').keyup(function (e){
            var comment = $('#comment').val();
            comment=comment.replace(/[\r\n\x0B\x0C\u0085\u2028\u2029]+/g," ");
            comment=escape(comment);
            $('#counter').html("("+comment.length+" / 3927)");    //글자수 실시간 카운팅
 
 
        });
 

화면에 대한 자세한 설명은 생략하겠다.

다만 여기서 사용된 것은

- 텍스트 작성 공간 만들기

- 버튼 생성

- 입력 전 글씨 써져있게 하기(뭐라 부르지..?)

- 글 자 수 실시간 체크

 

이 정도 기능이다. 자세한건 코드를 살펴보길 바란다. HTML을 설명하려 이 글을 쓰는 것이 아니니까 말이다...

 

요점은 이것이다. 저 쓰기 버튼을 클릭하면 실행되는 함수! 그 함수는 스프레드 시트를 참조하여 원하는 칸에 문자열값을 그대로 작성해 준다. 그 함수는 다음과 같다.

 

      function write(){
        $("#loading").show();
        var comment = $('#comment').val();
        comment=comment.replace(/[\r\n\x0B\x0C\u0085\u2028\u2029]+/g," ");//엔터제거 주소뒤에 붙여 post하기 때문에 엔터가 존재하면 안됨
        comment=escape(comment);//url로 넘기기 때문에 comment를 url로 넘길 수 있게 encoding 해주고 넘기면 된다
        var Params='?action=update3';
        Params += '&name='+selectedname;
        Params += '&grade='+comment;
        Params += '&date=pray';
      //만약 url길이가 3969를 넘어가면 -노가다로 이게 한계치인걸 알아냈음.. 노가다에게 찬사를 - 경고창을 띄운다. 안넘었으면 작동.
        if(Params.length<3969){
        $.post('https://script.google.com/macros/s/자기스크립트웹앱배포주소/exec'+Params, {
            action : "update3"
          }).done(function(){
            $("#pray").hide(); ->>>여기서부터
            $("#field2").show();
            $("#loading").hide();
            if(codyname=="b"){
              $("#field2").hide();
              $("#choiceview_notleader").show(); -->>>여기까지는 신경 안써도 된다.
            }
            console.log("완료");
            document.getElementById("comment").value='';
          }).fail(function(){
            console.log("Fail to load\nError code: ");
          });
        }
        else{
          $("#loading").hide();
          alert("너무 깁니다..ㅠㅜ");
        }
      }
 

 

스크립트 파일의 웹앱 배포 방법은 해당 카테고리의 3번 게시물을 참조해주길 바란다.

https://rollingpig.tistory.com/3

스프레드시트 스크립트편집기를 이용하여 데이터 받아오기 - follow(3)

 

그리고 지난 게시물에서 스크립트 파일의 전체를 올렸으니 이또한 필요하면 참고바란다.

 

위 코드의 핵심은 다음과 같다.

1. 입력받은 문자열을 url전달 가능한 형태로 바꾼다.

   이때 엔터를 제거한 이유는 내가 사용할 목적에 맞추어 엔터를 없앤 것이다. 즉, url 포맷과 전혀 상관 없다. 원한다면 삭제하여야 할 것이다.

2. 원하는 형태의 url을 만든다.

   저번 게시물의 설명을 반복하자면 https://script.google.com/macros/s/자기스크립트웹앱배포주소/exec 뒷 단에 붙어있는 '?action=update3' 를 통해 스크립트 파일의 update3를 실행시키고, 이 때 함께 넘기는 인자(args)로 '&name='+selectedname, '&grade='+comment, '&date=pray'  를 넘기는 것이다.

 해당 프로젝트는 여러모로 대충 만들었다..ㅎㅎㅎ 이전에 만든 함수를 재사용하면서 변수명을 바꾸지 않고 그냥 냅뒀는데.. grade는 작성된 내용을 넘길 때 사용했고, date는 해당 셀을 찾는 이름 즉 cell name을 찾을 때 사용하기 위해 넘겼다. 변수명에서 헷갈리지 않기를 바란다..ㅠㅠ (만들때 좀 귀찮았다... 이렇게 블로그에 올릴줄 알았으면 맞출 걸 그랬다.)

 

그러면 스크립트 파일의 update3는 어떻게 되어있는지 보자. (전체 스크립트 파일은 이전게시물에 올려두었다.)

 

function update3(e)
{
  var name = e.parameter.name;
  var date = e.parameter.date;
  var pray = e.parameter.grade;
 
  var names = activesheet.getRange(2,5,activesheet.getLastRow()).getValues();
  var dates = activesheet.getRange(1,6,1,activesheet.getLastColumn()).getValues();
  Logger.log(dates);
  for(var i=0;i<names.length; i++)
  {
    if(names[i]==name)
    {
      for(var j=0;j<dates[0].length;j++)
      {
       if(dates[0][j]==date)
       {
         Logger.log(j);
        activesheet.getRange(i+2,j+6).setValue(pray); 
       }
      }
    }
  }
   return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
}

코드를 보기 위해서는 엑셀 파일이 어떻게 되어있는지 알아야 할 것 같다.

엑셀파일의 형태를 보자!

 

 

위에 모든 코드들은 엑셀파일의 pray 부분에 name을 찾아 입력받은 내용을 기입하는 코드들이다.

웹 상에서 comment 를 통해 문자열을 받아왔고, 해당 문자열을 Parmas를 통해 url로 변환시켜 스크립트 코드로 넘겼다.

스크립트 코드상에서는 이름(name)과 행 이름(pray, 대충만들어서 변수명은 date이다..ㅠ), 그리고 입력할 값(문자열이다. 변수명은 pray이다.)을 받아왔다. 즉, 다음과 같다.

 

  var name = e.parameter.name; -> 입력할 사람의 이름

  var date = e.parameter.date; -> 이 값은 "pray"이다. 즉, 입력할 행의 이름.

  var pray = e.parameter.grade; -> 셀에 넣을 값이다.

 

이 상태에서

 var names = activesheet.getRange(2,5,activesheet.getLastRow()).getValues();

 var dates = activesheet.getRange(1,6,1,activesheet.getLastColumn()).getValues();

 

이 코드들은 각각 이름이 입력된 행 전체를 names에 넣는 작업과(첫 줄은 행 이름이기 때문에 2번째 줄부터 가져온다는 2, 왼쪽에서 5번째에 있다는 의미의 5, 가장 밑 단의 숫자를 위한 activesheet.getLastRow() 이다. 해당 Range를 선택 한 후 getValues를 통해 가져온다.) 첫 열 전체를 dates에 넣는 작업(첫 줄을 가져오기 때문에 1, 왼쪽부터 6번째 부터 가져올 것이기 때문에 6, 그 뒤에는 특정 셀 위치를 입력하기 위해 인자가 2개 들어간다. 1, 가장마지막 셀 까지의 range가 저장된다.)을 한다고 볼 수 있다.

 

getRange에 대한 정리를 하도록 하겠다.

getRange(열 번호, 행 번호, 열 번호, 행번호) 해당 인자들을 통해 앞에 두 열과 행번호로 선택된 셀에서 부터 뒤에 두 열과 행 번호로 선택된 셀까지의 범위를 가져올 수 있다. 이 때 앞과 뒤의 열번호가 같으면 뒷단의 열을 생략할 수 있다.

 

그림으로 표현하자면

다음과 같이 변수에 저장한 것이다.

그 뒤

 for(var i=0;i<names.length; i++)

  {

    if(names[i]==name)

    {

      for(var j=0;j<dates[0].length;j++)

      {

       if(dates[0][j]==date)

       {

         Logger.log(j);

        activesheet.getRange(i+2,j+6).setValue(pray); 

       }

      }

    }

  }

해당 코드를 통해 이름이 같은 사람을 찾고, dates중 값이 pray(date에 'pray'값을 넘겼다.) 인 곳을 찾아 해당 셀에 입력받은 값을 적어 넣는다.

 

문자열을 넘기는 것은 이전 포스팅에서 한것과 동일하다. 그냥 똑같이 넣어주면 된다. 다만 유의점은 다음과 같다.

 

- 내가 한 방식은 url로 값을 넘기는 방식이다. 이때 특수문자를 그대로 넘기면 안된다.

  이를 위해서 escape() 함수가 사용되었다. 해당 함수는 문자열을 url로 넘길 수 있게 바꾸어준다.

- 이 때 url길이에 한계가 있다.

  총 길이가 4000자로 한계가 있는 것으로 보인다. 이보다 더 긴 문자열을 넘기는 방법은 구글링을 통해 알아보는 것을 추천한다..(필자는 찾아보았으나 찾지 못하였다.) 나는 그정도로 긴 문자열을 넘길 일이 없어 그냥 url로 인자를 넘기는 방법을 택하였다.

 

이번 게시물을 통해 POST를 하는 방법과 형태에 대한 개괄적인 마무리가 된 것 같다. 숫자, 특정한 문자열값, 입력받은 문자열값 등 원하는 대로 입력 할 수 있을 것이다. 해당 작업은 웹단에서 컨트롤 해도 될 것이며 스크립트 단에서 컨트롤 해도 될 것이다.

 

필자는 함수를 3개로 나누었지만 멍청한 선택이였다.. 함수는 하나만 만들고 인자로 입력할 값을 넘기는 형태를 취하는 것이 나았을 것 같다. 다시금 말하지만 사전 지식이 없었고, 설계가 안된 상태로 기능들을 추가하며 만들다 보니 알면서도 그냥 대충 한 부분이 너무 많다..ㅜㅜ 너그러운 마음으로 넘어가주길 바란다.