الجداول المحورية

تتيح لك Google Sheets API إنشاء الجداول المحورية وتعديلها ضمن جداول البيانات. توضِّح الأمثلة الواردة في هذه الصفحة كيفية تنفيذ بعض عمليات جدول pivot الشائعة باستخدام واجهة برمجة التطبيقات Sheets API.

يتم تقديم هذه الأمثلة في شكل طلبات HTTP لتكون محايدة اللغة. لمعرفة كيفية تنفيذ تعديل مجمّع بلغات مختلفة باستخدام مكتبات برامج واجهة Google API، يمكنك الاطّلاع على مقالة تحديث جداول البيانات.

في هذه الأمثلة، يشير العنصران النائبان SPREADSHEET_ID وSHEET_ID إلى مكان تقديم هذين المعرّفَين. يمكنك العثور على رقم تعريف جدول البيانات في عنوان URL لجدول البيانات. يمكنك الحصول على معرّف جدول البيانات باستخدام الأسلوب spreadsheets.get. يتم تحديد النطاقات باستخدام ترميز A1. مثال على النطاق Sheet1!A1:D5.

بالإضافة إلى ذلك، يشير العنصر النائب SOURCE_SHEET_ID إلى جدول بياناتك الذي يتضمّن البيانات المصدر. في هذه الأمثلة، هذا هو الجدول المُدرَج ضمن بيانات مصدر الجدول المحوري.

بيانات مصدر الجدول المحوري

في ما يلي أمثلة على جدول البيانات الذي يحتوي على مصدر data "المبيعات" التالي في ورقة البيانات الأولى ("الورقة1"). السلاسل في الصف الأول هي تسميات للأعمدة الفردية. للاطّلاع على أمثلة عن كيفية القراءة من أوراق بيانات أخرى في جدول البيانات، اطّلِع على ترميز A1.

	A	ب	C	D	ج	ح	G
1	فئة العنصر	رقم الطراز	التكلفة	الكمية	المنطقة	مندوب مبيعات	تاريخ الشحن
2	العجلة	W-24	20.50 دولار أمريكي	4	الغرب	هبة	2016/3/1
3	باب	D-01X	$15.00	2	الجنوب	أمير	2016/3/15
4	المحرك	ENG-0134	$100.00‎	1	الشمال	Carmen	3/20/2016
5	إطار	FR-0B1	34.00 دولار أمريكي	8	الشرق	حنا	2016/3/12
6	لوحة	P-034	‫6.00 دولار أمريكي	4	الشمال	Devyn	2016/4/2
7	لوحة	P-052	11.50 دولار أمريكي	7	الشرق	إريك	2016/5/16
8	العجلة	W-24	20.50 دولار أمريكي	11	الجنوب	Sheldon	30/4/2016
9	المحرك	ENG-0161	330.00 دولار أمريكي	2	الشمال	هبة	2/7/2016
10	باب	D-01Y	29.00 دولار أمريكي	6	الغرب	Armando	3/13/2016
11	إطار	FR-0B1	34.00 دولار أمريكي	9	الجنوب	يوليانا	2016/2/27
12	لوحة	P-102	$3,00	15	الغرب	Carmen	18/4/2016
13	لوحة	P-105	8.25 دولار أمريكي	13	الغرب	هبة	2016/6/20
14	المحرك	ENG-0211	283.00 دولار أمريكي	1	الشمال	أمير	21‏/6‏/2016
15	باب	D-01X	$15.00	2	الغرب	Armando	3/7/2016
16	إطار	FR-0B1	34.00 دولار أمريكي	6	الجنوب	Carmen	2016/7/15
17	العجلة	W-25	$20.00	8	الجنوب	حنا	2016/5/2
18	العجلة	W-11	29.00 دولار أمريكي	13	الشرق	إريك	2016/5/19
19	باب	D-05	17.70 دولار أمريكي	7	الغرب	هبة	28/6/2016
20	إطار	FR-0B1	34.00 دولار أمريكي	8	الشمال	Sheldon	3/30/2016

إضافة جدول محوري

يوضّح نموذج الرمز البرمجي التالي spreadsheets.batchUpdate كيفية استخدام دالة UpdateCellsRequest لإنشاء جدول محوري من البيانات المصدر، مع تثبيته في الخلية A50 من جدول SHEET_ID.

يضبط الطلب الجدول المحوري باستخدام السمات التالية:

• مجموعة قيم واحدة (الكمية) تشير إلى عدد المبيعات بما أنّه تتوفّر مجموعة قيم واحدة فقط، فإنّ الإعدادَين المحتملَين valueLayout هما متكافئان.
• مجموعتَان من الصفوف (فئة السلعة ورقم الطراز) يرتّب الإجراء الأول القيمة الصاعدَة لإجمالي الكمية من المنطقة "الغرب". لذلك، تظهر كلمة "Engine" (بدون مبيعات للغرب) أعلى "الباب" (مع 15 مبيعات في الغرب). تقوم مجموعة رقم الطراز بالفرز بترتيب تنازلي لإجمالي المبيعات في جميع المناطق، بحيث تظهر "W-24" (15 عملية مبيعات) فوق "W-25" (8 مبيعات). ويتم ذلك من خلال ضبط الحقل valueBucket على {}.
• مجموعة أعمدة واحدة (المنطقة) يتم ترتيبها تصاعديًا حسب أكبر عدد من المبيعات مرة أخرى، تم ضبط 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"
      }
    }
  ]
}

ينشئ الطلب جدولاً محوريًا على النحو التالي:

إضافة جدول محوري يحتوي على قيم محسوبة

يوضّح نموذج الرمز البرمجي التالي spreadsheets.batchUpdate كيفية استخدام دالة UpdateCellsRequest لإنشاء جدول محوري يتضمّن مجموعة قيم محسوبة من البيانات المصدر، مع تثبيته في الخلية A50 من ورقة البيانات المحدّدة بواسطة SHEET_ID.

يضبط الطلب الجدول المحوري باستخدام السمات التالية:

• مجموعتَي قيمتَين (الكمية والسعر الإجمالي) الأول يشير إلى عدد المبيعات. أما القيمة الثانية، فهي قيمة محسوبة استنادًا إلى ناتج تكلفة المادة وإجمالي عدد مبيعاتها، باستخدام الصيغة التالية: =Cost*SUM(Quantity).
• ثلاث مجموعات من الصفوف (فئة السلعة ورقم الطراز والتكلفة)
• مجموعة أعمدة واحدة (المنطقة)
• يتم ترتيب مجموعات الصفوف والأعمدة حسب الاسم (بدلاً من الكمية) في كل مجموعة، ما يؤدي إلى ترتيب الجدول أبجديًا. ويتم ذلك من خلال حذف الحقل valueBucket من PivotGroup.
• لتبسيط مظهر الجدول، يخفي الطلب المجاميع الفرعية لجميع المجموعات باستثناء مجموعات الصفوف والأعمدة الرئيسية.
• يضبط الطلب valueLayout على VERTICAL للحصول على مظهر جدول محسّن. valueLayout مهمة فقط إذا كان هناك مجموعتا قيم أو أكثر.

في ما يلي بروتوكول الطلب.

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"
      }
    }
  ]
}

ينشئ الطلب جدولاً محوريًا على النحو التالي:

حذف جدول محوري

يوضّح نموذج الرمز البرمجي التالي spreadsheets.batchUpdate كيفية استخدام الإجراء UpdateCellsRequest لحذف جدول محوري (إن توفّر) تم تثبيته في الخلية A50 من جدول البيانات الذي يحدّده SHEET_ID.

يمكن أن يزيل UpdateCellsRequest جدولاً محوريًا عن طريق تضمين "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"
      }
    }
  ]
}

تعديل أعمدة الجدول المحوري وصفوفه

يوضّح نموذج الرمز البرمجي التالي spreadsheets.batchUpdate كيفية استخدام الرمز UpdateCellsRequest لتعديل الجدول المحوري الذي تم إنشاؤه في إضافة جدول محوري.

لا يمكن تغيير المجموعات الفرعية لحقل pivotTable في مورد CellData بشكل فردي باستخدام المَعلمة fields. لإجراء تعديلات، يجب تقديم حقل "pivotTable" بالكامل. يتطلّب تعديل جدول محوري استبداله بجدول جديد.

يُجري الطلب التغييرات التالية على الجدول المحوري الأصلي:

• تزيل مجموعة الصف الثاني من الجدول المحوري الأصلي (رقم الطراز).
• تُضيف مجموعة أعمدة (Salesperson). يتم ترتيب الأعمدة تنازليًا حسب إجمالي عدد مبيعات اللوحة. تظهر "كارمن" (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"
      }
    }
  ]
}

ينشئ الطلب جدولاً محوريًا مثل هذا:

قراءة بيانات الجدول المحوري

يوضّح نموذج الرمز البرمجي التالي spreadsheets.get كيفية الحصول على بيانات الجدول المحوري من جدول بيانات. تحدِّد مَعلمة طلب البحث fields أنّه يجب عرض بيانات الجدول المحوري فقط (بدلاً من بيانات قيمة الخلية).

يظهر بروتوكول الطلب أدناه.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

تتألف الاستجابة من موارد Spreadsheet ، التي تحتوي على عنصر Sheet يتضمّن عناصر SheetProperties. تتوفّر أيضًا مصفوفة من عناصر GridData التي تحتوي على معلومات عن PivotTable. تتضمّن معلومات الجدول المحوري ملف موارد CellData في ورقة البيانات للخلية التي تم تثبيت الجدول عليها (أي الركن العلوي الأيمن للجدول). إذا تم ضبط حقل الردّ على القيمة التلقائية، سيتم حذفه من الردّ.

في هذا المثال، تحتوي الورقة الأولى (SOURCE_SHEET_ID) على جدول أولي يضمّ البيانات المصدر، بينما تحتوي الورقة الثانية (SHEET_ID) على الجدول المحوري المرتكز على B3. تشير الأقواس المعقوفة الفارغة إلى الأوراق أو الخلايا التي لا تحتوي على بيانات الجدول المحوري. يُرجى العِلم أنّ هذا الطلب يعرض أيضًا معرّفات ملف الجدول.

{
  "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
      }
    }
  ],
}