تتيح لك Google Sheets API إنشاء جداول محورية وتحديثها داخل جداول البيانات. توضح الأمثلة الموجودة في هذه الصفحة كيف يمكنك تحقيق بعض عمليات الجدول المحوري الشائعة باستخدام Sheets API.
يتم تقديم هذه الأمثلة في شكل طلبات HTTP لتكون محايدة اللغة. لمعرفة كيفية تنفيذ تعديل مجمّع بلغات مختلفة باستخدام مكتبات برامج واجهة Google API، يمكنك الاطّلاع على مقالة تحديث جداول البيانات.
في هذه الأمثلة، يشير العنصران النائبان SPREADSHEET_ID
وSHEET_ID
إلى مكان تقديم هذين المعرّفَين. يمكنك العثور على رقم تعريف
جدول البيانات في عنوان URL لجدول البيانات. يمكنك الحصول على معرّف ورقة البيانات باستخدام الطريقة spreadsheets.get
. يتم تحديد النطاقات باستخدام ترميز A1. مثال على النطاق
Sheet1!A1:D5.
بالإضافة إلى ذلك، يشير العنصر النائب SOURCE_SHEET_ID
إلى ورقة البيانات
التي تتضمن بيانات المصدر. في هذه الأمثلة، هذا هو الجدول المُدرَج ضمن بيانات مصدر الجدول المحوري.
بيانات مصدر الجدول المحوري
بالنسبة لهذه الأمثلة، افترض أن جدول البيانات المستخدم يحتوي على بيانات "المبيعات" التالية في الورقة الأولى ("Sheet1"). السلاسل في الصف الأول هي تسميات للأعمدة الفردية. لعرض أمثلة حول كيفية القراءة من أوراق أخرى في جدول البيانات، راجع ترميز A1.
جيم | B | C | D | ج | F | 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 | الشمال | كارمن | 2016/3/20 |
5 | إطار | FR-0B1 | 34.00 دولار أمريكي | 8 | الشرق | حنا | 2016/3/12 |
6 | لوحة | P-034 | 6.00 دولار أمريكي | 4 | الشمال | ديفين | 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 | الغرب | أرماندو | 2016/3/13 |
11 | إطار | FR-0B1 | 34.00 دولار أمريكي | 9 | الجنوب | يوليانا | 27/2/2016 |
12 | لوحة | P-102 | 3.00 دولارات أمريكية (أو ما يعادلها بالعملة المحلية) | 15 | الغرب | كارمن | 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 | الغرب | أرماندو | 3/7/2016 |
16 | إطار | FR-0B1 | 34.00 دولار أمريكي | 6 | الجنوب | كارمن | 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
المحتملان مكافئَين. - مجموعتا صفوف (فئة العنصر ورقم الطراز). الترتيب الأول هو الترتيب التصاعدي لقيمة الكمية الإجمالية من المنطقة "الغربية". لذلك، تظهر كلمة "Engine" (بدون مبيعات للغرب) أعلى "الباب" (مع 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 لوحة مبيعات) على يسار "جيسي" (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
} } ], }