여러 시트의 데이터 요약

코딩 수준: 초급
시간: 5분
프로젝트 유형: 커스텀 함수

목표

• 솔루션의 기능을 이해합니다.
• 솔루션 내에서 Apps Script 서비스의 기능을 이해합니다.
• 스크립트를 설정합니다.
• 스크립트를 실행합니다.

이 솔루션 정보

스프레드시트의 여러 시트에 유사한 구조화된 데이터(여러 팀원의 고객 지원 측정항목 등)가 있는 경우 이 커스텀 함수를 사용하면 각 시트의 요약을 만들 수 있습니다. 이 솔루션은 고객 지원 티켓에 초점을 맞추고 있지만 니즈에 따라 맞춤설정할 수 있습니다.

작동 방식

getSheetsData()라는 커스텀 함수는 스프레드시트의 각 시트에서 시트의 상태 열을 기준으로 데이터를 요약합니다. 스크립트는 ReadMe 및 요약 시트와 같이 집계에 포함해서는 안 되는 시트를 무시합니다.

Apps Script 서비스

이 솔루션은 다음 서비스를 사용합니다.

• 스프레드시트 서비스: 요약해야 하는 시트를 가져오고 지정된 문자열과 일치하는 항목 수를 계산합니다. 그런 다음 스크립트는 계산된 정보를 스프레드시트에서 커스텀 함수가 호출된 위치를 기준으로 범위에 추가합니다.

기본 요건

이 샘플을 사용하려면 다음 기본 요건이 필요합니다.

• Google 계정 (Google Workspace 계정의 경우 관리자 승인이 필요할 수 있음)
• 인터넷에 액세스할 수 있는 웹브라우저

스크립트 설정

스프레드시트 데이터 요약 커스텀 함수 스프레드시트의 사본을 만들려면 다음 버튼을 클릭하세요.

사본 만들기

이 솔루션의 Apps Script 프로젝트는 스프레드시트에 연결되어 있습니다.

스크립트 실행

1. 복사된 스프레드시트에서 요약 시트로 이동합니다.
2. 셀 A4를 클릭합니다. getSheetsData() 함수가 이 셀에 있습니다.
3. 소유자 시트 중 하나로 이동하여 시트의 데이터를 업데이트하거나 추가합니다. 다음과 같은 작업을 시도해 볼 수 있습니다.
   • 샘플 티켓 정보가 포함된 새 행을 추가합니다.
   • 상태 열에서 기존 티켓의 상태를 변경합니다.
   • 상태 열의 위치를 변경합니다. 예를 들어 Owner1 시트에서 상태 열을 C열에서 D열로 이동합니다.
4. 요약 시트로 이동하여 getSheetsData()가 셀 A4에서 만든 업데이트된 요약 표를 검토합니다. 커스텀 함수의 캐시 처리된 결과를 새로고침하려면 10행의 체크박스 를 선택해야 할 수 있습니다. Google은 성능을 최적화하기 위해 커스텀 함수를 캐시합니다.
   • 행을 추가하거나 업데이트한 경우 스크립트는 티켓 및 상태 수를 업데이트합니다.
   • 상태 열의 위치를 이동한 경우 스크립트는 새 열 색인으로 의도한 대로 계속 작동합니다.

코드 검토

이 솔루션의 Apps Script 코드를 검토하려면 소스 코드 보기를 클릭하세요.

소스 코드 보기

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js

GitHub에서 보기

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/custom-functions/summarize-sheets-data

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Gets summary data from other sheets. The sheets you want to summarize must have columns with headers that match the names of the columns this function summarizes data from.
 *
 * @return {string} Summary data from other sheets.
 * @customfunction
 */

// The following sheets are ignored. Add additional constants for other sheets that should be ignored.
const READ_ME_SHEET_NAME = "ReadMe";
const PM_SHEET_NAME = "Summary";

/**
 * Reads data ranges for each sheet. Filters and counts based on 'Status' columns. To improve performance, the script uses arrays
 * until all summary data is gathered. Then the script writes the summary array starting at the cell of the custom function.
 */
function getSheetsData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (const s in sheets) {
    // Gets sheet name.
    const sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) {
      continue;
    }
    // Gets sheets data.
    const values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    const headerRowValues = values[0];
    // Finds the columns with the heading names 'Owner Name' and 'Status' and gets the index value of each.
    // Using 'indexOf()' to get the position of each column prevents the script from breaking if the columns change positions in a sheet.
    const columnOwner = headerRowValues.indexOf("Owner Name");
    const columnStatus = headerRowValues.indexOf("Status");
    // Removes header row.
    values.splice(0, 1);
    // Gets the 'Owner Name' column value by retrieving the first data row in the array.
    const owner = values[0][columnOwner];
    // Counts the total number of tasks.
    const taskCnt = values.length;
    // Counts the number of tasks that have the 'Complete' status.
    // If the options you want to count in your spreadsheet differ, update the strings below to match the text of each option.
    // To add more options, copy the line below and update the string to the new text.
    const completeCnt = filterByPosition(
      values,
      "Complete",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    const inProgressCnt = filterByPosition(
      values,
      "In-Progress",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    const scheduledCnt = filterByPosition(
      values,
      "Scheduled",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'Overdue' status.
    const overdueCnt = filterByPosition(values, "Overdue", columnStatus).length;
    // Builds the output array.
    outputArr.push([
      owner,
      taskCnt,
      completeCnt,
      inProgressCnt,
      scheduledCnt,
      overdueCnt,
      sheetNm,
    ]);
  }
  // Writes the output array.
  return outputArr;
}

/**
 * Below is a helper function that filters a 2-dimenstional array.
 */
function filterByPosition(array, find, position) {
  return array.filter((innerArray) => innerArray[position] === find);
}

수정사항

필요에 따라 커스텀 함수를 원하는 만큼 수정할 수 있습니다. 커스텀 함수 결과를 수동으로 새로고침하는 선택적 추가사항을 보려면 캐시 처리된 결과 새로고침을 클릭하세요.

캐시 처리된 결과 새로고침

기본 제공 함수와 달리 Google은 성능을 최적화하기 위해 커스텀 함수를 캐시합니다. 즉, 계산 중인 값과 같이 커스텀 함수 내에서 무언가를 변경해도 업데이트가 즉시 적용되지 않을 수 있습니다. 함수 결과를 수동으로 새로고침하려면 다음 단계를 따르세요.

1. 삽입 > 체크박스를 클릭하여 빈 셀에 체크박스를 추가합니다.
2. 체크박스가 있는 셀을 커스텀 함수의 매개변수로 추가합니다. 예를 들어 getSheetsData(B11).
3. 체크박스를 선택하거나 선택 해제하여 커스텀 함수 결과를 새로고침합니다.

참여자

이 샘플은 Google Developer Experts의 도움을 받아 Google에서 유지관리합니다.

다음 단계

• Sheets의 커스텀 함수
• Sheets 확장