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