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 | 금 | G | |
| 1 | 항목 카테고리 | 모델 번호 | 비용 | Quantity | 지역 | 영업 담당자 | 배송 날짜 |
| 2 | 관람차 | W-24 | 20.50달러 | 4 | 서부 | 베스 | 2016년 3월 1일 |
| 3 | 문 | D-01X | 15달러 | 2 | 남부 | 아미르 | 2016년 3월 15일 |
| 4 | Engine | ENG-0134 | 100,000원 | 1 | 북 | 카르멘 | 2016년 3월 20일 |
| 5 | 프레임 | FR-0B1 | 34달러 | 8 | 동부 | Hannah | 2016년 3월 12일 |
| 6 | 패널 | P-034 | 6달러 | 4 | 북 | 데빈 | 2016년 4월 2일 |
| 7 | 패널 | P-052 | 11.50달러 | 7 | 동부 | 에리크 | 2016년 5월 16일 |
| 8 | 관람차 | W-24 | 20.50달러 | 11 | 남부 | Sheldon | 2016년 4월 30일 |
| 9 | Engine | ENG-0161 | 330달러 | 2 | 북 | 제시 | 2016년 7월 2일 |
| 10 | 문 | D-01Y | 29달러 | 6 | 서부 | 아르만도 | 2016년 3월 13일 |
| 11 | 프레임 | FR-0B1 | 34달러 | 9 | 남부 | 율리아나 | 2016년 2월 27일 |
| 12 | 패널 | P-102 | 3달러 | 15 | 서부 | 카르멘 | 2016년 4월 18일 |
| 13 | 패널 | P-105 | 8.25달러 | 13 | 서부 | 제시 | 2016년 6월 20일 |
| 14 | Engine | ENG-0211 | 283달러 | 1 | 북 | 아미르 | 2016년 6월 21일 |
| 15 | 문 | D-01X | 15달러 | 2 | 서부 | 아르만도 | 2016년 7월 3일 |
| 16 | 프레임 | FR-0B1 | 34달러 | 6 | 남부 | 카르멘 | 7/15/2016 |
| 17 | 관람차 | W-25 | 20달러 | 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를 사용하여 소스 데이터에서 피벗 테이블을 만들고 SHEET_ID로 지정된 시트의 A50 셀에 고정하는 방법을 보여줍니다.
이 요청은 다음 속성을 사용하여 피벗 테이블을 구성합니다.
- 판매 수를 나타내는 값 그룹 (수량)이 하나 있습니다. 값 그룹이 하나만 있으므로 가능한 2개의
valueLayout설정은 동일합니다. - 2개의 행 그룹 (상품 카테고리 및 모델 번호) 첫 번째 항목은 'West' Region에서 총 Quantity의 오름차순 값으로 정렬됩니다. 따라서 '엔진' (West 판매 없음)은 'Door' (West 판매 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 필드를 제공해야 합니다. 기본적으로 피벗 테이블을 수정하려면
새 테이블로 바꿔야 합니다
이 요청은 원래 피벗 테이블을 다음과 같이 변경합니다.
- 원래 피벗 테이블에서 두 번째 행 그룹을 삭제합니다 (Model Number).
- 열 그룹을 추가합니다 (영업 담당자). 열은 패널의 총 판매 수량을 기준으로 내림차순으로 정렬됩니다. 'Carmen' (15개의 Panel 판매)은 'Jessie' (13개의 Panel 판매) 왼쪽에 표시됩니다.
- '서부'를 제외한 각 지역의 열을 접고 해당 지역의 영업 담당자 그룹이 숨겨집니다. 이렇게 하려면 리전 열 그룹의 해당 열에 대한
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
}
}
],
}