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

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

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

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

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

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

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

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

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

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

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

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

يوضح نموذج التعليمات البرمجية 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
      }
    }
  ],
}