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

تتيح لك Google Sheets API إنشاء الجداول المحورية وتعديلها ضمن جداول البيانات. توضِّح الأمثلة الواردة في هذه الصفحة كيفية تنفيذ بعض عمليات جدول pivot الشائعة باستخدام واجهة برمجة التطبيقات 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 الغرب Beth 1/3/2016
3 باب D-01X $15.00 2 الجنوب Amir 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 الشمال دينا 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 الجنوب Yuliana 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 الشمال Amir 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 الغرب Beth 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
      }
    }
  ],
}