Google Sheets API를 사용하면 스프레드시트 내에 피벗 테이블을 만들고 업데이트할 수 있습니다. 이 페이지의 예시는 Sheets API로 일반적인 피벗 테이블 작업을 수행하는 방법을 보여줍니다.
이 예시는 언어 중립적인 HTTP 요청의 형태로 제공됩니다. Google API 클라이언트 라이브러리를 사용하여 다양한 언어로 일괄 업데이트를 구현하는 방법을 알아보려면 스프레드시트 업데이트를 참고하세요.
이 예시에서 자리표시자 SPREADSHEET_ID 및 SHEET_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.00 | 2 | 남부 | 아미르 | 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 | 북 | 아미르 | 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 | 서부 | 베스 | 2016년 6월 28일 |
| 20 | 프레임 | FR-0B1 | $34.00 | 8 | 북 | Sheldon | 2016년 3월 30일 |
피벗 테이블 추가하기
다음 spreadsheets.batchUpdate 코드 샘플은 UpdateCellsRequest를 사용하여 소스 데이터에서 피벗 테이블을 만들고 SHEET_ID로 지정된 시트의 셀 A50에 고정하는 방법을 보여줍니다.
이 요청은 다음 속성으로 피벗 테이블을 구성합니다.
- 판매 수량을 나타내는 하나의 값 그룹 (Quantity) 값 그룹이 하나뿐이므로 가능한 두 가지
valueLayout설정이 동일합니다. - 두 개의 행 그룹 (Item Category 및 Model Number). 첫 번째는 '서부' 지역의 총 수량을 오름차순으로 정렬합니다. 따라서 서부 판매가 없는 '엔진'이 서부 판매가 15개인 '문' 위에 표시됩니다. Model Number 그룹은 모든 지역에서 총 판매를 내림차순으로 정렬되므로 'W-24' (판매 15개)는 'W-25' (판매 8개) 위에 표시됩니다.
valueBucket필드를{}로 설정하면 됩니다. - 대부분의 판매가 오름차순으로 정렬되는 열 그룹 (지역) 1개
다시 말해
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에 고정하는 방법을 보여줍니다.
이 요청은 다음 속성으로 피벗 테이블을 구성합니다.
- 두 개의 값 그룹 (수량 및 총 가격). 첫 번째는 판매 수를 나타냅니다. 두 번째는 다음 수식을 사용하여 부품 비용과 총 판매 수량을곱한 값입니다.
=Cost*SUM(Quantity). - 세 개의 행 그룹 (Item Category, Model Number, Cost).
- 열 그룹 1개 (Region)
- 행 및 열 그룹은 각 그룹에서 수량이 아닌 이름별로 정렬되며, 테이블이 사전순으로 정렬됩니다. 이를 수행하려면
PivotGroup에서valueBucket필드를 생략합니다. - 테이블 모양을 단순화하기 위해 이 요청은 기본 행 및 열 그룹을 제외한 모든 그룹의 소계를 숨깁니다.
- 이 요청은 더 나은 테이블 모양을 위해
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 필드를 제공해야 합니다. 기본적으로, 피벗 테이블을 편집하려면 새 테이블로 대체해야 합니다.
이 요청은 원래 피벗 테이블을 다음과 같이 변경합니다.
- 원본 피벗 테이블에서 두 번째 행 그룹 (Model Number)을 삭제합니다.
- 열 그룹 (Salesperson)을 추가합니다. 열은 Panel의 총 판매 수량별로 내림차순으로 정렬됩니다. 'Carmen' (15개 Panel 판매)은 'Jessie' (13개 Panel 판매)의 왼쪽에 나타납니다.
- 'West'를 제외한 각 Region의 열을 축소하여 해당 지역의 Salesperson 그룹을 숨깁니다. 이를 수행하려면 Region 열 그룹의 해당 열에 대해
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
}
}
],
}