반드시 알아야 할 구글 스프레드시트 스크립트 기능 5가지

구글 스프레드시트(Google Sheets)는 마이크로소프트 엑셀에서 가능한 거의 모든 작업을 클라우드 환경에서 처리할 수 있는 강력한 스프레드시트 도구입니다. 그러나 구글 스프레드시트의 진짜 힘은 함께 제공되는 구글 앱스 스크립트(Google Apps Script) 기능에 숨어 있습니다.

구글 앱스 스크립트는 구글 스프레드시트뿐만 아니라 구글 문서, 지메일, 구글 애널리틱스 등 사실상 모든 구글 클라우드 서비스에서 동작하는 백그라운드 스크립팅 도구입니다. 이를 활용하면 개별 앱의 작업을 자동화하고, 여러 앱을 서로 연동하여 훨씬 강력한 워크플로우를 만들 수 있습니다.

이 글에서는 구글 앱스 스크립트를 시작하는 기본 방법부터, 스프레드시트에서 셀 데이터를 읽고 쓰는 간단한 스크립트 작성법, 그리고 실무에서 가장 유용하게 쓰이는 고급 스크립트 함수까지 차근차근 살펴보겠습니다.

구글 앱스 스크립트 만드는 방법

구글 스프레드시트 안에서 바로 첫 번째 스크립트를 만들 수 있습니다. 메뉴에서 도구(Tools)를 선택한 뒤 스크립트 편집기(Script Editor)를 클릭하세요.

스크립트 편집기 창이 열리면 기본적으로 myFunction()이라는 이름의 함수가 생성됩니다. 바로 이곳에서 구글 스크립트를 작성하고 테스트할 수 있습니다.

간단한 예제로, 한 셀에서 데이터를 읽어 계산을 수행한 후 그 결과를 다른 셀에 출력하는 함수를 만들어 보겠습니다.

셀에서 데이터를 가져올 때 사용하는 함수는 getRange()와 getValue()입니다. 행(row)과 열(column) 번호로 셀을 지정할 수 있습니다. 예를 들어 2행 1열(A열)에 값이 있다면 스크립트의 첫 부분은 다음과 같습니다.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

이 코드는 해당 셀의 값을 data 변수에 저장합니다. 이제 이 값에 계산을 적용한 뒤 다른 셀에 결과를 쓰면 됩니다. 함수의 마지막 부분은 다음과 같습니다.

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

함수 작성을 마쳤으면 디스크 아이콘을 눌러 저장합니다.

새로 만든 스크립트를 처음 실행할 때(실행 아이콘 클릭)는 해당 스크립트가 내 구글 계정에서 동작하도록 권한 승인을 해야 합니다.

권한을 허용하고 실행하면, 스크립트가 계산 결과를 대상 셀에 자동으로 입력한 것을 확인할 수 있습니다.

기본적인 구글 앱스 스크립트 함수 작성법을 익혔다면, 이제 좀 더 고급 기능들을 살펴보겠습니다.

getValues로 배열 한 번에 불러오기

배열(array)을 활용하면 스크립트로 스프레드시트 데이터를 다루는 수준을 한 단계 끌어올릴 수 있습니다. 구글 앱스 스크립트에서 getValues로 변수를 불러오면, 해당 변수는 시트의 여러 값을 담을 수 있는 배열이 됩니다.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

data 변수는 시트의 모든 데이터를 담고 있는 다차원 배열입니다. 이 데이터에 계산을 적용하려면 for 반복문을 사용합니다. 반복문의 카운터가 각 행을 순회하고, 열은 데이터를 가져올 열 위치에 맞춰 고정하면 됩니다.

예제 스프레드시트에서 세 개 행의 데이터를 다음과 같이 처리할 수 있습니다.

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

앞서와 같이 저장 후 실행하면 모든 계산 결과가 스프레드시트의 2열에 채워지는 것을 볼 수 있습니다.

여기서 주목할 점은 배열 변수에서 셀과 행을 참조하는 방식이 getRange 함수와 다르다는 것입니다.

data[i][0]은 배열의 차원을 나타내며, 첫 번째 차원이 행, 두 번째 차원이 열입니다. 두 인덱스 모두 0부터 시작합니다.

getRange(i+1, 2)는 i=1일 때 두 번째 행(첫 번째 행은 헤더이므로)을 가리키며, 2는 결과가 저장되는 두 번째 열을 의미합니다.

appendRow로 새 행에 결과 쓰기

새로운 열이 아니라 새로운 행에 데이터를 추가해야 하는 경우도 있습니다. 이럴 때는 appendRow 함수를 사용하면 간단합니다. 이 함수는 시트의 기존 데이터를 전혀 건드리지 않고, 맨 아래에 새 행을 덧붙여줍니다.

예를 들어 1부터 10까지 세면서, 2의 배수를 Counter 열에 표시하는 함수를 만들어 보겠습니다.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

이 함수를 실행하면 아래와 같은 결과를 얻을 수 있습니다.

URLFetchApp으로 RSS 피드 처리하기

앞서 배운 스크립트 함수와 URLFetchApp을 결합하면, 원하는 웹사이트의 RSS 피드를 가져와 최신 게시물마다 스프레드시트에 한 행씩 기록할 수 있습니다.

쉽게 말해, 나만의 RSS 리더 스프레드시트를 직접 만드는 DIY 방식입니다!

생각보다 스크립트도 크게 복잡하지 않습니다.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

위 코드에서 Xml.parse가 RSS 피드에서 각 항목을 추출하고, 각 줄을 제목(title), 링크(link), 날짜(date), 설명(description)으로 분리합니다.

그리고 appendRow 함수를 사용해 RSS 피드의 모든 항목을 알맞은 열에 차례대로 입력합니다.

실행 결과는 시트에서 대략 다음과 같이 나타납니다.

RSS 피드 URL을 스크립트에 직접 넣는 대신, 시트 안에 URL 입력 필드를 만들고 웹사이트마다 시트를 하나씩 두면 여러 사이트를 동시에 모니터링할 수도 있습니다.

문자열 연결과 줄바꿈 추가하기

RSS 스프레드시트를 한 단계 더 발전시켜, 텍스트 조작 기능을 추가한 뒤 이메일 전송 기능과 결합하면 해당 사이트 RSS 피드의 신규 게시물 요약을 담은 이메일을 스스로에게 보낼 수 있습니다.

이를 위해 앞 섹션에서 만든 스크립트 아래에, 스프레드시트의 모든 정보를 추출하는 코드를 추가해야 합니다.

제목줄(subject)과 이메일 본문(message)은 RSS 데이터를 시트에 기록할 때 사용했던 동일한 "items" 배열에서 정보를 조합해 만들면 됩니다.

먼저 "items" for 반복문 앞에 다음 줄을 추가해 subject와 message를 초기화합니다.

var subject = 'Latest 10 articles published at mysite.com'
var message = ''

그다음 "items" for 반복문의 끝부분(appendRow 함수 바로 뒤)에 아래 줄을 추가합니다.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

"+" 기호는 네 가지 항목을 하나로 연결하며, 각 줄 뒤의 "\n"은 줄바꿈(캐리지 리턴)을 의미합니다. 각 게시물 블록이 끝날 때마다 줄바꿈을 두 번 넣으면 이메일 본문이 깔끔하게 정렬됩니다.

모든 행이 처리되면 "message" 변수에 이메일 전체 문자열이 담깁니다. 이제 이메일을 보낼 준비가 되었습니다!

구글 앱스 스크립트로 이메일 보내는 방법

스크립트의 마지막 단계는 "subject"와 "message"를 이메일로 전송하는 것입니다. 구글 스크립트에서는 매우 간단하게 처리할 수 있습니다.

var emailAddress = myemail@gmail.com;
MailApp.sendEmail(emailAddress, subject, message);

MailApp은 구글 앱스 스크립트에 내장된 매우 편리한 클래스로, 내 구글 계정의 이메일 서비스에 접근해 메일을 주고받을 수 있게 해줍니다. 덕분에 sendEmail 함수 한 줄만으로 수신 주소, 제목, 본문만 지정해서 어떤 이메일이든 보낼 수 있습니다.

실제로 전송된 이메일은 아래와 같은 형태입니다.

웹사이트의 RSS 피드를 추출하고, 이를 구글 스프레드시트에 저장한 뒤, 링크까지 포함해 자신에게 이메일로 전송하는 이 일련의 과정은 어떤 웹사이트든 최신 콘텐츠를 손쉽게 추적할 수 있는 아주 편리한 방법입니다.

이것은 구글 앱스 스크립트로 작업을 자동화하고 여러 클라우드 서비스를 통합할 수 있는 무한한 가능성 중 극히 일부에 불과합니다.