جداول محوری، جداول محوری

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
      }
    }
  ],
}