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

API گوگل شیت به شما امکان می‌دهد جداول محوری را در صفحات گسترده ایجاد و به‌روزرسانی کنید. مثال‌های این صفحه نشان می‌دهد که چگونه می‌توانید با استفاده از API شیت، برخی از عملیات رایج جدول محوری را انجام دهید.

این مثال‌ها به صورت درخواست‌های HTTP ارائه شده‌اند تا از نظر زبانی بی‌طرف باشند. برای یادگیری نحوه پیاده‌سازی به‌روزرسانی دسته‌ای در زبان‌های مختلف با استفاده از کتابخانه‌های کلاینت API گوگل، به صفحات گسترده به‌روزرسانی مراجعه کنید.

در این مثال‌ها، متغیرهای SPREADSHEET_ID و SHEET_ID نشان می‌دهند که این شناسه‌ها را کجا باید ارائه دهید. می‌توانید شناسه صفحه گسترده را در URL صفحه گسترده پیدا کنید. می‌توانید شناسه صفحه را با استفاده از متد spreadsheets.get دریافت کنید. محدوده‌ها با استفاده از نمادگذاری A1 مشخص شده‌اند. یک محدوده به عنوان مثال Sheet1!A1:D5 است.

علاوه بر این، عبارت SOURCE_SHEET_ID نشان دهنده‌ی شیت شما با داده‌های منبع است. در این مثال‌ها، این جدولی است که در زیر Pivot table source data فهرست شده است.

داده‌های منبع جدول محوری

برای این مثال‌ها، فرض کنید صفحه‌گسترده مورد استفاده، داده‌های منبع "فروش" زیر را در اولین برگه خود ("صفحه1") دارد. رشته‌های موجود در ردیف اول، برچسب‌هایی برای ستون‌های منفرد هستند. برای مشاهده مثال‌هایی از نحوه خواندن از سایر برگه‌های صفحه‌گسترده خود، به نمادگذاری A1 مراجعه کنید.

الف ب سی دی ای ف جی
۱ دسته بندی کالا شماره مدل هزینه مقدار منطقه فروشنده تاریخ ارسال
۲ چرخ W-24 ۲۰.۵۰ دلار ۴ غرب بث ۱/۳/۲۰۱۶
۳ درب D-01X ۱۵٫۰۰ دلار ۲ جنوب امیر ۱۵/۳/۲۰۱۶
۴ موتور ENG-0134 ۱۰۰٫۰۰ دلار ۱ شمال کارمن ۲۰/۳/۲۰۱۶
۵ قاب FR-0B1 ۳۴٫۰۰ دلار ۸ شرق هانا ۳/۱۲/۲۰۱۶
۶ پنل پ-034 ۶٫۰۰ دلار ۴ شمال دوین ۴/۲/۲۰۱۶
۷ پنل پ-۰۵۲ ۱۱.۵۰ دلار ۷ شرق اریک ۱۶/۵/۲۰۱۶
۸ چرخ W-24 ۲۰.۵۰ دلار ۱۱ جنوب شلدون ۳۰/۴/۲۰۱۶
۹ موتور ENG-0161 ۳۳۰٫۰۰ دلار ۲ شمال جسی ۷/۲/۲۰۱۶
۱۰ درب D-01Y ۲۹٫۰۰ دلار ۶ غرب آرماندو ۱۳/۳/۲۰۱۶
۱۱ قاب FR-0B1 ۳۴٫۰۰ دلار ۹ جنوب یولیانا ۲۷/۲/۲۰۱۶
۱۲ پنل پ-۱۰۲ ۳٫۰۰ دلار ۱۵ غرب کارمن ۱۸/۴/۲۰۱۶
۱۳ پنل پ-۱۰۵ ۸.۲۵ دلار ۱۳ غرب جسی ۲۰/۶/۲۰۱۶
۱۴ موتور ENG-0211 ۲۸۳٫۰۰ دلار ۱ شمال امیر ۲۱/۶/۲۰۱۶
۱۵ درب D-01X ۱۵٫۰۰ دلار ۲ غرب آرماندو ۷/۳/۲۰۱۶
۱۶ قاب FR-0B1 ۳۴٫۰۰ دلار ۶ جنوب کارمن ۱۵/۷/۲۰۱۶
۱۷ چرخ W-25 ۲۰٫۰۰ دلار ۸ جنوب هانا ۵/۲/۲۰۱۶
۱۸ چرخ W-11 ۲۹٫۰۰ دلار ۱۳ شرق اریک ۱۹/۵/۲۰۱۶
۱۹ درب دی-۰۵ ۱۷.۷۰ دلار ۷ غرب بث ۶/۲۸/۲۰۱۶
۲۰ قاب FR-0B1 ۳۴٫۰۰ دلار ۸ شمال شلدون ۳/۳۰/۲۰۱۶

اضافه کردن جدول محوری

نمونه کد spreadsheets.batchUpdate زیر نحوه استفاده از UpdateCellsRequest را برای ایجاد یک جدول محوری از داده‌های منبع نشان می‌دهد و آن را در سلول A50 از برگه مشخص شده توسط SHEET_ID قرار می‌دهد.

این درخواست، جدول محوری را با ویژگی‌های زیر پیکربندی می‌کند:

  • یک گروه مقادیر ( Quantity ) که تعداد فروش را نشان می‌دهد. از آنجایی که فقط یک گروه مقادیر وجود دارد، دو تنظیم valueLayout ممکن معادل یکدیگر هستند.
  • دو گروه ردیفی ( رده کالا و شماره مدل ). اولین گروه، مرتب‌سازی را بر اساس مقدار صعودی کل تعداد از منطقه "غرب" انجام می‌دهد. بنابراین، "موتور" (بدون فروش غرب) بالای "درب" (با 15 فروش غرب) ظاهر می‌شود. گروه شماره مدل، مرتب‌سازی را به ترتیب نزولی کل فروش در همه مناطق انجام می‌دهد، بنابراین "W-24" (15 فروش) بالای "W-25" (8 فروش) ظاهر می‌شود. این کار با تنظیم فیلد valueBucket روی {} انجام می‌شود.
  • یک گروه ستون ( Region ) که به ترتیب صعودی بیشترین فروش را مرتب می‌کند. باز هم، valueBucket روی {} تنظیم شده است. "North" کمترین کل فروش را دارد و بنابراین به عنوان اولین ستون 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"
                          }
                        ]
                      }
                    },
                    {
                      "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 را برای ویرایش جدول محوری ایجاد شده در Add a pivot table نشان می‌دهد.

زیرمجموعه‌های فیلد 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
      }
    }
  ],
}