Google Sheets API به شما امکان می دهد جداول محوری را در صفحات گسترده ایجاد و به روز کنید. مثال‌های موجود در این صفحه نشان می‌دهند که چگونه می‌توانید به برخی از عملیات رایج جدول محوری با Sheets API دست پیدا کنید.
این نمونه ها در قالب درخواست های HTTP برای خنثی بودن زبان ارائه شده اند. برای یادگیری نحوه اجرای یک به‌روزرسانی دسته‌ای به زبان‌های مختلف با استفاده از کتابخانه‌های سرویس گیرنده Google API، به به‌روزرسانی صفحات گسترده مراجعه کنید.
در این مثال‌ها، متغیرهای SPREADSHEET_ID و SHEET_ID نشان می‌دهند که کجا آن شناسه‌ها را ارائه می‌کنید. می‌توانید شناسه صفحه‌گسترده را در URL صفحه‌گسترده پیدا کنید. با استفاده از روش spreadsheets.get می توانید شناسه برگه را دریافت کنید. محدوده ها با استفاده از نماد A1 مشخص می شوند. محدوده نمونه Sheet1!A1:D5 است.
علاوه بر این، مکان نگهدار SOURCE_SHEET_ID برگه شما را با داده های منبع نشان می دهد. در این مثال‌ها، این جدولی است که در زیر داده‌های منبع جدول محوری فهرست شده است.
داده های منبع جدول محوری
برای این مثال‌ها، فرض کنید صفحه‌گسترده مورد استفاده، داده‌های «فروش» منبع زیر را در صفحه اول خود دارد («Sheet1»). رشته‌های ردیف اول برچسب‌هایی برای ستون‌های جداگانه هستند. برای مشاهده نمونه هایی از نحوه خواندن از برگه های دیگر در صفحه گسترده خود، به نماد A1 مراجعه کنید.
| الف | ب | سی | D | E | اف | جی | |
| 1 | دسته بندی آیتم | شماره مدل | هزینه | مقدار | منطقه | فروشنده | تاریخ ارسال |
| 2 | چرخ | W-24 | 20.50 دلار | 4 | غرب | بث | 3/1/2016 |
| 3 | درب | D-01X | 15.00 دلار | 2 | جنوب | امیر | 1395/3/15 |
| 4 | موتور | ENG-0134 | 100.00 دلار | 1 | شمال | کارمن | 2016/3/20 |
| 5 | قاب | FR-0B1 | 34.00 دلار | 8 | شرق | هانا | 3/12/2016 |
| 6 | پانل | P-034 | 6.00 دلار | 4 | شمال | دوین | 4/2/2016 |
| 7 | پانل | P-052 | 11.50 دلار | 7 | شرق | اریک | 1395/5/16 |
| 8 | چرخ | W-24 | 20.50 دلار | 11 | جنوب | شلدون | 30/4/2016 |
| 9 | موتور | ENG-0161 | 330.00 دلار | 2 | شمال | جسی | 7/2/2016 |
| 10 | درب | D-01Y | 29.00 دلار | 6 | غرب | آرماندو | 1395/3/13 |
| 11 | قاب | FR-0B1 | 34.00 دلار | 9 | جنوب | یولیانا | 2016/2/27 |
| 12 | پانل | P-102 | 3.00 دلار | 15 | غرب | کارمن | 1395/4/18 |
| 13 | پانل | P-105 | 8.25 دلار | 13 | غرب | جسی | 2016/6/20 |
| 14 | موتور | ENG-0211 | 283.00 دلار | 1 | شمال | امیر | 2016/6/21 |
| 15 | درب | D-01X | 15.00 دلار | 2 | غرب | آرماندو | 7/3/2016 |
| 16 | قاب | FR-0B1 | 34.00 دلار | 6 | جنوب | کارمن | 1395/07/15 |
| 17 | چرخ | W-25 | 20.00 دلار | 8 | جنوب | هانا | 5/2/2016 |
| 18 | چرخ | W-11 | 29.00 دلار | 13 | شرق | اریک | 1395/5/19 |
| 19 | درب | D-05 | 17.70 دلار | 7 | غرب | بث | 2016/6/28 |
| 20 | قاب | FR-0B1 | 34.00 دلار | 8 | شمال | شلدون | 30/3/2016 |
یک جدول محوری اضافه کنید
نمونه کد spreadsheets.batchUpdate زیر نحوه استفاده از UpdateCellsRequest را برای ایجاد یک جدول محوری از داده‌های منبع نشان می‌دهد و آن را در سلول A50 صفحه مشخص‌شده توسط SHEET_ID لنگر می‌اندازد.
درخواست، جدول محوری را با ویژگی های زیر پیکربندی می کند:
- یک گروه ارزش ( کمیت ) که تعداد فروش را نشان می دهد. از آنجایی که تنها یک گروه مقادیر وجود دارد، 2 تنظیمات ممکن
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تنها زمانی مهم است که 2 یا چند گروه ارزش وجود داشته باشد.
پروتکل درخواست در زیر نشان داده شده است.
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برای آن ستون در گروه ستون Region انجام می شود.
پروتکل درخواست در زیر نشان داده شده است.
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
}
}
],
}