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')에 다음 'sales' 소스 데이터가 있다고 가정합니다. 첫 번째 행의 문자열은 개별 열의 라벨입니다. 스프레드시트의 다른 시트에서 읽는 방법의 예를 보려면 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 | 북 | 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 | 서부 | Carmen | 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 | 남부 | 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 셀에 고정하는 방법을 보여줍니다.
요청은 다음 속성으로 피벗 테이블을 구성합니다.
- 판매 수를 나타내는 값 그룹 (수량) 값 그룹이 하나만 있으므로 가능한
valueLayout설정 2개가 동일합니다. - 두 개의 행 그룹 (상품 카테고리 및 모델 번호) 첫 번째는 '서부' 지역의 총 수량의 오름차순으로 정렬됩니다. 따라서 'Engine' (West 판매 없음)이 'Door' (West 판매 15개) 위에 표시됩니다. 모델 번호 그룹은 모든 지역의 총매출을 기준으로 내림차순으로 정렬되므로 'W-24' (15건)가 'W-25' (8건)보다 위에 표시됩니다. 이 작업은
valueBucket필드를{}로 설정하여 수행됩니다. - 판매량이 가장 많은 순서대로 오름차순으로 정렬되는 열 그룹 (지역)
다시
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) - 3개의 행 그룹 (상품 카테고리, 모델 번호, 비용)
- 열 그룹 1개 (지역)
- 행 및 열 그룹은 각 그룹에서 수량이 아닌 이름으로 정렬되어 테이블이 알파벳순으로 정렬됩니다.
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 필드를 제공해야 합니다. 기본적으로 피벗 테이블을 수정하려면 새 피벗 테이블로 바꿔야 합니다.
이 요청은 원본 피벗 테이블을 다음과 같이 변경합니다.
- 원본 피벗 테이블 (모델 번호)에서 두 번째 행 그룹을 삭제합니다.
- 열 그룹 (영업사원)을 추가합니다. 열은 패널 판매 총수를 기준으로 내림차순으로 정렬됩니다. '카르멘' (15개 패널 판매)이 '제시' (13개 패널 판매) 왼쪽에 표시됩니다.
- '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} } ], }