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 | 서부 | Beth | 2016년 3월 1일 |
| 3 | 문 | D-01X | $15.00 | 2 | 남부 | Amir | 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 | 북 | Amir | 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 | 서부 | Beth | 2016년 6월 28일 |
| 20 | 프레임 | FR-0B1 | $34.00 | 8 | 북 | Sheldon | 2016년 3월 30일 |
피벗 테이블 추가하기
다음 spreadsheets.batchUpdate 코드 샘플은 UpdateCellsRequest를 사용하여 소스 데이터에서 피벗 테이블을 만들고 SHEET_ID로 지정된 시트의 셀 A50에 고정하는 방법을 보여줍니다.
요청은 다음 속성으로 피벗 테이블을 구성합니다.
- 판매 수를 나타내는 값 그룹 1개 (수량) 값 그룹은 하나뿐이므로 가능한 두 가지
valueLayout설정은 동일합니다. - 행 그룹 2개 (상품 카테고리 및 모델 번호) 첫 번째는 '서부' 지역의 총 수량을 오름차순으로 정렬합니다. 따라서 서부 판매가 없는 '엔진'이 서부 판매가 15개인 '문' 위에 표시됩니다. 모델 번호 그룹은 모든 지역의 총 판매액을 기준으로 내림차순으로 정렬되므로 'W-24' (판매 15건)가 'W-25' (판매 8건)보다 위에 표시됩니다.
valueBucket필드를{}로 설정하면 됩니다. - 판매량이 가장 많은 순으로 정렬되는 열 그룹 (지역) 1개
다시
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)수식을 사용합니다. - 행 그룹 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 필드를 제공해야 합니다. 기본적으로 피벗 테이블을 수정하려면 새 피벗 테이블로 교체해야 합니다.
이 요청은 원본 피벗 테이블을 다음과 같이 변경합니다.
- 원본 피벗 테이블 (모델 번호)에서 두 번째 행 그룹을 삭제합니다.
- 열 그룹 (Salesperson)을 추가합니다. 열은 총 패널 판매 수를 기준으로 내림차순으로 정렬됩니다. '카르멘' (15개 패널 판매)이 '제시' (13개 패널 판매) 왼쪽에 표시됩니다.
- '서부'를 제외한 각 지역의 열을 접고 해당 지역의 영업사원 그룹을 숨깁니다. 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} } ], }