스프레드시트 스크립트편집기를 이용하여 데이터 쓰기(웹) - follow(5)

본 게시물은 웹에서(꼭 웹이 아니여도 된다. Java나 Python에서도 같은 형식으로 작동한다.) 스크립트편집기를 통해 스프레드 시트에 접근하여 글을 작성하는 기능을 포함하고 있다.

 

프로젝트 follow에서 스프레드시트(엑셀파일)에 글을 쓰는 부분은 총 3부분이 있다.

 

- 출석 여부에 따라 O,X를 기입하는 부분

- 출석한 사람 수에 따라 숫자를 기입하는 부분

- 작성한 글을 특정 셀에 기입하는 부분

 

이 3가지 예시를 통해 원하는 문구를 기입하거나 프로그래밍 된 변수를 기입하거나 문자열값을 전달하여 기입하는 동작을 할 수 있을 것이다.

 

위에서 부터 차례대로 하나씩 보도록 하자.

 

먼저 이 프로그램을 통해 하고자하는 동작을 설명하도록 하겠다.

입력할 스프레드 시트는 다음과 같이 생겼다.

형태를 잘 보길 바란다.

 

위 사진과 같은 엑셀표에 날짜에 맞춰 O 또는 X를 기입하는 것이 목표이다.

즉, 출석한 사람의 이름을 통해 그 사람의 열을 찾고, 현재 날짜를 찾아가 해당 셀에 O 또는 X를 입력한다.

 

이를 위한 웹코드는 다음과 같다.

 

      function submit(){
        $("#loading").show();
 
 
        //리더로 로그인 한 경우
        var checkedValue = null;
        //앞서 add_div에서 생성한 class checkboxes를 가져옴
        var inputE = document.getElementsByClassName('checkboxes');
        var number = 0;
        var today = new Date();
        var dd = today.getDate();
        var mm = today.getMonth()+1; //January is 0!
        var yyyy = today.getFullYear();
        var yy=yyyy.toString();
        var numofE = inputE.length; //길이를 가진 변수. 이걸가지고 전체가 다 끝났는지 판별한다.
 
        //날짜를 구글 스프레드 시트에 전달하기 위해서 알맞는 형태로 변환하는 기능
        if(dd<10) {
            dd='0'+dd
        }
        if(mm<10) {
            mm='0'+mm
        }
        yy=yy.substring(2);
 
        //생성된 체크박스들을 순차적으로 방문 하면서 체크된 객체와 되지 않은 객체를 구분
        for(var i=0;inputE[i];++i){
          $("#loading").show();
          if(inputE[i].checked){
            checkedValue = inputE[i].value;
            console.log(checkedValue);
            number++;
 
            //체크가 된 객체의 경우 이름과 학년(0)과 날짜를 post로 구글 스프레드 시트에 넘겨서 True로 저장함
            var Params='?action=update';
            Params += '&name='+checkedValue;
            Params += '&grade=0';
            Params += '&date='+yy+'년'+mm+dd;
            $.ajax({
                url : 'https://script.google.com/macros/s/자기주소/exec'+Params,
                type : 'POST',
                tryCount : 0,
                retryLimit : 100,
                success : function(json) {
                    //do something
                    numofE = numofE-1;
                    if(numofE==0){
                      $("#loading").hide();
                    }// 하나씩 감소시키다가 0이 되면 다 끝났다는 얘기니까 이때 로딩바를 하이드 시킨다.
                },
                error : function(xhr, textStatus, errorThrown ) {
                    if (textStatus == 'timeout') {
                        this.tryCount++;
                        if (this.tryCount <= this.retryLimit) {
                            //try again
                            $("#loading").show();
                            $.ajax(this);
                            return;
                        }
                        return;
                    }
                    if (xhr.status == 500) {
                        //handle error
                        this.tryCount++;
                        if (this.tryCount <= this.retryLimit) {
                            //try again
                            $("#loading").show();
                            $.ajax(this);//만약에 에러생기면 다시한다 100번까지!@
                            return;
                        }
                        return;
                    } else {
                        //handle error
                        this.tryCount++;
                        if (this.tryCount <= this.retryLimit) {
                            //try again
                            $("#loading").show();
                            $.ajax(this);
                            return;
                        }
                        return;
                    }
                }
            });
          }
 
          //체크가 되지 않은 놈들도 넘겨서 구글 스프레드 시트에 False로 저장함
          if(!inputE[i].checked){
            checkedValue = inputE[i].value;
 
            console.log("체크안됨 "+checkedValue);
            var Params='?action=update2';
            Params += '&name='+checkedValue;
            Params += '&grade=0';
            Params += '&date='+yy+'년'+mm+dd;
 
 
            $.ajax({
                url : 'https://script.google.com/macros/s/자기주소/exec'+Params,
                type : 'POST',
                tryCount : 0,
                retryLimit : 100,
                success : function(json) {
                    //do something
                    numofE = numofE-1;
                    if(numofE==0){
                      $("#loading").hide();
                    }
                },
                error : function(xhr, textStatus, errorThrown ) {
                    if (textStatus == 'timeout') {
                        this.tryCount++;
                        if (this.tryCount <= this.retryLimit) {
                            //try again
                            $("#loading").show();
                            $.ajax(this);
                            return;
                        }
                        return;
                    }
                    if (xhr.status == 500) {
                        //handle error
                        this.tryCount++;
                        if (this.tryCount <= this.retryLimit) {
                            //try again
                            $("#loading").show();
                            $.ajax(this);
                            return;
                        }
                        return;
                    } else {
                        //handle error
                        this.tryCount++;
                        if (this.tryCount <= this.retryLimit) {
                            //try again
                            $("#loading").show();
                            $.ajax(this);
                            return;
                        }
                        return;
                    }
                }
            });
 
 
          }
        }
 
        var Params='?action=add';
        Params += '&leadername='+name;
        Params += '&number='+number;
        Params += '&date='+yy+'년'+mm+dd;
        $.post('https://script.google.com/macros/s/자기주소2/exec'+Params, {
            action : "add"
          }).done(function(){
            //여기서도 지우는게 존재 해야함. 코디가 출석 체크 하고 리더관리 들어가서 리더 선택하고 출석 사이트 나올떄 초기화 해야함
            var k=document.getElementById("field");
            console.log(" :"+listid);
            for(var i=0;i<listid*3;i++){
              k.removeChild(k.lastChild);
            }
            listid=0;
            console.log("완료");
            $("#loading").hide();
            $("#field").hide();
            if(bool==2)
            {
              //리더가 아닌경우 choiceview_notleader를 보여줌
              if(pe!=1){
                codyname="b";
              }
              pe=0;
              $("#choiceview_notleader").show();
            }
            else {
              //리더인 경우 choiceview를 보여줌
              selfcheck(name);
              $("#choiceview").show();
            }
          }).fail(function(){
            console.log("Fail to load\nError code: ");
          });
          //number가 누적되지 않게 초기화 해줌
          number=0;
          if(codyname!='a'&&codyname!='b'){
            name = codyname;
          }
      }

코드가 길다... 욕심내서 함수 전체를 올리다보니 길어졌다. 해당 함수에는 저~~ 위에서 설명한 두번째 기능인 전체 출석인원을 카운트하여 스프레드 시트에 입력하는 부분도 포함되어 있다. 하지만 지금은 일단 O, X를 입력하는 것을 중점으로 봐보자.

 

이 코드의 부분부분을 통해 설명하도록 하겠다.

먼저

var inputE = document.getElementsByClassName('checkboxes');

해당 코드는 출석한 사람들의 목록을 받아오기 위한 코드이다. 

 

for(var i=0;inputE[i];++i){

 

즉, 받아온 목록을 한바퀴 돌려 작동한다.(리더당 조원수는 8명 가량 된다.)

 

if(inputE[i].checked){

            checkedValue = inputE[i].value;

 

만약 체크가 되있다면 그 사람은 출석한 사람이므로 O를 넣어줄 것이다.

 

O를 넣어줄 위치는 현재 날짜를 기반으로 한다. 정해진 형태에 맞추어 현재 날짜를 변경하였다.

(// 참고 : 스프레드 시트상의 date형식과 JavaScript단의 date형식을 맞춰주어 해결하는 것이 더 깔끔한 방법일 것이다. 하지만 몇번의 시도를 하였으나 date형식에서 equal 판별이 잘 되지 않아 우회하여 문자열 방식으로 변경하였다.)

 

var Params='?action=update';

            Params += '&name='+checkedValue;

            Params += '&grade=0';

            Params += '&date='+yy+'년'+mm+dd;

 

Params는 매우 중요하다. 우리가 사용할 스크립트 url뒤에 해당 문자열을 붙여 파싱해주면 스크립트단에서 받을 수 있다. 즉, 코드에서 코드로 넘어가는 연결다리인 것이다.

 

name은 출석한 사람의 이름을 넘겼고, grade 같은 경우 지금은 사용하지 않아 0으로 통일시켰다. 그리고 해당 코드를 통해 현재 날짜를 원하는 포맷의 문자열로 변경시킨다. 예를 들면 오늘인 2019-07-26 은 19년0726이 된다.

이를 

$.ajax({

                url : 'https://script.google.com/macros/s/자기주소/exec'+Params

                type : 'POST',

                tryCount : 0,

                retryLimit : 100,

                success : function(json){

해당 코드를 통해 붙여준다. 잘 보면 이전과 다르게 type이 POST인것을 알 수 있다. 전에는 GET이었다! (물론 $get이긴 했지만 동일한 동작을 하는 코드이다.)

 

자 그렇다면 저 스크립트 파일은 어떻게 되어있는지 보자.

 

 

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/자기주소/edit");
 
var activesheet = ss.getActiveSheet();
 
function doPost(e){
  var action = e.parameter.action;
 
  Logger.log("here1");
  if(action == 'update'){
    Logger.log("here2");
    return update(e);
  }
  else if(action == 'update2'){
    return update2(e);
  }
  else if(action == 'update3'){
    return update3(e);
  }
}
 
function doGet(e){
 
var action = e.parameter.action;
 
  if(action == 'update'){
    return update(e);
  }
  else if(action == 'update2'){
    return update2(e);
  }
  else if(action == 'update3'){
    return update3(e);
  }
  else if(action == 'getItems'){
    return getItems(e);
  }
}
 
function update(e)
{
  var name = e.parameter.name;
  var date = e.parameter.date;
  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("O"); 
       }
      }
    }
  }
   return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
}
 
function update2(e)
{
  var name = e.parameter.name;
  var date = e.parameter.date;
  Logger.log(name);
  Logger.log(date);
  var names = activesheet.getRange(2,5,activesheet.getLastRow()).getValues();
  var dates = activesheet.getRange(1,6,1,activesheet.getLastColumn()).getValues();
 
  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("X"); 
       }
      }
    }
  }
   return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
}
function update3(e)
{
  var name = e.parameter.name;
  var date = e.parameter.date;
  var pray = e.parameter.grade;
  Logger.log(name);
  Logger.log(date);
  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);
}
function getItems(e){
  
  var date = e.parameter.date;
  var name = e.parameter.name;
  var dates = activesheet.getRange(1,6,1,activesheet.getLastColumn()).getValues();
  var names = activesheet.getRange(2,2,activesheet.getLastRow()).getValues();
  var records={};
      record  = {};
  var data = [];
  var str;
  LABEL:
  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)
       {
         if(activesheet.getRange(i+2,j+6).getValue()==''){
           str='no';
           break LABEL;
         }
         else{
           
           str='yes';
           
           break;
         }
       }
      }
    }
  }
  record['ischecked']=str;
  data.push(record);
 
 
  records.items = data;
  var result=JSON.stringify(records);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

길다!! 왜냐하면 스크립트 파일 전문을 가져왔기 때문이다..

스크립트를 통해 POST하는 방법은 구글링을 하여도 잘 나오지 않는다. 그러므로 해당 코드를 잘 분석하여 사용해보길 권장한다.

 

정말 정말 중요한 코드가 맨 위 두 줄이다.

 

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/자기주소/edit");

var activesheet = ss.getActiveSheet();

 

이 코드를 통해 시트를 액티브시켜야 한다. 이때 해당 코드로 액티브되는 시트는 제일 앞에 있는 시트이다.

만약 제일 앞에 있는 시트가 아닌 두번째 또는 그 이상의 시트로 접근하여 POST를 하고 싶다면... 어떻게 하는지 모른다.

누군가 알게되면 알려주면 좋겠다.. 필자는 방법을 찾지 못해 그냥 엑셀파일을 나눴다...ㅋ

 

우선 POST를 하였기 때문에

function doPost(e){

  var action = e.parameter.action;

  if(action == 'update'){

    Logger.log("here2");

    return update(e);

  }

  else if(action == 'update2'){

    return update2(e);

  }

  else if(action == 'update3'){

    return update3(e);

  }

}

이게 먼저 실행된다.

 

우리는 action을 update로 넘겼다. 그렇기 때문에 update가 실행될 것이다.

 

function update(e)

{

  var name = e.parameter.name;

  var date = e.parameter.date;

  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("O"); 

       }

      }

    }

  }

   return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);

}

 

바로 이것이다.

코드를 주목하자. Params를 통해 넘긴 name과 date를 e.parameter.name과 같이 받아왔다.

names와 dates는 엑셀파일에서 이름들이 담긴 열과 날짜들이 담긴 첫번째 행을 가져온 것이다.

이중 for문을 통해 적절한 칸을 찾아 .setValue()메소드를 가지고 O를 입력한다.

 

 

자, 흐름이 이해가 되었는가??

X의 경우 update2를 통해 작동한다. 똑같은 동작을 입력문구만 살짝 바꾸어 만들었다..

이것도 사실 Params를 통해 O,X를 넘긴 뒤 그 받은 값을 입력하게 하면 될 일인데.. 수정하기 귀찮아서 안바꾸었다ㅋㅋ

 

 

웹단의 코드를 잘 보면 이런 코드가 있다.

 var Params='?action=add';

        Params += '&leadername='+name;

        Params += '&number='+number;

        Params += '&date='+yy+'년'+mm+dd;

        $.post('https://script.google.com/macros/s/자기주소2/exec'+Params, {

            action : "add"

          }).done(function(){

          }

해당 코드는 다른 스프레드 시트에 그 조의 출석한 사람 수를 입력해주는 코드이다.

이 코드는 다른 스크립트파일을 참조해서 그 스크립트 파일의 add 함수를 실행시킨다.

add 함수는 다음과 같다.

 

function add(e)
{
  var number = e.parameter.number;
  var leadername = e.parameter.leadername;
  var leadernames = activesheet.getRange(2,5,activesheet.getLastRow()).getValues();
  var numbers = activesheet.getRange(2,6,activesheet.getLastRow()).getValues();
  var date = e.parameter.date;
  var dates = activesheet.getRange(1,6,1,activesheet.getLastColumn()).getValues();
  for(var i=0;i<leadernames.length; i++)
  {
    if(leadernames[i]==leadername)
    {
      for(var j=0;j<dates[0].length;j++)
      {
        if(dates[0][j]==date)
        {
         activesheet.getRange(i+2,j+6).setValue(number); 
        }
      }
      
    }
  }
  return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
}

눈여겨 볼 점은 17줄에 있는 setValue(number)이다. 

우리는 setValue에 인자를 전달함으로써 원하는 대로 주물럭댄 변수들을 셀에 넣을 수 있는 것이다! 이는 변수의 종류와 상관없이 다 들어가진다.

 

 

스크립트를 사용하고 주소를 뽑아내어 적용시키는 것은 이전 포스트를 참고하길 바란다.

https://rollingpig.tistory.com/3

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

 

다음 포스팅은 웹에서 문자열을 받아 원하는 셀에 넣는 것을 포스팅 하겠다.

물론 오늘 올린 내용과 거의 동일하다. 이미 방법을 알아차린 사람들도 있을 것 같다. 하지만 몇가지 팁과 주의할 점이 있다! 다음 포스팅에서 보도록 하겠다.