ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • 구글 스프레드시트 - 데이터 누적/축적하기 (feat. Apps Script)
    Google Speadsheet 2021. 1. 8. 02:41

    프로젝트 배경

    • 필요했던 기능 : 특정 시트에 연동 된 값을 이벤트 발생에 따라 지정 된 시트에 누적/축적

    • 서비스 개요 : 입력 시트에 사용자가 값 입력 → 처리 시트에 데이터가 함수로 계산되어 연동 → '저장' 이벤트 발생 시 누적/축적 시트에 처리 시트의 계산 된 값을 누적 

    • 사용 언어 및 툴 : Google Spreadsheet, Google Apps Script(자바스크립트)


    프로젝트 내용

     

    스프레드시트의 데이터를 연동해서 정제 된 값으로 보여주는 것 뿐이 아니라, DB에 저장하듯 누적이 가능하도록 시트를 만드는 것이 목적이었다. 서로 다른 시트 사이의 연동은 IMPORTRANGE 등을 통해서 쉽게 가져오는데, 매번 값이 업데이트 되도록 하는 것은 스크립트를 사용해야했다. 스크립트는 스프레드시트의 메뉴바 - 도구 아래에 있다.

     

    function storeContents(){
     var ds = SpreadsheetApp.getActiveSpreadsheet();
     var sheetData = ds.getSheetByName("전송할 데이터 보유 시트 이름");
     var sheetStore = ds.getSheetByName("데이터 축적할 시트 이름");
     var rangeStore = sheetStore.getDataRange(); // 데이터 축적할 시트의 범위
     var numRows = rangeStore.getNumRows(); // 데이터 축적할 시트의 값이 있는 행의 수
     var numColumns = rangeStore.getNumColumns(); // 데이터 축적할 시트의 값이 있는 열의 수
     var rangeTest = sheetData.getRange("a2:AI2").getValues(); // 전송할 데이터의 범위를 지정하고 값을 가져옴
     sheetStore.getRange(numRows + 1, 1, 1, 35).setValues(rangeTest); // 데이터 축적할 시트의 행의 아래(+1) 행과 더불어 열을 지정하고, 값을 넣을 크기를 지정한 후 값을 넣음
    }

     

    function storeContents(){

    해당 함수의 이름을 정의하여, 버튼에 스크립트를 넣거나 메뉴를 만드는 createMenu 함수에 사용한다.


     var ds = SpreadsheetApp.getActiveSpreadsheet();

    스프레드시트와 연결한다.


     var sheetData = ds.getSheetByName("전송할 데이터 보유 시트 이름");

    전송할 데이터가 있는 시트를 변수에 넣는다.


     var sheetStore = ds.getSheetByName("데이터 축적할 시트 이름");

    데이터를 축적 할 시트를 변수에 넣는다.


     var rangeStore = sheetStore.getDataRange(); 

    데이터를 축적할 시트의 범위를 가져온다.


     var numRows = rangeStore.getNumRows(); 

    데이터를 축적할 시트의 행을 파악한다. 값이 있는 마지막 행을 알아야 새로운 값이 입력 될 위치를 알 수 있다. 포인터의 개념으로 이해했다.


     var numColumns = rangeStore.getNumColumns(); 

    데이터를 축적할 시트의 열을 파악한다. 위의 행과 마찬가지의 목적으로 파악한다. 다른 분이 열 방향으로 누적되는 데이터를 위해 사용하는 걸 참고했다.


     var rangeTest = sheetData.getRange("a2:AI2").getValues();

    getRange로 전송할 데이터의 범위를 지정하고, 그 안의 값들을 가져오기 위해 getValues 함수를 사용한다.


     sheetStore.getRange(numRows + 1, 1, 1, 35).setValues(rangeTest); }

    데이터를 축적할 시트에 범위를 가져오는데, 이때 내가 어느 방향(행 또는 열)으로 데이터를 누적할지에 따라 numRows + 1 이 되거나 numColumns + 1이 될 수 있다.

    여기서 getRange는 setValues 함수를 통해 값들이 입력 될 범위를 지정하였고, 그 인자로 (시작행, 시작열, 행의 개수, 열의 개수) 를 받았다. 나는 행 방향으로 계속 누적을 하고 싶었으므로 시작행을 현재 행 개수에서 1을 더한 곳에서 저장할 범위의 행을 지정했다.

     

     

    내 프로젝트의 경우는 저장 주기가 따로 없고 사용자가 고객이 있을 때에만 데이터를 입력하는 것이어서, 데이터를 입력하면 저장버튼을 누르도록 이미지에 스크립트를 부여했다. 이미지를 삽입 후 스크립트 할당 이라는 메뉴에서 적용할 함수 이름만 입력하면 된다. 

     

    사용자 입력 시트 - 연노란색 부분에 값을 입력함

     

    계산 시트 - data studio에 나타나는 정보 및 축적할 데이터 출력

     

    누적/축적 시트 - 입력 시트에서 저장 매크로 버튼 클릭 시 계산 시트 데이터 연동 저장

     


    소감

     

    전부터 스프레드시트를 종종 사용하면서 가장 구현해보고 싶었던 기능이었는데, 최근 헬스케어 서비스의 데이터 업무에 참여하면서 만들어 볼 수 있었다. 대충 하루정도 걸렸던 것 같다. 기능은 구현했는데 추후에 데이터도 많아지고 다른 데이터들이랑도 연결할 일이 많을 예정이라 스프레드시트보다는 DB를 이용하는 게 좋을까 생각 중이다. 그래도 복잡하거나 어려운 코드 없이 데이터 업데이트 기능을 구현해서 뿌듯하다.

    댓글

made by default womyn