ตาราง Pivot

Google Sheets API ช่วยให้คุณสร้างและอัปเดตตาราง Pivot ภายในสเปรดชีตได้ ตัวอย่างในหน้านี้จะแสดงให้เห็นวิธีสร้างตาราง Pivot ทั่วไปบางส่วน กับ Sheets API

ตัวอย่างเหล่านี้จะแสดงในรูปแบบคำขอ HTTP เป็นภาษา เป็นกลาง หากต้องการทราบวิธีใช้การอัปเดตเป็นกลุ่มในภาษาต่างๆ โดยใช้ ไลบรารีของไคลเอ็นต์ Google API โปรดดูการอัปเดต สเปรดชีต

ในตัวอย่างต่อไปนี้ ตัวยึดตำแหน่ง SPREADSHEET_ID และ SHEET_ID จะระบุตําแหน่งที่คุณจะส่งรหัสเหล่านั้น คุณจะพบสเปรดชีต รหัสใน URL ของสเปรดชีต คุณสามารถ รหัสชีตโดยใช้เมธอด spreadsheets.get มีการระบุช่วงโดยใช้รูปแบบ A1 CANNOT TRANSLATE ช่วงตัวอย่างคือ Sheet1!A1:D5

นอกจากนี้ ตัวยึดตำแหน่ง SOURCE_SHEET_ID จะระบุชีตของคุณ กับข้อมูลต้นฉบับได้ ในตัวอย่างต่อไปนี้ นี่คือตารางที่แสดงอยู่ใน Pivot ข้อมูลแหล่งที่มาของตาราง

ข้อมูลแหล่งที่มาของตาราง Pivot

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

A C D E F G
1 หมวดหมู่รายการ หมายเลขรุ่น ค่าใช้จ่าย จำนวน ภูมิภาค พนักงานขาย วันที่จัดส่ง
2 ตั๋วขึ้นชิงช้าสวรรค์ W-24 ฿600.00 4 ตะวันตก เบธ 1/3/2016
3 ประตู D-01X 450.00 บาท 2 ใต้ อาเมียร์ 15/3/2016
4 เครื่องยนต์ ENG-0134 3,000 บาท 1 เหนือ Carmen 20/3/2016
5 กรอบ ฝรั่งเศส - 0B1 ฿340.00 8 ตะวันออก ฮันนาห์ 12/3/2016
6 แผง P-034 $6.00 4 เหนือ Devyn 2/4/2016
7 แผง P-052 335 บาท 7 ตะวันออก อิริค 16/5/2016
8 ตั๋วขึ้นชิงช้าสวรรค์ W-24 ฿600.00 11 ใต้ Sheldon 30/4/2016
9 เครื่องยนต์ ENG-0161 ฿9,300.00 2 เหนือ เจสซี 2/7/2016
10 ประตู D-01Y ฿290.00 6 ตะวันตก Armando 13/3/2016
11 กรอบ ฝรั่งเศส - 0B1 ฿340.00 9 ใต้ Yuliana 27/2/2016
12 แผง P-102 $3.00 15 ตะวันตก Carmen 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 ตะวันตก Armando 3/7/2016
16 กรอบ ฝรั่งเศส - 0B1 ฿340.00 6 ใต้ Carmen 15/7/2016
17 ตั๋วขึ้นชิงช้าสวรรค์ W-25 $20.00 8 ใต้ ฮันนาห์ 2/5/2016
18 ตั๋วขึ้นชิงช้าสวรรค์ W-11 ฿290.00 13 ตะวันออก อิริค 19/5/2016
19 ประตู D-05 17.70 บาท 7 ตะวันตก เบธ 28/6/2016
20 กรอบ ฝรั่งเศส - 0B1 ฿340.00 8 เหนือ Sheldon 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

เพิ่มตาราง 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

ลบตาราง 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

อ่านข้อมูลตาราง 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
      }
    }
  ],
}