피벗 테이블

Google Sheets API를 사용하면 스프레드시트 내에서 피벗 테이블을 만들고 업데이트할 수 있습니다. 이 페이지의 예에서는 Sheets API로 일반적인 피벗 테이블 작업을 실행하는 방법을 보여줍니다.

이러한 예는 언어 중립적인 HTTP 요청의 형식으로 제공됩니다. Google API 클라이언트 라이브러리를 사용하여 다양한 언어로 일괄 업데이트를 구현하는 방법은 스프레드시트 업데이트를 참고하세요.

이 예에서 자리표시자 SPREADSHEET_IDSHEET_ID는 이러한 ID를 제공하는 위치를 나타냅니다. 스프레드시트 URL에서 스프레드시트 ID를 확인할 수 있습니다. spreadsheets.get 메서드를 사용하여 시트 ID를 가져올 수 있습니다. 범위는 A1 표기법을 사용하여 지정됩니다. 범위의 예로는 Sheet1!A1:D5가 있습니다.

또한 자리표시자 SOURCE_SHEET_ID는 소스 데이터가 있는 시트를 나타냅니다. 이 예에서는 피벗 테이블 소스 데이터 아래에 나열된 테이블입니다.

피벗 테이블 소스 데이터

이 예에서는 사용 중인 스프레드시트의 첫 번째 시트 ('Sheet1')에 다음과 같은 소스 '판매' 데이터가 있다고 가정합니다. 첫 번째 행의 문자열은 개별 열의 라벨입니다. 스프레드시트의 다른 시트를 읽는 방법의 예를 보려면 A1 표기법을 참고하세요.

A B C D E F G
1 항목 카테고리 모델 번호 비용 수량 지역 영업 담당자 배송 날짜
2 관람차 W-24 20.50달러 4 서부 베스 2016년 3월 1일
3 D-01X 15달러 2 남부 아미르 2016년 3월 15일
4 엔진 ENG-0134 100,000원 1 카르멘 2016년 3월 20일
5 프레임 FR-0B1 34달러 8 동부 Hannah 2016년 3월 12일
6 패널 P-034 6달러 4 데빈 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달러 6 서부 아르만도 2016년 3월 13일
11 프레임 FR-0B1 34달러 9 남부 율리아나 2016년 2월 27일
12 패널 P-102 3달러 15 서부 카르멘 2016년 4월 18일
13 패널 P-105 8.25달러 13 서부 제시 2016년 6월 20일
14 엔진 ENG-0211 283,000원 1 아미르 2016년 6월 21일
15 D-01X 15달러 2 서부 아르만도 2016년 7월 3일
16 프레임 FR-0B1 34달러 6 남부 카르멘 7/15/2016
17 관람차 W-25 20달러 8 남부 Hannah 2016년 5월 2일
18 관람차 W-11 29달러 13 동부 에리크 2016년 5월 19일
19 D-05 17.70달러 7 서부 베스 2016년 6월 28일
20 프레임 FR-0B1 34달러 8 Sheldon 2016년 3월 30일

피벗 테이블 추가하기

다음 spreadsheets.batchUpdate 코드 샘플은 UpdateCellsRequest를 사용하여 소스 데이터에서 피벗 테이블을 만들고 SHEET_ID로 지정된 시트의 A50 셀에 고정하는 방법을 보여줍니다.

이 요청은 다음 속성을 사용하여 피벗 테이블을 구성합니다.

  • 판매 수를 나타내는 하나의 값 그룹 (Quantity) 값 그룹이 하나만 있으므로 가능한 2개의 valueLayout 설정이 동일합니다.
  • 2개의 행 그룹 (Item CategoryModel Number) 첫 번째는 '서부' 지역의 총 수량을 오름차순으로 정렬합니다. 따라서 '엔진' (West 판매 없음)은 'Door' (West 판매 15개) 위에 표시됩니다. 모델 번호 그룹은 모든 리전에서 총 판매의 내림차순으로 정렬되므로 'W-24' (판매 15건)는 'W-25' (판매 8건) 위에 표시됩니다. valueBucket 필드를 {}로 설정하면 됩니다.
  • 열 그룹 1개 (Region): 대부분의 판매가 오름차순으로 정렬됨 다시 한번 valueBucket{}로 설정됩니다. 'North'는 총 판매가 가장 적으므로 첫 번째 Region 열로 표시됩니다.

요청 프로토콜은 아래와 같습니다.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
          "rows": [
              {
            "values": [
              {
                "pivotTable": {
                  "source": {
                    "sheetId": SOURCE_SHEET_ID,
                    "startRowIndex": 0,
                    "startColumnIndex": 0,
                    "endRowIndex": 20,
                    "endColumnIndex": 7
                  },
                  "rows": [
                    {
                      "sourceColumnOffset": 0,
                      "showTotals": true,
                      "sortOrder": "ASCENDING",
                      "valueBucket": {
                        "buckets": [
                          {
                            "stringValue": "West"
                          }
                        ]
                      }
                    },
                    {
                      "sourceColumnOffset": 1,
                      "showTotals": true,
                      "sortOrder": "DESCENDING",
                      "valueBucket": {}
                    }
                  ],
                  "columns": [
                    {
                      "sourceColumnOffset": 4,
                      "sortOrder": "ASCENDING",
                      "showTotals": true,
                      "valueBucket": {}
                    }
                  ],
                  "values": [
                    {
                      "summarizeFunction": "SUM",
                      "sourceColumnOffset": 3
                    }
                  ],
                  "valueLayout": "HORIZONTAL"
                }
              }
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

이 요청은 다음과 같이 피벗 테이블을 만듭니다.

피벗 테이블 추가 레시피 결과

계산된 값이 포함된 피벗 테이블 추가하기

다음 spreadsheets.batchUpdate 코드 샘플은 UpdateCellsRequest를 사용하여 소스 데이터의 계산 값 그룹이 포함된 피벗 테이블을 만들고 SHEET_ID로 지정된 시트의 A50 셀에 고정하는 방법을 보여줍니다.

이 요청은 다음 속성을 사용하여 피벗 테이블을 구성합니다.

  • 두 개의 값 그룹 (QuantityTotal Price) 첫 번째는 판매 수를 나타냅니다. 두 번째는 =Cost*SUM(Quantity) 수식을 사용하여 부품 비용과 총 판매량을 곱하여 계산된 값입니다.
  • 세 개의 행 그룹 (Item Category, Model Number, Cost)
  • 열 그룹 1개 (리전)
  • 행 및 열 그룹은 각 그룹에서 수량이 아닌 이름별로 테이블을 알파벳순으로 정렬합니다. PivotGroup에서 valueBucket 필드를 생략하면 됩니다.
  • 테이블 모양을 단순화하기 위해 이 요청은 기본 행과 열 그룹을 제외한 모든 그룹의 소계를 숨깁니다.
  • 이 요청은 테이블 모양을 개선하기 위해 valueLayoutVERTICAL로 설정합니다. valueLayout는 값 그룹이 2개 이상 있는 경우에만 중요합니다.

요청 프로토콜은 아래와 같습니다.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
        "rows": [
              {
            "values": [
              {
                "pivotTable": {
                  "source": {
                    "sheetId": SOURCE_SHEET_ID,
                    "startRowIndex": 0,
                    "startColumnIndex": 0,
                    "endRowIndex": 20,
                    "endColumnIndex": 7
                  },
                  "rows": [
                    {
                      "sourceColumnOffset": 0,
                      "showTotals": true,
                      "sortOrder": "ASCENDING"
                    },
                    {
                      "sourceColumnOffset": 1,
                      "showTotals": false,
                      "sortOrder": "ASCENDING",
                    },
                    {
                      "sourceColumnOffset": 2,
                      "showTotals": false,
                      "sortOrder": "ASCENDING",
                    }
                  ],
                  "columns": [
                    {
                      "sourceColumnOffset": 4,
                      "sortOrder": "ASCENDING",
                      "showTotals": true
                    }
                  ],
                  "values": [
                    {
                      "summarizeFunction": "SUM",
                      "sourceColumnOffset": 3
                    },
                    {
                      "summarizeFunction": "CUSTOM",
                      "name": "Total Price",
                      "formula": "=Cost*SUM(Quantity)"
                    }
                  ],
                  "valueLayout": "VERTICAL"
                }
              }
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

이 요청은 다음과 같이 피벗 테이블을 만듭니다.

피벗 값 그룹 추가 레시피 결과

피벗 테이블 삭제하기

다음 spreadsheets.batchUpdate 코드 샘플은 UpdateCellsRequest를 사용하여 SHEET_ID로 지정된 시트의 A50 셀에 고정된 피벗 테이블 (있는 경우)을 삭제하는 방법을 보여줍니다.

UpdateCellsRequestfields 매개변수에 '피벗 테이블'을 포함하는 동시에 앵커 셀의 pivotTable 필드를 생략하여 피벗 테이블을 삭제할 수 있습니다.

요청 프로토콜은 아래와 같습니다.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
          "rows": [ 
            {
            "values": [
              {}
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

피벗 테이블의 열 및 행 수정

다음 spreadsheets.batchUpdate 코드 샘플은 UpdateCellsRequest를 사용하여 피벗 테이블 추가에서 만든 피벗 테이블을 수정하는 방법을 보여줍니다.

CellData 리소스에서 pivotTable 필드의 하위 집합은 fields 매개변수를 사용하여 개별적으로 변경할 수 없습니다. 수정하려면 전체 pivotTable 필드를 제공해야 합니다. 기본적으로 피벗 테이블을 수정하려면 새 피벗 테이블을 바꿔야 합니다.

이 요청은 원래의 피벗 테이블을 다음과 같이 변경합니다.

  • 원래 피벗 테이블에서 두 번째 행 그룹을 삭제합니다 (모델 번호).
  • 열 그룹을 추가합니다 (영업 담당자). 열은 총 패널 판매 수를 기준으로 내림차순으로 정렬됩니다. 'Carmen' (Panel 15개 판매)은 'Jessie' (Panel 13개 판매) 왼쪽에 표시됩니다.
  • '서부'를 제외하고 각 지역의 열을 접고 해당 지역의 영업 담당자 그룹은 숨깁니다. 이렇게 하려면 리전 열 그룹에 있는 해당 열의 valueMetadata에서 collapsedtrue로 설정하면 됩니다.

요청 프로토콜은 아래와 같습니다.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
        "rows": [
            {
          "values": [
              {
                "pivotTable": {
                  "source": {
                    "sheetId": SOURCE_SHEET_ID,
                    "startRowIndex": 0,
                    "startColumnIndex": 0,
                    "endRowIndex": 20,
                    "endColumnIndex": 7
                  },
                  "rows": [
                    {
                      "sourceColumnOffset": 0,
                      "showTotals": true,
                      "sortOrder": "ASCENDING",
                      "valueBucket": {
                        "buckets": [
                          {
                            "stringValue": "West"
                          }
                        ]
                      }
                    }
                  ],
                  "columns": [
                    {
                      "sourceColumnOffset": 4,
                      "sortOrder": "ASCENDING",
                      "showTotals": true,
                      "valueBucket": {},
                      "valueMetadata": [
                        {
                          "value": {
                            "stringValue": "North"
                          },
                          "collapsed": true
                        },
                        {
                          "value": {
                            "stringValue": "South"
                          },
                          "collapsed": true
                        },
                        {
                          "value": {
                            "stringValue": "East"
                          },
                          "collapsed": true
                        }
                      ]
                    },
                    {
                      "sourceColumnOffset": 5,
                      "sortOrder": "DESCENDING",
                      "showTotals": false,
                      "valueBucket": {
                        "buckets": [
                          {
                            "stringValue": "Panel"
                          }
                        ]
                      },
                    }
                  ],
                  "values": [
                    {
                      "summarizeFunction": "SUM",
                      "sourceColumnOffset": 3
                    }
                  ],
                  "valueLayout": "HORIZONTAL"
                }
              }
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

이 요청은 다음과 같이 피벗 테이블을 만듭니다.

피벗 테이블 레시피 결과 수정

피벗 테이블 데이터 읽기

다음 spreadsheets.get 코드 샘플은 스프레드시트에서 피벗 테이블 데이터를 가져오는 방법을 보여줍니다. fields 쿼리 매개변수는 셀 값 데이터가 아닌 피벗 테이블 데이터만 반환되도록 지정합니다.

요청 프로토콜은 아래와 같습니다.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

응답은 SheetProperties 요소가 있는 Sheet 객체를 포함하는 Spreadsheet 리소스로 구성됩니다. PivotTable에 관한 정보를 포함하는 GridData 요소의 배열도 있습니다. 피벗 테이블 정보는 테이블이 고정된 셀 (즉, 테이블의 왼쪽 상단)에 대한 시트의 CellData 리소스 내에 포함되어 있습니다. 응답 필드가 기본값으로 설정되면 응답에서 생략됩니다.

이 예에서 첫 번째 시트 (SOURCE_SHEET_ID)에는 원시 테이블 소스 데이터가 있고 두 번째 시트 (SHEET_ID)에는 B3에 고정된 피벗 테이블이 있습니다. 빈 중괄호는 피벗 테이블 데이터가 포함되지 않은 시트나 셀을 나타냅니다. 참고로 이 요청은 시트 ID도 반환합니다.

{
  "sheets": [
    {
      "data": [{}],
      "properties": {
        "sheetId": SOURCE_SHEET_ID
      }
    },
    {
      "data": [
        {
          "rowData": [
            {},
            {},
            {
              "values": [
                {},
                {
                  "pivotTable": {
                    "columns": [
                      {
                        "showTotals": true,
                        "sortOrder": "ASCENDING",
                        "sourceColumnOffset": 4,
                        "valueBucket": {}
                      }
                    ],
                    "rows": [
                      {
                        "showTotals": true,
                        "sortOrder": "ASCENDING",
                        "valueBucket": {
                          "buckets": [
                            {
                              "stringValue": "West"
                            }
                          ]
                        }
                      },
                      {
                        "showTotals": true,
                        "sortOrder": "DESCENDING",
                        "valueBucket": {},
                        "sourceColumnOffset": 1
                      }
                    ],
                    "source": {
                      "sheetId": SOURCE_SHEET_ID,
                      "startColumnIndex": 0,
                      "endColumnIndex": 7,
                      "startRowIndex": 0,
                      "endRowIndex": 20
                    },
                    "values": [
                      {
                        "sourceColumnOffset": 3,
                        "summarizeFunction": "SUM"
                      }
                    ]
                  }
                }
              ]
            }
          ]
        }
      ],
      "properties": {
        "sheetId": SHEET_ID
      }
    }
  ],
}