피벗 테이블

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

이 예는 언어적 표현인 HTTP 요청의 형식으로 중립적입니다. 다음을 사용하여 다양한 언어로 일괄 업데이트를 구현하는 방법을 알아봅니다. 자세한 내용은 업데이트 스프레드시트를 사용합니다.

이 예에서 SPREADSHEET_IDSHEET_ID 자리표시자는 는 해당 ID를 제공할 위치를 나타냅니다. 스프레드시트 ID를 입력합니다. 얻을 수 있는 혜택 시트 IDspreadsheets.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 Carmen 2016년 3월 20일
5 프레임 FR-0B1 34달러 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달러 2 제시 2016년 7월 2일
10 D-01Y 29달러 6 서부 Armando 2016년 3월 13일
11 프레임 FR-0B1 34달러 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달러 1 아미르 2016년 6월 21일
15 D-01X $15.00 2 서부 Armando 2016년 7월 3일
16 프레임 FR-0B1 34달러 6 남부 Carmen 7/15/2016
17 W-25 $20.00 8 남부 Hannah 2016년 5월 2일
18 W-11 29달러 13 동부 에리크 2016년 5월 19일
19 D-05 17.7달러 7 서부 베스 2016년 6월 28일
20 프레임 FR-0B1 34달러 8 Sheldon 2016년 3월 30일

피벗 테이블 추가하기

다음 spreadsheets.batchUpdate 드림 코드 샘플을 사용하여 UpdateCellsRequest 를 사용하여 소스 데이터에서 피벗 테이블을 만들고 셀 A50에 고정합니다. 시트 SHEET_ID으로 지정.

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

  • 판매 수를 나타내는 값 그룹 (수량)이 하나 있습니다. 이후 값 그룹이 하나만 있으므로 가능한 2개는 valueLayout 드림 설정은 동일합니다.
  • 2개의 행 그룹 (상품 카테고리모델 번호) 첫 번째 정렬은 'West'에서 총 수량의 오름차순 값 리전. 따라서 '엔진' (West 판매 없음)가 'Door' 위에 표시됨 (서부에서 15건 판매). 이 모델 번호 그룹은 전체 판매의 총판매액을 내림차순으로 정렬합니다. 지역, 즉 'W-24' (판매 15건)가 'W-25' 위에 표시됨 (판매 8건). 완료되었습니다. 포드의 상태를 valueBucket 드림 필드를 {}로 변경합니다.
  • 열 그룹 1개 (지역)는 대부분의 매출을 기준으로 오름차순으로 정렬됩니다. 다시 말하지만 valueBucket{}로 설정됩니다. '북' 총매출이 가장 적은 첫 번째 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 셀에 고정합니다.

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

  • 두 개의 값 그룹 (수량총 가격) 첫 번째는 파악할 수 있습니다. 두 번째는 특정 기간의 곱을 기준으로 계산한 부품 비용과 총 판매 건수를 계산하는데 다음 공식을 사용합니다. =Cost*SUM(Quantity)
  • 3개의 행 그룹 (상품 카테고리, 모델 번호, 비용)
  • 열 그룹 1개 (리전)
  • 행 및 열 그룹은 각각 수량이 아닌 이름별로 정렬됩니다. 표를 알파벳순으로 정렬합니다. 이렇게 하려면 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 시트의 A50 셀에 고정된 피벗 테이블 (있는 경우)을 삭제합니다. SHEET_ID에 의해 지정됩니다.

UpdateCellsRequest는 'pivotTable'을 포함하여 피벗 테이블을 삭제할 수 있습니다. 인치 fields 매개변수를 사용하면서 앵커의 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 피벗 테이블 추가에서 만든 피벗 테이블을 수정합니다.

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

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

  • 원래 피벗 테이블에서 두 번째 행 그룹을 삭제합니다 (Model Number).
  • 열 그룹을 추가합니다 (영업 담당자). 열은 내림차순으로 정렬됩니다. 총 Panel 판매 건수입니다. '카르멘' (패널 판매 15건)은 'Jessie'의 왼쪽에 (패널 판매 13건).
  • '서부'를 제외한 각 지역의 열을 접고 해당 지역의 영업 담당자 그룹이 있어야 합니다. collapsed를 다음과 같이 설정하면 됩니다. truevalueMetadata 리전 열 그룹의 해당 열에 대한 데이터 액세스 권한이 포함됩니다.

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

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)

응답은 Spreadsheet 드림 이 리소스에는 Sheet 객체를 SheetProperties 요소 또한 다양한 GridData 드림 요소에 대한 정보가 포함된 PivotTable 피벗 테이블 정보는 시트의 CellData 리소스 표가 고정된 셀 (즉, 표의 왼쪽 상단 합니다. 응답 필드가 기본값으로 설정된 경우 있습니다.

이 예시에서는 첫 번째 시트 (SOURCE_SHEET_ID)에 원시 테이블이 있습니다. 두 번째 시트 (SHEET_ID)에는 피벗 테이블이 있으므로 B3에 고정되어 있습니다. 빈 중괄호는 그렇지 않은 시트나 셀을 나타냅니다. 피벗 테이블 데이터를 포함할 수 없습니다. 참고로 이 요청은 있습니다.

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