Google Sheets API를 사용하면 스프레드시트 내에 피벗 테이블을 만들고 업데이트할 수 있습니다. 이 페이지의 예는 일반적인 피벗 테이블을 만드는 방법을 보여줍니다. 작업을 수행할 수 있습니다.
이 예는 언어적 표현인 HTTP 요청의 형식으로 중립적입니다. 다음을 사용하여 다양한 언어로 일괄 업데이트를 구현하는 방법을 알아봅니다. 자세한 내용은 업데이트 스프레드시트를 사용합니다.
이 예에서 SPREADSHEET_ID 및 SHEET_ID 자리표시자는
는 해당 ID를 제공할 위치를 나타냅니다. 스프레드시트
ID를 입력합니다. 얻을 수 있는 혜택
시트 ID를
spreadsheets.get 메서드를 사용하여 지도 가장자리에
패딩을 추가할 수 있습니다. 이
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달러 | 2 | 남부 | 아미르 | 2016년 3월 15일 |
| 4 | 엔진 | ENG-0134 | 100,000원 | 1 | 북 | 카르멘 | 2016년 3월 20일 |
| 5 | 프레임 | FR-0B1 | 34달러 | 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달러 | 2 | 북 | 제시 | 2016년 7월 2일 |
| 10 | 문 | D-01Y | 29달러 | 6 | 서부 | Armando | 2016년 3월 13일 |
| 11 | 프레임 | FR-0B1 | 34달러 | 9 | 남부 | 율리아나 | 2016년 2월 27일 |
| 12 | 패널 | P-102 | $3.00 | 15 | 서부 | 카르멘 | 2016년 4월 18일 |
| 13 | 패널 | P-105 | $8.25 | 13 | 서부 | 제시 | 2016년 6월 20일 |
| 14 | 엔진 | ENG-0211 | 283달러 | 1 | 북 | 아미르 | 2016년 6월 21일 |
| 15 | 문 | D-01X | 15달러 | 2 | 서부 | Armando | 2016년 7월 3일 |
| 16 | 프레임 | FR-0B1 | 34달러 | 6 | 남부 | 카르멘 | 7/15/2016 |
| 17 | 휠 | W-25 | $20.00 | 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
를 사용하여 소스 데이터에서 피벗 테이블을 만들고 셀 A50에 고정합니다.
시트 SHEET_ID으로 지정.
이 요청은 다음 속성을 사용하여 피벗 테이블을 구성합니다.
- 판매 수를 나타내는 값 그룹 (수량)이 하나 있습니다. 이후
값 그룹이 하나만 있으므로 가능한 2개는
valueLayout설정은 동일합니다. - 2개의 행 그룹 (상품 카테고리 및 모델 번호) 첫 번째 정렬은
'서부'에서 총 수량의 오름차순 값 리전. 따라서
'엔진' (West 판매 없음)가 'Door' 위에 표시됨 (서부에서 15건 판매). 이
Model Number 그룹은 전체 판매의 총판매액을 내림차순으로 정렬합니다.
지역, 즉 'W-24' (판매 15건)가 'W-25' 위에 표시됨 (판매 8건). 완료되었습니다.
포드의 상태를
valueBucket필드를{}로 변경합니다. - 열 그룹 1개 (지역)는 대부분의 매출을 기준으로 오름차순으로 정렬됩니다.
다시 말하지만
valueBucket는{}로 설정됩니다. '북' 총매출이 가장 적은 첫 번째 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개 (리전)
- 행 및 열 그룹은 각각 수량이 아닌 이름별로 정렬됩니다.
표를 알파벳순으로 정렬합니다. 이렇게 하려면
valueBucket필드를PivotGroup - 표 모양을 단순화하기 위해 요청에서 소계를 숨깁니다. 를 사용합니다.
- 요청은
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
시트의 A50 셀에 고정된 피벗 테이블 (있는 경우)을 삭제합니다.
SHEET_ID에 의해 지정됩니다.
UpdateCellsRequest는 'pivotTable'을 포함하여 피벗 테이블을 삭제할 수 있습니다. 인치
fields 매개변수를 사용하면서 앵커의 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
피벗 테이블 추가에서 만든 피벗 테이블을 수정합니다.
하위 집합
pivotTable
필드를
CellData 리소스
fields 매개변수를 사용하여 개별적으로 변경할 수 없습니다. 수정하려면
pivotTable 필드 전체를 제공해야 합니다. 기본적으로 피벗 테이블을 수정하면
새 것으로 교체해야 합니다
이 요청은 원래 피벗 테이블을 다음과 같이 변경합니다.
- 원래 피벗 테이블에서 두 번째 행 그룹을 삭제합니다 (Model Number).
- 열 그룹을 추가합니다 (영업 담당자). 열은 내림차순으로 정렬됩니다. 총 Panel 판매 건수입니다. '카르멘' (패널 판매 15건)은 'Jessie'의 왼쪽에 (패널 판매 13건).
- '서부'를 제외한 각 지역의 열을 접고
해당 지역의 영업 담당자 그룹이 있어야 합니다.
collapsed를 다음과 같이 설정하면 됩니다.true에valueMetadata리전 열 그룹의 해당 열에 대한 데이터 액세스 권한이 포함됩니다.
요청 프로토콜은 아래와 같습니다.
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)
응답은
Spreadsheet
이 리소스에는
Sheet 객체를
SheetProperties
요소 또한 다양한
GridData
요소에 대한 정보가 포함된
PivotTable
피벗 테이블 정보는 시트의
CellData 리소스
표가 고정된 셀 (즉, 표의 왼쪽 상단
합니다. 응답 필드가 기본값으로 설정된 경우
있습니다.
이 예시에서는 첫 번째 시트 (SOURCE_SHEET_ID)에 원시 테이블이 있습니다.
두 번째 시트 (SHEET_ID)에는 피벗 테이블이 있으므로
B3에 고정되어 있습니다. 빈 중괄호는 그렇지 않은 시트나 셀을 나타냅니다.
피벗 테이블 데이터를 포함할 수 없습니다. 참고로 이 요청은
있습니다.
{
"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
}
}
],
}