Google Sheets API ช่วยให้คุณสร้างและอัปเดตตาราง Pivot ภายในสเปรดชีตได้ ตัวอย่างในหน้านี้แสดงให้เห็นวิธีใช้ Sheets API เพื่อดำเนินการตาราง Pivot ที่พบบ่อย
ตัวอย่างเหล่านี้จะแสดงในรูปแบบคำขอ 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 กลุ่ม (Quantity และ Total Price) โดยตัวแรกแสดง จํานวนการขาย ส่วนค่าที่ 2 คือค่าที่คำนวณโดยอิงตามผลคูณของต้นทุนของ ชิ้นส่วนและจำนวนยอดขายทั้งหมด โดยใช้สูตรต่อไปนี้
=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" } } ] }
คำขอจะสร้างตาราง Pivot ดังนี้
ลบตาราง Pivot
ตัวอย่างโค้ดต่อไปนี้ spreadsheets.batchUpdate แสดงวิธีใช้ UpdateCellsRequest เพื่อลบตาราง Pivot (หากมี) ที่ยึดไว้ที่เซลล์ A50 ของชีต ที่ระบุโดย SHEET_ID
UpdateCellsRequest สามารถนำตาราง Pivot ออกได้โดยใส่ "pivotTable" ในพารามิเตอร์ fields ขณะเดียวกันก็ละเว้นฟิลด์ pivotTable ในเซลล์ Anchor
โปรโตคอลคำขอแสดงอยู่ด้านล่าง
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 เดิม
- นำกลุ่มแถวที่ 2 ออกจากตาราง Pivot เดิม (หมายเลขรุ่น)
- เพิ่มกลุ่มคอลัมน์ (พนักงานขาย) คอลัมน์จะจัดเรียงจากมากไปน้อยตาม จำนวนการขายแผงทั้งหมด "คาร์เมน" (ยอดขายแผง 15 รายการ) จะปรากฏทางด้านซ้ายของ "เจสซี" (ยอดขายแผง 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} } ], }