Google Sheets API ช่วยให้คุณสร้างและอัปเดตตาราง Pivot ภายในสเปรดชีตได้ ตัวอย่างในหน้านี้จะแสดงวิธีดำเนินการบางส่วนของตาราง Pivot ทั่วไปด้วย Sheets API
ตัวอย่างเหล่านี้มาในรูปแบบคำขอ HTTP ควรเป็นกลางทางภาษา หากต้องการดูวิธีใช้การอัปเดตเป็นกลุ่มในภาษาต่างๆ โดยใช้ไลบรารีของไคลเอ็นต์ Google API โปรดดูอัปเดตสเปรดชีต
ในตัวอย่างต่อไปนี้ ตัวยึดตำแหน่ง SPREADSHEET_ID
และ SHEET_ID
ระบุตำแหน่งที่คุณจะระบุรหัสเหล่านั้น คุณจะเห็นรหัสของสเปรดชีตใน URL ของสเปรดชีต คุณดูรหัสชีตได้โดยใช้เมธอด spreadsheets.get
ระบบระบุช่วงโดยใช้รูปแบบ A1 ช่วงตัวอย่างคือ Sheet1!A1:D5
นอกจากนี้ ตัวยึดตำแหน่ง SOURCE_SHEET_ID
จะระบุชีตที่มีข้อมูลต้นทาง ในตัวอย่างต่อไปนี้ นี่คือตารางที่ระบุไว้ในข้อมูลต้นทางของตาราง Pivot
ข้อมูลแหล่งที่มาของตาราง Pivot
สำหรับตัวอย่างต่อไปนี้ ให้สมมติว่าสเปรดชีตที่ใช้มีข้อมูล "ยอดขาย" แหล่งที่มาต่อไปนี้ในชีตแรก ("Sheet1") สตริงในแถวแรก เป็นป้ายกำกับสำหรับแต่ละคอลัมน์ หากต้องการดูตัวอย่างวิธีการอ่านจากชีตอื่นๆ ในสเปรดชีต โปรดดูที่หมายเหตุ A1
A | B | C | ว | E | F | G | |
1 | Item Category | หมายเลขรุ่น | ค่าใช้จ่าย | จำนวน | ภูมิภาค | พนักงานขาย | วันที่จัดส่ง |
2 | ตั๋วขึ้นชิงช้าสวรรค์ | W-24 | ฿600.00 | 4 | ตะวันตก | เบธ | 1/3/2016 |
3 | ประตู | D-01X | 450.00 บาท | 2 | ใต้ | อาเมียร์ | 15/3/2016 |
4 | เครื่องยนต์ | ENG-0134 | 3,000 บาท | 1 | เหนือ | คาร์เมน | 20/3/2016 |
5 | กรอบ | ฝรั่งเศส - 0B1 | ฿340.00 | 8 | ตะวันออก | ฮันนาห์ | 12/3/2016 |
6 | แผง | P-034 | $6.00 | 4 | เหนือ | เดวิน | 2/4/2016 |
7 | แผง | P-052 | 335 บาท | 7 | ตะวันออก | อิริค | 16/5/2016 |
8 | ตั๋วขึ้นชิงช้าสวรรค์ | W-24 | ฿600.00 | 11 | ใต้ | เชลดอน | 30/4/2016 |
9 | เครื่องยนต์ | ENG-0161 | ฿9,300.00 | 2 | เหนือ | เจสซี | 2/7/2016 |
10 | ประตู | D-01Y | ฿290.00 | 6 | ตะวันตก | อาร์มันโด | 13/3/2016 |
11 | กรอบ | ฝรั่งเศส - 0B1 | ฿340.00 | 9 | ใต้ | ยูเลียนา | 27/2/2016 |
12 | แผง | P-102 | 90.00 บาท | 15 | ตะวันตก | คาร์เมน | 18/4/2016 |
13 | แผง | P-105 | 247.50 บาท | 13 | ตะวันตก | เจสซี | 20/6/2016 |
14 | เครื่องยนต์ | ENG-0211 | ฿2,830.00 | 1 | เหนือ | อาเมียร์ | 21/6/2016 |
15 | ประตู | D-01X | 450.00 บาท | 2 | ตะวันตก | อาร์มันโด | 3/7/2016 |
16 | กรอบ | ฝรั่งเศส - 0B1 | ฿340.00 | 6 | ใต้ | คาร์เมน | 15/7/2016 |
17 | ตั๋วขึ้นชิงช้าสวรรค์ | W-25 | $20.00 | 8 | ใต้ | ฮันนาห์ | 2/5/2016 |
18 | ตั๋วขึ้นชิงช้าสวรรค์ | W-11 | ฿290.00 | 13 | ตะวันออก | อิริค | 19/5/2016 |
19 | ประตู | D-05 | 521 บาท | 7 | ตะวันตก | เบธ | 28/6/2016 |
20 | กรอบ | ฝรั่งเศส - 0B1 | ฿340.00 | 8 | เหนือ | เชลดอน | 30/3/2016 |
เพิ่มตาราง Pivot
ตัวอย่างโค้ด spreadsheets.batchUpdate
ต่อไปนี้แสดงวิธีใช้ UpdateCellsRequest
เพื่อสร้างตาราง Pivot จากข้อมูลต้นทาง โดยตรึงไว้ในเซลล์ A50 ของชีตที่ระบุโดย SHEET_ID
คำขอจะกำหนดค่าตาราง Pivot โดยมีพร็อพเพอร์ตี้ต่อไปนี้
- กลุ่มค่าหนึ่ง (จำนวน) ที่ระบุจำนวนการขาย เนื่องจากมีกลุ่มค่าเพียงกลุ่มเดียว การตั้งค่าที่เป็นไปได้ 2 รายการ
valueLayout
จึงเทียบเท่ากัน - กลุ่มแถว 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 กลุ่ม (หมวดหมู่สินค้า หมายเลขรุ่น และต้นทุน)
- 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 เดิม (หมายเลขรุ่น)
- เพิ่มกลุ่มคอลัมน์ (พนักงานขาย) คอลัมน์จะจัดเรียงตามลำดับจากมากไปน้อยตามจำนวน ยอดขายทั้งหมดบนแผง "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
} } ], }