[구글 앱스 스크립트 기본 2] Spreadsheet Service 기본 원리 및 엑세스 권한 부여

 

 

구글 앱스 스크립트 기본 2, Spreadsheet Service 기본 원리 및 엑세스 권한 부여

 

 

Spreadsheet Service(스프레드시트 서비스)

구글 앱스 스크립트 내에  구글 시트 내에 여러 기능들을 수정할 수 있게 해주는 서비스는 Spreadsheet Service(스프레드시트 서비스)입니다. 이와 비슷하게 구글 앱스 스크립트에서 지메일의 여러 기능에 접근할 수 있게 해주는 서비스는 Gmail Service(지메일 서비스), 구글 드라이브 기능에 접근하게 해주는 서비스는 Drive Service(드라이브 서비스)입니다. 따라서 앱스 스크립트를 통해 구글 시트의 기능에 접근하는 방법에 대해 궁금한 경우 구글 앱스 스크립트 메뉴얼 중 Spreadsheet Service 항목을 찾으시면 됩니다. 

 

 

Spreadsheet Service(스프레드시트 서비스)의 작동원리

우리가 어떤 구글 시트 파일의 특정 시트(탭)의 특정 셀의 값을 변경하려고 하는 경우 가장 먼저 1. 구글 시트 파일을 열고, 2. 해당 시트(탭)를 열고, 3. 특정 셀을 선택한 후, 4. 그 셀의 값을 변경합니다. 앱스 스크립트의 Spreadsheet Service를 이용해 특정 셀의 값을 변경하려고 하는 경우도 이와 비슷한 과정을 따릅니다. (여기서 설명하는 클래스들은 지금은 이해하지 못해도 괜찮습니다. 다만, 이런 흐름으로 앱스 스크립트의 Spreadsheet Service가 동작한다고 이해하시면 편합니다. 여기에서 예로 든 클래스들은 다른 글을 통해 차차 설명하겠습니다.) 가장 먼저 특정 구글 시트 파일을 생성하거나 기존에 있는 구글 시트 파일에 접근할 수 있게 해주는 Spreadsheet Service의 클래스인 SpreadsheetApp을 먼저 호출합니다. 그 후에는 현재 활성화되어 있는 스프레드시트 파일을 선택하는 메서드인  getActiveSpreadsheet를 호출합니다. 여기까지가 1번의 과정입니다. 파일을 열었으면 원하는 시트를 선택해야 합니다. 우리는 1번 과정을 통해 파일에 접근한 상태이기에 특정 구글 시트 파일의 특정 시트(탭)에 접근할 수 있게 해주는 클래스 SpreadsheetApp의 메서드 중 현재 활성화된 시트에 접근할 수 있게 해주는 getActiveSheet 메서드를 이용해 우리가 원하는 시트에 접근합니다. 여기까지가 2번 과정입니다.  우리는 지금 원하는 시트(탭)에 접근한 상태입니다. 3번 과정에서는 구글 시트 파일의 특정 시트(탭)에서 원하는 셀을 선택해야 합니다. 시트(탭)에 접근하고 수정할 수 있게 해주는 클래스 Sheet의 getRange 메서드를 통해 구글 앱스 스크립트가 특정 셀에 접근할 수 있게 해 줍니다. 여기까지가 3번 과정입니다. 마지막으로 값을 변경하는 과정인 4번입니다. 우리는 기존에 있는 값을 지우고 새로운 값을 넣을 수도 있지만, 기존에 있는 값을 무시하고 새로운 값을 넣어주어 값을 변경하려고 합니다. 우리는 3번 과정을 통해 특정 구글 시트 파일의 특정 시트(탭)에 특정 셀(범위)에 접근해 있는 상태입니다. 따라서 마지막 단계는 클래스 Sheet가 아닌 특정 범위에 접근할 수 있게 해주는 클래스 Range의 메서드 중 단일 셀의 값을 설정하게 해주는 setValue 메서드를 통해 원하는 셀에 원하는 값(Value)을 설정할 수 있습니다. 

 

 

유저와 구글 앱스 스크립트 Spreadsheet Service의 작동 방식

 

 

구글 앱스 스크립트의 스프레드시트 서비스는 이처럼 우리 인간이 구글 시트 파일 내에 여러 기능들을 이용하는 순서와 거의 비슷하게 이루어집니다. 예를 들어, 구글 시트의 데이터 확인 규칙(Data Validation)을 작성하고자 하는 경우, 데이터 확인 규칙을 특정 셀/범위에 지정하기 위해서는 위와 같은 순서로 특정 범위까지 접근해야 합니다. 하지만 데이터 확인 규칙을 설정하는 것은 특정 셀/범위에 접근하는 것과 다른, 독립적인 이벤트입니다. (왜냐하면 데이터 확인 규칙을 어디에 넣을지 모르더라도, 어떤 규칙을 넣을지는 정할 수 있으니까요) 따라서 구글 앱스 스크립트를 통해 데이터 확인 규칙을 작성하기 위해서는 먼저 클래스 SpreadsheetApp의 메서드인 newDataValidation와 클래스 DataValidationBuilder의 여러 메서드(데이터 확인 규칙을 구성하는 여러 규칙들)와 build 메서드를 통해 데이터 확인 규칙을 정한다음, 위의 1~3단계를 통해 구글 앱스 스크립트가 특정 범위에 접근하게 한 다음, 클래스 Range의 setDataValidations 메서드를 통해 실제 시트에 데이터 확인 규칙을 넣을 수 있습니다.

 

물론 처음 앱스 스크립트를 접하는 분들의 입장에서 위와 같은 설명은 매우 어렵게 느껴질 것이 분명하고, 굳이 저 이야기를 지금 이해하실 필요는 없습니다. 다만, 앞으로 앱스 스크립트를 작성하고 이를 실행시킬 때, 대부분의 경우 이 Spreadsheet Service의 작동 원리에 따라 특정 메서드를 실행하기 위한 정확한 접근법을 따르지 않아서 일 확률이 높습니다. 물론 자바스크립트를 처음 접하시는 경우 자바스크립트 내의 문법을 따르지 않아서 오류가 나는 경우도 있겠습니다만,  Spreadsheet Service의 오류일 경우가 많습니다. 그렇다고 벌써부터 단념하시면 안 됩니다. 이 글은 앞으로 앱스 스크립트를 배워가시면서 어느 정도 앱스 스크립트의 작동 원리를 알겠다 싶은 경우 다시 방문하셔서 읽으시면 됩니다. 

 

 

Spreadsheet Service(스프레드시트 서비스)를 활용한 첫 스크립트

그럼 Spreadsheet Service(스프레드시트 서비스)를 활용해 위에서 언급한 특정 범위에 특정 값을 넣는 스크립트를 작성해보겠습니다. 가장 먼저 새로운 구글 시트 파일을 만들고, 그 파일과 결합된 앱스 스크립트 파일을 하나 만듭니다. 저는 구글 시트 파일과 앱스 스크립트 파일 모두 '[구글 앱스 스크립트 기본 2] Spreadsheet Service 기본 원리 및 액세스 권한 부여'라는 이름으로 변경하겠습니다. 

 

 

 

 

앱스 스크립트 파일을 만들게 되면 myFunction으로 이름이 붙여진 function을 볼 수 있습니다. 자바 스크립트에서 함수(function)는 특정 작업을 수행하거나 값을 계산하는 시퀀스를 가진 명령문입니다. 함수를 만드는 것을 함수를 선언한다고 말하는데 함수를 선언하기 위해서는 소문자로 'function'을 쓰고 한칸한 칸 띄운 후 함수의 이름을 쓰고 띄지 않고 바로 (소괄호)를 열고 닫습니다. 그 후에 한 칸을 띄운 후 [중괄호]를 열고 닫습니다. 함수를 구성하는 시퀀스를 가진 명령문은 이 {중괄호} 안에 들어가게 됩니다. (소괄호) 안에는 매개변수가 들어가게 됩니다. 자바 스크립트 함수에 대해서는 이를 잘 설명하신 다른 분들의 글을 참고하시면 될 것 같습니다. 참고로 자바스크립트 함수의 이름은 첫 단어는 모두 소문자로 쓰고 그다음 단어부터는 단어의 첫 글자만 대문자로 쓰는 lowerCamelCase로 주로 작성합니다. 우리는 이미 선언된 myFunction에 새로 만든 구글 시트 파일의 첫 번째 시트인 Sheet1 시트의 B2셀에 'my first function'이라는 값을 넣어주는 시퀀스를 넣어보겠습니다. 

 

가장 먼저 위에서 설명한 1번 단계인 현재 활성화된 구글 시트 파일을 선택하는 스크립트를 써보겠습니다. 여기서 활성화된 구글 시트 파일이라는 말은 현재 작업중인 구글 앱스 스크립트 파일과 결합된 구글 시트 파일을 뜻합니다. 가장 먼저 클래스 SpreadsheetApp을 호출한 후, 현재 활성화된 구글 시트 파일을 선택하는 getActiveSpreadsheet 메서드를 호출합니다. 

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet()
}

 

파일을 선택했으니, 2번 단계인 우리가 선택한 구글 시트 파일내에서 접근할 시트를 알려주는 스크립트를 써야 합니다. 물론 우리가 만든 구글 시트 파일에는 'sheet1'이라고 이름 붙여진 시트 1개만 존재하지만, 구글 앱스 스크립트는 이를 알 방법이 없으므로 이를 일일이 알려주어야 합니다. 1번과 같은 클래스 SpreadsheetApp에 속해있는, 활성화된 시트(탭)를 선택하는 메서드인 getActiveSheet를 호출합니다. 

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
}

 

시트를 선택한 후에는 선택한 시트 내에 우리가 원하는 범위를 선택하는 3번 단계를 수행하는 스크립트를 서야 합니다. 우리는 B2셀에 새로운 값을 넣으려고 하기 때문에 B2셀을 선택해야 합니다. 우리는 이미 2번 단계를 통해 시트까지 접근한 상태이기 때문에 구글 시트 파일에 접근하게 해주는 클래스 SpreadsheetApp가 아닌, 특정 시트에 접근하게 해주는 클래스 Sheet의 메서드를 활용합니다. 다만, 1번 단계에서 처럼 클래스 Sheet를 따로 호출하지 않는 이유는 이미 2번까지의 스크립트를 통해 구글 앱스 스크립트가 Sheet까지 접근했기 때문이라고 생각하시면 편합니다. 클래스 Sheet에서 특정 범위에 접근할 수 있게 해주는 메서드인 getRange를 이용해 우리가 원하는 B2셀을 선택합니다. getRange는 원하는 범위를 지정할 수 있는 다양한 방법의 매개변수(파라미터)를 제공합니다. 우리의 예제에서는 가장 간단하게 직접 셀을 지정하는 a1Notation 타입의 매개변수를 활용해 원하는 범위를 지정합니다. 따라서 "B2"라는 값을 소괄호 안에 넣으면 됩니다. 주의할 점은 자바 스크립트로 선언된 변수가 아닌 문자열을 어떤 매개변수로 지정할 때는 이게 변수가 아닌 문자열이라는 것을 명확하게 앱스 스크립트에게 알려주어야 합니다. 따라서 문자열을 알려주는 큰따옴표 또는 작은따옴표를 B2값의 앞뒤로 꼭 넣어주어야 합니다. 

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

 

우리가 원하는 범위까지 접근했으니, 그 범위에 원하는 값을 넣는 마지막 4단계 스크립트를 작성할 때입니다. 먼저, 우리는 원하는 범위(Range)까지 접근한 상태입니다. 따라서 클래스 SpreadsheetApp이나 클래스 Sheet가 아닌 범위를 컨트롤 할 수 있게 해주는 클래스의 메서드를 사용해야 합니다. 이때 사용되는 것이 클래스 Range입니다. 클래스 Range의 메서드 중 하나의 값을 지정된 범위에 넣어주는 setValue 메서드를 사용합니다. setValue는 매개변수로 우리가 넣고 싶은 값을 받습니다. 우리는 my first function이라는 말을 넣기로 했으니 이를 setValue에 뒤따르는 소괄호 사이에 넣어주면 됩니다. 이때 주의할 점은, 우리는 문자열을 넣는 것이기 때문에 my first function이 문자열이라는 것을 알려주는 따옴표를 앞뒤로 꼭 넣어주어야 합니다. 그리고 마지막으로 해당 구문을 종료하는 것을 알려주는 세미콜론을 붙여줍니다.

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

 

이렇게 우리는 myFunction이라는 이름의 첫 함수를 만들었습니다. 이제 우리가 만든 첫 스크립트의 첫 함수를 실행해 볼 때입니다. 

 

 

구글 앱스 스크립트 에디터의 UI

 

구글 앱스 스크립트 에디터

 

 

우리가 작성한 함수를 실행하기 전에 구글 앱스 스크립트 에디터의 인터페이스에 대해 간단히 알아보겠습니다.

1. <> : 현재 구글 앱스 스크립트 파일의 에디터 탭에 있음을 알려줍니다. 위/아래에 있는 다른 아이콘들을 통해 각각 다른 탭으로 이동해 해당 구글 앱스 스크립트 파일의 다른 옵션들을 조정하거나, 파일에 대한 정보를 볼 수 있습니다. 

2. Code.gs: 현재 작업중인 구글 앱스 스크립트 파일 내에 있는 코드 파일들을 보여줍니다. 현재 우리는 Code.gs라는 이름의 파일에 작업 중이라는 것을 알 수 있습니다. gs는 구글 앱스 스크립트 파일의 확장자이며 위의 + 버튼을 통해 같은 파일 내에 다른 gs파일을 추가할 수 있습니다. gs파일뿐만 아니라 html파일도 만들 수 있는데 이와 관련된 html 서비스에 대해서는 나중에 심화 편에서 다루겠습니다.

3. 💾: 현재 작업중인 파일을 저장하는 버튼입니다. 스크립트를 작성 중 틈틈이 스크립트를 저장하는 습관을 들이는 것이 좋습니다.

4. ▷ Run: 스크립트 파일 내의 특정 함수를 실행시키는 버튼입니다. 현재 이 버튼은 비활성화되어 있고, 그 옆의 No Functions이라는 버튼도 비활성화되어 있는 것을 알 수 있습니다. 이는 스크립트를 작성한 후 저장을 하지 않았기 때문입니다. 3번의 저장 버튼을 누르면 이 Run 버튼과 더불어 옆의 두 버튼도 활성화됩니다.

5. Execution log: 함수를 실행하는 경우 함수 실행과 관련된 로그를 표시하는 창입니다. 4번 Run을 누르는 경우 Excution log가 활성화 되며 에디터 하단에 실행 로그가 나타납니다. 

 

 

구글 앱스 스크립트 에디터에서 myFunction 실행

 

 

작성한 스크립트를 저장하면 위의 Run 버튼이 활성화 되고 그 옆에 자동으로 myFunction이 선택된 것을 알 수 있습니다. 기본적으로는 가장 위쪽에 있는 함수나 가장 최근에 실행한 함수가 선택되어 있습니다. 현재 우리가 실행하려고 하는 함수가 제대로 선택되어 있기 때문에 바로 Run 버튼을 누르시면 됩니다. 

 

 

SpreadsheetApp에 엑세스 권한 부여

Run 버튼을 누르게 되면 함수가 실행되는 대신 Authorization required(승인 필요). This project requires your permission to access your data(이 프로젝트에서 내 데이터에 액세스 하려면 내 승인이 필요합니다).라는 경고창이 뜨게 됩니다. 이는 앱스 스크립트를 실행하는 주체와 해당 구글 시트 및 앱스 스크립트 파일의 소유권이 다르기 때문에 나오는 경고창입니다. 만약 자신의 계정이 소유하고 있는 구글 시트 파일에 그 계정으로 로그인한 상태에서 직접 값을 넣는다면 그 값을 넣는 주체는 그 파일을 소유하고 있는 그 계정이기 때문에 권한문제가 발생하지 않습니다. 하지만 스크립트에서 값을 넣는다면 그 값을 넣는 주체는 스크립트, 정확히는 Spreadsheet Service(스프레드시트 서비스)입니다. 따라서 이 주체에게 내 소유의 파일에 접근해 그 파일을 변경할 수 있는 권한을 주어야 합니다. 이 권한을 주기 위해 Review permissions(권한 검토) 버튼을 누릅니다.

 

 

권한 요청 팝업

 

 

권한 검토 버튼을 누르면 해당 권한을 부여하는 주체, 즉 계정을 선택하라고 합니다. 당연히 그 파일을 소유하고 있는 계정을 선택해야 합니다. 계정을 선택하면 구글에서 확인하지 않은 앱이라는 경고창이 뜹니다. 이는 해당 스크립트(앱)가 구글에서 정하는 보안에 대한 가이드라인을 따르지 않기 때문에 해당 스크립트(앱)가 위험할 수도 있다고 구글이 판단했기 때문입니다. 하지만 이 스크립트(앱)는 우리가 직접 작성하고 그 어떠한 위험한 코드가 있는 것이기 아니기 때문에 고급설정을 누르고 스크립트 파일에 기본 원리 및 액세스 권한을 부여합니다. 이후 마지막으로 나오는 권한 설정 팝업에서 Allow(허용)을 눌러 권한을 부여하면 됩니다. 참고로 이 권한 부여는 최초 1회 부여하게 되면 이후에는 함수를 실행하더라도 다시 권한을 부여하지 않아도 됩니다. 

 

 

구글 앱스 스크립트에 권한 부여 1

구글 앱스 스크립트에 권한 부여 2

 

 

권한을 부여했음에도 우리가 지정한 셀에 우리가 넣을 'my first function' 값은 아직 들어가지 않았습니다. 이는 권한을 부여하는 과정에서 우리가 실행한 myFunction 함수가 취소 되었기 때문입니다. 다시 구글 앱스 스크립트 에디터로 돌아가 Run버튼을 눌러준다면 아래 실행 로그에 실행이 성공적으로 완료되었다는 메시지가 나오고, 파일을 확인해 보면 우리가 넣으려고 했던 'my first function' 값이 B2셀에 들어가 있는 것을 볼 수 있습니다. 

 

 

구글 앱스 스크립트 함수 실행 완료

실제 B2 셀에 값이 들어간 상황