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

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

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

في هذه الأمثلة، يشير العنصران النائبان SPREADSHEET_ID و SHEET_ID إلى المكان الذي ستُدخل فيه أرقام التعريف هذه. يمكنك العثور على رقم تعريف جدول البيانات في عنوان URL الخاص به. يمكنك الحصول على رقم تعريف ورقة البيانات باستخدام طريقة spreadsheets.get. يتم تحديد النطاقات باستخدام A1 تدوين. مثال على النطاق هو Sheet1!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 الجنوب Amir 2016/3/15
4 المحرّك ENG-0134 $100.00 1 الشمال Carmen 2016/3/20
5 إطار FR-0B1 $34.00 8 الشرق Hannah 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 الشمال Amir 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 الجنوب Hannah 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"
      }
    }
  ]
}

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

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

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

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

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