Google Sheets의 맞춤 함수

Google Sheets는 AVERAGE, SUM, VLOOKUP 등 수백 개의 기본 함수를 제공합니다. 이것만으로는 충분하지 않은 경우 Google Apps Script를 사용하여 커스텀 함수(예: 미터를 마일로 변환하거나 인터넷에서 실시간 콘텐츠를 가져오기)를 작성한 다음 기본 제공 함수처럼 Google 스프레드시트에서 사용할 수 있습니다.

시작하기

맞춤 함수는 표준 자바스크립트를 사용하여 만들어집니다. 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 작성 방법을 모르고 배울 시간이 없으면 부가기능 스토어를 확인하여 다른 사용자가 필요한 맞춤 함수를 이미 빌드했는지 확인하세요.

커스텀 함수 만들기

커스텀 함수를 작성하려면 다음 안내를 따르세요.

1. Google Sheets에서 스프레드시트를 만들거나 엽니다.
2. 메뉴 항목 확장 프로그램 > Apps Script를 선택합니다.
3. 스크립트 편집기에서 코드를 삭제합니다. 위의 DOUBLE 함수의 경우 코드를 복사하여 스크립트 편집기에 붙여넣기만 하면 됩니다.
4. 상단에서 저장 을 클릭합니다.

이제 커스텀 함수를 사용할 수 있습니다.

Google Workspace Marketplace에서 커스텀 함수 가져오기

Google Workspace Marketplace 는 여러 커스텀 함수를 Google Sheets의 부가기능으로 제공합니다. 이러한 부가기능을 사용하거나 살펴보려면 다음 안내를 따르세요.

1. Google Sheets에서 스프레드시트를 만들거나 엽니다.
2. 상단에서 부가기능 > 부가기능 설치하기를 클릭합니다.
3. Google Workspace Marketplace가 열리면 오른쪽 상단에 있는 검색창을 클릭합니다.
4. 'custom function'을 입력하고 Enter 키를 누릅니다.
5. 관심 있는 커스텀 함수 부가기능을 찾으면 설치를 클릭하여 설치합니다.
6. 부가기능에 승인이 필요하다는 메시지가 표시될 수 있습니다. 이러한 경우 알림을 주의 깊게 읽은 다음 허용을 클릭합니다.
7. 해당 부가기능을 스프레드시트에서 사용할 수 있게 됩니다. 다른 스프레드시트에서 부가기능을 사용하려면 다른 스프레드시트를 열고 상단에서 부가기능 > 부가기능 관리를 클릭합니다. 사용하려는 부가기능을 찾아 옵션 > 이 문서에서 사용을 클릭합니다.

커스텀 함수 사용

커스텀 함수를 작성하거나Google Workspace Marketplace에서 설치한 후에는 기본 제공 함수만큼 쉽게 사용할 수 있습니다.

1. 함수를 사용할 셀을 클릭합니다.
2. 등호(=) 뒤에 함수 이름과 입력 값(예: =DOUBLE(A1))을 입력하고 Enter 키를 누릅니다.
3. 셀에 잠시 후 Loading...가 표시된 다음 결과가 반환됩니다.

맞춤 함수 가이드라인

맞춤 함수를 작성하기 전에 알아야 할 몇 가지 가이드라인이 있습니다.

이름 지정

자바스크립트 함수 이름을 지정하기 위한 표준 규칙 외에도 다음 사항에 유의하세요.

• 커스텀 함수의 이름은 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초 이내에 반환되어야 합니다. 그렇지 않으면 셀에 Internal error executing the custom function. 오류가 표시됩니다.

데이터 유형

Google 스프레드시트는 데이터의 특성에 따라 다양한 형식으로 데이터를 저장합니다. 이러한 값이 맞춤 함수에 사용되면 Apps Script에서 이 값을 자바스크립트에서 적절한 데이터 유형으로 취급합니다. 다음은 가장 일반적인 혼동을 야기하는 영역입니다.

• 스프레드시트의 시간과 날짜는 Apps Script에서 날짜 객체가 됩니다. 스프레드시트와 스크립트에서 다른 시간대를 사용하는 경우 (드물게 발생하는 문제) 커스텀 함수로 보완해야 합니다.
• Sheets의 기간 값도 Date 객체가 되지만 이 값을 사용하는 것이 복잡할 수 있습니다.
• Sheets의 백분율 값은 Apps Script에서 십진수가 됩니다. 예를 들어 값이 10%인 셀은 Apps Script에서 0.1가 됩니다.

자동 완성

Google 스프레드시트는 기본 제공 함수와 마찬가지로 커스텀 함수에 자동 완성을 지원합니다. 셀에 함수 이름을 입력하면 입력한 내용과 일치하는 기본 제공 함수와 커스텀 함수 목록이 표시됩니다.

아래 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 유형과 달리 맞춤 함수는 사용자에게 개인 정보에 대한 액세스 승인을 요청하지 않습니다. 따라서 개인 정보에 액세스할 수 없는 서비스, 특히 다음과 같은 서비스만 호출할 수 있습니다.

지원되는 서비스	Notes
캐시	작동하지만 맞춤 함수에서 특별히 유용하지 않음
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 편집자 부가기능으로 게시합니다.

컨테이너에 바인딩된 모든 스크립트는 컨테이너와 동일한 액세스 목록을 갖습니다. 즉, 스프레드시트를 수정할 권한이 있는 사용자는 누구나 스프레드시트에 첨부된 모든 Apps Script 코드를 수정할 수 있습니다.

최적화

스프레드시트에서 맞춤 함수가 사용될 때마다 Google 스프레드시트는 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;
}

위의 접근 방식은 자바스크립트 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;
}

이러한 기법은 스프레드시트 전체에서 반복적으로 사용되는 거의 모든 커스텀 함수에 적용할 수 있지만 구현 세부정보는 함수의 동작에 따라 달라집니다.