ตาราง Pivot

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

สำหรับตัวอย่างต่อไปนี้ สมมติว่าสเปรดชีตที่ใช้มีข้อมูล "ยอดขาย" ต้นทางต่อไปนี้ในชีตแรก ("Sheet1") สตริงในแถวแรกคือ ป้ายกำกับสำหรับแต่ละคอลัมน์ หากต้องการดูตัวอย่างวิธีการอ่านจากชีตอื่นๆ ในสเปรดชีต โปรดดูที่หมายเหตุ A1

	ก	B	C	D	จ.	F	G
1	Item Category	หมายเลขรุ่น	ค่าใช้จ่าย	จำนวน	ภูมิภาค	พนักงานขาย	วันที่จัดส่ง
2	ตั๋วขึ้นชิงช้าสวรรค์	W-24	205.00 THB	4	ตะวันตก	เบธ	1/3/2016
3	ประตู	D-01X	450 บาท	2	ใต้	อาเมียร์	15/3/2016
4	เครื่องยนต์	ENG-0134	3,000 บาท	1	เหนือ	คาร์เมน	20/3/2016
5	กรอบ	ฝรั่งเศส-0ข1	340.00 THB	8	ตะวันออก	วรรณา	12/3/2016
6	แผง	P-034	$6.00	4	เหนือ	เดวิน	2/4/2016
7	แผง	P-052	335 บาท	7	ตะวันออก	อิริค	16/5/2016
8	ตั๋วขึ้นชิงช้าสวรรค์	W-24	205.00 THB	11	ใต้	เชลดอน	30/4/2016
9	เครื่องยนต์	ENG-0161	330.00 บาท	2	เหนือ	เจสซี่	2/7/2016
10	ประตู	D-01Y	290.00 THB	6	ตะวันตก	อาร์มันโด	13/3/2016
11	กรอบ	ฝรั่งเศส-0ข1	340.00 THB	9	ใต้	ยูเลียนา	27/2/2016
12	แผง	P-102	90.00 บาท	15	ตะวันตก	คาร์เมน	18/4/2016
13	แผง	P-105	286 บาท	13	ตะวันตก	เจสซี่	20/6/2016
14	เครื่องยนต์	ENG-0211	283.00 บาท	1	เหนือ	อาเมียร์	21/6/2016
15	ประตู	D-01X	450 บาท	2	ตะวันตก	อาร์มันโด	3/7/2016
16	กรอบ	ฝรั่งเศส-0ข1	340.00 THB	6	ใต้	คาร์เมน	15/7/2016
17	ตั๋วขึ้นชิงช้าสวรรค์	W-25	$20.00	8	ใต้	วรรณา	2/5/2016
18	ตั๋วขึ้นชิงช้าสวรรค์	W-11	290.00 THB	13	ตะวันออก	อิริค	19/5/2016
19	ประตู	D-05	177.00 THB	7	ตะวันตก	เบธ	28/6/2016
20	กรอบ	ฝรั่งเศส-0ข1	340.00 THB	8	เหนือ	เชลดอน	30/3/2016

เพิ่มตาราง Pivot

ตัวอย่างโค้ด spreadsheets.batchUpdate ต่อไปนี้แสดงวิธีใช้ UpdateCellsRequest เพื่อสร้างตาราง Pivot จากข้อมูลต้นฉบับ โดยตรึงไว้ในเซลล์ A50 ของชีตที่ระบุโดย SHEET_ID

คำขอกำหนดค่าตาราง Pivot ด้วยพร็อพเพอร์ตี้ต่อไปนี้

• ค่า 1 กลุ่ม (Quantity) ซึ่งระบุจำนวนการขาย เนื่องจากกลุ่มค่ามีเพียงกลุ่มเดียว การตั้งค่า 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 กลุ่ม (หมวดหมู่สินค้า หมายเลขโมเดล และต้นทุน)
• กลุ่มคอลัมน์ 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
      }
    }
  ],
}