Google Sheets API की मदद से, स्प्रेडशीट में पिवट टेबल बनाई और अपडेट की जा सकती हैं. इस पेज पर दिए गए उदाहरणों से पता चलता है कि Sheets API की मदद से, पिवट टेबल के कुछ सामान्य काम कैसे किए जा सकते हैं.
ये उदाहरण, एचटीटीपी अनुरोधों के तौर पर दिए गए हैं, ताकि ये किसी भी भाषा के लिए काम कर सकें. Google API क्लाइंट लाइब्रेरी का इस्तेमाल करके, अलग-अलग भाषाओं में एक साथ कई बदलाव करने का तरीका जानने के लिए, स्प्रेडशीट अपडेट करना लेख पढ़ें.
इन उदाहरणों में, प्लेसहोल्डर SPREADSHEET_ID और SHEET_ID से पता चलता है कि आपको ये आईडी कहां देने हैं. स्प्रेडशीट के यूआरएल में, स्प्रेडशीट आईडी देखा जा सकता है. spreadsheets.get तरीके का इस्तेमाल करके, शीट आईडी पाया जा सकता है. रेंज को A1 नोटेशन का इस्तेमाल करके तय किया जाता है. रेंज का उदाहरण, Sheet1!A1:D5 है.
इसके अलावा, प्लेसहोल्डर SOURCE_SHEET_ID, सोर्स डेटा वाली आपकी शीट को दिखाता है. इन उदाहरणों में, यह टेबल पिवट टेबल के सोर्स डेटा में दी गई है.
पिवट टेबल का सोर्स डेटा
इन उदाहरणों के लिए, मान लें कि इस्तेमाल की जा रही स्प्रेडशीट की पहली शीट ("शीट1") में, सोर्स के तौर पर "बिक्री" का यह डेटा है. पहली पंक्ति में मौजूद स्ट्रिंग, अलग-अलग कॉलम के लेबल हैं. अपनी स्प्रेडशीट में मौजूद अन्य स्प्रेडशीट से डेटा पढ़ने का तरीका जानने के लिए, A1 नोटेशन देखें.
| A | B | C | D | E | F | G | |
| 1 | आइटम की कैटगरी | मॉडल नंबर | लागत | संख्या | क्षेत्र | सेल्सपर्सन | शिप करने की तारीख |
| 2 | पहिया | W-24 | 20.50 डॉलर | 4 | पश्चिम | बेथ | 3/1/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 | 1,150 रुपये | 7 | पूर्व | एरिक | 16/5/2016 |
| 8 | पहिया | W-24 | 20.50 डॉलर | 11 | दक्षिण | Sheldon | 4/30/2016 |
| 9 | इंजन | ENG-0161 | 330.00 डॉलर | 2 | उत्तरी | जेसी | 7/2/2016 |
| 10 | दरवाज़ा | D-01Y | 29.00 डॉलर | 6 | पश्चिम | Armando | 3/13/2016 |
| 11 | फ़्रेम | FR-0B1 | 34.00 डॉलर | 9 | दक्षिण | यूलियाना | 27/2/2016 |
| 12 | पैनल | P-102 | 3.00 डॉलर | 15 | पश्चिम | Carmen | 4/18/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 | पश्चिम | 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 | पूर्व | एरिक | 19/5/2016 |
| 19 | दरवाज़ा | D-05 | 17.70 डॉलर | 7 | पश्चिम | बेथ | 28/6/2016 |
| 20 | फ़्रेम | FR-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). - तीन लाइन ग्रुप (आइटम कैटगरी, मॉडल नंबर, और लागत).
- कॉलम का एक ग्रुप (क्षेत्र).
- टेबल में, हर ग्रुप में पंक्ति और कॉलम ग्रुप, संख्या के बजाय नाम के हिसाब से क्रम में लगाए जाते हैं. ऐसा करने के लिए,
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 कोड सैंपल में, SHEET_ID से तय की गई शीट की सेल A50 पर ऐंकर की गई पिवट टेबल (अगर मौजूद हो) को मिटाने के लिए, UpdateCellsRequest का इस्तेमाल करने का तरीका बताया गया है.
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 फ़ील्ड का पूरा डेटा देना ज़रूरी है. असल में, पिवट टेबल में बदलाव करने के लिए, उसे किसी नई पिवट टेबल से बदलना ज़रूरी है.
इस अनुरोध से, ओरिजनल पिवट टेबल में ये बदलाव होते हैं:
- ओरिजनल पिवट टेबल (मॉडल नंबर) से दूसरी लाइन का ग्रुप हटाता है.
- कॉलम ग्रुप (सेल्सपर्सन) जोड़ता है. कॉलम, पैनल की बिक्री की कुल संख्या के हिसाब से, घटते क्रम में लगाए जाते हैं. "जस्सी" (13 पैनल की बिक्री) की बाईं ओर, "कारमेन" (15 पैनल की बिक्री) दिखता है.
- "पश्चिम" को छोड़कर, हर क्षेत्र के लिए कॉलम को छोटा कर देता है. साथ ही, उस क्षेत्र के सेल्सपर्सन ग्रुप को छिपा देता है. ऐसा करने के लिए, क्षेत्र कॉलम ग्रुप में उस कॉलम के लिए,
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
}
}
],
}