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

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

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

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

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

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

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

	A	ب	C	D	ج	ح	G
1	فئة العنصر	رقم الطراز	التكلفة	الكمية	المنطقة	مندوب مبيعات	تاريخ الشحن
2	العجلة	W-24	20.50 دولار أمريكي	4	الغرب	بيث	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	2/4/2016
7	لوحة	P-052	11.50 دولارًا أمريكيًا (أو ما يعادل هذا المبلغ بالعملة المحلية)	7	الشرق	إريك	16/5/2016
8	العجلة	W-24	20.50 دولار أمريكي	11	الجنوب	Sheldon	30/4/2016
9	المحرك	ENG-0161	330.00 دولار أمريكي	2	الشمال	جيسي	2/7/2016
10	باب	D-01Y	29.00 دولار أمريكي	6	الغرب	Armando	2016/3/13
11	إطار	FR-0B1	34.00 دولار أمريكي	9	الجنوب	Yuliana	27/2/2016
12	لوحة	P-102	$3,00	15	الغرب	Carmen	18/4/2016
13	لوحة	P-105	8.25 دولار أمريكي	13	الغرب	جيسي	20/6/2016
14	المحرك	ENG-0211	283.00 دولار أمريكي	1	الشمال	أمير	2016/6/21
15	باب	D-01X	$15.00	2	الغرب	Armando	3/7/2016
16	إطار	FR-0B1	34.00 دولار أمريكي	6	الجنوب	Carmen	15/7/2016
17	العجلة	W-25	$20.00	8	الجنوب	حنا	2016/5/2
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 من مبيعات اللوحة) تظهر لـ على يسار "جيسي" (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
      }
    }
  ],
}