피벗 테이블

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

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

이러한 예에서 자리표시자 SPREADSHEET_ID 및 SHEET_ID 는 이러한 ID를 제공할 위치를 나타냅니다. 스프레드시트 URL에서 스프레드시트 ID를 확인할 수 있습니다. 시트 ID는 spreadsheets.get 메서드를 사용하여 가져올 수 있습니다. 범위는 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.00 2 남부 아미르 2016년 3월 15일
4 엔진 ENG-0134 $100.00 1 북부 카르멘 2016년 3월 20일
5 프레임 FR-0B1 $34.00 8 동부 한나 2016년 3월 12일
6 패널 P-034 $6.00 4 북부 데빈 2016년 4월 2일
7 패널 P-052 $11.50 7 동부 에리크 2016년 5월 16일
8 휠 W-24 $20.50 11 남부 셸던 2016년 4월 30일
9 엔진 ENG-0161 $330.00 2 북부 Jessie 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 서부 카르멘 2016년 4월 18일
13 패널 P-105 $8.25 13 서부 Jessie 2016년 6월 20일
14 엔진 ENG-0211 $283.00 1 북부 아미르 2016년 6월 21일
15 문 D-01X $15.00 2 서부 Armando 2016년 7월 3일
16 프레임 FR-0B1 $34.00 6 남부 카르멘 2016년 7월 15일
17 휠 W-25 $20.00 8 남부 한나 2016년 5월 2일
18 휠 W-11 $29.00 13 동부 에리크 2016년 5월 19일
19 문 D-05 $17.70 7 서부 베스 2016년 6월 28일
20 프레임 FR-0B1 $34.00 8 북부 셸던 2016년 3월 30일

피벗 테이블 추가하기

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

요청은 다음과 같은 속성으로 피벗 테이블을 구성합니다.

  • 판매 수를 나타내는 값 그룹 (수량) 하나. 값 그룹이 하나만 있으므로 가능한 두 가지 valueLayout 설정은 동일합니다.
  • 두 개의 행 그룹 (항목 카테고리 및 모델 번호). 첫 번째는 '서부' 지역의 총 수량 값을 오름차순으로 정렬합니다. 따라서 '엔진'(서부 판매 없음)이 '문'(서부 판매 15개) 위에 표시됩니다. 모델 번호 그룹은 모든 지역의 총 판매량을 내림차순으로 정렬하므로 'W-24'(판매 15개)가 'W-25'(판매 8개) 위에 표시됩니다. 이는 필드를 valueBucket {}로 설정하여 수행됩니다.
  • 판매량이 가장 많은 순으로 오름차순으로 정렬되는 열 그룹 (지역) 하나. 다시 말하지만 valueBucket은 {}로 설정됩니다. '북부'의 총 판매량이 가장 적으므로 첫 번째 지역 열로 표시됩니다.

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

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에 고정하는 방법을 보여줍니다.

요청은 다음과 같은 속성으로 피벗 테이블을 구성합니다.

  • 두 개의 값 그룹 (수량 및 총 가격). 첫 번째는 판매 수를 나타냅니다. 두 번째는 이 공식인 =Cost*SUM(Quantity)를 사용하여 부품의 비용과 총 판매량을 곱한 값을 기반으로 계산된 값입니다.
  • 세 개의 행 그룹 (항목 카테고리, 모델 번호 및 비용).
  • 열 그룹 (지역) 하나.
  • 행 및 열 그룹은 각 그룹에서 수량 이 아닌 이름으로 정렬하여 테이블을 알파벳순으로 정렬합니다. 이는 valueBucket 필드를 PivotGroup에서 생략하여 수행됩니다.
    • 테이블 모양을 간소화하기 위해 요청은 기본 행 및 열 그룹을 제외한 모든 그룹의 소계를 숨깁니다.
  • 요청은 valueLayout 을 VERTICAL로 설정하여 테이블 모양을 개선합니다. 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에 고정된 피벗 테이블 (있는 경우)을 삭제하는 방법을 보여줍니다.

UpdateCellsRequest는 fields 매개변수에 'pivotTable'을 포함하면서 앵커 셀에서 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 필드를 제공해야 합니다. 기본적으로 피벗 테이블을 수정하려면 새 피벗 테이블로 바꿔야 합니다.

요청은 원래 피벗 테이블에 다음과 같은 변경사항을 적용합니다.

  • 원래 피벗 테이블에서 두 번째 행 그룹 (모델 번호)을 삭제합니다.
  • 열 그룹 (영업 담당자)을 추가합니다. 열은 총 패널 판매량을 기준으로 내림차순으로 정렬됩니다. '카르멘'(패널 판매 15개)이 '제시'(패널 판매 13개) 왼쪽에 표시됩니다.
  • '서부'를 제외한 각 지역의 열을 축소하여 해당 지역의 영업 담당자 그룹을 숨깁니다. 이는 지역 열 그룹의 해당 열에 있는 valueMetadata 에서 collapsed을 true로 설정하여 수행됩니다.

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

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
      }
    }
  ],
}