[Google App Script] 구글 스프레드시트 데이터 연동하여 웹앱 차트 만들기 📊

퇴사까지 얼마 남은 시간에 얼마나 실용적인 업무를 제공할 수 있을까? 마지막 직장인으로 커리어를 쌓는 일이었다.  항상 모든 일에 있어서 데이터를 분석하는 일이 가장 중요하다고 생각한다. 데이터 과학자, 데이터 분석가 등 많은 팀이 생길 정도니 '라떼'는 DB 설계하며 그냥 했던 일인데 😭 , 요즘은 세분화되고 정규과정을 안밟으면 해당 업무를 맡긴 어려우니 참으로 슬픈현실이다.

하지만 모든 회사에 그만큼 똑똑한 인력이 배치가 된다고도 생각한다. 여하튼 업무를 하다보니 종종 리더가 요구하는 지표가 있다. 모니터링 서비스가 가독성이 떨어져 주기적인 리포팅 방법을 생각하게 되었다.

Datadog Log & ImportJson

• Aggregate 사용 API : https://api.datadoghq.com/api/v2/logs/analytics/aggregate
• ImportJson : https://gist.github.com/chrislkeller/5719258
• Importjson은 소스 복사해서 사용하면 되기 때문에 기재하지 않았다 

function analyticsAggregate(querystring,from,to,pagelimit,groupBy,parseOptions) {

  var parseOptions = "";
  var url = "https://api.datadoghq.com/api/v2/logs/analytics/aggregate";
  var query = "/data/buckets/computes/c0,/data/buckets/by/@grouby필드명";

/*   테스트 샘플
  querystring = 검색 조건"
  from = "2022-01-11";
  to = "2022-02-11";
  pagelimit = "50";
  groupBy = "groupby필드명";
*/

  from = dateToUnixtime(from);
  to = dateToUnixtime(to);

  var body = JSON.stringify({  
  "filter": {
    "query": querystring,
    "from": from,
    "to": to ,
    "indexes": [
      "*"
    ]
  },
  "group_by": [
    {
      "facet": groupBy,
      "limit": pagelimit,
      "sort":{"metric":"count","aggregation":"count","type":"measure"}
    }    
  ]
});

  var requestOptions = {
    method: 'POST',
    headers: { 
    'Content-Type': 'application/json', 
    'DD-API-KEY': APIKEY,
    'DD-APPLICATION-KEY': APPKEY
  },
    'payload': body,
  };
  
  var data = ImportJSONAdvanced(url, requestOptions, query, parseOptions, includeXPath_, defaultTransform_) ;

  Logger.log(data); //데이터가 궁금할 때 찍는 법

  var arrOrder = new Array() ;
  var count = new Array() ;

  for (var i = 0; i < data[0].length; i++) {
    for(var j = 0; j < data.length; j++){
      if(data[0][i] == "by/groupby필드명"){
          if(data[j][i] != "groupby필드명" && arrOrder.indexOf(data[j][i]) == -1){       
            arrOrder.push(data[j][i]);
            Logger.log(data[j][i]);
          }
      }
      if(data[0][i] == "computes/c0"){
          count.push(data[j][i]);
          Logger.log(data[j][i]);
      }
    }
  }

  var reportValue = new Array();
  reportValue.push(arrOrder);
  reportValue.push(count);

 /* 이차원 배열 세로로 변환 */ 
  var transposed = Array.from({ length:arrOrderAgencyId.length }, () => new Array(2).fill(0))

  for(var i = 0; i < data[0].length; i++) {
    for(var j = 0; j < data.length; j++) {
      [transposed[j][i]] = [reportValue[i][j]]; 
    }
  }

  transposed[0].splice(0,1,"arrOrder");
  transposed[0].splice(1,1,"Count");

  return transposed;
}

* 참고 사항 

//unixtimestamp
function dateToUnixtime(date){
  var d = new Date(date);
  return d.getTime();
}

Google APP Script

• 웹 앱 링크 : https://developers.google.com/apps-script/guides/web 
• 타이틀은 스프레드시트 시작줄 이라고 생각하면 된다
• 소스 내용 : 열려있는 구글 스프레드 시트의 데이터를 차트화 한다.
• 나는 행을 3칸 이후부터 데이터를 불러와서 TITLE 이라는 상수를 정의했다.

const SHEET_NAME = "시트명";
const INFO = "찾고자하는 정보";
const TITLE = 3;

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// 추후에 활용할 예정이다
function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  SCRIPT_PROP.setProperty("key", doc.getId());
}

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}

function getDataUntactMemoSheet(){
    var a = "";
  	var spreadsheet =  SpreadsheetApp.getActiveSpreadsheet();
		var sheet = spreadsheet.getSheetByName(SHEET_NAME);

    var headers = sheet.getRange(TITLE, 1, 1, sheet.getLastColumn()).getValues()[0];

    //Find column by name
    column = headers.indexOf(INFO);

    Logger.log(column);
    Logger.log(sheet.getLastRow());
    

    var data = sheet.getRange(TITLE + 1, column + 1, sheet.getLastRow()-1, 2).getValues();
    var results = [];

    //Get data and parse it to Chart.js format
    for (row in data) {
      if(data[row][0]!="")
      {
        results.push({
          "x": data[row][0],
          "y": data[row][1]
        })
      }
    }
    Logger.log(results); //Logger is very usefull for debugging.

    return results;
  }

 

차트 스크립트 : Chart.Js 

• 차트 링크 :  https://www.chartjs.org/docs/latest/
• 차트 스크립트는 여기저기 예제를 보면 금방 따라 할 수 있다. 다만 이해하고 커스텀 하는게 조금 시간이 걸린다
• Chart.js 쓸때 유용한 자바스크립트 Parsing Tool  https://beautifier.io/

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
	<link href="https://unpkg.com/survey-jquery@1.8.21/modern.css" type="text/css" rel="stylesheet" />
    <title>Chart.js and Google Sheets example</title>
  </head>

  <!-- 본문시작 -->
  <body>
  <main role="main" class="container">
	<h3>제휴사 주문접수 API 필드별 데이터 모니터링</h3><br/>
	<!-- 차트 그리는 canvas 작업 -->
	<div id="myChartContainer" style="width:600px height:400px">
	  <canvas id="noUntact" width="600" height="400"></canvas>
	</div>  
  </main>	
  <script src="https://code.jquery.com/jquery-3.3.1.min.js" crossorigin="anonymous"></script>
  <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.min.js" integrity="sha512-d9xgZrVZpmmQlfonhQUvTR7lMPtO7NkZMkA0ABN3PHCbKA5nqylQ/yWlFAyY6hYgdF1Qh6nYiuADWwKB4C2WSw==" crossorigin="anonymous"></script>

  <!-- 데이터 바뀔 때 다시 그리는 작업 -->
  <script>
    $("#noUntact").remove();
    $("#myChartContainer").append('<canvas id="noUntact"></canvas>');; 

    var ctx = $("#noUntact");
    var HISTOGRAM = JSON.parse(<?= JSON.stringify(getDataUntactMemoSheet()); ?>);
    console.log(HISTOGRAM.columns);
    var myChart = new Chart(ctx, {
      type: 'bar',
      data: {
        labels: HISTOGRAM.map(function(r) {return r.x}),
        datasets: [{
          label: '메모 주문수',
          minBarLength: 10,
          data: 	HISTOGRAM,
          backgroundColor: [
              'rgba(255, 99, 132, 0.2)',
              'rgba(54, 162, 235, 0.2)',
              'rgba(255, 206, 86, 0.2)',
              'rgba(75, 192, 192, 0.2)',
              'rgba(153, 102, 255, 0.2)',
              'rgba(255, 159, 64, 0.2)'
          ],
          borderColor: [
              'rgba(255, 99, 132, 1)',
              'rgba(54, 162, 235, 1)',
              'rgba(255, 206, 86, 1)',
              'rgba(75, 192, 192, 1)',
              'rgba(153, 102, 255, 1)',
              'rgba(255, 159, 64, 1)'
          ],
          borderWidth: 1
        }]
      },
      options: { 
          // 컨테이너가 수행 할 때 차트 캔버스의 크기를 조정(dafalut : true)
          responsive: true, 
          // 크기 조정 이벤트 후 새 크기로 애니메이션하는 데 걸리는 시간(밀리 초) (defalut : 0) 
          responsiveAnimationDuration: 1000, 
          // (width / height) 크기를 조정할 떄 원래 캔버스 종횡비를 유지 (defalut : true) 
          maintainAspectRatio: true, 
          // 캔버스 종횡비( width / height, 정사각형 캔버스를 나타내는 값) 높이가 속성으로 또는 스타일 통해 명시적으로 정의된 경우이 옵션은 무시 
          aspectRatio: 2, 
          // 크기 조정이 발생할 때 호출 
          onResize: function () { 
            console.log('onResize'); 
          },
          //툴팁
          tooltips: { 
            mode: 'index', intersect: false, 
          }, 
          //마우스오버
          hover: { 
            mode: 'nearest', intersect: true
          },
          //x,y축 라인 및 수치
          scales: { 
            xAxes: [{ gridLines: { display: true }, }], 
            yAxes: [{ gridLines: { drawBorder: false}, 
            ticks: { display: true, max: 400000, min: 0 } }] 
          },
          //애니메이션 효과
          animation:false, 
          showValue:{ 
            fontStyle: 'Helvetica', //Default Arial 
            fontSize: 20 
          }
        }
    });

</script>
</body>
</html>

결과물

툴팁도 뜨고 각 차트 x,y축 설정도 가능하다. 색상도 x축별로 지정이 가능하지만 필드수가 많으면 귀찮다. 하다보니 사람들은 왜 github에 소스 정리하는지 알겠다. 이 긴글을 읽으려니 피로도가 높다. 나도 이 참에 포트폴리오용 github을 만들어야겠다.