이 페이지는 Cloud Translation API를 통해 번역되었습니다.

피벗 테이블

이 가이드에서는 Google Sheets API를 사용하여 스프레드시트에서 피벗 테이블을 만드는 방법과 이유를 설명합니다.

피벗 테이블이란 무엇인가요?

피벗 테이블은 스프레드시트의 데이터를 요약하는 방법을 제공합니다. 데이터를 자동으로 집계, 정렬, 계산 또는 평균화하면서 요약된 결과를 새 표에 표시합니다. 피벗 테이블은 소스 데이터 세트에 대한 일종의 쿼리 역할을 합니다. 이 소스 데이터는 스프레드시트의 다른 위치에 있으며 피벗 테이블은 데이터의 처리된 뷰를 표시합니다.

예를 들어 다음과 같은 판매 데이터 세트를 고려해 보겠습니다.

	A	B	C	D	E	F	G
1	항목 카테고리	모델 번호	비용	수량	지역	영업 담당자	배송 날짜
2	휠	W-24	$20.50	4	서부	Beth	2016년 3월 1일
3	문	D-01X	$15.00	2	남부	Amir	2016년 3월 15일
4	엔진	ENG-0134	$100.00	1	북	Carmen	2016년 3월 20일
5	프레임	FR-0B1	$34.00	8	동부	Hannah	2016년 3월 12일
6	패널	P-034	6달러	4	북	Devyn	2016년 4월 2일
7	패널	P-052	$11.50	7	동부	에리크	2016년 5월 16일
8	휠	W-24	$20.50	11	남부	Sheldon	2016년 4월 30일
9	엔진	ENG-0161	$330.00	2	북	제시	2016년 7월 2일
10	문	D-01Y	$29.00	6	서부	Armando	2016년 3월 13일
11	프레임	FR-0B1	$34.00	9	남부	Yuliana	2016년 2월 27일
12	패널	P-102	$3.00	15	서부	Carmen	2016년 4월 18일
13	패널	P-105	$8.25	13	서부	제시	2016년 6월 20일
14	엔진	ENG-0211	$283.00	1	북	Amir	2016년 6월 21일
15	문	D-01X	$15.00	2	서부	Armando	2016년 7월 3일
16	프레임	FR-0B1	$34.00	6	남부	Carmen	7/15/2016
17	휠	W-25	$20.00	8	남부	Hannah	2016년 5월 2일
18	휠	W-11	$29.00	13	동부	에리크	2016년 5월 19일
19	문	D-05	$17.70	7	서부	Beth	2016년 6월 28일
20	프레임	FR-0B1	$34.00	8	북	Sheldon	2016년 3월 30일

피벗 테이블을 사용하여 각 지역에서 판매된 각 모델 번호의 수를 보여주는 보고서를 만들 수 있습니다.

지역별 모델 번호 수를 보여주는 피벗 테이블의 스크린샷

이 피벗 테이블을 생성하는 데 사용된 소스 코드는 아래의 예 섹션을 참고하세요.

피벗 테이블이 스프레드시트에 배치되면 사용자는 Sheets UI를 사용하여 요약의 구조와 세부정보를 대화식으로 변경할 수 있습니다.

피벗 테이블 작업

피벗 테이블 정의는 시트의 단일 셀과 연결됩니다. 렌더링된 모양은 높이와 너비 모두 여러 셀이지만 프로그래매틱 방식으로는 단일 셀 좌표에 있습니다. 이 셀은 렌더링된 피벗 테이블의 왼쪽 상단 모서리가 되며 가로 및 세로 범위는 정의에 따라 결정됩니다.

피벗 테이블 추가

피벗 테이블을 추가하려면 updateCells 요청을 제공하는 batchUpdate 메서드를 사용합니다. 이 요청을 사용하여 아래와 같이 PivotTable 정의를 셀의 콘텐츠로 제공합니다.

"updateCells": {
  "rows": {
    "values": [{
      "pivotTable": MyPivotTable
    },
    "start": {
      "sheetId": sheetId,
      "rowIndex": 0,
      "columnIndex": 0
    },
    "fields": "pivotTable"
  }

그러면 지정된 시트에 MyPivotTable로 설명된 피벗 테이블이 왼쪽 상단이 셀 A1에 오도록 배치됩니다. 피벗 테이블의 높이와 너비는 동적입니다. 출발점만 지정합니다.

PivotTable 유형을 사용하면 다음을 지정할 수 있습니다.

소스 데이터 범위
피벗 테이블의 행을 구성하는 데이터가 포함된 하나 이상의 필드
데이터가 피벗 테이블의 열을 형성하는 하나 이상의 필드
필터링 및 집계 기준
피벗 테이블 레이아웃

피벗 테이블 수정 및 삭제

피벗 테이블을 수정하거나 삭제하는 명시적인 요청이 없습니다. 대신 다른 셀 콘텐츠가 포함된 updateCells 요청을 사용하세요.

피벗 테이블을 수정하려면 새 피벗 테이블을 추가하는 것과 마찬가지로 수정된 PivotTable 정의를 만들고 이를 사용하여 셀을 업데이트합니다.
피벗 테이블을 삭제하려면 셀을 빈 값으로 업데이트합니다. 예를 보려면 피벗 테이블 삭제 샘플을 참고하세요.

사용 사례

피벗 테이블은 통계 분석, ERP 애플리케이션, 재무 보고 등 다양한 분야에서 다양하게 사용됩니다. 기존 피벗 테이블의 사용 사례는 다음과 같습니다.

지역 및 분기별 총매출
직책 및 위치별 평균 급여
제품 및 시간대별 이슈 수

피벗 테이블의 잠재적 애플리케이션 수는 방대하며 프로그래매틱 방식으로 피벗 테이블을 생성하는 기능은 매우 강력합니다. 다음과 같이 양방향 탐색을 지원하지만 특정 상황에 맞게 조정된 피벗 테이블을 생성할 수 있습니다.

최근 24시간 동안의 이슈 데이터 살펴보기
현재 선택한 계정에 해당하는 집계된 데이터 보기/분석
현재 사용자의 지역 판매 데이터 검토

예

이 예에서는 데이터 세트에서 피벗 테이블을 만들어 이 페이지의 시작 부분에 표시된 '지역별 모델 번호' 보고서를 생성합니다. 추가 예시는 피벗 테이블 샘플 페이지를 참고하세요.

Apps Script

sheets/api/spreadsheet_snippets.gs

GitHub에서 보기

/**
 * Create pivot table
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} pivot table's spreadsheet
 */
Snippets.prototype.pivotTable = (spreadsheetId) => {
  try {
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);

    // Create two sheets for our pivot table, assume we have one.
    let sheet = spreadsheet.getSheets()[0];
    sheet.copyTo(spreadsheet);

    const sourceSheetId = spreadsheet.getSheets()[0].getSheetId();
    const targetSheetId = spreadsheet.getSheets()[1].getSheetId();

    // Create pivot table
    const pivotTable = Sheets.newPivotTable();

    let gridRange = Sheets.newGridRange();
    gridRange.sheetId = sourceSheetId;
    gridRange.startRowIndex = 0;
    gridRange.startColumnIndex = 0;
    gridRange.endRowIndex = 20;
    gridRange.endColumnIndex = 7;
    pivotTable.source = gridRange;

    let pivotRows = Sheets.newPivotGroup();
    pivotRows.sourceColumnOffset = 1;
    pivotRows.showTotals = true;
    pivotRows.sortOrder = 'ASCENDING';
    pivotTable.rows = pivotRows;

    let pivotColumns = Sheets.newPivotGroup();
    pivotColumns.sourceColumnOffset = 4;
    pivotColumns.sortOrder = 'ASCENDING';
    pivotColumns.showTotals = true;
    pivotTable.columns = pivotColumns;

    let pivotValue = Sheets.newPivotValue();
    pivotValue.summarizeFunction = 'COUNTA';
    pivotValue.sourceColumnOffset = 4;
    pivotTable.values = [pivotValue];

    // Create other metadata for the updateCellsRequest
    let cellData = Sheets.newCellData();
    cellData.pivotTable = pivotTable;

    let rows = Sheets.newRowData();
    rows.values = cellData;

    let start = Sheets.newGridCoordinate();
    start.sheetId = targetSheetId;
    start.rowIndex = 0;
    start.columnIndex = 0;

    let updateCellsRequest = Sheets.newUpdateCellsRequest();
    updateCellsRequest.rows = rows;
    updateCellsRequest.start = start;
    updateCellsRequest.fields = 'pivotTable';

    // Batch update our spreadsheet
    let batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
    let updateCellsRawRequest = Sheets.newRequest();
    updateCellsRawRequest.updateCells = updateCellsRequest;
    batchUpdate.requests = [updateCellsRawRequest];
    const response = Sheets.Spreadsheets.batchUpdate(batchUpdate,
      spreadsheetId);

    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

자바

sheets/snippets/src/main/java/PivotTables.java

GitHub에서 보기

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddSheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.CellData;
import com.google.api.services.sheets.v4.model.GridCoordinate;
import com.google.api.services.sheets.v4.model.GridRange;
import com.google.api.services.sheets.v4.model.PivotGroup;
import com.google.api.services.sheets.v4.model.PivotTable;
import com.google.api.services.sheets.v4.model.PivotValue;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.RowData;
import com.google.api.services.sheets.v4.model.UpdateCellsRequest;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import com.google.common.collect.Lists;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Create Pivot Tables API */
public class PivotTables {
  /**
   * Create pivot table.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @return pivot table's spreadsheet
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    // Create two sheets for our pivot table.
    List<Request> sheetsRequests = new ArrayList<>();
    BatchUpdateSpreadsheetResponse result = null;
    try {
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));
      sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));

      BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(sheetsRequests);
      BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets()
          .batchUpdate(spreadsheetId, createSheetsBody).execute();
      int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties()
          .getSheetId();
      int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties()
          .getSheetId();

      PivotTable pivotTable = new PivotTable()
          .setSource(
              new GridRange()
                  .setSheetId(sourceSheetId)
                  .setStartRowIndex(0)
                  .setStartColumnIndex(0)
                  .setEndRowIndex(20)
                  .setEndColumnIndex(7)
          )
          .setRows(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(1)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setColumns(Collections.singletonList(
              new PivotGroup()
                  .setSourceColumnOffset(4)
                  .setShowTotals(true)
                  .setSortOrder("ASCENDING")
          ))
          .setValues(Collections.singletonList(
              new PivotValue()
                  .setSummarizeFunction("COUNTA")
                  .setSourceColumnOffset(4)
          ));
      List<Request> requests = Lists.newArrayList();
      Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest()
          .setFields("*")
          .setRows(Collections.singletonList(
              new RowData().setValues(
                  Collections.singletonList(
                      new CellData().setPivotTable(pivotTable))
              )
          ))
          .setStart(new GridCoordinate()
              .setSheetId(targetSheetId)
              .setRowIndex(0)
              .setColumnIndex(0)

          ));

      requests.add(updateCellsRequest);
      BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest()
          .setRequests(requests);
      result = service.spreadsheets().batchUpdate(spreadsheetId, updateCellsBody).execute();
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

자바스크립트

sheets/snippets/sheets_pivot_tables.js

GitHub에서 보기

function pivotTable(spreadsheetId, callback) {
  // Create two sheets for our pivot table
  const requests = [{
    addSheet: {},
  }, {
    addSheet: {},
  }];
  const batchUpdateRequest = {requests: requests};
  try {
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: batchUpdateRequest,
    }).then((response) => {
      const sourceSheetId = response.result.replies[0].addSheet.properties.sheetId;
      const targetSheetId = response.result.replies[1].addSheet.properties.sheetId;

      const requests = [{
        updateCells: {
          rows: {
            values: [{
              pivotTable: {
                source: {
                  sheetId: sourceSheetId,
                  startRowIndex: 0,
                  startColumnIndex: 0,
                  endRowIndex: 20,
                  endColumnIndex: 7,
                },
                rows: [{
                  sourceColumnOffset: 1,
                  showTotals: true,
                  sortOrder: 'ASCENDING',
                }],
                columns: [{
                  sourceColumnOffset: 4,
                  sortOrder: 'ASCENDING',
                  showTotals: true,
                }],
                values: [{
                  summarizeFunction: 'COUNTA',
                  sourceColumnOffset: 4,
                }],
                valueLayout: 'HORIZONTAL',
              },
            },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      }];

      const body = {
        requests,
      };
      gapi.client.sheets.spreadsheets.batchUpdate({
        spreadsheetId: spreadsheetId,
        resource: body,
      }).then((response) => {
        if (callback) callback(response);
      });
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_pivot_table.js

GitHub에서 보기

/**
 * Adds a pivot table to a spreadsheet.
 * @param {string} spreadsheetId The Spreadsheet to add the pivot table to.
 * @return {obj} spreadsheet information
 */
async function pivotTable(spreadsheetId) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  try {
    // Create two sheets for our pivot table
    let requests = [
      {
        addSheet: {},
      },
      {
        addSheet: {},
      },
    ];
    let resource = {requests};
    let response = await service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    const sourceSheetId = response.data.replies[0].addSheet.properties.sheetId;
    const targetSheetId = response.data.replies[1].addSheet.properties.sheetId;

    requests = [
      {
        updateCells: {
          rows: {
            values: [
              {
                pivotTable: {
                  source: {
                    sheetId: sourceSheetId,
                    startRowIndex: 0,
                    startColumnIndex: 0,
                    endRowIndex: 20,
                    endColumnIndex: 7,
                  },
                  rows: [
                    {
                      sourceColumnOffset: 1,
                      showTotals: true,
                      sortOrder: 'ASCENDING',
                    },
                  ],
                  columns: [
                    {
                      sourceColumnOffset: 4,
                      sortOrder: 'ASCENDING',
                      showTotals: true,
                    },
                  ],
                  values: [
                    {
                      summarizeFunction: 'COUNTA',
                      sourceColumnOffset: 4,
                    },
                  ],
                  valueLayout: 'HORIZONTAL',
                },
              },
            ],
          },
          start: {
            sheetId: targetSheetId,
            rowIndex: 0,
            columnIndex: 0,
          },
          fields: 'pivotTable',
        },
      },
    ];
    resource = {
      requests,
    };
    response = service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetPivotTables.php

GitHub에서 보기

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;


function pivotTables($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        try{
            $requests = [
                new Google_Service_Sheets_Request([
                    'addSheet' => [
                        'properties' => [
                            'title' => 'Sheet 1'
                            ]
                ]
            ]),
            new Google_Service_Sheets_Request([
                'addSheet' => [
                    'properties' => [
                        'title' => 'Sheet 2'
                        ]
                        ]
                        ])
                    ];
                    // Create two sheets for our pivot table
                    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                        'requests' => $requests
                    ]);
        $batchUpdateResponse = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        $sourceSheetId = $batchUpdateResponse->replies[0]->addSheet->properties->sheetId;
        $targetSheetId = $batchUpdateResponse->replies[1]->addSheet->properties->sheetId;
        $requests = [
            'updateCells' => [
                'rows' => [
                    'values' => [
                        [
                            'pivotTable' => [
                                'source' => [
                                    'sheetId' => $sourceSheetId,
                                    'startRowIndex' => 0,
                                    'startColumnIndex' => 0,
                                    'endRowIndex' => 20,
                                    'endColumnIndex' => 7
                                ],
                                'rows' => [
                                    [
                                        'sourceColumnOffset' => 1,
                                        'showTotals' => true,
                                        'sortOrder' => 'ASCENDING',
                                    ],
                                ],
                                'columns' => [
                                    [
                                        'sourceColumnOffset' => 4,
                                        'sortOrder' => 'ASCENDING',
                                        'showTotals' => true,
                                    ]
                                ],
                                'values' => [
                                    [
                                        'summarizeFunction' => 'COUNTA',
                                        'sourceColumnOffset' => 4
                                        ]
                                    ],
                                'valueLayout' => 'HORIZONTAL'
                            ]
                        ]
                        ]
                ],
                'start' => [
                    'sheetId' => $targetSheetId,
                    'rowIndex' => 0,
                    'columnIndex' => 0
                ],
                'fields' => 'pivotTable'
            ]
        ];
        return $batchUpdateResponse;
    }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
        }
    }

Python

sheets/snippets/sheets_pivot_tables.py

GitHub에서 보기

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def pivot_tables(spreadsheet_id):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)
    # Create two sheets for our pivot table.
    body = {"requests": [{"addSheet": {}}, {"addSheet": {}}]}
    batch_update_response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    source_sheet_id = (
        batch_update_response.get("replies")[0]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    target_sheet_id = (
        batch_update_response.get("replies")[1]
        .get("addSheet")
        .get("properties")
        .get("sheetId")
    )
    requests = []
    requests.append(
        {
            "updateCells": {
                "rows": {
                    "values": [
                        {
                            "pivotTable": {
                                "source": {
                                    "sheetId": source_sheet_id,
                                    "startRowIndex": 0,
                                    "startColumnIndex": 0,
                                    "endRowIndex": 20,
                                    "endColumnIndex": 7,
                                },
                                "rows": [
                                    {
                                        "sourceColumnOffset": 1,
                                        "showTotals": True,
                                        "sortOrder": "ASCENDING",
                                    },
                                ],
                                "columns": [{
                                    "sourceColumnOffset": 4,
                                    "sortOrder": "ASCENDING",
                                    "showTotals": True,
                                }],
                                "values": [{
                                    "summarizeFunction": "COUNTA",
                                    "sourceColumnOffset": 4,
                                }],
                                "valueLayout": "HORIZONTAL",
                            }
                        }
                    ]
                },
                "start": {
                    "sheetId": target_sheet_id,
                    "rowIndex": 0,
                    "columnIndex": 0,
                },
                "fields": "pivotTable",
            }
        }
    )
    body = {"requests": requests}
    response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id
  pivot_tables("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

GitHub에서 보기

requests = [{
  update_cells: {
    rows:   {
      values: [
        {
          pivot_table: {
            source:       {
              sheet_id:           source_sheet_id,
              start_row_index:    0,
              start_column_index: 0,
              end_row_index:      20,
              end_column_index:   7
            },
            rows:         [
              {
                source_column_offset: 1,
                show_totals:          true,
                sort_order:           'ASCENDING',
              },
            ],
            columns:      [
              {
                source_column_offset: 4,
                sort_order:           'ASCENDING',
                show_totals:          true,
              }
            ],
            values:       [
              {
                summarize_function:   'COUNTA',
                source_column_offset: 4
              }
            ],
            value_layout: 'HORIZONTAL'
          }
        }
      ]
    },
    start:  {
      sheet_id:     target_sheet_id,
      row_index:    0,
      column_index: 0
    },
    fields: 'pivotTable'
  }
}]
result = service.batch_update_spreadsheet(spreadsheet_id, body, {})