Google Apps Script : getRange, getValues (셀 선택하기, 셀의 값 얻어오기)

 

 

 

getRange method는 특정 셀 객체를 얻어와줍니다.

getValues method는 특정 셀 객체로부터 해당 셀에 담겨있는 값을 얻어와줍니다.

 

 

Syntax - getRange

 

getRange(cell_adress);
getRange(row, col);

 

getRange는 2종류의 형태로 사용할 수 있습니다.

먼저 A1, C10 같이 흔히 Excel에서 다루는 셀의 주소를 전달하면 그 주소의 셀 객체를 얻어와줍니다.

 

다른 방법은 row 번호와 column 번호를 이용하는겁니다.

 

 

구글 시트를 보면 위와 같습니다.

Row 번호는 1, 2, 3, 4, 5, ... 등과 같은 자연수로 매겨져있습니다.

Column 번호는 A, B, C, D, E, ... 등과 같은 알파벳 대문자로 매겨져 있습니다.

 

Google Script를 사용할 때 Column 번호는 알파벳 말고도 숫자로도 대응하는 경우가 있습니다.

A = 1

B = 2

C = 3

D = 4

E = 5

...

이런 방식으로 행번호와 동일한 규칙을 가집니다.

1부터 시작해서 1씩 증가하는 자연수이죠.

 

getRange method에서 행/열 번호를 전달할 때에도 행 번호는 A, B, C, D, E가 아닌 1, 2, 3, 4, 5 와 같은 숫자로 된 행 번호를 사용합니다.

 

 

 

 

Syntax - getValues

getRange().getValues()

 

getValues는 getRange 또는 다른 어딘가에서 얻어진 Cell 객체로부터 그 셀에 입력된 값들을 얻어와줍니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

실제 예시를 보시죠.

 

 

일단 sheet에 위처럼 데이터를 입력해뒀습니다.

 

 

한번 C2셀에 있는 item_name이란 값을 얻어와봅시다.

 

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  sheet = spreadsheet.getSheetByName('Sheet1');

  var test_value = sheet.getRange('C2').getValues();
  Logger.log(test_value);
}

 

위 코드는 C2 셀에 입력된 값을 얻어오는 코드입니다.

실제 실행해보면 아래와 같이 C2 셀에 입력된 iten_name이라는 값을 얻어올 수 있는걸 볼 수 있습니다.

 

 

코드를 하나씩 분석해봅시다.

 

- var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

일단 먼저 spreadsheet 객체를 얻어옵니다.

현재 제가 데이터를 입력한 위 시트에 대한 객체를 얻어온다고 생각하면 됩니다.

 

 

- sheet = spreadsheet.getSheetByName('Sheet1');

위 이미지에서 보면 데이터가 입력된 시트의 이름은 Sheet1입니다.

따라서 Sheet1이라는 이름을 기준으로 Sheet1의 spreadsheet 객체를 얻어옵니다.

이 객체에는 Sheet1에 대한 정보가 담겨있겠죠.

 

 

- var test_value = sheet.getRange('C2').getValues();

sheet 객체로부터 getRange method를 이용해 C2 셀의 정보를 얻어옵니다.

그리고 getValues method를 이용해 C2셀에 입력된 값을 얻어와서 test_value 변수에 저장합ㄴ디ㅏ.

 

 

- Logger.log(test_value);

test_value 변수에 저장된 값을 출력합니다.

이 값은 C2 셀에 입력된 값이므로 item_name이라는 값을 가집니다.

 

 

 

 

 

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  sheet = spreadsheet.getSheetByName('Sheet1');

  var test_value = sheet.getRange('C2:D5').getValues();
  Logger.log(test_value);
}

 

getRange method에는 단순히 셀 주소 하나만이 아니라 위처럼 셀 주소의 범위를 지정할 수 있습니다.

 

 

위 코드를 실행하면 위처럼 출력이 됩니다.

 

array 속에 다른 array가 속해있는 2차원 array의 형태가 return됩니다.

 

[
    [item_name,   price],
    [        d,  1500.0],
    [        b,  2000.0],
    [        a,  3000.0]
]

 

결과값을 좀 더 보기좋게 정리해보면 위와 같습니다.

실제 엑셀에 담긴 값들 2차원 array에 담아서 return해준 것이 보이죠?

 

 

 

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  sheet = spreadsheet.getSheetByName('Sheet1');

  var test_value = sheet.getRange('C2:D5').getValues();
  Logger.log(test_value);
  Logger.log(test_value[0])
  Logger.log(test_value[0][1]);
  Logger.log(test_value[1])
  Logger.log(test_value[1][1]);
}


-- Result
[[item_name, price], [d, 1500.0], [b, 2000.0], [a, 3000.0]]
[item_name, price]
price
[d, 1500.0]
1500.0

 

return된 값의 형태가 array이기 때문에 위처럼 indexing을 이용해서 return된 값 중 특정 위치에 존재하는 값에 접근할 수 있습니다.

 

 

 

 

 

 

 

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  sheet = spreadsheet.getSheetByName('Sheet1');

  var test_value = sheet.getRange('A1').getValues();
  Logger.log(test_value);
}


-- Result
[[]]

 

 

 

A1셀은 비어있는데 비어있는 A1 셀의 값을 얻어온 경우 위처럼 공백이 return됩니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  sheet = spreadsheet.getSheetByName('Sheet1');

  var test_value = sheet.getRange(2, 3).getValues();
  Logger.log(test_value);
}

 

이번에는 getRange에 행/열 번호를 입력했습니다.

 

getRange(2, 3)을 봅시다.

이것의 의미는 row = 2, column = 3인 위치의 셀 객체를 얻어오라는 의미입니다.

 

row = 2는 2번 행이라는걸 알 수 있습니다.

column = 3은 아까 위에서 설명했듯이 A = 1, B = 2, C = 3이므로 C 컬럼을 의미합니다.

 

따라서 (2, 3)은 C2 셀을 의미한다고 보면 되겠네요.

 

 

 

 

코드를 실행했을 때 item_name이라는 값이 출력되며 item_name이라는 값은 C2셀에 있으므로 저희가 의도한대로 잘 나온 것을 알 수 있습니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

getRange와 getValue를 이용하면 다음과 같은 것도 가능합니다.

 

B3 셀에 입력된 값을 복사해서 C 컬럼에 입력된 값들 중 가장 아래에 있는 값보다 한칸 아래에 붙여넣는겁니다.

 

위같은 데이터가 있다면

B3 셀에 4라는 값이 입력되어있습니다.

이 4라는 값을 C 컬럼에 데이터가 입력된 가장 마지막 셀보다 한칸 아래에 4라는 값을 입력하는거죠.

 

 

 

결과를 미리 보면 위같은 형태가 될겁니다.

C컬럼의 가장 마지막보다 한칸 아래에 B3 셀에 있는 값인 4가 입력되었죠.

 

 

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  sheet = spreadsheet.getSheetByName('Sheet1');

  row = 2;
  col = 3;

  while (sheet.getRange(row, col).getValues()[0][0] != ""){
    row = row + 1;
  }

  sheet.getRange('B3').copyTo(sheet.getRange(row, col), SpreadsheetApp.CopyPasteType.PASTE_NORMAL);
}

 

코드는 위와 같습니다.

특징적인 점은 while loop를 이용한 것입니다.

row = 2

col = 3

으로 초기 행/열 번호를 지정해뒀습니다.

col = 3 이므로 C 컬럼을 의미하며 행 번호는 2입니다.

 

따라서 C2 셀부터 반복문을 시작한다는 의미입니다.

 

while loop의 조건을 보면 

 

 

  while (sheet.getRange(row, col).getValues()[0][0] != ""){
    row = row + 1;
  }

위같은 내용이 적혀있는데 row, col 위치의 셀에 대한 값을 얻어와서 그것이 공백("")이 아닌지 체크하는 것입니다.

 

그리고 공백이 아닐 경우 row 번호를 1 증가시키죠.

 

즉, C2부터 시작해서 C2가 공백이 아닐 경우 행번호(row)에 1을 더해서 C3 셀의 값이 공백인지 체크하고,

C3가 공백이 아닐 경우 행변호(row)에 1을 더해서 C4 셀의 값이 공백인지 체크하고,

C4가 공백이 아닐 경우 행변호(row)에 1을 더해서 C5 셀의 값이 공백인지 체크하고, ...

이같은 과정입니다.

 

주의할 점은 셀의 값이 공백인지 아닌지를 체크하는 것이기 때문에 C컬럼 중간에 공백인 셀이 있으면 그 셀에서 while loop가 멈출 수 있습니다.

 

 

 

위 데이터를 보면 C2부터 C8까지 모두 값이 입력되어있으므로

C9까지 반복문이 실행될 것이며 C9이 되는 순간 멈출겁입니다. 왜냐면 C9 셀에는 아무 값이 입력되어있지 않으니까요.

 

 

 

- sheet.getRange('B3').copyTo(sheet.getRange(row, col), SpreadsheetApp.CopyPasteType.PASTE_NORMAL);

그리고 이렇게 정해진 row, col 번호에 대해 B3 셀의 값을 복사/붙여넣기합니다.

 

위 코드를 실행하면 아래와 같이 저희가 의도했던 대로 C 컬럼에서 데이터가 입력된 가장 아래쪽 위치인 C8 셀보다 한칸 아래에 있는 C9 셀에 B3 셀의 값인 4가 붙여넣어진 것을 볼 수 있습니다.

 

 

 

 

 

 

- 참고

copyTo method 관련 내용 = https://cosmosproject.tistory.com/353

Google Spreadsheet Script : copyTo (셀의 값 복사 붙여넣기 하기, copy and paste)