Google ชีต API ช่วยให้คุณสร้างและอัปเดตตาราง Pivot ภายในสเปรดชีตได้ ตัวอย่างในหน้านี้แสดงวิธีดำเนินการกับตาราง Pivot ทั่วไปบางอย่างด้วย Sheets API
ตัวอย่างเหล่านี้แสดงในรูปแบบคำขอ HTTP เพื่อไม่ระบุภาษา ดูวิธีใช้การอัปเดตแบบเป็นกลุ่มในภาษาต่างๆ โดยใช้ไลบรารีของไคลเอ็นต์ Google API ได้ที่อัปเดตสเปรดชีต
ในตัวอย่างเหล่านี้ ตัวยึดตําแหน่ง SPREADSHEET_ID และ SHEET_ID จะระบุตําแหน่งที่คุณควรระบุรหัสเหล่านั้น คุณจะพบรหัสสเปรดชีตใน URL ของสเปรดชีต คุณรับรหัสชีตได้โดยใช้เมธอด spreadsheets.get โดยระบุช่วงโดยใช้รูปแบบ A1 ตัวอย่างช่วงคือ Sheet1!A1:D5
นอกจากนี้ ตัวยึดตําแหน่ง SOURCE_SHEET_ID จะระบุชีตที่มีข้อมูลต้นทาง ในตัวอย่างนี้ ตารางนี้จะแสดงอยู่ในส่วนข้อมูลต้นทางของตาราง Pivot
ข้อมูลแหล่งที่มาของตาราง Pivot
สําหรับตัวอย่างเหล่านี้ สมมติว่าสเปรดชีตที่ใช้มีข้อมูล "ยอดขาย" แหล่งที่มาต่อไปนี้ในชีตแรก ("ชีต 1") สตริงในแถวแรกคือป้ายกำกับของคอลัมน์แต่ละคอลัมน์ ดูตัวอย่างวิธีอ่านจากชีตอื่นๆ ในสเปรดชีตได้ที่การเขียนรูปแบบ A1
| A | ข | C | D | E | F | G | |
| 1 | หมวดหมู่รายการ | หมายเลขรุ่น | ค่าใช้จ่าย | จำนวน | ภูมิภาค | พนักงานขาย | วันที่จัดส่ง |
| 2 | ตั๋วขึ้นชิงช้าสวรรค์ | W-24 | $20.50 | 4 | ตะวันตก | Beth | 1/3/2016 |
| 3 | ประตู | D-01X | $15.00 | 2 | ใต้ | Amir | 15/3/2016 |
| 4 | เครื่องยนต์ | ENG-0134 | 3,000 บาท | 1 | เหนือ | Carmen | 20/3/2016 |
| 5 | กรอบ | FR-0B1 | $34.00 | 8 | ตะวันออก | Hannah | 12/3/2016 |
| 6 | แผง | P-034 | $6.00 | 4 | เหนือ | Devyn | 2/4/2016 |
| 7 | แผง | P-052 | $11.50 | 7 | ตะวันออก | อิริค | 16/5/2016 |
| 8 | ตั๋วขึ้นชิงช้าสวรรค์ | W-24 | $20.50 | 11 | ใต้ | Sheldon | 30/4/2016 |
| 9 | เครื่องยนต์ | ENG-0161 | $330.00 | 2 | เหนือ | Jessie | 2/7/2016 |
| 10 | ประตู | D-01Y | $29.00 | 6 | ตะวันตก | Armando | 13/3/2016 |
| 11 | กรอบ | FR-0B1 | $34.00 | 9 | ใต้ | Yuliana | 27/2/2016 |
| 12 | แผง | P-102 | $3.00 | 15 | ตะวันตก | Carmen | 18/4/2016 |
| 13 | แผง | P-105 | $8.25 | 13 | ตะวันตก | Jessie | 20/6/2016 |
| 14 | เครื่องยนต์ | ENG-0211 | $283.00 | 1 | เหนือ | Amir | 21/6/2016 |
| 15 | ประตู | D-01X | $15.00 | 2 | ตะวันตก | Armando | 3/7/2016 |
| 16 | กรอบ | FR-0B1 | $34.00 | 6 | ใต้ | Carmen | 15/7/2016 |
| 17 | ตั๋วขึ้นชิงช้าสวรรค์ | W-25 | $20.00 | 8 | ใต้ | Hannah | 2/5/2016 |
| 18 | ตั๋วขึ้นชิงช้าสวรรค์ | W-11 | $29.00 | 13 | ตะวันออก | อิริค | 19/5/2016 |
| 19 | ประตู | D-05 | $17.70 | 7 | ตะวันตก | Beth | 28/6/2016 |
| 20 | กรอบ | FR-0B1 | $34.00 | 8 | เหนือ | Sheldon | 30/3/2016 |
เพิ่มตาราง Pivot
ตัวอย่างโค้ดต่อไปนี้
spreadsheets.batchUpdate
แสดงวิธีใช้ UpdateCellsRequestเพื่อสร้างตาราง Pivot จากข้อมูลต้นทาง โดยยึดไว้ที่เซลล์ A50 ของสเปรดชีตที่ระบุโดย SHEET_ID
คําขอจะกําหนดค่าตาราง Pivot ด้วยพร็อพเพอร์ตี้ต่อไปนี้
- กลุ่มค่า 1 กลุ่ม (จำนวน) ที่ระบุจำนวนยอดขาย เนื่องจากมีกลุ่มค่าเพียงกลุ่มเดียว การตั้งค่า
valueLayoutที่เป็นไปได้ 2 แบบจึงเทียบเท่ากัน - กลุ่มแถว 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"
}
}
]
}
คําขอจะสร้างตาราง Pivot ดังนี้
เพิ่มตาราง Pivot ที่มีค่าที่คำนวณแล้ว
ตัวอย่างโค้ดต่อไปนี้
spreadsheets.batchUpdate
แสดงวิธีใช้ UpdateCellsRequestเพื่อสร้างตาราง Pivot ที่มีกลุ่มค่าที่คำนวณจากข้อมูลต้นทาง โดยยึดไว้ในเซลล์ A50 ของชีตที่ระบุโดย SHEET_ID
คําขอจะกําหนดค่าตาราง Pivot ด้วยพร็อพเพอร์ตี้ต่อไปนี้
- กลุ่มค่า 2 กลุ่ม (จำนวนและราคารวม) รายการแรกระบุจํานวนการขาย ค่าที่ 2 คือค่าที่คำนวณจากผลคูณของต้นทุนของชิ้นส่วนและจำนวนยอดขายทั้งหมดของชิ้นส่วนนั้นๆ โดยใช้สูตรนี้
=Cost*SUM(Quantity) - กลุ่มแถว 3 กลุ่ม (หมวดหมู่สินค้า หมายเลขรุ่น และต้นทุน)
- กลุ่มคอลัมน์เดียว (ภูมิภาค)
- กลุ่มแถวและคอลัมน์จะจัดเรียงตามชื่อ (แทนที่จะจัดเรียงตามจำนวน) ในแต่ละกลุ่ม โดยจัดเรียงตารางตามลําดับตัวอักษร ซึ่งทำได้โดยการละเว้นช่อง
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"
}
}
]
}
คําขอจะสร้างตาราง Pivot ดังนี้
ลบตาราง Pivot
ตัวอย่างโค้ดต่อไปนี้
spreadsheets.batchUpdate
แสดงวิธีใช้ UpdateCellsRequest เพื่อลบตาราง Pivot (หากมี) ที่ยึดอยู่ที่เซลล์ A50 ของชีตที่ระบุโดย SHEET_ID
UpdateCellsRequest นำตาราง Pivot ออกได้โดยใส่ "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"
}
}
]
}
แก้ไขคอลัมน์และแถวของตาราง Pivot
ตัวอย่างโค้ดต่อไปนี้
spreadsheets.batchUpdate
แสดงวิธีใช้ UpdateCellsRequest เพื่อแก้ไขตาราง Pivot ที่สร้างขึ้นในส่วนเพิ่มตาราง Pivot
คุณจะเปลี่ยนแปลงชุดย่อยของช่อง pivotTable ในทรัพยากร CellData ทีละรายการด้วยพารามิเตอร์ fields ไม่ได้ หากต้องการแก้ไข คุณต้องระบุข้อมูลในช่อง pivotTable ทั้งหมด โดยพื้นฐานแล้ว การแก้ไขตาราง Pivot จะต้องแทนที่ด้วยตาราง Pivot ใหม่
คำขอดังกล่าวจะทําการเปลี่ยนแปลงต่อไปนี้ในตาราง Pivot เดิม
- นํากลุ่มแถวที่ 2 ออกจากตาราง Pivot เดิม (หมายเลขรุ่น)
- เพิ่มกลุ่มคอลัมน์ (ผู้ขาย) คอลัมน์จะจัดเรียงจากมากไปน้อยตามจำนวนยอดขายแผงทั้งหมด "Carmen" (ยอดขาย 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"
}
}
]
}
คําขอจะสร้างตาราง Pivot ดังนี้
อ่านข้อมูลตาราง Pivot
ตัวอย่างโค้ด spreadsheets.get ต่อไปนี้แสดงวิธีรับข้อมูลจากตาราง Pivot ในสเปรดชีต พารามิเตอร์การค้นหา fields จะระบุให้แสดงเฉพาะข้อมูลตาราง Pivot (ไม่ใช่ข้อมูลค่าเซลล์)
โปรโตคอลคำขอแสดงอยู่ด้านล่าง
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
การตอบกลับประกอบด้วยแหล่งข้อมูล Spreadsheet ซึ่งมีออบเจ็กต์ Sheet ที่มีองค์ประกอบ SheetProperties นอกจากนี้ยังมีอาร์เรย์ขององค์ประกอบ GridData ที่มีข้อมูลเกี่ยวกับ PivotTable
ข้อมูลตาราง Pivot จะอยู่ในแหล่งข้อมูล CellData ของชีตสำหรับเซลล์ที่ปักหมุดตารางไว้ (นั่นคือมุมซ้ายบนของตาราง) หากตั้งค่าช่องคำตอบเป็นค่าเริ่มต้น ระบบจะไม่รวมช่องนั้นไว้ในคำตอบ
ในตัวอย่างนี้ ชีตแรก (SOURCE_SHEET_ID) มีข้อมูลตารางดิบ ขณะที่ชีตที่ 2 (SHEET_ID) มีตาราง Pivot ที่ยึดอยู่ที่ B3 เครื่องหมายวงเล็บปีกกาว่างเปล่าบ่งบอกถึงชีตหรือเซลล์ที่ไม่มีข้อมูลตาราง Pivot คำขอนี้จะแสดงรหัสชีตด้วยสำหรับใช้อ้างอิง
{
"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
}
}
],
}