여러 시트의 데이터 요약

코딩 수준: 초급
기간: 5분
프로젝트 유형: 맞춤 함수

목표

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

이 솔루션 정보

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

getSheetsData 함수 출력 스크린샷

작동 방식

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

Apps Script 서비스

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

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

기본 요건

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

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

스크립트 설정

아래 버튼을 클릭하여 스프레드시트 데이터 요약 맞춤 함수 스프레드시트의 사본을 만듭니다. 이 솔루션의 Apps Script 프로젝트는 스프레드시트에 첨부되어 있습니다.
사본 만들기

스크립트 실행

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

코드 검토

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

소스 코드 보기

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js
// 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() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();
  let outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (let s in sheets) {
    // Gets sheet name.
    let sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) { continue; }
    // Gets sheets data.
    let values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    let 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.
    let columnOwner = headerRowValues.indexOf("Owner Name");
    let 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.
    let owner = values[0][columnOwner];
    // Counts the total number of tasks.
    let 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.
    let completeCnt = filterByPosition(values,'Complete', columnStatus).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    let inProgressCnt = filterByPosition(values,'In-Progress', columnStatus).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    let scheduledCnt = filterByPosition(values,'Scheduled', columnStatus).length;
    // Counts the number of tasks that have the 'Overdue' status.
    let 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 Expert의 도움을 받아 Google에서 유지관리합니다.

다음 단계