पिवट टेबल

Google Sheets API की मदद से, स्प्रेडशीट में पिवट टेबल बनाई और अपडेट की जा सकती हैं. इस पेज पर दिए गए उदाहरणों से पता चलता है कि Sheets API की मदद से, पिवट टेबल से जुड़ी सामान्य कार्रवाइयां कैसे की जा सकती हैं.

ये उदाहरण, एचटीटीपी अनुरोधों के तौर पर दिए गए हैं, ताकि ये किसी भी भाषा में इस्तेमाल किए जा सकें. Google API क्लाइंट लाइब्रेरी का इस्तेमाल करके, अलग-अलग भाषाओं में बैच अपडेट लागू करने का तरीका जानने के लिए, स्प्रेडशीट अपडेट करना लेख पढ़ें.

इन उदाहरणों में, प्लेसहोल्डर SPREADSHEET_ID और SHEET_ID से पता चलता है कि आपको ये आईडी कहां देने होंगे. स्प्रेडशीट आईडी, स्प्रेडशीट के यूआरएल में देखा जा सकता है. शीट आईडी पाने के लिए, तरीके spreadsheets.get का इस्तेमाल किया जा सकता है. रेंज, A1 नोटेशन का इस्तेमाल करके बताई जाती हैं. रेंज का एक उदाहरण, Sheet1!A1:D5 है.

इसके अलावा, प्लेसहोल्डर SOURCE_SHEET_ID से पता चलता है कि सोर्स डेटा वाली शीट कौनसी है. इन उदाहरणों में, यह टेबल है जो पिवट टेबल सोर्स डेटा के अंतर्गत सूचीबद्ध है.

पिवट टेबल का सोर्स डेटा

इन उदाहरणों के लिए, मान लें कि इस्तेमाल की जा रही स्प्रेडशीट की पहली शीट ("Sheet1") में, "sales" का यह सोर्स डेटा मौजूद है. पहली लाइन में मौजूद स्ट्रिंग, अलग-अलग कॉलम के लेबल हैं. अपनी स्प्रेडशीट की अन्य शीट से डेटा पढ़ने के उदाहरण देखने के लिए, A1 नोटेशन लेख पढ़ें.

A B C D E F G
1 आइटम की कैटगरी मॉडल नंबर लागत मात्रा क्षेत्र सेल्सपर्सन भेजने की तारीख
2 पहिया W-24 20.50 डॉलर 4 पश्चिम Beth 1/3/2016
3 दरवाज़ा D-01X 15.00 डॉलर 2 दक्षिण Amir 15/3/2016
4 इंजन ENG-0134 100.00 डॉलर 1 उत्तर Carmen 20/3/2016
5 फ़्रेम FR-0B1 34.00 डॉलर 8 पूर्व Hannah 12/3/2016
6 पैनल P-034 6.00 डॉलर 4 उत्तर Devyn 2/4/2016
7 पैनल P-052 11.50 डॉलर 7 पूर्व Erik 16/5/2016
8 पहिया W-24 20.50 डॉलर 11 दक्षिण Sheldon 30/4/2016
9 इंजन ENG-0161 330.00 डॉलर 2 उत्तर Jessie 2/7/2016
10 दरवाज़ा D-01Y 29.00 डॉलर 6 पश्चिम Armando 13/3/2016
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 पश्चिम Jessie 20/6/2016
14 इंजन ENG-0211 283.00 डॉलर 1 उत्तर Amir 21/6/2016
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 दक्षिण Hannah 2/5/2016
18 पहिया W-11 29.00 डॉलर 13 पूर्व Erik 19/5/2016
19 दरवाज़ा D-05 17.70 डॉलर 7 पश्चिम Beth 28/6/2016
20 फ़्रेम FR-0B1 34.00 डॉलर 8 उत्तर Sheldon 30/3/2016

पिवट टेबल जोड़ना

यहां दिया गया spreadsheets.batchUpdate कोड सैंपल दिखाता है कि सोर्स डेटा से पिवट टेबल बनाने के लिए, UpdateCellsRequest का इस्तेमाल कैसे किया जाता है. साथ ही, यह भी दिखाता है कि SHEET_ID से तय की गई शीट के सेल A50 पर इसे कैसे ऐंकर किया जाता है.

अनुरोध, पिवट टेबल को इन प्रॉपर्टी के साथ कॉन्फ़िगर करता है:

  • वैल्यू का एक ग्रुप (Quantity), जो बिक्री की संख्या दिखाता है. वैल्यू का सिर्फ़ एक ग्रुप होने की वजह से, दो संभावित valueLayout सेटिंग एक जैसी होती हैं.
  • लाइन के दो ग्रुप (Item Category और Model Number). पहला ग्रुप, "West" Region से Quantity के कुल मान को बढ़ते क्रम में सॉर्ट करता है. इसलिए, "Engine" (जिसकी बिक्री पश्चिम में नहीं हुई है) "Door" (जिसकी बिक्री पश्चिम में 15 हुई है) से ऊपर दिखता है. Model Number ग्रुप, सभी क्षेत्रों में कुल बिक्री को घटते क्रम में सॉर्ट करता है. इसलिए, "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 का इस्तेमाल कैसे किया जाता है. साथ ही, यह भी दिखाता है कि SHEET_ID से तय की गई शीट के सेल A50 पर इसे कैसे ऐंकर किया जाता है.

अनुरोध, पिवट टेबल को इन प्रॉपर्टी के साथ कॉन्फ़िगर करता है:

  • वैल्यू के दो ग्रुप (Quantity और Total Price). पहला ग्रुप, बिक्री की संख्या दिखाता है. दूसरा ग्रुप, कैलकुलेट की गई वैल्यू है. यह वैल्यू, किसी पार्ट की लागत और उसकी कुल बिक्री को गुणा करके निकाली जाती है. इसके लिए, इस फ़ॉर्मूले का इस्तेमाल किया जाता है: =Cost*SUM(Quantity).
  • लाइन के तीन ग्रुप (Item Category, Model Number, और Cost).
  • कॉलम का एक ग्रुप (Region).
  • लाइन और कॉलम के ग्रुप, हर ग्रुप में 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 का इस्तेमाल करके, पिवट टेबल को कैसे मिटाया जाता है. यह पिवट टेबल, SHEET_IDसे तय की गई शीट के सेल A50 पर ऐंकर की गई है. अगर यह पिवट टेबल मौजूद नहीं है, तो इसे मिटाया नहीं जा सकता.

UpdateCellsRequest की मदद से, पिवट टेबल को हटाया जा सकता है. इसके लिए, fields पैरामीटर में "pivotTable" शामिल करें. साथ ही, ऐंकर सेल पर 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 का इस्तेमाल करके, पिवट टेबल जोड़ना में बनाई गई पिवट टेबल में बदलाव कैसे किया जाता है.

CellData संसाधन में मौजूद pivotTable फ़ील्ड के सबसेट को, fields पैरामीटर की मदद से अलग-अलग नहीं बदला जा सकता. बदलाव करने के लिए, पूरा pivotTable फ़ील्ड देना होगा. असल में, पिवट टेबल में बदलाव करने के लिए, उसे नई पिवट टेबल से बदलना होता है.

अनुरोध से, ओरिजनल पिवट टेबल में ये बदलाव होते हैं:

  • ओरिजनल पिवट टेबल से, लाइन का दूसरा ग्रुप (Model Number) हट जाता है.
  • कॉलम का एक ग्रुप (Salesperson) जुड़ जाता है. कॉलम, Panel की कुल बिक्री के हिसाब से घटते क्रम में सॉर्ट होते हैं. "Carmen" (जिसकी बिक्री Panel की 15 हुई है) "Jessie" (जिसकी बिक्री Panel की 13 हुई है) के बाईं ओर दिखता है.
  • हर Region के लिए कॉलम को छोटा कर दिया जाता है. हालांकि, "West" के लिए कॉलम छोटा नहीं किया जाता. इससे उस क्षेत्र के लिए Salesperson ग्रुप छिप जाता है. ऐसा करने के लिए, Region कॉलम ग्रुप में मौजूद उस कॉलम के लिए, valueMetadata में collapsed को true पर सेट किया जाता है.

अनुरोध का प्रोटोकॉल यहां दिखाया गया है.

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)

रिस्पॉन्स में, a Spreadsheet संसाधन शामिल होता है. इसमें a 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
      }
    }
  ],
}