여러 시트의 데이터 요약

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

목표

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

이 솔루션 정보

스프레드시트의 여러 시트에 팀원의 고객 지원 측정항목과 같이 비슷하게 구조화된 데이터가 있는 경우 이 커스텀 함수를 사용하여 각 시트의 요약을 만들 수 있습니다. 이 솔루션은 고객 지원 티켓에 중점을 두지만 필요에 맞게 맞춤설정할 수 있습니다.

사용 방법

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

Apps Script 서비스

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

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

기본 요건

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

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

스크립트 설정

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

스크립트 실행

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

코드 검토

이 솔루션의 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() {
  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에서 관리합니다.

다음 단계

• Google Sheets의 맞춤 함수
• Google Sheets 확장