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

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

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

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

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

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

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

	A	ب	C	D	ج	ح	G
1	فئة العنصر	رقم الطراز	التكلفة	الكمية	المنطقة	مندوب مبيعات	تاريخ الشحن
2	العجلة	W-24	$20.50	4	الغرب	Beth	2016/3/1
3	باب	D-01X	$15.00	2	الجنوب	أمير	2016/3/15
4	المحرّك	ENG-0134	$100.00‎	1	الشمال	Carmen	2016/3/20
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	2016/4/30
9	المحرّك	ENG-0161	$330.00	2	الشمال	Jessie	‫2016/7/2
10	باب	D-01Y	$29.00	6	الغرب	Armando	2016/3/13
11	إطار	FR-0B1	$34.00	9	الجنوب	Yuliana	2016/2/27
12	لوحة	P-102	$3,00	15	الغرب	Carmen	2016/4/18
13	لوحة	P-105	$8.25	13	الغرب	Jessie	2016/6/20
14	المحرّك	ENG-0211	$283.00	1	الشمال	أمير	2016/6/21
15	باب	D-01X	$15.00	2	الغرب	Armando	2016/7/3
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	الغرب	Beth	2016/6/28
20	إطار	FR-0B1	$34.00	8	الشمال	Sheldon	2016/3/30

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

يوضّح نموذج الرمز البرمجي التالي 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"
      }
    }
  ]
}

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

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

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

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

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