구글 앱스 스크립트로 차트 그리기

시트에 있는 데이터가 가변적이거나 자동화가 필요할 때 사용하는 방법입니다.  구글 시트에서는  구글 앱스 스크립트로 가능합니다. 상단 메뉴 확장 프로그램에서 Apps Script 를 선택해서 들어가면 편집기가 나옵니다.

구글 앱스 스크립트 레퍼런스 EmbeddedChartBuilder 클래스에 보면 addRange 코너에 간단한 차트를 그리는 소스가 있습니다.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var chart = sheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(sheet.getRange("A1:B8"))
    .setPosition(5, 5, 0, 0)
    .build();

sheet.insertChart(chart);

 

addRange에 sheet.getRange("영역")  영역을 지정하면 되며 addRange 함수는 여러 개 추가할 수 있습니다. 선차트로 구현하면 .asLineChart() 나 .setCharType(Charts.ChartType.LINE); 으로 해주면 여러개의 데이터를 비교할 수 있습니다.

setPosition 의 사용법은 setPosition(anchorRowPos, anchorColPos, offsetX, offsetY)  인데  
차트가 위치할  행위치, 열위치 지정입니다. 시트의 좌측 상단 셀 좌표로 생각 하시면 됩니다.
OffsetX 는  차트내 그래프가 위치될 곳을 픽셀단위로 지정해 놓은 것 같습니다. 
.bulid() 는 그리는 명령어 입니다.  

​조금 응용하여 실제로 예제에 있는 일별 펀드 수익률 Data가 들어가 있는 영역을 기준으로 그래프를 그려보았습니다. 

 

function newSimpleChart() 
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = ss.getSheetByName("기준가");
  var allRange = thisSheet.getDataRange(); //데이터 영역
  var maxC = allRange.getNumColumns(); // 데이터영역 열수 
  var maxR = allRange.getNumRows(); // 데이터영역 행수 
    
  var chart = thisSheet.newChart()
  .addRange(allRange.offset(0,0,allRange.getNumRows(),1)) // 날짜 
  .addRange(allRange.offset(0,2,allRange.getNumRows(),1)) // 영역1
  .addRange(allRange.offset(0,4,allRange.getNumRows(),1)) // 영역2
  .addRange(allRange.offset(0,6,allRange.getNumRows(),1)) // 영역3 
  .setNumHeaders(1) // 1열을 헤더로 사용
  .asLineChart() // 라인차트로 그리기 
  .setOption('title','펀드 비교') // 이름 지정
  .setOption('textStyle.color', '#000000') // 텍스트 칼라 지정
  .setOption('height', 600) // 높이 
  .setOption('width', 1024); // 너비 지정 
 
  // 차트 위치 선정 
  chart.setPosition(4, 2 , 12, 12);

  // 현재 시트에 차트 생성 후 넣기  
  thisSheet.insertChart( chart.build());
}

.setOption으로  차트의 이름, 색깔, 너비, 높이등을 지정할 수 있습니다.
addRange (영역) 인데  으로 영역에 들어간  allRange.offset(0,0,allRange.getNumRows(),1) 는 영역으로 부터 수치만큼 영역을 잘라내어 새 영역으로 만들어주는 명령어 입니다. 
매개변수로 ( 기준열, 기준행, 행 수,열 수) 인데 0,0 인 allRange의 왼쪽 상단 모서리 부터 데이터가 있는 영역의 제일 하단 그리고 1개 열이 영역으로 잡힙니다.

첫 열은 날짜이고 둘째 열은  데이터 인데 실제 그래프를 그리는 열은 홀수 열입니다. 
offset에서는 배열인자가 0 부터 시작하기에 2는 3열을 뜻합니다

​newSimpleChart 를 다음과 같이 실행 하면 차트가 그려 집니다. 

시트에 있는 데이터가 몇개이든 차후에 추가 되던 규칙적으로 데이터가 위치 하고 추가할 때 마다 자동으로 그리기 위하여 다음과 같이 했습니다. 

function newSimpleChart2() 
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = ss.getSheetByName("기준가");
  var allRange = thisSheet.getDataRange();

  var maxC = allRange.getNumColumns(); // 데이터영역 열수 
  var maxR = allRange.getNumRows(); // 데이터영역 행수 
    
  var chart = graphSheet.newChart()
   // 날짜 영역만 지정
  .addRange(allRange.offset(0,0,allRange.getNumRows(),1)) 
  .setNumHeaders(1) // 1열을 헤더로 사용
  .asLineChart() // 라인차트로 그리기 
  .setOption('title','펀드 비교') // 이름 지정
  .setOption('textStyle.color', '#000000') // 텍스트 칼라 지정
  .setOption('height', 600) // 높이 
  .setOption('width', 1024); // 너비 지정 
 
  // getDataRange()를 이용하여 시트의 데이터의 모든 펀드 포함 동적 추가 
  for(var i=2; i < maxC; i+=2 ){   
    chart.addRange(allRange.offset(0,i,maxR,1)); 
  }
  // 2열 씩 규칙적으로 데이터 영역이 있다면 

  // 차트 정보 얻기 차트 하단 정보 
  var charts = graphSheet.getCharts();
  var r = 4;
  if(charts.length > 0)
  {
      var containerInfo = charts[charts.length-1].getContainerInfo();
      var r = containerInfo.getAnchorRow();
      r += 30;
  } 
  
  // 차트 위치 선정 
  chart.setPosition(r, 2 , 12, 12);
  graphSheet.insertChart( chart.build());
  
}

차트를 그리기 위한 기본 정보 미리 설정하고 addRange() 함수로 뒤에 정보를 입력하는 방식입니다.

뒷 부분에 getContainerInfo() 는 setPositon 함수의 매개변수인  차트 위치 정보의 정보를 얻는 기능 입니다.

차트를 생성하면 같은 위치에  중첩되어 그려 지는데  그러면 수작업으로 분리해야 합니다.
 setPosition 의  첫 번째 인수는 차트의 행 위치인데   getAnchorRow() 함수로 행 위치를 알아내면  차트 조금 밑에 중첩되지 않게 그릴 수 있습니다. 

​행을 숨긴다면 그 날짜의 값은 차트에 표시되지 않습니다.  이를 이용하여  수익률 기간 비교 차트를 만들었습니다. 

예제 차트는 다음과 같습니다. 

https://docs.google.com/spreadsheets/d/1spjsP45-Jcichu-YD9_bB2WewlVgh6U3_IvCgDQuqlw/edit

수익률 기간 비교 차트