গুগল শিটস এপিআই আপনাকে স্প্রেডশিটের মধ্যে পিভট টেবিল তৈরি এবং আপডেট করার সুবিধা দেয়। এই পৃষ্ঠার উদাহরণগুলিতে দেখানো হয়েছে, কীভাবে আপনি শিটস এপিআই ব্যবহার করে কিছু সাধারণ পিভট টেবিল অপারেশন সম্পাদন করতে পারেন।
ভাষা নিরপেক্ষতার জন্য এই উদাহরণগুলো HTTP অনুরোধ আকারে উপস্থাপন করা হয়েছে। গুগল এপিআই ক্লায়েন্ট লাইব্রেরি ব্যবহার করে বিভিন্ন ভাষায় কীভাবে ব্যাচ আপডেট বাস্তবায়ন করতে হয় তা জানতে, “স্প্রেডশিট আপডেট করুন” দেখুন।
এই উদাহরণগুলিতে, SPREADSHEET_ID এবং SHEET_ID প্লেসহোল্ডারগুলি নির্দেশ করে যে আপনি কোথায় সেই আইডিগুলি প্রদান করবেন। আপনি স্প্রেডশিট URL-এ স্প্রেডশিট আইডি খুঁজে পেতে পারেন। আপনি spreadsheets.get মেথড ব্যবহার করে শীট আইডি পেতে পারেন। রেঞ্জগুলি A1 নোটেশন ব্যবহার করে নির্দিষ্ট করা হয়। একটি উদাহরণ রেঞ্জ হল Sheet1!A1:D5।
এছাড়াও, প্লেসহোল্ডার SOURCE_SHEET_ID আপনার সোর্স ডেটা সহ শীটটিকে নির্দেশ করে। এই উদাহরণগুলিতে, এটি হল পিভট টেবিল সোর্স ডেটা-এর অধীনে তালিকাভুক্ত টেবিল।
পিভট টেবিলের উৎস ডেটা
এই উদাহরণগুলোর জন্য, ধরে নিন যে ব্যবহৃত স্প্রেডশীটটির প্রথম শীটে ("শীট১") নিম্নলিখিত উৎস "বিক্রয়" ডেটা রয়েছে। প্রথম সারির স্ট্রিংগুলো হলো প্রতিটি কলামের লেবেল। আপনার স্প্রেডশীটের অন্যান্য শীট থেকে কীভাবে ডেটা পড়তে হয় তার উদাহরণ দেখতে, A1 নোটেশন দেখুন।
| একটি | বি | সি | ডি | ই | এফ | জি | |
| ১ | আইটেম বিভাগ | মডেল নম্বর | খরচ | পরিমাণ | অঞ্চল | বিক্রয়কর্মী | চালানের তারিখ |
| ২ | চাকা | ডাব্লিউ-২৪ | ২০.৫০ ডলার | ৪ | পশ্চিম | বেথ | ৩/১/২০১৬ |
| ৩ | দরজা | ডি-০১এক্স | $১৫.০০ | ২ | দক্ষিণ | আমির | ৩/১৫/২০১৬ |
| ৪ | ইঞ্জিন | ইএনজি-০১৩৪ | $100.00 | ১ | উত্তর | কারমেন | ২০/৩/২০১৬ |
| ৫ | ফ্রেম | FR-0B1 | ৩৪.০০ ডলার | ৮ | পূর্ব | হানা | ৩/১২/২০১৬ |
| ৬ | প্যানেল | পি-০৩৪ | $৬.০০ | ৪ | উত্তর | ডেভিন | ৪/২/২০১৬ |
| ৭ | প্যানেল | পি-০৫২ | ১১.৫০ ডলার | ৭ | পূর্ব | এরিক | ৫/১৬/২০১৬ |
| ৮ | চাকা | ডাব্লিউ-২৪ | ২০.৫০ ডলার | ১১ | দক্ষিণ | শেলডন | ৩০/৪/২০১৬ |
| ৯ | ইঞ্জিন | ইএনজি-০১৬১ | $৩৩০.০০ | ২ | উত্তর | জেসি | ৭/২/২০১৬ |
| ১০ | দরজা | ডি-০১ওয়াই | ২৯.০০ ডলার | ৬ | পশ্চিম | আরমান্দো | ১৩/৩/২০১৬ |
| ১১ | ফ্রেম | FR-0B1 | ৩৪.০০ ডলার | ৯ | দক্ষিণ | ইউলিয়ানা | ২৭/২/২০১৬ |
| ১২ | প্যানেল | পি-১০২ | $৩.০০ | ১৫ | পশ্চিম | কারমেন | ৪/১৮/২০১৬ |
| ১৩ | প্যানেল | পি-১০৫ | ৮.২৫ ডলার | ১৩ | পশ্চিম | জেসি | ২০/৬/২০১৬ |
| ১৪ | ইঞ্জিন | ইএনজি-০২১১ | ২৮৩.০০ ডলার | ১ | উত্তর | আমির | ২১/৬/২০১৬ |
| ১৫ | দরজা | ডি-০১এক্স | $১৫.০০ | ২ | পশ্চিম | আরমান্দো | ৭/৩/২০১৬ |
| ১৬ | ফ্রেম | FR-0B1 | ৩৪.০০ ডলার | ৬ | দক্ষিণ | কারমেন | ৭/১৫/২০১৬ |
| ১৭ | চাকা | ডাব্লিউ-২৫ | ২০.০০ ডলার | ৮ | দক্ষিণ | হানা | ৫/২/২০১৬ |
| ১৮ | চাকা | ডাব্লিউ-১১ | ২৯.০০ ডলার | ১৩ | পূর্ব | এরিক | ৫/১৯/২০১৬ |
| ১৯ | দরজা | ডি-০৫ | ১৭.৭০ ডলার | ৭ | পশ্চিম | বেথ | ২৮/৬/২০১৬ |
| ২০ | ফ্রেম | FR-0B1 | ৩৪.০০ ডলার | ৮ | উত্তর | শেলডন | ৩০/৩/২০১৬ |
একটি পিভট টেবিল যোগ করুন
নিম্নলিখিত spreadsheets.batchUpdate কোড নমুনাটি দেখায় কিভাবে SHEET_ID দ্বারা নির্দিষ্ট শীটের A50 সেলে অ্যাঙ্কর করে উৎস ডেটা থেকে একটি পিভট টেবিল তৈরি করতে UpdateCellsRequest ব্যবহার করতে হয়।
অনুরোধটি নিম্নলিখিত বৈশিষ্ট্যগুলির সাথে পিভট টেবিলটি কনফিগার করে:
- একটি ভ্যালু গ্রুপ ( কোয়ান্টিটি ) রয়েছে যা বিক্রয়ের সংখ্যা নির্দেশ করে। যেহেতু ভ্যালু গ্রুপ একটাই, তাই দুটি সম্ভাব্য
valueLayoutসেটিং সমতুল্য। - দুটি সারি গ্রুপ ( আইটেম ক্যাটাগরি এবং মডেল নম্বর )। প্রথমটি "পশ্চিম" অঞ্চলের মোট পরিমাণের আরোহী ক্রমে সাজানো হয়। তাই, "ইঞ্জিন" (পশ্চিমে কোনো বিক্রি নেই) "ডোর" (পশ্চিমে ১৫টি বিক্রি) এর উপরে প্রদর্শিত হয়। মডেল নম্বর গ্রুপটি সমস্ত অঞ্চলের মোট বিক্রির অবরোহী ক্রমে সাজানো হয়, তাই "W-24" (১৫টি বিক্রি) "W-25" (৮টি বিক্রি) এর উপরে প্রদর্শিত হয়। এটি
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 কোড নমুনাটি দেখায় কিভাবে SHEET_ID দ্বারা নির্দিষ্ট শীটের A50 সেলে অ্যাঙ্কর করে, উৎস ডেটা থেকে একটি ক্যালকুলেটেড ভ্যালু গ্রুপ সহ একটি পিভট টেবিল তৈরি করতে UpdateCellsRequest ব্যবহার করতে হয়।
অনুরোধটি নিম্নলিখিত বৈশিষ্ট্যগুলির সাথে পিভট টেবিলটি কনফিগার করে:
- দুটি মান গোষ্ঠী ( পরিমাণ এবং মোট মূল্য )। প্রথমটি বিক্রয়ের সংখ্যা নির্দেশ করে। দ্বিতীয়টি হলো একটি গণনাকৃত মান, যা একটি যন্ত্রাংশের খরচ এবং তার মোট বিক্রয়ের সংখ্যার গুণফলের উপর ভিত্তি করে নিম্নলিখিত সূত্র ব্যবহার করে নির্ণয় করা হয়:
=Cost*SUM(Quantity)। - তিনটি সারি গ্রুপ ( পণ্যের বিভাগ , মডেল নম্বর এবং মূল্য )।
- এক কলাম গ্রুপ ( অঞ্চল )।
- প্রতিটি গ্রুপে সারি এবং কলাম গ্রুপগুলো পরিমাণ (Quantity ) অনুসারে না সাজিয়ে নাম অনুসারে বিন্যস্ত করে, যা টেবিলটিকে বর্ণানুক্রমিকভাবে সাজায়। এটি করার জন্য
PivotGroupথেকেvalueBucketফিল্ডটি বাদ দেওয়া হয়।- টেবিলের চেহারা সরল করার জন্য, অনুরোধটি প্রধান সারি এবং কলাম গ্রুপ ব্যতীত বাকি সবগুলোর উপ-মোট লুকিয়ে রাখে।
- টেবিলের উন্নত চেহারার জন্য অনুরোধটি
valueLayoutVERTICALএ সেট করে।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 কোড নমুনাটি দেখায় কিভাবে "Add a pivot table" -এ তৈরি করা পিভট টেবিলটি সম্পাদনা করতে UpdateCellsRequest ব্যবহার করতে হয়।
CellData রিসোর্সের pivotTable ফিল্ডের উপসেটগুলো fields প্যারামিটার ব্যবহার করে আলাদাভাবে পরিবর্তন করা যায় না। সম্পাদনা করার জন্য, সম্পূর্ণ pivotTable ফিল্ডটি সরবরাহ করতে হবে। মূলত, একটি পিভট টেবিল সম্পাদনা করার জন্য এটিকে একটি নতুন টেবিল দিয়ে প্রতিস্থাপন করতে হয়।
অনুরোধটি মূল পিভট টেবিলে নিম্নলিখিত পরিবর্তনগুলি করে:
- মূল পিভট টেবিল থেকে দ্বিতীয় সারি গ্রুপটি সরিয়ে দেয় ( মডেল নম্বর )।
- একটি কলাম গ্রুপ ( বিক্রয়কর্মী ) যোগ করে। কলামগুলো মোট প্যানেল বিক্রয়ের সংখ্যা অনুসারে অবরোহী ক্রমে সাজানো হয়। "কারমেন" (১৫টি প্যানেল বিক্রয়) "জেসি" (১৩টি প্যানেল বিক্রয়)-এর বাম দিকে প্রদর্শিত হয়।
- 'পশ্চিম' অঞ্চল ব্যতীত প্রতিটি অঞ্চলের জন্য কলামটি সংকুচিত করে, যার ফলে সেই অঞ্চলের বিক্রয়কর্মী গ্রুপটি লুকানো থাকে। এটি করার জন্য, অঞ্চল কলাম গ্রুপের অন্তর্গত সেই কলামের
valueMetadataতেcollapsedtrueসেট করতে হয়।
অনুরোধ প্রোটোকলটি নিচে দেখানো হলো।
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 অবজেক্ট থাকে। এছাড়াও, PivotTable সম্পর্কিত তথ্য ধারণকারী GridData এলিমেন্টের একটি অ্যারে থাকে। পিভটটেবিলের তথ্য সেই সেলের শীটের 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} } ], }