Google Sheets에서는 AVERAGE
, SUM
, VLOOKUP
와 같은 수백 개의 기본 제공 함수를 제공합니다. 기본 제공 함수로는 필요한 작업을 할 수 없을 때 Google Apps Script를 사용해 미터를 마일로 변환하거나 인터넷에서 실시간 콘텐츠를 가져오는 등의 커스텀 함수를 작성한 후 Google Sheets에서 기본 제공 함수처럼 사용하면 됩니다.
시작하기
커스텀 함수는 표준 JavaScript를 사용하여 생성됩니다. JavaScript를 처음 사용하는 경우 Codecademy에서 제공하는 초보자를 위한 훌륭한 과정을 확인해 보세요. (참고: 이 과정은 Google에서 개발하지 않았으며 Google과 관련이 없습니다.)
다음은 입력 값에 2를 곱하는 간단한 맞춤 함수 DOUBLE
입니다.
/**
* Multiplies an input value by 2.
* @param {number} input The number to double.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
return input * 2;
}
JavaScript를 작성하는 방법을 모르고 배울 시간이 없다면 부가기능 스토어를 확인하여 다른 사용자가 이미 필요한 맞춤 함수를 빌드했는지 확인하세요.
커스텀 함수 만들기
맞춤 함수를 작성하려면 다음 단계를 따르세요.
- Google Sheets에서 스프레드시트를 만들거나 엽니다.
- 메뉴 항목 확장 프로그램 > Apps Script를 선택합니다.
- 스크립트 편집기에서 코드를 삭제합니다. 위의
DOUBLE
함수의 경우 코드를 복사하여 스크립트 편집기에 붙여넣기만 하면 됩니다. - 상단에서 저장 을 클릭합니다.
이제 맞춤 함수를 사용할 수 있습니다.
Google Workspace Marketplace에서 맞춤 함수 가져오기
Google Workspace Marketplace 는 여러 맞춤 함수를 Google 스프레드시트의 부가기능으로 제공합니다. 이러한 부가기능을 사용하거나 살펴보려면 다음 단계를 따르세요.
- Google Sheets에서 스프레드시트를 만들거나 엽니다.
- 상단에서 부가기능 > 부가기능 설치하기를 클릭합니다.
- Google Workspace Marketplace가 열리면 오른쪽 상단의 검색창을 클릭합니다.
- '맞춤 함수'를 입력하고 Enter 키를 누릅니다.
- 관심 있는 맞춤 함수 부가기능을 찾으면 설치를 클릭하여 설치합니다.
- 부가기능에 승인이 필요하다는 대화상자가 표시될 수 있습니다. 이 경우 알림을 주의 깊게 읽은 다음 허용을 클릭합니다.
- 스프레드시트에서 부가기능을 사용할 수 있게 됩니다. 다른 스프레드시트에서 부가기능을 사용하려면 다른 스프레드시트를 열고 상단에서 부가기능 > 부가기능 관리를 클릭합니다. 사용하려는 부가기능을 찾아 옵션 > 이 문서에 적용을 클릭합니다.
커스텀 함수 사용
커스텀 함수를 작성하거나Google Workspace Marketplace에서 설치한 후에는 기본 제공 함수처럼 쉽게 사용할 수 있습니다.
- 함수를 사용하려는 셀을 클릭합니다.
- 등호(
=
) 뒤에 함수 이름과 입력 값(예:=DOUBLE(A1)
)을 입력하고 Enter 키를 누릅니다. - 셀에 잠시
Loading...
이 표시된 후 결과가 반환됩니다.
맞춤 함수 가이드라인
맞춤 함수를 작성하기 전에 알아야 할 몇 가지 가이드라인이 있습니다.
이름 지정
JavaScript 함수의 이름 지정과 관련된 표준 규칙 외에도 다음 사항에 유의하세요.
- 커스텀 함수의 이름은
SUM()
과 같은 기본 제공 함수의 이름과 구분되어야 합니다. - 맞춤 함수의 이름은 Apps Script에서 비공개 함수를 나타내는 밑줄 (
_
)로 끝날 수 없습니다. - 맞춤 함수의 이름은
var myFunction = new Function()
가 아닌function myFunction()
문법으로 선언해야 합니다. - 대소문자는 중요하지 않지만 스프레드시트 함수의 이름은 일반적으로 대문자입니다.
인수
맞춤 함수는 기본 제공 함수와 마찬가지로 인수를 입력 값으로 사용할 수 있습니다.
- 단일 셀 참조를 인수로 사용하여 함수를 호출하면(예:
=DOUBLE(A1)
) 인수가 셀의 값이 됩니다. 셀 범위 참조를 인수로 사용하여 함수를 호출하면 (예:
=DOUBLE(A1:B10)
) 인수가 셀 값의 2차원 배열이 됩니다. 예를 들어 아래 스크린샷에서=DOUBLE(A1:B2)
의 인수는 Apps Script에서double([[1,3],[2,4]])
로 해석됩니다. 위에서 가져온DOUBLE
의 샘플 코드는 배열을 입력으로 허용하도록 수정해야 합니다.커스텀 함수 인수는 확정적이어야 합니다. 즉, 계산할 때마다 다른 결과를 반환하는 내장 스프레드시트 함수(예:
NOW()
또는RAND()
)는 맞춤 함수의 인수로 허용되지 않습니다. 맞춤 함수가 이러한 휘발성 내장 함수 중 하나를 기반으로 값을 반환하려고 하면Loading...
이 무한대로 표시됩니다.
반환 값
모든 맞춤 함수는 다음과 같이 표시할 값을 반환해야 합니다.
- 맞춤 함수가 값을 반환하면 함수가 호출된 셀에 값이 표시됩니다.
- 맞춤 함수가 값의 2차원 배열을 반환하는 경우 셀이 비어 있는 한 값이 인접한 셀로 오버플로됩니다. 이로 인해 배열이 기존 셀 콘텐츠를 덮어쓰면 맞춤 함수에서 대신 오류가 발생합니다. 예를 보려면 맞춤 함수 최적화 섹션을 참고하세요.
- 맞춤 함수는 값을 반환하는 셀이 아닌 다른 셀에 영향을 줄 수 없습니다. 즉, 맞춤 함수는 임의의 셀을 수정할 수 없으며, 호출된 셀과 그 셀과 인접한 셀만 수정할 수 있습니다. 임의의 셀을 수정하려면 맞춤 메뉴를 사용하여 함수를 실행하세요.
- 맞춤 함수 호출은 30초 이내에 반환되어야 합니다. 그렇지 않으면 셀에
#ERROR!
이 표시되고 셀 메모는Exceeded maximum execution time (line 0).
입니다.
데이터 유형
Google Sheets는 데이터의 특성에 따라 다양한 형식으로 데이터를 저장합니다. 이러한 값이 맞춤 함수에서 사용되면 앱 스크립트는 이를 JavaScript의 적절한 데이터 유형으로 취급합니다. 다음은 가장 일반적인 혼동의 영역입니다.
- Sheets의 시간과 날짜는 Apps Script에서 Date 객체가 됩니다. 스프레드시트와 스크립트에서 서로 다른 시간대를 사용하는 경우 (드문 문제) 맞춤 함수에서 이를 보정해야 합니다.
- Sheets의 시간 값도
Date
객체가 되지만 사용하기가 복잡할 수 있습니다. - Sheets의 비율 값은 Apps Script에서 소수점 숫자로 변환됩니다. 예를 들어 값이
10%
인 셀은 Apps Script에서0.1
이 됩니다.
자동 완성
Google Sheets는 기본 제공 함수와 마찬가지로 맞춤 함수의 자동 완성을 지원합니다. 셀에 함수 이름을 입력하면 입력한 내용과 일치하는 기본 제공 함수 및 맞춤 함수 목록이 표시됩니다.
아래 DOUBLE()
예와 같이 스크립트에 JsDoc
@customfunction
태그가 포함된 경우 맞춤 함수가 이 목록에 표시됩니다.
/**
* Multiplies the input value by 2.
*
* @param {number} input The value to multiply.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
return input * 2;
}
고급
Google Apps Script 서비스 사용
맞춤 함수는 특정 Google Apps Script 서비스를 호출하여 더 복잡한 작업을 실행할 수 있습니다. 예를 들어 맞춤 함수는 Language 서비스를 호출하여 영어 문구를 스페인어로 번역할 수 있습니다.
대부분의 다른 유형의 Apps Script와 달리 맞춤 함수는 사용자에게 개인 정보 액세스 권한을 승인하도록 요청하지 않습니다. 따라서 개인 정보에 액세스할 수 없는 서비스, 특히 다음과 같은 서비스를 호출할 수 있습니다.
지원되는 서비스 | 참고 |
---|---|
캐시 | 작동하지만 맞춤 함수에서는 그다지 유용하지 않음 |
HTML | HTML을 생성할 수 있지만 표시할 수는 없습니다 (유용하지 않음). |
JDBC | |
언어 | |
잠그기 | 작동하지만 맞춤 함수에서는 그다지 유용하지 않음 |
지도 | 경로를 계산할 수 있지만 지도를 표시할 수 없음 |
속성 | getUserProperties() 는 스프레드시트 소유자의 속성만 가져옵니다. 스프레드시트 편집기는 맞춤 함수에서 사용자 속성을 설정할 수 없습니다. |
스프레드시트 | 읽기 전용 (대부분의 get*() 메서드는 사용할 수 있지만 set*() 는 사용할 수 없음).다른 스프레드시트 ( SpreadsheetApp.openById() 또는 SpreadsheetApp.openByUrl() )를 열 수 없습니다. |
URL 가져오기 | |
유틸리티 | |
XML |
맞춤 함수에서 You do not have permission to
call X service.
오류 메시지가 발생하면 서비스에 사용자 승인이 필요하므로 맞춤 함수에서 사용할 수 없습니다.
위에 나열된 서비스 이외의 서비스를 사용하려면 맞춤 함수를 작성하는 대신 Apps Script 함수를 실행하는 맞춤 메뉴를 만드세요. 메뉴에서 트리거되는 함수는 필요한 경우 사용자에게 승인을 요청하며, 따라서 모든 Apps Script 서비스를 사용할 수 있습니다.
공유
맞춤 함수는 생성된 스프레드시트에 바인딩되어 시작됩니다. 즉, 다음 메서드 중 하나를 사용하지 않는 한 한 스프레드시트에서 작성된 맞춤 함수는 다른 스프레드시트에서 사용할 수 없습니다.
- 확장 프로그램 > Apps Script를 클릭하여 스크립트 편집기를 연 다음 원본 스프레드시트에서 스크립트 텍스트를 복사하여 다른 스프레드시트의 스크립트 편집기에 붙여넣습니다.
- 파일 > 사본 만들기를 클릭하여 맞춤 함수가 포함된 스프레드시트의 사본을 만듭니다. 스프레드시트를 복사하면 스프레드시트에 첨부된 모든 스크립트도 복사됩니다. 스프레드시트에 액세스할 수 있는 사용자는 누구나 스크립트를 복사할 수 있습니다. 보기 전용 액세스 권한이 있는 공동작업자는 원본 스프레드시트에서 스크립트 편집기를 열 수 없습니다. 하지만 사본을 만들면 사본의 소유자가 되어 스크립트를 볼 수 있습니다.)
- 스크립트를 Google Sheets 편집기 부가기능으로 게시합니다.
최적화
스프레드시트에서 맞춤 함수가 사용될 때마다 Google Sheets는 Apps Script 서버를 별도로 호출합니다. 스프레드시트에 수십 개, 수백 개 또는 수천 개의 맞춤 함수 호출이 포함된 경우 이 프로세스가 매우 느려질 수 있습니다.
따라서 광범위한 데이터에 커스텀 함수를 여러 번 사용하려는 경우 범위를 2차원 배열 형식의 입력으로 허용한 후 적절한 셀로 오버플로될 수 있는 2차원 배열을 반환하도록 함수를 수정하는 것이 좋습니다.
예를 들어 위의 DOUBLE()
함수는 다음과 같이 단일 셀 또는 셀 범위를 허용하도록 다시 작성할 수 있습니다.
/**
* Multiplies the input value by 2.
*
* @param {number|Array<Array<number>>} input The value or range of cells
* to multiply.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
return Array.isArray(input) ?
input.map(row => row.map(cell => cell * 2)) :
input * 2;
}
위의 방법에서는 JavaScript Array
객체의 map 메서드를 사용하여 셀의 2차원 배열에 있는 모든 값에 대해 DOUBLE
를 재귀적으로 호출합니다. 결과가 포함된 2차원 배열을 반환합니다. 이렇게 하면 아래 스크린샷과 같이 DOUBLE
를 한 번만 호출하되 한 번에 많은 수의 셀을 계산하도록 할 수 있습니다. map
호출 대신 중첩된 if
문을 사용하여 동일한 작업을 실행할 수도 있습니다.
마찬가지로 아래의 맞춤 함수는 인터넷에서 실시간 콘텐츠를 효율적으로 가져오고 2차원 배열을 사용하여 단일 함수 호출로 두 열의 결과를 표시합니다. 각 셀에 자체 함수 호출이 필요한 경우 Apps Script 서버가 매번 XML 피드를 다운로드하고 파싱해야 하므로 작업에 훨씬 더 오래 걸립니다.
/**
* Show the title and date for the first page of posts on the
* Developer blog.
*
* @return Two columns of data representing posts on the
* Developer blog.
* @customfunction
*/
function getBlogPosts() {
var array = [];
var url = 'https://gsuite-developers.googleblog.com/atom.xml';
var xml = UrlFetchApp.fetch(url).getContentText();
var document = XmlService.parse(xml);
var root = document.getRootElement();
var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
var entries = document.getRootElement().getChildren('entry', atom);
for (var i = 0; i < entries.length; i++) {
var title = entries[i].getChild('title', atom).getText();
var date = entries[i].getChild('published', atom).getValue();
array.push([title, date]);
}
return array;
}
이러한 기법은 스프레드시트 전체에서 반복적으로 사용되는 거의 모든 맞춤 함수에 적용할 수 있지만 구현 세부정보는 함수의 동작에 따라 다릅니다.