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

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

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

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

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

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

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

	جيم	B	C	D	ج	F	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	الشمال	كارمن	2016/3/20
5	إطار	FR-0B1	34.00 دولار أمريكي	8	الشرق	حنا	2016/3/12
6	لوحة	P-034	‫6.00 دولار أمريكي	4	الشمال	ديفين	2/4/2016
7	لوحة	P-052	11.50 دولارًا أمريكيًا (أو ما يعادل هذا المبلغ بالعملة المحلية)	7	الشرق	إريك	16/5/2016
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	الغرب	أرماندو	2016/3/13
11	إطار	FR-0B1	34.00 دولار أمريكي	9	الجنوب	يوليانا	27/2/2016
12	لوحة	P-102	3.00 دولارات أمريكية (أو ما يعادلها بالعملة المحلية)	15	الغرب	كارمن	18/4/2016
13	لوحة	P-105	8.25 دولار أمريكي	13	الغرب	جيسي	20/6/2016
14	المحرك	ENG-0211	283.00 دولار أمريكي	1	الشمال	أمير	2016/6/21
15	باب	D-01X	15.00 دولار أمريكي	2	الغرب	أرماندو	3/7/2016
16	إطار	FR-0B1	34.00 دولار أمريكي	6	الجنوب	كارمن	15/7/2016
17	العجلة	W-25	$20.00	8	الجنوب	حنا	2016/5/2
18	العجلة	W-11	29.00 دولار أمريكي	13	الشرق	إريك	19/5/2016
19	باب	D-05	17.70 دولار أمريكي	7	الغرب	بيث	28/6/2016
20	إطار	FR-0B1	34.00 دولار أمريكي	8	الشمال	Sheldon	30/3/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" بالكامل. بشكل أساسي، يتطلب تحرير الجدول المحوري استبداله بجدول جديد.

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

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