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