पिवट टेबल

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

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

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

इसके अलावा, प्लेसहोल्डर SOURCE_SHEET_ID आपकी शीट को सोर्स डेटा के साथ दिखाता है. इन उदाहरणों में, यह पिवट टेबल के सोर्स डेटा में दी गई टेबल है.

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

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

	जवाब	B	C	D	E	F	G
1	आइटम की कैटगरी	मॉडल नंबर	कीमत	संख्या	इलाका	सेल्सपर्सन	भेजने की तारीख
2	व्हील	W-24	20.50 डॉलर	4	पश्चिम	बेथ	1/3/2016
3	दरवाज़ा	D-01X	15.00 डॉलर	2	दक्षिण	आमिर	15/3/2016
4	इंजन	ENG-0134	INR4500.00	1	उत्तरी	कारमेन	20/3/2016
5	Frame	फ़्रेंच-0B1	34.00 डॉलर	8	पूर्व	हाना	12/3/2016
6	पैनल	P-034	INR270	4	उत्तरी	डैविन	2/4/2016
7	पैनल	P-052	11.50 डॉलर	7	पूर्व	एरिक	16/5/2016
8	व्हील	W-24	20.50 डॉलर	11	दक्षिण	Sheldon	30/4/2016
9	इंजन	ENG-0161	330.00 डॉलर	2	उत्तरी	जेसी	2/7/2016
10	दरवाज़ा	D-01Y	29.00 डॉलर	6	पश्चिम	अरमांडो	13/3/2016
11	Frame	फ़्रेंच-0B1	34.00 डॉलर	9	दक्षिण	यूलियाना	27/2/2016
12	पैनल	P-102	3.00 डॉलर	15	पश्चिम	कारमेन	18/4/2016
13	पैनल	P-105	8.25 डॉलर	13	पश्चिम	जेसी	20/6/2016
14	इंजन	ENG-0211	283.00 डॉलर	1	उत्तरी	आमिर	21/6/2016
15	दरवाज़ा	D-01X	15.00 डॉलर	2	पश्चिम	अरमांडो	3/7/2016
16	Frame	फ़्रेंच-0B1	34.00 डॉलर	6	दक्षिण	कारमेन	15/7/2016
17	व्हील	W-25	20.00 डॉलर	8	दक्षिण	हाना	2/5/2016
18	व्हील	W-11	29.00 डॉलर	13	पूर्व	एरिक	19/5/2016
19	दरवाज़ा	D-05	17.70 डॉलर	7	पश्चिम	बेथ	28/6/2016
20	Frame	फ़्रेंच-0B1	34.00 डॉलर	8	उत्तरी	Sheldon	30/3/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).
• तीन पंक्तियों के ग्रुप (आइटम की कैटगरी, मॉडल नंबर, और कीमत).
• एक कॉलम ग्रुप (इलाका).
• पंक्ति और कॉलम के ग्रुप, हर ग्रुप में नाम के हिसाब से क्रम में लगाए जाते हैं (न कि संख्या के हिसाब से), जिससे टेबल की अंग्रेज़ी वर्णमाला के हिसाब से क्रम में लगाया जाता है. ऐसा करने के लिए, 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"
      }
    }
  ]
}

पिवट टेबल के कॉलम और पंक्तियों में बदलाव करें

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

CellData संसाधन में pivotTable फ़ील्ड के सबसेट को fields पैरामीटर से अलग-अलग नहीं बदला जा सकता. बदलाव करने के लिए, पूरा 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 संसाधन शामिल होते हैं, जिसमें SheetProperties एलिमेंट वाला Sheet ऑब्जेक्ट होता है. 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
      }
    }
  ],
}