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.00 | 2 | 남부 | 아미르 | 2016년 3월 15일 |
4 | 엔진 | ENG-0134 | $100.00 | 1 | 북 | Carmen | 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 | 서부 | Carmen | 2016년 4월 18일 |
13 | 패널 | P-105 | $8.25 | 13 | 서부 | 제시 | 2016년 6월 20일 |
14 | 엔진 | ENG-0211 | 283달러 | 1 | 북 | 아미르 | 2016년 6월 21일 |
15 | 문 | D-01X | $15.00 | 2 | 서부 | Armando | 2016년 7월 3일 |
16 | 프레임 | FR-0B1 | 34달러 | 6 | 남부 | Carmen | 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
} } ], }