들어가며

구글 드라이브^{Google Drive}나 G 스위트^{G Suite}의 매력 중 하나는 구글 앱스 스크립트^{Google Apps Script} 기반의 자동화가 가능하다는 점입니다. 구글 스프레드^{Google Spreadsheet} 구글 문서^{Google Document}의 경우 파일에 종속된 스크립트를 작성할 수도 있지만, 독립적인 구글 앱스 스크립트 프로젝트를 만들고 구글의 다양한 서비스들에 접근하는 것이 가능합니다.

이 글에서는 구글 스프레드시트를 조작하는 간단한 구글 앱스 스크립트 프로젝트를 만들고, 이 스크립트를 구글 밖에서 다른 프로그래밍 언어로 호출하는 방법을 소개하고자 합니다.

스프레드시트 준비: 주가 데이터

먼저 구글 드라이브에서 작업을 위한 디렉터리 하나를 생성해줍니다. 그리고 구글 앱스 스크립트에서 접근할 구글 스프레드시트를 하나 만듭니다. 제목은 주가 데이터라고 붙여줍니다.

A1에 GOOG, B1에 =GOOGLEFINANCE(A1)를 입력합니다. 이는 GOOG(Alphabet Inc Class C)의 주가 정보를 가져오는 함수입니다.

마지막으로 URL을 확인합니다. 주소에서 /spreadsheets/d/ 다음부터 다음 /까지가 문서의 고유 아이디가 됩니다.

https://docs.google.com/spreadsheets/d/139PdU07J3JAT-P9Qn5rzur3P6LIlx5aNNny5LkEuA9c/edit#gid=0

이 주소에서 스프레드 시트의 고유 아이디는 아래와 같습니다.

139PdU07J3JAT-P9Qn5rzur3P6LIlx5aNNny5LkEuA9c

구글 앱스 스크립트에서 이 스프레드시트 문서에 접근하기 위해 이 아이디를 기록해둡니다.

구글 앱스 스크립트에서 구글 스프레드시트 데이터 다루기

구글 앱스에서는 두 가지 방식으로 스크립트를 작성할 수 있습니다. 첫 번째 방법은 문서 자체에 포함된 스크립트를 작성하는 방식입니다. 예를 들어 스프레드시트에 스크립트를 추가할 수 있습니다. 이 스크립트는 스프레드 시트에 종속적이고 드라이브에서는 보이지 않습니다. 이를 사용하면 스프레드시트에서 사용가능한 커스텀 함수를 작성하는 등 몇 가지 장점을 가지고 있습니다.

두 번째 방법은 다른 문서들과는 독립된 구글 앱스 스크립트 프로젝트를 만드는 방법입니다. 구글 앱스 스크립트를 독립된 프로젝트로 만들면 다른 문서와 마찬가지로 드라이브에서 관리할 수 있습니다. 또한 다수의 서비스에 접근할 때 자연스러운 구조로 스크립트를 작성할 수 있습니다. 하지만 커스텀 함수를 만드는 등 특정 문서에 특화된 기능은 만들 수 없습니다.

스크립트를 만들 때는 필요에 따라서 적절한 방식을 선택해서 사용하시기 바랍니다. 여기서는 두 번째 방법을 사용해 구글 앱스 스크립트에서 구글 스프레드시트의 데이터를 다루는 방법을 알아보겠습니다.

구글 앱스 스크립트 프로젝트 준비

먼저 구글 앱스 스크립트 프로젝트를 생성합니다. 구글 드라이브에서 새로 만들기를 선택하고 더 보기에서 구글 앱스 스크립트^{Google Apps Script}를 찾을 수 있습니다.

구글 앱스 스크립트는 기본적으로 활성화되어있지 않기 때문에, 목록에 보이지 않는다면 연결할 앱 더보기를 선택해 앱을 추가해주어야합니다. 드라이브에 앱 연결 팝업이 뜨면 ’google apps script’를 검색해서 연결을 해줍니다. 성공적으로 연결이 되면 이제 구글 앱스 스크립트 프로젝트를 생성할 수 있습니다.

문서가 생성되면 스프레드시트와 마찬가지로 먼제 제목을 붙여줍니다. 여기서는 ’First Project’라고 문서 제목을 붙여주었습니다.

이제 이 프로젝트에서 앞서 생성해둔 주가 데이터 문서에서 데이터를 가져오거나 추가하는 함수를 만들어보겠습니다.

스프레드시트에 데이터를 읽어오는 스크립트

구글 앱스 스크립트는 자바스크립트로 작성합니다. ES6 등 최신 자바스크립트 문법은 지원하지 않으므로 주의가 필요합니다.*

B1 셀에 있는 데이터를 가져오는 새로운 함수를 추가해보겠습니다.

function myFunction() {
  Logger.log(getAlphabetStockPrice())
}

function getAlphabetStockPrice() {
  var SHEET_FILE_ID = "<SPREADSHEET_ID>"
  var sheet = SpreadsheetApp.openById(SHEET_FILE_ID).getSheetByName('시트1')
  return sheet.getRange('B1').getValue()
}

SHEET_FILE_ID에는 앞서 준비한 스프레드시트의 ID를 지정합니다. SpreadsheetApp.openById 메서드는 스프레드시트 ID로 스프레드시트 객체를 생성합니다. 그리고 getSheetByName로 특정 시트를 가져옵니다. 스프레드시트 생성시 자동으로 만들어지는 시트의 이름은 시트1이므로 이 시트를 가져옵니다. 스프레드시트에서 특정 영역을 범위(Range)라고 부릅니다. getRange 메서드로 B1을 가져온 후 getValue함수로 이 범위의 값을 가져옵니다. 마지막으로 Logger.log로 이 내용을 출력합니다.

이 내용을 저장합니다(커맨드 + S). 메뉴바에서 실행 아이콘을 클릭합니다.

실행되지 않고 인증이 필요하다는 팝업이 출력됩니다. 이 인증이 왜 생각해보겠습니다. 사용자가 스프레드시트나 앱스 스크립트에서 어떤 작업을 할 때는 사용자의 권한으로 작업을 실행합니다. 하지만 앱스 스크립트에서 스프레드시트에 접근하려고 하면 이 작업을 하는 주체가 구글 앱스 스크립트가 됩니다. 사용자는 앱스 스크립트를 통해서 간접적으로 스프레드시트에 접근합니다. 하지만 기본적으로 구글 앱스 스크립트에는 스프레드시트에 접근할 권한이 없습니다. 따라서 이 작업을 수행하기 위해서는 추가적은 인증 절차가 필요합니다.

권한 검토를 클릭해 인증을 진행합니다. 인증을 진행합니다.

확인되지 않은 앱이라는 경고가 출력될 수 있습니다. 아직 개발중인 상태이므로 무시하고 진행합니다. 고급을 클릭하고 First Project으로 이동을 클릭합니다.

스프레드시트에 대한 접근을 허용해줍니다. 구글 앱스 스크립트로 돌아오면 다시 실행 버튼을 클릭합니다. 아무일도 일어나지 않습니다. 로그를 보기 위해서 커맨드 + 엔터를 입력합니다.

로그가 출력되었습니다. 정상적으로 스프레드시트의 값을 가지고 왔습니다.

스프레드시트에 데이터를 추가하는 스크립트

이번에는 스프레드시트에 행을 추가하는 함수를 작성해보겠습니다.

function appendStockRow(ticker) {
  var SHEET_FILE_ID = "<SPREADSHEET_ID>"
  var sheet = SpreadsheetApp.openById(SHEET_FILE_ID).getSheetByName("시트1")
  var row = [ticker, "=GOOGLEFINANCE(\"" + ticker + "\")"]
  sheet.appendRow(row)
}

이 함수는 ticker 매개변수를 받아 앞서 만든 주가 데이터 스프레드시트의 마지막 줄에 특정 주식의 주가 정보를 추가하는 함수입니다. sheet를 가져오는 부분은 getAlphabetStockPrice와 같습니다. 그 다음에 row 변수에 스프레드시트에 추가할 내용을 배열로 작성합니다. 그리고 appendRow 메서드를 사용해 이 내용을 실제로 추가해줍니다.

myFunction의 내용을 아래와 같이 수정합니다.

function myFunction() {
  appendStockRow('SBUX')
}

이제 저장하고 myFunction을 다시 실행해봅니다. 스크립트 상에서는 아무일도 일어나지 않습니다.

하지만 스프레드시트에 가보면 새로운 행이 추가된 것을 확인할 수 있습니다.

위에서 작성한 myFunction 함수를 개선해서 임의의 ticker를 전달받을 수 있도록 수정해보겠습니다.

function myFunction(ticker) {
  appendStockRow(ticker)
}

이제 myFunction 함수는 ticker 변수를 전달받을 수 있습니다. 여기까지 구글 앱스 스크립트에서 스프레드시트의 데이터를 가져오거나 추가하는 방법에 대해 알아보았습니다.

구글 앱스 스크립트 외부에서 실행하기

여기까지는 구글 앱스 내부에서 스크립트를 실행하는 방법을 알아보았습니다. 외부에서 이 스크립트를 실행하려면 구글 클라우드 플랫폼을 넘나드는 여정이 필요합니다. 여기서부터는 구글 앱스 외부에서 스크립트를 실행하는 방법에 대해 한 단계씩 알아보겠습니다.

실행가능한 API로 구글 앱스 스크립트 프로젝트 배포

먼저 외부에서 앱스 스크립트를 실행하려면 실행가능한 API로 프로젝트를 배포해야합니다. 게시 메뉴에서 API 실행 가능으로 배포을 선택합니다.

프로젝트 배포를 위한 팝업이 나타납니다.

버전에 0.1을 입력하고 배포를 클릭합니다. 경고가 출력될 수도 있습니다. 내용을 확인하고 계속을 클릭합니다.

배포가 완료되면 API ID가 출력됩니다. 외부에서 스크립트를 호출할 때는 이 ID가 필요하므로 이를 기록해둡니다.

구글 클라우드 플랫폼에서 OAuth 토큰 준비

First Project의 리소스 메뉴에서 클라우드 플랫폼 프로젝트^{Cloud Platform 프로젝트}를 선택합니다.

구글 클라우드 플랫폼에는 이 앱스 스크립트에 연동된 프로젝트가 자동으로 생성됩니다. 아래의 API 콘솔 보기를 클릭하면 구글 클라우드 플랫폼으로 이동합니다.

왼쪽 메뉴에서 API 및 서비스 > 라이브러리를 선택합니다. Apps Script를 검색해서 Apps Script API를 찾습니다.

상세 페이지에서 사용 설정을 클릭합니다. 자동으로 대시보드로 이동합니다. API 사용 설정에 약간의 시간이 걸립니다. 설정이 완료되면 왼쪽 메뉴의 사용자 인증 정보를 선택합니다.

이미 OAuth 2.0 클라이언트가 하나 만들어져있는 것을 확인할 수 있습니다. 이 OAuth 클라이언트 ID는 웹 애플리케이션 용이라 그냥 사용할 수 없습니다. 리스트 위 쪽의 사용자 인증 정보 만들기를 클릭하고, OAuth 클라이언트 ID를 선택합니다. 그 다음 화면에서 기타를 선택하고 적절한 이름을 붙여줍니다.

다시 리스트로 돌아오면 방금 만든 항목의 오른쪽에 있는 다운로드 아이큰을 클릭해 인증 정보를 다운로드 받습니다. 이 파일은 client_secret_<ID>.apps.googleusercontent.com.json 형식으로 저장됩니다. 스크립트를 호출할 때 꼭 필요한 내용이므로 안전하게 보관해둡니다.

구글 앱스 스크립트를 실행하는 루비 코드 작성

이제 거의 다 왔습니다. 프로그래밍 언어를 사용해 앞서 작성한 스크립트를 실행만 하면 됩니다. 프로그래밍 언어를 사용해 OAuth 인증을 하거나 스크립트를 실행하는 방법에 대해서는 공식 문서에 설명되어있습니다. 이 내용을 바탕으로 외부 실행을 위한 코드를 작성해보겠습니다.

먼저 스크립트 작성을 위한 디렉터리를 생성해줍니다. 앞서 저장해둔 clients_secret_<ID>.json 파일을 이 디렉터리 아래에 client_secrets.json으로 저장합니다. 그리고 아래 내용을 gas.rb라는 이름으로 저장합니다.

require 'json'
require 'fileutils'

require 'google/apis/script_v1'
require 'googleauth'
require 'googleauth/stores/file_token_store'

OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'.freeze
APPLICATION_NAME = 'Google Apps Script'.freeze
CLIENT_SECRETS_PATH = './client_secrets.json'.freeze
CREDENTIALS_PATH='./gas_token.yaml'
SCOPE = ['https://www.googleapis.com/auth/spreadsheets']
SCRIPT_ID = '<SCRIPT_ID>'.freeze

def credentials
  client_secrets = JSON.parse(File.read(CLIENT_SECRETS_PATH))
  client_id = Google::Auth::ClientId.from_hash(client_secrets)
  token_store = Google::Auth::Stores::FileTokenStore.new(file: CREDENTIALS_PATH)
  authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
  user_id = 'default'
  credentials = authorizer.get_credentials(user_id)
  if credentials.nil?
    url = authorizer.get_authorization_url(base_url: OOB_URI)
    puts 'Open the following URL in the browser and enter the ' \
         "resulting code after authorization:\n" + url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI
    )
  end
  credentials
end

def script_service
  service = Google::Apis::ScriptV1::ScriptService.new
  service.authorization = credentials
  service
end

def run_script(ticker)
  parameters = [ticker]
  request = Google::Apis::ScriptV1::ExecutionRequest.new(
    function: 'myFunction', parameters: parameters
  )
  script_service.run_script(SCRIPT_ID, request)
end

run_script('DATA')

SCRIPT_ID = '<SCRIPT_ID>'.freeze에서 <SCRIPT_ID>는 앞서 구글 앱스 스크립트 프로젝트에서 생성한 API의 ID를 지정해줍니다. credentials 함수는 OAuth 인증을 위한 함수입니다. 뒤에서 살펴보겠지만 이 코드를 실행하면 실제로 CLI에서 OAuth 인증이 진행됩니다. script_service는 credentials를 기반으로 구글 앱스 스크립트의 서비스 객체를 가져오는 함수입니다. 마지막으로 run_script에서는 script_service 객체로 앞서 만들었던 구글 앱스 스크립트 프로젝트를 실행합니다. ExcutionRequest 객체를 생성할 때 function매개변수에는 실행하고자 하는 함수를 지정하고, parameters에는 배열로 호출하는 함수의 매개변수를 배열로 지정해줍니다.*

이제 이 루비 스크립트를 실행해보겠습니다. google-api-client 젬이 필요합니다. 이를 설치해줍니다.

$ gem install google-api-client --no-ri --no-rdoc

이제 스크립트를 실행합니다.

$ ./ruby gas.rb
Open the following URL in the browser and enter the resulting code after authorization:
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force&client_id=<CLIENT_ID>.apps.googleusercontent.com&include_granted_scopes=true&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets

스크립트를 실행하면 위와 같이 주소 하나가 출력됩니다. 이 주소를 그대로 복사해서 웹브라우저의 주소창에 붙여줍니다. 이 스크립트에서 구글 스프레드시트의 사용할 수 있도록 OAuth 인증을 진행합니다. OAuth 진행이 완료되면 긴 문자열로 된 코드가 출력됩니다. 웹브라우저에서 이 코드를 복사해서 실행중인 커맨드라인에 붙여줍니다.*

실행이 정상적으로 끝나면 앞서 만들었던 주가 데이터 스프레드시트를 열어봅니다.

DATA 티커의 주가 데이터가 추가된 것을 확인할 수 있습니다 🎉

서비스 계정을 사용한 연동

여기서 사용자로 OAuth 인증을 하는 과정은 번거로워 보입니다. 구글 클라우드 플랫폼에서는 이 대신 사용할 수 있는 서비스 계정^{Service Account}이라고 하는 특별한 계정을 지원하고 있습니다. 이 계정은 자동화에 좀 더 적합하지만 구글 이슈 트레커에 따르면 아직 Service Acocunt를 사용해 구글 앱스 스크립트를 실행하는 것은 불가능하다고 합니다.

더 읽을거리

• Standalone Scripts | Google Developers
• Ruby Quickstart | Google Apps Script
• Executing Functions using the Apps Script API | Google Apps Script