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

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

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

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

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

পিভট টেবিলের উৎস ডেটা

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

ক খ গ দ ই চ গ
১ আইটেম বিভাগ মডেল নম্বর খরচ পরিমাণ অঞ্চল বিক্রয়কর্মী পাঠানোর তারিখ
২ চাকা W-24 সম্পর্কে $২০.৫০ ৪ পশ্চিম বেথ ৩/১/২০১৬
৩ দরজা ডি-০১এক্স $১৫.০০ ২ দক্ষিণ আমির ৩/১৫/২০১৬
৪ ইঞ্জিন ENG-0134 সম্পর্কে $১০০.০০ ১ উত্তর কারমেন ৩/২০/২০১৬
৫ ফ্রেম এফআর-০বি১ $৩৪.০০ ৮ পূর্ব হান্না ৩/১২/২০১৬
৬ প্যানেল পি-০৩৪ $৬.০০ ৪ উত্তর ডেভিন ৪/২/২০১৬
৭ প্যানেল পি-০৫২ $১১.৫০ ৭ পূর্ব এরিক ৫/১৬/২০১৬
৮ চাকা W-24 সম্পর্কে $২০.৫০ ১১ দক্ষিণ শেলডন ৪/৩০/২০১৬
৯ ইঞ্জিন ENG-0161 সম্পর্কে $৩৩০.০০ ২ উত্তর জেসি ৭/২/২০১৬
১০ দরজা ডি-০১ওয়াই $২৯.০০ ৬ পশ্চিম আরমান্ডো ৩/১৩/২০১৬
১১ ফ্রেম এফআর-০বি১ $৩৪.০০ ৯ দক্ষিণ ইউলিয়ানা ২/২৭/২০১৬
১২ প্যানেল পি-১০২ $৩.০০ ১৫ পশ্চিম কারমেন ৪/১৮/২০১৬
১৩ প্যানেল পি-১০৫ $৮.২৫ ১৩ পশ্চিম জেসি ২০/৬/২০১৬
১৪ ইঞ্জিন ENG-0211 সম্পর্কে $২৮৩.০০ ১ উত্তর আমির ২১/৬/২০১৬
১৫ দরজা ডি-০১এক্স $১৫.০০ ২ পশ্চিম আরমান্ডো ৭/৩/২০১৬
১৬ ফ্রেম এফআর-০বি১ $৩৪.০০ ৬ দক্ষিণ কারমেন ৭/১৫/২০১৬
১৭ চাকা W-25 সম্পর্কে $২০.০০ ৮ দক্ষিণ হান্না ৫/২/২০১৬
১৮ চাকা ডাব্লু-১১ $২৯.০০ ১৩ পূর্ব এরিক ৫/১৯/২০১৬
১৯ দরজা ডি-০৫ $১৭.৭০ ৭ পশ্চিম বেথ ৬/২৮/২০১৬
২০ ফ্রেম এফআর-০বি১ $৩৪.০০ ৮ উত্তর শেলডন ৩০/৩/২০১৬

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

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

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

  • একটি মান গ্রুপ ( Quantity ) যা বিক্রয় সংখ্যা নির্দেশ করে। যেহেতু শুধুমাত্র একটি মান গ্রুপ আছে, তাই 2টি সম্ভাব্য valueLayout সেটিংস সমতুল্য।
  • দুটি সারির গ্রুপ ( আইটেম ক্যাটাগরি এবং মডেল নম্বর )। প্রথমটি "পশ্চিম" অঞ্চল থেকে মোট পরিমাণের ঊর্ধ্বমুখী মানের সাথে সাজানো হয়। অতএব, "ইঞ্জিন" (পশ্চিম বিক্রয় ছাড়া) "দরজা" (১৫টি পশ্চিম বিক্রয় সহ) এর উপরে প্রদর্শিত হয়। মডেল নম্বর গ্রুপটি সমস্ত অঞ্চলে মোট বিক্রয়ের ঊর্ধ্বমুখী ক্রম অনুসারে সাজানো হয়, তাই "W-24" (১৫টি বিক্রয়) "W-25" (৮টি বিক্রয়) এর উপরে প্রদর্শিত হয়। এটি valueBucket ক্ষেত্রটিকে {} এ সেট করে করা হয়।
  • একটি কলামের গ্রুপ ( Region ) যা বেশিরভাগ বিক্রয়ের ঊর্ধ্বমুখী ক্রম অনুসারে সাজানো হয়। আবার, valueBucket {} তে সেট করা আছে। "North"-এ সর্বনিম্ন মোট বিক্রয় থাকে, এবং তাই এটি প্রথম Region কলাম হিসাবে প্রদর্শিত হয়।

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

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

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

পিভট মান যোগ করুন গ্রুপ রেসিপি ফলাফল

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

নিম্নলিখিত 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"
      }
    }
  ]
}

পিভট টেবিলের কলাম এবং সারি সম্পাদনা করুন

নিম্নলিখিত spreadsheets.batchUpdate কোড নমুনাটি দেখায় কিভাবে UpdateCellsRequest ব্যবহার করে Add a pivot table এ তৈরি করা pivot table সম্পাদনা করতে হয়।

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

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

  • মূল পিভট টেবিল ( মডেল নম্বর ) থেকে দ্বিতীয় সারির গ্রুপটি সরিয়ে দেয়।
  • একটি কলাম গ্রুপ ( বিক্রয়কারী ) যোগ করে। প্যানেল বিক্রয়ের মোট সংখ্যা অনুসারে কলামগুলি অবরোহী ক্রমে সাজানো হয়। "Jessie" (13 প্যানেল বিক্রয়) এর বাম দিকে "Carmen" (15 Panel বিক্রয়) প্রদর্শিত হয়।
  • "পশ্চিম" ব্যতীত প্রতিটি Region এর জন্য কলামটি সঙ্কুচিত করে, সেই অঞ্চলের জন্য বিক্রয়কর্মী গোষ্ঠীকে লুকিয়ে রাখে। Region কলাম গ্রুপের সেই কলামের জন্য 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 query প্যারামিটারটি নির্দিষ্ট করে যে শুধুমাত্র পিভট টেবিল ডেটা ফেরত দেওয়া উচিত (সেল মান ডেটার বিপরীতে)।

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

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

প্রতিক্রিয়াটিতে একটি Spreadsheet রিসোর্স থাকে, যার মধ্যে SheetProperties উপাদান সহ একটি Sheet অবজেক্ট থাকে। PivotTable সম্পর্কে তথ্য ধারণকারী GridData উপাদানগুলির একটি অ্যারেও রয়েছে। টেবিলটি যে কক্ষে নোঙ্গর করা হয়েছে (অর্থাৎ, টেবিলের উপরের-বাম কোণে) তার জন্য Pivot টেবিলের তথ্য শীটের 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
      }
    }
  ],
}