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

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

يتم تقديم هذه الأمثلة في شكل طلبات 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 الجنوب أمير 3/15/2016
4 المحرك ENG-0134 $100.00‎ 1 الشمال كارمن 3/20/2016
5 إطار FR-0B1 34.00 دولار أمريكي 8 الشرق Hannah 3/12/2016
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 الشمال منى 2/7/2016
10 باب D-01Y 29.00 دولار أمريكي 6 الغرب Armando 3/13/2016
11 إطار FR-0B1 34.00 دولار أمريكي 9 الجنوب يوليانا 2016/2/27
12 لوحة P-102 $3,00 15 الغرب كارمن 2016/4/18
13 لوحة P-105 8.25 دولار أمريكي 13 الغرب منى 2016/6/20
14 المحرك ENG-0211 283.00 دولار أمريكي 1 الشمال أمير 21‏/6‏/2016
15 باب D-01X $15.00 2 الغرب Armando 3/7/2016
16 إطار FR-0B1 34.00 دولار أمريكي 6 الجنوب كارمن 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 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
      }
    }
  ],
}