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

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

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

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

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

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

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

	A	ب	C	D	ج	ح	G
1	فئة العنصر	رقم الطراز	التكلفة	الكمية	المنطقة	مندوب مبيعات	تاريخ الشحن
2	العجلة	W-24	20.50 دولار أمريكي	4	الغرب	هبة	1/3/2016
3	باب	D-01X	$15.00	2	الجنوب	أمير	3/15/2016
4	المحرك	ENG-0134	$100.00‎	1	الشمال	Carmen	3/20/2016
5	إطار	FR-0B1	34.00 دولار أمريكي	8	الشرق	هناء	3/12/2016
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	2016/4/30
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	2016/4/18
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 هما متكافئان.
• مجموعتَان من الصفوف (فئة السلعة ورقم الطراز) يرتّب الإجراء الأول القيمة الصاعدَة لإجمالي الكمية من المنطقة "الغرب". لذلك، يظهر "المحرك" (الذي لم يتم بيعه في الغرب) أعلى "الباب" (الذي تم بيعه 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
      }
    }
  ],
}