[Google App Script] ๊ตฌ๊ธ€ ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ ๋ฐ์ดํ„ฐ ์—ฐ๋™ํ•˜์—ฌ ์›น์•ฑ ์ฐจํŠธ ๋งŒ๋“ค๊ธฐ ๐Ÿ“Š
๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
๋””์ง€ํ„ธ ๊ฟ€ํŒ

[Google App Script] ๊ตฌ๊ธ€ ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ ๋ฐ์ดํ„ฐ ์—ฐ๋™ํ•˜์—ฌ ์›น์•ฑ ์ฐจํŠธ ๋งŒ๋“ค๊ธฐ ๐Ÿ“Š

by ์ผ์ƒ์„ ๊ณต์œ ํ•ฉ๋‹ˆ๋‹ค 2022. 2. 12.

ํ‡ด์‚ฌ๊นŒ์ง€ ์–ผ๋งˆ ๋‚จ์€ ์‹œ๊ฐ„์— ์–ผ๋งˆ๋‚˜ ์‹ค์šฉ์ ์ธ ์—…๋ฌด๋ฅผ ์ œ๊ณตํ•  ์ˆ˜ ์žˆ์„๊นŒ? ๋งˆ์ง€๋ง‰ ์ง์žฅ์ธ์œผ๋กœ ์ปค๋ฆฌ์–ด๋ฅผ ์Œ“๋Š” ์ผ์ด์—ˆ๋‹ค.  ํ•ญ์ƒ ๋ชจ๋“  ์ผ์— ์žˆ์–ด์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถ„์„ํ•˜๋Š” ์ผ์ด ๊ฐ€์žฅ ์ค‘์š”ํ•˜๋‹ค๊ณ  ์ƒ๊ฐํ•œ๋‹ค. ๋ฐ์ดํ„ฐ ๊ณผํ•™์ž, ๋ฐ์ดํ„ฐ ๋ถ„์„๊ฐ€ ๋“ฑ ๋งŽ์€ ํŒ€์ด ์ƒ๊ธธ ์ •๋„๋‹ˆ '๋ผ๋–ผ'๋Š” DB ์„ค๊ณ„ํ•˜๋ฉฐ ๊ทธ๋ƒฅ ํ–ˆ๋˜ ์ผ์ธ๋ฐ ๐Ÿ˜ญ , ์š”์ฆ˜์€ ์„ธ๋ถ„ํ™”๋˜๊ณ  ์ •๊ทœ๊ณผ์ •์„ ์•ˆ๋ฐŸ์œผ๋ฉด ํ•ด๋‹น ์—…๋ฌด๋ฅผ ๋งก๊ธด ์–ด๋ ค์šฐ๋‹ˆ ์ฐธ์œผ๋กœ ์Šฌํ”ˆํ˜„์‹ค์ด๋‹ค.

ํ•˜์ง€๋งŒ ๋ชจ๋“  ํšŒ์‚ฌ์— ๊ทธ๋งŒํผ ๋˜‘๋˜‘ํ•œ ์ธ๋ ฅ์ด ๋ฐฐ์น˜๊ฐ€ ๋œ๋‹ค๊ณ ๋„ ์ƒ๊ฐํ•œ๋‹ค. ์—ฌํ•˜ํŠผ ์—…๋ฌด๋ฅผ ํ•˜๋‹ค๋ณด๋‹ˆ ์ข…์ข… ๋ฆฌ๋”๊ฐ€ ์š”๊ตฌํ•˜๋Š” ์ง€ํ‘œ๊ฐ€ ์žˆ๋‹ค. ๋ชจ๋‹ˆํ„ฐ๋ง ์„œ๋น„์Šค๊ฐ€ ๊ฐ€๋…์„ฑ์ด ๋–จ์–ด์ ธ ์ฃผ๊ธฐ์ ์ธ ๋ฆฌํฌํŒ… ๋ฐฉ๋ฒ•์„ ์ƒ๊ฐํ•˜๊ฒŒ ๋˜์—ˆ๋‹ค.

Datadog Log & 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์„ ๋งŒ๋“ค์–ด์•ผ๊ฒ ๋‹ค.

 

 

๋Œ“๊ธ€