구글스프레드시트 스크립트 Google Spreadsheet Script – 한 시트의 특정 영역을 다른 시트 첫 열에 밀어넣어 이동하기

구글스프레드시트를 이용해서 특정열을 상단 첫 열에 밀어넣고 싶어하는 기능이 필요했습니다. 같은 시트안에서 하단에 몇 열을 복사해서 마우스로 첫 열에서 오른쪽 버튼을 누르면 “밀어넣기” 뭐 그런 메뉴가 떠야하는데 없어서 당황이 되었습니다. 키로 복사하기, 붙어넣기 하면 밀어 붙어넣기가 아니라 덮어쓰기가 되어서 불편합니다. 찾아보니깐 이때에는 마우스로 먼저 열을 선택하고, 드래그해서 원하는 부위에 가져가면 그냥 밀어서 붙어넣기가 되네요.

마우스로 한 시트에서 밀어 붙이기

위 그림처럼 열을 먼저 선택을 하고, 해당 열의 Header에서 마우스로 드래그해서 원하는 부분으로 넣으면 됩니다.

스크립트(Script)를 이용해서 한시트에서 다른 시트로 복사하기

What to do – 상황설명 : 구글스프레드시트 주식 매매관리

시트에서 선택박스에 마우스를 클릭하면(버튼을 만들면 해당 열을 인식하는 작업이 쉽지 않아서) 클릭한 열을 기준으로 다음의 회색라인 전까지를 자동으로 영역지정을하고, 이 부분을 다른 시트지의 제일 상단에 넣는 것입니다.   구글 스프레드시트를 가지고 작업을 하다보면 이런 기능이 필요한 경우가 많을 겁니다. 한 시트에서 최근 것을 기록하고, 완료가 되면 다른 시트지에 log 차원으로 집어 넣고, 통계를 내고 싶을 테니깐요.

 

위에서 처럼 처리 행에서 체크박스를 데이터확인으로 넣습니다. 그리고 클릭을 하면 아래와 같이 다른 시트에 상단에 넣고 싶습니다.

여기에서는 첫번째 열이 아니고, 2번째 열에 밀어넣기가 됩니다.

How to do – Script에 아래 와 같이 넣는다.

다만 사용자 함수는 따로 파일을 만들어서 넣는다.

 

코드

// 전역변수들
var sp = SpreadsheetApp.getActiveSpreadsheet();
var ss = sp.getActiveSheet();
var activeCell = ss.getActiveCell();
var cellValue = activeCell.getValue();

function onEdit(){
  // 현재거래종목에서 매매일지로 복사
  // 현재 시트에서 체크박스가 있는 행과 열에 변화가 있는지 감시
  if(activeCell.getColumn() == 14 && activeCell.getRow() > 1 && ss.getSheetName() == '현재거래종목'){
    // 체크박스가 선택이 되었다면
    if(cellValue == true) {
      //******** 선택한 영역 복사 ***********
      // 체크박스의 열의 위치를 구하고, 
      var aRow = activeCell.getRow(); 
      
      //대략적인 선택영역을 선택한다. 한 섹션이 10 열을 넘지 않기때문에 10개로 했다. 
      var sectionRange = ss.getRange(aRow+1,1,10,10); 
      
      //위 영역을 이용해서 색깔 코드를 배열로 담는다. 
      var rowColors = sectionRange.getBackgrounds();
      
      //사용자 함수를 이용해서 다음 회색(#f3f3f3) 값이 몇번째 오는 가를 찾는다. 
      var aSection = getSection(rowColors);
      
      // 실제 Data가 들어가서 복사할 섹션전체를 담는다. 
      var aRange = ss.getRange(aRow,1,aSection+1,10); 
      
      // 복사를 할 시트지를 선택하고, 
      var stockLog = sp.getSheetByName("매매일지");
      
      // 2번째 행에서 섹션열 만큼 빈 행을 먼저 넣는다. 
      stockLog.insertRowsBefore(2, aSection+1);

      // 영역을 복사하는데, 규칙이나 수식등이 있으면 따라가기때문에 값만 먼저 넣고, 
      aRange.copyTo(stockLog.getRange(2,1,aSection+1,10),SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
      // Format를 붙어넣기 한다. 
      aRange.copyTo(stockLog.getRange(2,1,aSection+1,10),SpreadsheetApp.CopyPasteType.PASTE_FORMAT,false);

      // 기존시트지에서 해당 행 모두를 삭제
      ss.deleteRows(aRow,aSection+1); 
       
    } 
  }
}

//// 사용자 함수는 파일을 따로 만들어서 호출하도록 한다. 
function getSection(input) {
  var aSection = 5; // 기본 섹션 열 값
  
  for(var r = 0; r < input.length; r++) {
    //Logger.log(input[r][0]);
    if(input[r][0] == "#f3f3f3") {
      aSection = r;
      return aSection; 
    }
  }
  return aSection; 
}

 

방법을 찾아보면서 많이 배웠습니다. 스크립트로 해놓으니깐 많은 수작업들을 좀더 변하게 작업을 할 수 있게 되었습니다.

 

 TIP: 만약에 Undo를 하고 싶다면,Ctr+Z를 누르면 복사 이전으로 한단계식 돌아갑니다.

 

차로여행 구글스프레드시트 다른 링크

구글스프레드시트에 아이콘 Awesome Font 복사해서 넣기

구글스프레드시트 필터 활용

구글스프레드시트 버튼으로 열감추기

 

구글스프레드시트 레퍼런스

https://developers.google.com/apps-script/reference/spreadsheet