पिवट टेबल

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	2,050 रुपये	4	पश्चिम	बेथ	1/3/2016
3	दरवाज़ा	D-01X	15.00 डॉलर	2	दक्षिण	अमीर	15/3/2016
4	इंजन	ENG-0134	INR4500.00	1	उत्तरी	Carmen	20/3/2016
5	फ़्रेम	FR-0B1	34.00 डॉलर	8	पूर्व	हैना	3/12/2016
6	पैनल	P-034	INR270	4	उत्तरी	Devyn	4/2/2016
7	पैनल	P-052	11.50 डॉलर	7	पूर्व	एरिक	5/16/2016
8	पहिया	W-24	2,050 रुपये	11	दक्षिण	Sheldon	4/30/2016
9	इंजन	ENG-0161	$330.00	2	उत्तरी	Jessie	7/2/2016
10	दरवाज़ा	D-01Y	29.00 डॉलर	6	पश्चिम	Armando	3/13/2016
11	फ़्रेम	FR-0B1	34.00 डॉलर	9	दक्षिण	Yuliana	2/27/2016
12	पैनल	P-102	3.00 डॉलर	15	पश्चिम	Carmen	4/18/2016
13	पैनल	P-105	8.25 डॉलर	13	पश्चिम	Jessie	6/20/2016
14	इंजन	ENG-0211	283.00 डॉलर	1	उत्तरी	अमीर	6/21/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	दक्षिण	हैना	5/2/2016
18	पहिया	W-11	29.00 डॉलर	13	पूर्व	एरिक	5/19/2016
19	दरवाज़ा	D-05	1,770 रुपये	7	पश्चिम	बेथ	28/6/2016
20	फ़्रेम	FR-0B1	34.00 डॉलर	8	उत्तरी	Sheldon	3/30/2016

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

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

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

• एक वैल्यू ग्रुप (मात्रा), जो बिक्री की संख्या दिखाता है. सिर्फ़ एक वैल्यू ग्रुप होने की वजह से, 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 का इस्तेमाल करके, सोर्स डेटा से कैलकुलेट की गई वैल्यू का ग्रुप वाली पिवट टेबल बनाने का तरीका दिखाया गया है. साथ ही, इसे SHEET_ID से तय की गई शीट की सेल A50 पर ऐंकर किया गया है.

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

• वैल्यू के दो ग्रुप (मात्रा और कुल कीमत). पहले से बिक्री की संख्या का पता चलता है. दूसरी वैल्यू, किसी पार्ट की लागत और उसकी कुल बिक्री के आधार पर कैलकुलेट की गई वैल्यू होती है. इसके लिए, इस फ़ॉर्मूले का इस्तेमाल किया जाता है: =Cost*SUM(Quantity).
• तीन लाइन ग्रुप (आइटम कैटगरी, मॉडल नंबर, और कीमत).
• एक कॉलम ग्रुप (रीजन).
• हर ग्रुप में, लाइन और कॉलम ग्रुप को नाम के हिसाब से क्रम में लगाया जाता है, न कि संख्या के हिसाब से. इससे टेबल में मौजूद डेटा को वर्णमाला के हिसाब से क्रम में लगाया जाता है. इसके लिए, PivotGroup से valueBucket फ़ील्ड को हटाना होता है.
  • टेबल को आसान बनाने के लिए, अनुरोध में मुख्य लाइन और कॉलम ग्रुप को छोड़कर, बाकी सभी के लिए सबटोटल छिपाने का विकल्प चुना गया है.
• इस अनुरोध में, टेबल को बेहतर दिखाने के लिए 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 का इस्तेमाल करने का तरीका बताया गया है.

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

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

• इससे ओरिजनल पिवट टेबल (मॉडल नंबर) से दूसरा लाइन ग्रुप हट जाता है.
• कॉलम ग्रुप (सेल्सपर्सन) जोड़ता है. कॉलम को पैनल की कुल बिक्री के हिसाब से घटते क्रम में लगाया जाता है. "कारमेन" (15 पैनल की बिक्री) "जेसी" (13 पैनल की बिक्री) के बाईं ओर दिखती है.
• यह फ़ंक्शन, "पश्चिम" को छोड़कर हर क्षेत्र के कॉलम को छोटा कर देता है. साथ ही, उस क्षेत्र के लिए सेल्सपर्सन ग्रुप को छिपा देता है. इसके लिए, क्षेत्र कॉलम ग्रुप में मौजूद उस कॉलम के लिए, 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)

जवाब में एक 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
      }
    }
  ],
}