পিভট টেবিল, পিভট টেবিল

Google Sheets API আপনাকে স্প্রেডশীটের মধ্যে পিভট টেবিল তৈরি এবং আপডেট করতে দেয়। এই পৃষ্ঠার উদাহরণগুলি ব্যাখ্যা করে যে আপনি কীভাবে শীট API-এর সাথে কিছু সাধারণ পিভট টেবিল অপারেশনগুলি অর্জন করতে পারেন৷

এই উদাহরণগুলি ভাষা নিরপেক্ষ হওয়ার জন্য HTTP অনুরোধের আকারে উপস্থাপন করা হয়েছে। Google API ক্লায়েন্ট লাইব্রেরি ব্যবহার করে কিভাবে বিভিন্ন ভাষায় একটি ব্যাচ আপডেট বাস্তবায়ন করতে হয় তা জানতে, স্প্রেডশীট আপডেট করুন দেখুন।

এই উদাহরণগুলিতে, স্থানধারক SPREADSHEET_ID এবং SHEET_ID নির্দেশ করে যে আপনি সেই IDগুলি কোথায় দেবেন৷ আপনি স্প্রেডশীট ইউআরএলে স্প্রেডশীট আইডি খুঁজে পেতে পারেন। আপনি spreadsheets.get পদ্ধতি ব্যবহার করে শীট আইডি পেতে পারেন। A1 স্বরলিপি ব্যবহার করে রেঞ্জগুলি নির্দিষ্ট করা হয়েছে। একটি উদাহরণ পরিসীমা হল Sheet1!A1:D5।

উপরন্তু, স্থানধারক SOURCE_SHEET_ID উৎস ডেটা সহ আপনার শীট নির্দেশ করে৷ এই উদাহরণগুলিতে, এটি পিভট টেবিল উত্স ডেটার অধীনে তালিকাভুক্ত টেবিল।

পিভট টেবিল সোর্স ডেটা

এই উদাহরণগুলির জন্য, অনুমান করুন যে স্প্রেডশীটটি ব্যবহার করা হচ্ছে তার প্রথম শীটে ("শিট1") নিম্নলিখিত উত্স "বিক্রয়" ডেটা রয়েছে৷ প্রথম সারির স্ট্রিংগুলি পৃথক কলামগুলির জন্য লেবেল। আপনার স্প্রেডশীটে অন্যান্য শীট থেকে কীভাবে পড়তে হয় তার উদাহরণ দেখতে, A1 স্বরলিপি দেখুন।

	ক	খ	গ	ডি	ই	চ	জি
1	আইটেম বিভাগ	মডেল নম্বর	খরচ	পরিমাণ	অঞ্চল	বিক্রয়কর্মী	জাহাজের তারিখ
2	চাকা	W-24	$20.50	4	পশ্চিম	বেথ	3/1/2016
3	দরজা	D-01X	$15.00	2	দক্ষিণ	আমীর	3/15/2016
4	ইঞ্জিন	ENG-0134	$100.00	1	উত্তর	কারমেন	3/20/2016
5	ফ্রেম	FR-0B1	$34.00	8	পূর্ব	হান্না	3/12/2016
6	প্যানেল	পি-034	$6.00	4	উত্তর	ডেভিন	4/2/2016
7	প্যানেল	পি-052	$11.50	7	পূর্ব	এরিক	5/16/2016
8	চাকা	W-24	$20.50	11	দক্ষিণ	শেলডন	4/30/2016
9	ইঞ্জিন	ENG-0161	$330.00	2	উত্তর	জেসি	7/2/2016
10	দরজা	D-01Y	$২৯.০০	6	পশ্চিম	আরমান্দো	3/13/2016
11	ফ্রেম	FR-0B1	$34.00	9	দক্ষিণ	ইউলিয়ানা	2/27/2016
12	প্যানেল	P-102	$3.00	15	পশ্চিম	কারমেন	4/18/2016
13	প্যানেল	P-105	$8.25	13	পশ্চিম	জেসি	6/20/2016
14	ইঞ্জিন	ENG-0211	$283.00	1	উত্তর	আমীর	6/21/2016
15	দরজা	D-01X	$15.00	2	পশ্চিম	আরমান্দো	7/3/2016
16	ফ্রেম	FR-0B1	$34.00	6	দক্ষিণ	কারমেন	7/15/2016
17	চাকা	W-25	$20.00	8	দক্ষিণ	হান্না	5/2/2016
18	চাকা	W-11	$২৯.০০	13	পূর্ব	এরিক	5/19/2016
19	দরজা	D-05	$17.70	7	পশ্চিম	বেথ	6/28/2016
20	ফ্রেম	FR-0B1	$34.00	8	উত্তর	শেলডন	3/30/2016

একটি পিভট টেবিল যোগ করুন

নিম্নলিখিত spreadsheets.batchUpdate কোড নমুনাটি দেখায় যে কীভাবে উৎস ডেটা থেকে একটি পিভট টেবিল তৈরি করতে UpdateCellsRequest ব্যবহার করতে হয়, এটি SHEET_ID দ্বারা নির্দিষ্ট শীটের A50 কক্ষে নোঙ্গর করে।

অনুরোধটি নিম্নলিখিত বৈশিষ্ট্যগুলির সাথে পিভট টেবিল কনফিগার করে:

• একটি মান গোষ্ঠী ( পরিমাণ ) যা বিক্রয়ের সংখ্যা নির্দেশ করে৷ যেহেতু শুধুমাত্র একটি মান গ্রুপ আছে, 2টি সম্ভাব্য 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 শুধুমাত্র গুরুত্বপূর্ণ যদি 2 বা তার বেশি মান গোষ্ঠী থাকে।

অনুরোধ প্রোটোকল নীচে দেখানো হয়েছে.

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

অনুরোধটি এইরকম একটি পিভট টেবিল তৈরি করে:

একটি পিভট টেবিল মুছুন

SHEET_ID দ্বারা নির্দিষ্ট পত্রকের A50 কক্ষে নোঙর করা একটি পিভট টেবিল (যদি উপস্থিত থাকে) মুছে ফেলার জন্য UpdateCellsRequest কিভাবে ব্যবহার করতে হয় তা নিম্নলিখিত spreadsheets.batchUpdate কোড নমুনাটি দেখায়।

একটি 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 ব্যবহার করতে হয়।

CellData রিসোর্সে pivotTable ফিল্ডের সাবসেটগুলি fields প্যারামিটারের সাথে পৃথকভাবে পরিবর্তন করা যাবে না। সম্পাদনা করতে, সমগ্র pivotTable ক্ষেত্রটি অবশ্যই সরবরাহ করতে হবে। মূলত, একটি পিভট টেবিল সম্পাদনা করার জন্য এটিকে একটি নতুন দিয়ে প্রতিস্থাপন করতে হবে।

অনুরোধটি মূল পিভট টেবিলে নিম্নলিখিত পরিবর্তনগুলি করে:

• মূল পিভট টেবিল ( মডেল নম্বর ) থেকে দ্বিতীয় সারি গ্রুপটি সরিয়ে দেয়।
• একটি কলাম গ্রুপ যোগ করে ( বিক্রয়কর্মী ) প্যানেল বিক্রির মোট সংখ্যা অনুসারে কলামগুলি সাজানো ক্রমানুসারে। "কারমেন" (15 প্যানেল বিক্রয়) "Jessie" (13 প্যানেল বিক্রয়) এর বাম দিকে প্রদর্শিত হয়।
• "পশ্চিম" ব্যতীত প্রতিটি অঞ্চলের জন্য কলামকে সঙ্কুচিত করে, সেই অঞ্চলের জন্য বিক্রয়কর্মী গোষ্ঠীকে লুকিয়ে রাখে৷ অঞ্চল কলাম গোষ্ঠীতে সেই কলামের জন্য valueMetadata true collapsed সেট করে এটি করা হয়।

অনুরোধ প্রোটোকল নীচে দেখানো হয়েছে.

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