구글 스프레드시트 앱스 스크립트 연속해서 데이터 추가하는 영역 범위 지정

Posted by 넉넉한

2020. 10. 20. 17:34 구글/구글 스프레드시트

구글 스프레드시트 앱스 스크립트 연속해서 데이터 추가하는 영역 범위 지정

워크시트에 데이터를 마지막 행에 이어 연속해서 데이터를 추가 입력하는 작업의 경우 구글 스프레드시트에서는 액셀과는 다르게 대부분 사용자 정의 폼을 HTML 문서로 작성해 구글 앱스 스크립트에서 처리합니다.

HTML 사용자 정의 폼 사용은 다른 글에서 다루겠습니다.

▶ 워크시트에 데이터가 실려있는 영역

하나의 데이터 영역만 있는 경우:
appendRow() 함수를 사용하면 데이터 영역의 마지막행 에 이어 데이터를 추가할 수 있습니다.
여러 데이터 영역이 있는 경우:
- 여기서는 여러 데이터 영역 중 하나의 특정 영역에 데이터를 추가하는 스크립트에 대해 이야기해보겠습니다.
- 데이터가 실려있는 전체 영역의 범위는 getDdataRange() 함수로 구하며 엑셀의 Usedrange 속성과 유사합니다.

특정 데이터 영역의 범위를 구하는 스크립트 함수 getDataRegion()

참조 : 구글 도움말

getDataRegion() 함수로 주위가 빈 셀로 채워져 구분돼있으며 데이터가 실려있는 영역의 범위를 구할 수 있습니다.
스프레드시트 Range 클래스에 속한 함수입니다.
본 함수로 구해진 범위는 데이터 영역에서 Ctrl+A로 표시된 영역에 해당됩니다. 엑셀의 CurrentrRgion 속성과 유사합니다.

구해진 범위에서 getLastRow() 함수로 데이터 영역의 마지막 행을 구합니다.

예시 워크시트

아래의 워크시트의 데이터 영역 마지막 행에 이어 데이터를 추가하는 스크립트를 예로 들겠습니다.

위의 워크시트에는 두 개의 데이터가 실려 있는 영역이 있으며 주위가 빈셀로 구분돼있습니다.
좌측 영역(Ctrl+A로 반전된 영역)이 연속해서 데이터를 추가하는 영역이며 범위는 B2:E7입니다. 우측에 또다른 데이터 영역과는 빈셀로 구분돼있습니다.

B2:E7 범위에 이어서 데이터를 추가하는 스크립트 예를 들어보겠습니다.

스크립트 작성

아래의 스크립트는 워크시트에 데이터를 추가하는 간단한 스크립트 함수입니다.

1
2
3
4
5
6
7
8
9
10
11
12
13

function addNewRow(){
  var curDate = new Date();
  var rowData = ["에어컨",1,1200000,curDate]
    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0];
 
  lrow=sh.getRange("b1").getDataRegion().getLastRow();
  sh.getRange(lrow+1,2,1,4).setValues([rowData]);
  
  rowData.unshift("");
  sh.appendRow(rowData);
}
Colored by Color Scripter

cs

아래는 위 스크립트를 단계별로 나눈 내용입니다.

1. 추가할 데이터 구축

1
2
3

function addNewRow(){
  var curDate = new Date();
  var rowData = ["에어컨",1,1200000,curDate]

사용자 폼, 또는 다른 방법으로 데이터를 불러온다면 추가할 데이터를 인수로 받아야겠지만 여기서는 간단히 본 함수에서 입력 일을 오늘로 하는 간단한 상품 구입 데이터(상품명, 개수, 금액, 입력일)를 워크시트에 추가하겠습니다.
3번 행은 추가할 데이터( 상품명, 갯수, 금액, 입력일)를 대괄호([])로 묶어 배열로 만들어서. 복수의 데이터를 하나의 배열 변수에 지정합니다.

2. 워크시트 개체 변수 지정

5
6

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0];

구글 스크립트에서는 퍼포먼스 면에서 여러번의 스프레드시트 함수 콜을 피하기 위해 자주 쓰이는 스프레드시트 함수의 결과는 변수에 저장해 사용하는 것이 권장됩니다.
스프레드시트 개체를 변수 ss에 지정합니다.
워크시트 개체를 변수 sh에 지정합니다.

3. 데이터 추가 영역에 데이터 추가

8 9	lrow=sh.getRange("b2").getDataRegion().getLastRow(); sh.getRange(lrow+1,2,1,4).setValues([rowData]);

8번 행의 getDataRegion() 함수는 range 클래스 함수이며 데이터 추가 영역(B2:F8)의 임의의 셀(본 스크립트에서는 B2셀)에 해당되는 Dataregion의 범위를 getDataRegion() 함수로 구하고 getLastRow() 함수로 구해진 마지막행에 +1로 추가할 행 번호를 만듭니다.
setValues() 함수로 가져온 일차원 배열 데이터를 마지막행에 이어 표기합니다.
setValues() 함수는 이차원 배열을 사용합니다. 가져온 일차원 배열의 데이터를 대괄호([])로 묶어 이차원 배열로 만듭니다.

4. appendRow() 함수 테스트

11
12
13

  rowData.unshift("");
  sh.appendRow(rowData);
}

13행은 위의 결과와 비교하기 위해 appendRow()로 데이터를 추가했습니다. column수를 맞추기 위해 데이터 변수 rowData 배열 첫 번째 위치에 빈칸을 추가했습니다.
스크립트 작성을 마치고 저장할 때 진행되는 스크립트 인증 과정에서 허용을 선택해 스크립트를 저장합니다.