본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

구글 스프레드 시트에서 데이터 입력을 위한 폼 구현 Google Apps Script

구글 스프레드시트에서 입력 폼을 구현하는 방법을 제가 만든 주식관리 시트에서 추출하여 정리해 보았습니다.
다른 용도로 활용할 수 있게 최대한 간단히 요약했습니다.

이 예제를 이용하여 구글 시트/독스등에서 입력폼을 완벽하게 구현하실 수 있습니다. 예제를 대충 수정해서 입맛에 맞게 변경하실 수도 있겠네요.

먼저 구글 앱 스크립트로 폼을 만들려면 다음 3가지가 요소가 필요합니다.
1. FORM 생성 함수 : 폼 생성 전 준비 함수
2. HTML 코드와 자바스크립트로 구성된 HTML 파일 (확장자명 html)
3. HTML FORM에서 넘어온 데이터를 처리할 함수

// @코드.gs
// 1. 폼 생성 전 준비 함수 
function createDividendForm()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // 시트의 영역을 선택하여 값을 배열 형태로 추출한다. 
  var sName = ss.getRange("종합_종목명").getValues();
  var sCode = ss.getRange("종합_종목코드").getValues();  
  var finName = ss.getRange("증권사목록").getValues();
  
  // 아래 두줄과 동일 합니다. 
  //개별 시트별로 영역을 직접 지정하려면 시트를 선택해야 하겠지요.
  // ss.getSheetByName
  //var codeSheet = ss.getSheetByName("코드");
  //var sCode = codeSheet .getRange("A2:A5").getValues();  

  // HTML 파일로 부터 폼을 불러 들인다. 
  var template = HtmlService.createTemplateFromFile("dividend_form.html");
  
  // template 에 매개변수 전달 
  template.stockNames = sName;
  template.stockCodes = sCode;  
  template.fin_Names = finName;

  var htmlOutput = template.evaluate().setWidth(400).setHeight(300);
  
  var title ="배당금 입력";
  
  // 다이얼로그로 내 보낸다. 
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
}


이 함수를 실행시키면 마지막 부분의 SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
에 의하여 시트 상에서 폼이 대화상자 형태로 표시됩니다.
HTML 파일이 표시가 되면 여기서부터는 HTML 코드와 자바스크립트를 이용하여 홈페이지 만들 듯이 만들 수가 있기에 자바스크립트 프레임워크는 jQuery를 이용하였습니다.
template를 생성해서 매개변수를 전달하고. evaluate() 함수를 실행해 주고 대화상자에 매개변수로 보내면 완성입니다. 여기서 폼 생성 함수로부터 HTML 파일로 데이터를 넘기는 부분이 핵심입니다.
sName이라는 변수에는 ss.getRange("종합_종목명").getValues() 가 들어가면 종합_종목명이라는 영역의 값들을 취해서 배열로 만들어 줍니다. [ 'A','B','C','D','E'] 5개의 배열로 됩니다.
sName이라는 변수를 template에 전달하는 것이고
실제 HTML 파일에서는 <? ?> 사이에서 자바스크립트 형태의 변수로 사용되게 됩니다. 변수이니 [i] 인자를 사용하였습니다. 이렇게 되면 A~ E까지 콤보 박스에 나타나게 되는 것입니다.

       <select name="StockName" id="StockName">
          <option value=""> 선택 </option>
          <? for (var i in stockNames) { ?>
          <option value="<?= stockNames[i]?>" code="<?= stockCodes[i]?>" >
            <?= stockNames[i]?>
          </option>
          <? } ?> 
        </select>
        종목코드 :
        <input type="text" name="StockCode" id="StockCode" size="6" />        
      </td>
    </tr>
    <tr>
      <th>배당금액</th>
      <td>
        <input type="text" name="Price" id="Price" value="">
      </td>
    </tr>


stockCodes 와 fin_Names 변수도 똑같습니다. 시트에서 값을 가져와서 배열 변수로 만들어 줍니다.
이제 HTML 페이지에서 데이터를 구글 스프레드시트로 보낼 차례입니다.
<form> 태그의 기본은 POST 방식으로 데이터를 서버로 보냅니다.
Insert 버튼이 눌려지게 되면 postData 함수가 실행됩니다. 실제 서버로 전송하는 부분은 google.script.run에서 다음 부분이고 나머지는 보내기 전에 값을 체크하고 전송 후에 실패/성공이 되었을 때 후속 작업을 하기 위한 목적입니다.
.withUserObject(this) 뒤에 있는 함수가 담당합니다.
.insertDividendData(this.form);
성공했을 때 실행되는 showSuccess() 함수에 google.script.host.close()가 있는데 이것은 구글 스프레드시트에서 띄운 대화상자를 창을 닫는 명령어입니다.

<script>

    $(function(){
       // Save 버튼 누를 때 postData 함수 실행 
      $("#Save").click(postData);
    });
    
    function postData()
    {

        if(!$("#StockName").val()){ 
             checkField('주식종목명을 입력하세요',$("#StockName") ); return false; }
        if(!$("#Price").val()){ 
             checkField('배당금을 입력하세요',$("#Price")); return false; }

        google.script.run
            .withSuccessHandler(showSuccess) // 성공했을 때 showSuccess함수 실행
            .withFailureHandler(showError)  // 성공했을 때 showError함수 실행
            .withUserObject(this) 
            .insertDividendData(this.form); // 입력을 담당할 GAS 함수 

    }
  
    function checkField(msg,elem) {
      var div = $('<div class="success"><font color="red">' + msg + '</font></div>');
      elem.after(div);
      
      $("#Save").prop("disabled",false);
     
    }

    function showSuccess(msg,elem) 
    {
      elem.disabled = false;
      var div = $('<div id="success"><font color="green">' + msg + '</font></div>');
      $(elem).after(div);
      google.script.host.close()
    }
    
    
    function showError(msg, elem) 
    {
      elem.disabled = false;
      var div = $('<div id="error" class="error">' + msg + '</div>');
      $(elem).after(div);
    }
    

  </script>


다음은 실제 입력하는 함수입니다. google.script.run.withuserObject 뒤에 있는 함수와 이름을 같게 해주면 됩니다. 여기서 (e)를 반드시 해줘야 폼에서 넘어간 값이 전달이 됩니다.
e.Price 가 폼에서 input 박스의 name 이 Price에 넣은 값이 전달 되게 됩니다.

// 실제 입력 루틴
function insertDividendData(e){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var divSheet = ss.getSheetByName("배당금");  // 시트 지정

  var d = new Date(e.s_date); // 날짜 
  var Price = e.Price; // 배당금 
  var up = parseFloat(Price.replace(/,\s/g,'')); 
   
  var newLine = [];
  newLine.push(Utilities.formatDate(d, "GMT+9", "yyyy-MM-dd") ); // 날짜 형태 조정
  newLine.push(e.StockName); //E   
  newLine.push(e.StockCode); //E     
  newLine.push(up); //E   
  newLine.push(e.sec_name);
  newLine.push(e.div_year);
 
  divSheet.appendRow(newLine);  // 시트의 appendRow 함수로 newLine 배열 전달 
    
}


newLine 은 1차원 배열입니다. newLine[0], newline[1].....
divSheet.appendRow(newLine);에 의하여 입력됩니다.

입력 폼은 상단의 데이터입력 이라는 메뉴에 있습니다


Insert 버튼이 눌러지면 배당금 시트의 마지막 줄에 데이터가 삽입됩니다.

웹 앱(Web App)

function doGet(e){
    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var param = e.parameter;

  switch(param.mode)
  {
      case "dividend":
           
      // 시트의 영역을 선택하여 값을 배열 형태로 추출한다. 
      var sName = ss.getRange("종합_종목명").getValues();
      var sCode = ss.getRange("종합_종목코드").getValues();  
      var finName = ss.getRange("증권사목록").getValues();

      // HTML 파일로 부터 폼을 불러 들인다. 
      var template = HtmlService.createTemplateFromFile("dividend_form.html");
      
      // template 에 매개변수 전달 
      template.stockNames = sName;
      template.stockCodes = sCode;  
      template.fin_Names = finName;

      var htmlOutput = template.evaluate()
        .setWidth(400)
        .setHeight(300)
        .setTitle("배당금 입력");
      break;

      default:    
        var scriptUrl = ScriptApp.getService().getUrl();
        var htmlOutput = "<ul type='square'>" +
                  "<li><a href='" + scriptUrl  + "?mode=dividend'  target='_blank'>배당금 내역 입력하기</a></li>"+                  
                  "<li><a href='https://docs.google.com/spreadsheets/d/"+ss.getUrl()+"'  target='_blank'>스프레드시트로</a></li>"+
                  "<li><a href='https://docs.google.com/spreadsheets/d/"+ss.getId()+"/copy'  target='_blank'>스프레드시트 복사하기</a></li>"+  
                  "</ul>";
      break;
  
   }

    var htmlOutput = HtmlService.createHtmlOutput(htmlOutput);
    htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1'); // 모바일에서 동작 하도록 
    return htmlOutput;
}


웹 앱 주소
배포된 웹 앱은 구글 계정이 있으면 누구나 수정 가능합니다.

script.google.com/macros/s/AKfycbw-g6i8D6lWFdAuiPjvS6sCdLy2WeO6o2S804J-QfPPrmrz-IvKc6de2DIvFAtrLjVmhA/exec
e.parameter.mode 매개변수로 mode=dividend 형태로 전달 됩니다.
http://script.google.com/macros/s/AKfycbw-g6i8D6lWFdAuiPjvS6sCdLy2WeO6o2S804J-QfPPrmrz-IvKc6de2DIvFAtrLjVmhA/exec?mode=dividend
해당 예제가 담긴 구글 시트는 다음과 같습니다.

구글스프레드 시트 데이터 입력 폼 구현

주식목록 종목명,종목 코드 A,102110 B,122630 C,122631 D,122632 E,122633

docs.google.com

배당금을 입력할 수 있도록 국내 및 해외 주식 관리에 적용 하였습니다.



국내주식관리

구글 스프레드시트로 하는 주식 수익률 관리

예전에 엑셀로 만든 주식 수익률 관리를 구글 스프레드시트로 옮겨 봤습니다.Excel 소프트웨어가 필요하고...

blog.naver.com

해외주식관리

구글 스프레드시트로 하는 해외주식 포트폴리오 관리

저는 해외주식을 미래에셋대우에서 현재 하고 있는데 자산현황에 수익률이 국내 주식과 섞여 버려서 정확한...

blog.naver.com