[구글 앱스 스크립트 기본 3] 구글 시트에서 값 가져오고 값 입력하기, 변수 선언하기

 

 

[구글 앱스 스크립트 기본 3] 구글 시트에서 값 가져오고 값 입력하기, 변수 선언하기

 

 

글의 순서

• 한 개의 셀에 한 개의 값 입력하기
• 한 개의 셀의 값을 구글 스크립트로 가져오기
• 구글 앱스 스크립트에서 변수 이용하기
• 두 개 이상의 셀에서 값 가져오고 입력하기

 

 

1. 한 개의 셀에 한 개의 값 입력하기. 

지난 [구글 앱스 스크립트 기본 2] Spreadsheet Service 기본 원리 및 엑세스 권한 부여 글에서 만들었던 첫번째 스크립트 함수를 기억하시나요? 그때 우리가 만들었던 함수는 B2 셀에 'my first function'이라는 값을 입력하는 myFunction이라는 이름의 함수였고, 바로 그 함수가 한 개의 셀에 한 개의 값을 입력하는 스크립트입니다. 그때 만들었던 함수를 잠시 살펴보겠습니다.

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B2").setValue("my first function");
}

 

이 함수에서 우리는 가장 먼저 클래스 SpreadsheetApp을 호출한 후,  getActiveSpreadsheet 메서드를 실행시켜 스크립트가 현재 열려있는 (스크립트 파일과 결합되어 있는) 구글 시트 파일에 접근할 수 있도록 했습니다. 그 후에 (클래스 SpreadsheetApp의 메서드인) getActiveSheet 메서드를 실행시켜 스크립트가 현재 열려있는 구글 시트 파일의 특정 시트(탭)에 접근할 수 있도록 했고, (클래스 Sheet의 메서드인) getRange 메서드를 실행시켜 스크립트가 우리가 값을 입력하려고 하는 셀에 접근할 수 있도록 했습니다. 그리고 마지막으로 (클래스 Range의 메서드인) setValue 메서드를 실행시켜 구글 스크립트가 우리가 원하는 값을 입력시킬 수 있도록 했습니다. 

 

참고로 자바스크립트를 처음 접하시는 분이라면 클래스, 메서드가 뭔지 그리고 왜 어디에는 소괄호()가 들어가고 어디에는 들어가지 않는지, 또 왜 어디에는 소괄호 안에 값이 들어가고 어디에는 들어가지 않은지 이해가 되지 않을 수 있습니다. 이건 자바 스크립트 강의가 아니기에 이걸 최대한 쉽게 설명해 보자면 (더 깊게 들어가면 틀린 설명일 수도 있겠습니다만,) 메서드는 쉽게 말하면 함수의 일종이라고 생각하시면 되고, 클래스는 메서드를 만들기 위한 일종의 틀이라고 보시면 됩니다. 소괄호는 쉽게 말해 함수에 들어가는 매개변수(파라미터)를 입력하는 폼이라고 생각하시면 됩니다. 클래스는 함수의 일종은 아니기에 매개변수가 들어가는 소괄호가 필요 없지만, 메서드는 함수의 일종이기에 매개변수가 들어가는 소괄호가 필요합니다. 또한 함수의 매개변수는 함수에 따라 필수일 수도, 선택일 수도 있습니다. 위의 스크립트 예제에서 지금 활성화된 시트를 불러오는 getActiveSheet 메서드는 지금 활성화된 시트는 하나이기에 매개변수가 없어도 메서드가 불러올 시트가 무언지 알 수 있기에 그 시트를 불러올 수 있지만, 특정 범위를 불러오는 getRange 메서드의 경우는 어떤 범위를 불러오고 싶은지 알려주지 않으면 그 범위를 불러올 수 없기에 매개변수가 반드시 들어가야 합니다. 만약 제 설명이 뭔가 이상하거나 부족하다는 생각이 드는 분들은 자바 스크립트와 프로그래밍에 대해 이미 많이 알고 계실 분들이실 확률이 높기에, 이와 관련된 더 자세하고 정확한 다른 분들의 글을 참조하시면 될 것 같습니다. 

 

 

2. 한 개의 셀의 값을 구글 스크립트로 가져오기.

자 그럼 이번에는 구글 시트에 입력되어 있는 값을 구글 앱스 스크립트로 가져오는 스크립트를 써보도록 하겠습니다. 가장 먼저 저는 새로운 구글 시트 파일과 이에 연결된 구글 앱스 스크립트 파일을 하나 만들었습니다. 그리고 A1 셀에 "get this value"라는 값을 작성했습니다. 이 A1 셀에 있는 값을 구글 앱스 스크립트로 가져와보도록 하겠습니다. 

 

우리가 가져올 A1 셀의 값

 

가장 먼저 앱스 스크립트가 어떻게 하면 값을 가져올지 생각해 보겠습니다. 접근 방법은 값을 작성할 때와 동일합니다. 먼저 구글 시트 파일을 선택하고, 구글 시트 파일 안의 시트(탭)를 선택한 후, 값을 가져올 범위(셀)를 선택해야 할 겁니다. 여기까지의 코드를 작성해 보면 아래와 같습니다. 

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1")
}

 

우리가 원하는 범위를 지정했으니 마지막이자 다음 단계에서는 우리가 지정한 범위에서 값을 가져와야 합니다. 우리의 스크립트는 이미 범위(Range)까지 접근했기에 범위를 가져온 다음에 우리가 사용할 수 있는 기능들은 클래스 Range에 속해 있습니다. 그리고 클래스 Range의 메서드인 getValue가 바로 지정한 범위에서 값 하나를 가져오는 메서드입니다. 구글 앱스 스크립트 레퍼런스에 따르면 이 메서드에 파라미터는 없고 리턴 값은 해당 셀의 값(Object)입니다. 그럼 메서드 getValue를 이용해 스크립트를 완성해보겠습니다.

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1").getValue();
}

 

스크립트를 저장하고 (만약 저처럼 새로운 파일을 만드신 상황이라면 접근 권한을 허용해주어야 합니다) 스크립트를 실행해 보면 실행 로그에는 스크립트를 성공적으로 실행했다는 안내가 보이지만 구글 시트 파일 어디에도, 스크립트 어디에도 우리가 가져온 값이 표시되어 있지 않은 것을 볼 수 있습니다. 이는 우리가 작성한 스크립트는 값을 가져오기만 할 뿐, 가져온 값을 어디에 표시하라는 명령은 없기 때문입니다. 프로그래밍을 해보신 분들은 이 상황이 어색하지 않겠지만, 처음 스크립트를 작성해 보는 우리는 어디에도 결과가 표시되지 않는 상황이 낯설게 느껴질 겁니다. 따라서 우리가 만든 스크립트가 제대로 값을 가져오는지 확인해 보기 위해 우리가 가져온 값을 바로 옆 셀인 B1셀에 작성할 수 있도록 스크립트를 수정해 보겠습니다.

function myFunction() {
  var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1").getValue();
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B1").setValue(val);
}

 

이 스크립트를 실행해 보면 우리가 가져온 get this value라는 값이 B2셀에 잘 들어간다는 사실을 알 수 있습니다. 따라서 우리는 위의 코드가 특정 범위의 값을 잘 가져오고 잘 입력하고 있다는 사실을 알 수 있습니다. 그런데 위의 코드에서 갑자기 var = val이라는 부분이 생겨났습니다. 과연 이 부분은 왜 생겨난 걸까요?

 

B1 셀에 가져온 값이 잘 입력된 모습

 

 

3. 구글 앱스 스크립트에서 변수 이용하기

우리는 가장 처음으로 만들었던, 값을 입력하는 스크립트에서 setValue 메서드의 매개변수는 우리가 입력하고자 하는 값이라는 사실을 알고 있습니다. 그러면 우리가 2번에서 작성한, 값을 가져오는 스크립트가 1번에서 작성한 setValue의 매개변수가  되어야 하지 않을까라는 생각을 할 수 있습니다. 아래의 스크립트처럼 말입니다.

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B1").setValue(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1").getValue());
}

 

위의 코드를 실행시키면 2번의 마지막 코드와 정확히 같게 동작한다는 사실을 알 수 있습니다. 그렇다면 이 두 코드의 차이점은 뭘까요. 하나는 엄청 길고, 하나는 상대적으로 짧다? 맞는 말입니다. 정확히 말해보면 2번 마지막의 코드는 변수를 사용해 매개변수로 변수를 집어넣었고, 위의 2번 코드는 매개변수로 해당 값을 가져오는 코드를 그대로 집어넣었습니다. 그렇다면 변수를 사용하는 것과 사용하지 않는 것의 차이점은 무엇일까요?

1. 가장 큰 차이점은 바로 코드의 길이입니다. 지금 우리는 한두 줄짜리 코드를 만들고 있기 때문에 이 한두줄짜리 코드를 이해하는데 문제가 없을 수 있겠습니다만, 코드의 길이가 수백, 수천줄이 된다고 생각해 보고 한 줄의 길이가 엄청 길다고 생각해 보면 그 코드들을 읽고 이해하는데 더 많은 시간을 필요로 합니다. 혼자서 코드를 작성한다면 문제가 없을 것 같지만, 작성한 후 몇 달 뒤에 다시 와서 코드를 수정해야 할 때, 그리고 여럿이서 함께 작업을 할 때 이런 부분은 작업 시간과 효율성에 큰 영향을 미칠 수 있습니다. 이런 부분들을 코드의 가독성이라고 합니다. 변수를 사용하는 이유들 중 하나는 바로 이 가독성입니다.
2. 또 다른 차이점은 같은 코드의 재활용입니다. 만약 우리가 가져온 A1의 값을 한 번이 아닌 여러 번 사용한다고 가정한다면, A1의 값을 가져오는 코드를 여러번 사용하는 것보다는 짧은 이름을 가진 변수를 사용하는 것이 더 효율적입니다. 

 

변수는 쉽게 말하면 어떤 내용물을 담을 수 있는 상자라고 보시면 됩니다. 우리는 상자에 이름을 붙일 수 있고, 상자 안에는 원하는 것들을 집어넣을 수 있습니다. 2번의 마지막 코드를 예로 들자면 val이라는 이름을 붙인 상자에 저는 A1셀의 값을 가져오는 기능을 가진 코드를 담았고, 앞으로 또 A1 셀의 값을 가져오고 싶다면 그 기능을 가진 코드를 또 작성하는 것이 아닌 val이라는 이름을 붙인 상자를 불러오기만 하면 됩니다. 변수를 만들기(선언하기) 위해서는 가장 먼저 'var'을 쓰고 한 칸 띄운 다음에 변수 이름을 넣습니다. 그리고 '=' 문자를 넣은 후 원하는 내용물을 담으시면 됩니다. 이때 변수의 이름을 만드는 데에는 몇 가지 규칙이 있습니다. 첫째, 변수 이름은 숫자로 시작할 수는 없습니다. 둘째, 언더바(_)는 쓸 수 있지만 하이픈(-)은 쓸 수 없습니다. 셋째, 언더바(-)와 달러사인($) 등의 문자로 변수 이름을 시작할 수 있습니다. 넷째, 자바스크립트에 기본적으로 설정된 예약어는 변수 이름으로 쓸 수 없습니다. (이와 관련된 더 자세한 사항은 자바 스크립트 변수라는 키워드로 검색해 보시는 것을 추천합니다) 저는 변수를 선언할 때 변수 이름으로 주로 lowerCamelCase를 사용하기에 여기서도 그렇게 하겠습니다. 

 

자, 다시 제가 2번 마지막에서 예시로 든 스크립트로 돌아가보겠습니다. 이 스크립트에서도 중복되는 것들이 존재합니다. 특히 구글 시트 파일을 호출하는 메서드나, 구글 시트 파일의 특정 시트(탭)를 호출하는 메서드의 경우 구글 앱스 스크립트를 작성하면서 아주 많은 확률로 재활용되게 됩니다. 이런 부분들을 고려해서, 변수를 사용해 스크립트를 다시 써보면 이렇게 됩니다.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var val = sheet.getRange("A1").getValue();

  sheet.getRange("B1").setValue(val)
}

 

이 코드를 살펴보면 가장 먼저 ss(SpreadSheet)라는 변수에 현재 활성화된 구글 시트 파일을 가져오는 메서드를 담았습니다. sheet라는 변수에는 ss에서 선택된 구글 시트 파일에서 현재 활성화된 시트(탭)를 가져오는 메서드를 담았고, val이라는 변수에는 sheet라는 변수에서 선택된 시트의 A1셀의 값을 가져오는 메서드를 담았습니다. 그리고 마지막 줄에서는 sheet라는 변수에서 가져온 시트(탭)의 B1셀에 val이라는 변수에서 가져온 값을 입력하는 코드를 담았습니다. 어떠신가요. 이 문단의 처음에서 소개한, 변수를 하나도 사용하지 않은 스크립트와 바로 위의 스크립트 중 어떤 것이 더 읽고 이해하기 편하신가요?

 

4. 두 개 이상의 셀에서 값 가져오고 입력하기.

지금까지는 getValue와 setValue메서드를 통해 한 개의 셀에서 값을 가져오고 입력하는 방법에 대해 알아봤습니다. 구글 앱스 스크립트의 Spreadsheet Service에는 물론 두 개 이상의 셀에서 값을 가져오고 입력하는 기능을 지원하는 메서드도 존재합니다. 먼저, 두 개 이상의 셀에서 값을 가져와보도록 하겠습니다. 클래스 Sheet의 getRange메서드는 한 개의 셀을 범위로 가져오는 것뿐만 아니라 여러 개의 셀을 범위로 가져오는 것을 지원합니다. 이번 예제에서는 2X2 사이즈의 범위를 가지는 A3:B4 셀의 값을 가져오는 스크립트를 써보겠습니다. 가장 먼저 A3:B4 범위에 값을 넣어보도록 하겠습니다. 저는 행번호와 열번호를 넣었습니다만, 다른 값을 넣으셔도 무방합니다. 

 

우리가 가져올 A3:B4 범위의 값

 

한 개의 셀에서 값을 가져오고 입력했던 것과 같은 방법으로 우리가 원하는 클래스 Range의 메서드에 접근해 보겠습니다. 이 새로운 스크립트는 myFunction2라는 새로운 함수를 통해 구현해 보겠습니다. 가장 먼저 구글 시트 파일을 불러오고 (var ss), 구글 시트 파일의 시트(탭)를 불러오고 (var sheet), 우리가 원하는 범위를 불러와보겠습니다. 우리가 원하는 범위는 위에서 언급한 것처럼 A3:B4셀이고, 이 범위를 getRange메서드를 활용해 불러오는 새로운 변수 Range1을 만들어보겠습니다. 

function myFunction2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range1 = sheet.getRange("A3:B4");
}

 

다음은 우리가 지정한 두 개 이상의 범위의 값을 가져올 차례입니다. 클래스 Range의 getValues 메서드는 값 여러 개를 불러오는 기능을 가진 메서드입니다. 지금까지 우리가 사용해 왔던 getValue와 달리 뒤에 s가 붙습니다. 그 외의 사용법은 동일합니다. getValues의 메서드는 매개변수를 따로 사용하지 않습니다. getValues를 이용해 A3:B4 범위의 값을 가져오는 스크립트는 다음과 같습니다. 

function myFunction2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range1 = sheet.getRange("A3:B5");
  range1.getValues();
}

 

이제는 가져온 값을 새로운 범위에 입력해 보겠습니다. 같은 2X2 사이즈의 범위를 가지는 D3:E4 셀에 우리가 가져온 A3:B4셀의 값을 넣어보겠습니다. 가장 먼저 위에서 값을 가져오는 부분인 range1.getValues(); 코드를 val이라는 이름의 변수로 설정해줍니다. 그리고 새로운 범위인 D3:E4범위를 range2라는 이름의 변수로 설정해준 후, 그 범위에 우리가 가져온 val이라는 변수의 값을 입력해줄겁니다. 두 개 이상의 값을 입력하는 메서드는 setValue와 같은 클래스 Range에 속한 setValues라는 메서드입니다. getValues의 경우와 같이 setValue의 뒤에 s만 추가된 이름을 가진 메서드입니다. 작성된 스크립트는 다음과 같습니다. 

function myFunction2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range1 = sheet.getRange("A3:B4");
  var val = range1.getValues();
  var range2 = sheet.getRange("D3:E4");
  range2.setValues(val)
}

 

스크립트를 작성할 때 주의사항은, 스크립트에서 구글 시트 내의 범위를 직접 지정하거나 값을 직접 지정하는 매개변수를 작성할 때, 그 매개변수는 문자열 타입으로 작성해야 합니다. 즉, 따옴표를 넣어주어서 매개변수가 문자열이라는 사실을 명확히 해야 합니다. A3:B4가 아닌 "A3:B4"를, D3:E4 대신 "D3:E4"를 매개변수로 넣어주어야 합니다. 하지만 변수를 매개변수로 넣어줄 때는 따옴표 없이 변수 이름을 매개변수로 넣어야 합니다. 만약 변수의 이름 앞뒤로 따옴표를 넣게 되면 구글 앱스 스크립트는 그걸 변수가 아닌 문자열로 인식합니다. val이 아닌 "val"을 입력하게 되면 구글 앱스 스크립트는 그 매개변수가 우리가 선언한 val 이라는 변수가 가져오는 A3:B4범위의 값이 아닌 val이라는 문자열이라고 인식하게 되는 것이죠. 위의 스크립트를 저장하고 myFunction2라는 함수를 선택한 후 실행버튼을 눌러보면 아래의 스크린샷과 같이 잘 작동하는 것을 알 수 있습니다. 

 

D3:E4 셀에 값이 잘 입력된 모습