5টি গুগল শীট স্ক্রিপ্ট ফাংশন আপনার জানা দরকার

Google পত্রক হল একটি শক্তিশালী ক্লাউড-ভিত্তিক স্প্রেডশীট টুল যা আপনাকে Microsoft Excel এ যা করতে পারে তার প্রায় সবকিছুই করতে দেয়। কিন্তু Google পত্রকের আসল শক্তি হল Google স্ক্রিপ্টিং বৈশিষ্ট্য যা এটির সাথে আসে৷

Google Apps স্ক্রিপ্টিং হল একটি ব্যাকগ্রাউন্ড স্ক্রিপ্টিং টুল যা শুধুমাত্র Google Sheets নয়, Google Docs, Gmail, Google Analytics এবং প্রায় প্রতিটি Google ক্লাউড পরিষেবাতেও কাজ করে। এটি আপনাকে সেই পৃথক অ্যাপগুলিকে স্বয়ংক্রিয় করতে দেয় এবং সেই অ্যাপগুলির প্রতিটিকে একে অপরের সাথে একত্রিত করতে দেয়৷

এই নিবন্ধে আপনি শিখবেন কিভাবে Google Apps স্ক্রিপ্টিং দিয়ে শুরু করতে হয়, সেল ডেটা পড়তে এবং লিখতে Google পত্রকগুলিতে একটি মৌলিক স্ক্রিপ্ট তৈরি করা এবং সবচেয়ে কার্যকরী উন্নত Google পত্রক স্ক্রিপ্ট ফাংশনগুলি৷

কিভাবে একটি Google Apps স্ক্রিপ্ট তৈরি করবেন

আপনি এখনই Google পত্রকের ভিতর থেকে আপনার প্রথম Google Apps স্ক্রিপ্ট তৈরি করা শুরু করতে পারেন৷

এটি করতে, সরঞ্জাম নির্বাচন করুন মেনু থেকে, তারপর স্ক্রিপ্ট এডিটর .

এটি স্ক্রিপ্ট এডিটর উইন্ডো খোলে এবং myfunction() নামক একটি ফাংশনে ডিফল্ট হয় . এখানে আপনি আপনার Google স্ক্রিপ্ট তৈরি এবং পরীক্ষা করতে পারেন৷

এটিকে একটি শট দিতে, একটি Google পত্রক স্ক্রিপ্ট ফাংশন তৈরি করার চেষ্টা করুন যা একটি কক্ষ থেকে ডেটা পড়বে, এটিতে একটি গণনা করবে এবং ডেটার পরিমাণ অন্য কক্ষে আউটপুট করবে৷

একটি সেল থেকে ডেটা পাওয়ার ফাংশন হল getRange() এবং getValue() ফাংশন আপনি সারি এবং কলাম দ্বারা ঘর সনাক্ত করতে পারেন. সুতরাং আপনার যদি সারি 2 এবং কলাম 1 (A কলাম) তে একটি মান থাকে তবে আপনার স্ক্রিপ্টের প্রথম অংশটি এইরকম দেখাবে:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

এটি সেই ঘর থেকে ডেটা-এ মান সঞ্চয় করে পরিবর্তনশীল আপনি ডেটার উপর একটি গণনা সঞ্চালন করতে পারেন, এবং তারপর সেই ডেটাটি অন্য ঘরে লিখতে পারেন। সুতরাং এই ফাংশনের শেষ অংশটি হবে:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

আপনার ফাংশন লেখা শেষ হলে, সংরক্ষণ করতে ডিস্ক আইকনটি নির্বাচন করুন।

প্রথমবার যখন আপনি এইভাবে একটি নতুন Google পত্রক স্ক্রিপ্ট ফাংশন চালান (রান আইকন নির্বাচন করে), আপনাকে আপনার Google অ্যাকাউন্টে স্ক্রিপ্ট চালানোর জন্য অনুমোদন প্রদান করতে হবে।

অনুমতিগুলি চালিয়ে যাওয়ার অনুমতি দিন। একবার আপনার স্ক্রিপ্ট চলে গেলে, আপনি দেখতে পাবেন যে স্ক্রিপ্টটি লক্ষ্য কক্ষে গণনার ফলাফল লিখেছে।

এখন যেহেতু আপনি জানেন কিভাবে একটি মৌলিক Google Apps স্ক্রিপ্ট ফাংশন লিখতে হয়, আসুন আরও কিছু উন্নত ফাংশন দেখে নেওয়া যাক৷

অ্যারে লোড করতে getValues ​​ব্যবহার করুন

আপনি অ্যারে ব্যবহার করে স্ক্রিপ্টিং সহ আপনার স্প্রেডশীটে ডেটার উপর গণনা করার ধারণাটি একটি নতুন স্তরে নিয়ে যেতে পারেন। যদি আপনি getValues ​​ব্যবহার করে Google Apps স্ক্রিপ্টে একটি ভেরিয়েবল লোড করেন, তাহলে ভেরিয়েবলটি একটি অ্যারে হবে যা শীট থেকে একাধিক মান লোড করতে পারে৷

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

ডেটা ভেরিয়েবল হল একটি বহুমাত্রিক অ্যারে যা শীট থেকে সমস্ত ডেটা ধারণ করে। ডেটাতে একটি গণনা সম্পাদন করতে, আপনি একটি এর জন্য ব্যবহার করেন৷ লুপ. লুপের কাউন্টার প্রতিটি সারির মাধ্যমে কাজ করবে, এবং কলামটি স্থির থাকবে, যে কলামের উপর ভিত্তি করে আপনি ডেটা টানতে চান৷

আমাদের উদাহরণ স্প্রেডশীটে, আপনি নিম্নরূপ ডেটার তিনটি সারিতে গণনা করতে পারেন৷

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

এই স্ক্রিপ্টটি সংরক্ষণ করুন এবং চালান ঠিক যেমন আপনি উপরে করেছেন। আপনি দেখতে পাবেন যে সমস্ত ফলাফল আপনার স্প্রেডশীটের কলাম 2 এ পূর্ণ হয়েছে।

আপনি লক্ষ্য করবেন যে একটি অ্যারে ভেরিয়েবলে একটি সেল এবং সারি উল্লেখ করা একটি getRange ফাংশনের চেয়ে আলাদা।

ডেটা[i][0] অ্যারের মাত্রা বোঝায় যেখানে প্রথম মাত্রা সারি এবং দ্বিতীয়টি কলাম। এই দুটিই শূন্য থেকে শুরু হয়৷

গেট রেঞ্জ(i+1, 2) দ্বিতীয় সারি বোঝায় যখন i=1 (যেহেতু সারি 1 হেডার), এবং 2 হল দ্বিতীয় কলাম যেখানে ফলাফলগুলি সংরক্ষণ করা হয়।

ফলাফল লিখতে appendRow ব্যবহার করুন

যদি আপনার একটি স্প্রেডশীট থাকে যেখানে আপনি একটি নতুন কলামের পরিবর্তে একটি নতুন সারিতে ডেটা লিখতে চান?

appendRow দিয়ে এটি করা সহজ ফাংশন এই ফাংশনটি শীটে বিদ্যমান কোনো ডেটাকে বিরক্ত করবে না। এটি বিদ্যমান শীটে একটি নতুন সারি যুক্ত করবে৷

উদাহরণ হিসাবে, একটি ফাংশন তৈরি করুন যা 1 থেকে 10 পর্যন্ত গণনা করবে এবং একটি কাউন্টারে 2 এর গুণিতক সহ একটি কাউন্টার দেখাবে কলাম।

এই ফাংশনটি দেখতে এরকম হবে:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

আপনি যখন এই ফাংশনটি চালান তখন ফলাফলগুলি এখানে রয়েছে৷

URLFetchApp দিয়ে RSS ফিড প্রক্রিয়া করুন

আপনি আগের Google পত্রক স্ক্রিপ্ট ফাংশন এবং URLFetchApp একত্রিত করতে পারেন৷ যেকোনো ওয়েবসাইট থেকে RSS ফিড টানতে, এবং সেই ওয়েবসাইটে সম্প্রতি প্রকাশিত প্রতিটি নিবন্ধের জন্য একটি স্প্রেডশীটে একটি সারি লিখুন।

এটি মূলত আপনার নিজস্ব RSS ফিড রিডার স্প্রেডশীট তৈরি করার একটি DIY পদ্ধতি!

এটি করার জন্য স্ক্রিপ্টটিও খুব জটিল নয়।

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

আপনি দেখতে পাচ্ছেন, Xml.parse প্রতিটি আইটেমকে আরএসএস ফিড থেকে বের করে এবং প্রতিটি লাইনকে শিরোনাম, লিঙ্ক, তারিখ এবং বিবরণে আলাদা করে।

appendRow ব্যবহার করে ফাংশন, আপনি এই আইটেমগুলিকে RSS ফিডে প্রতিটি আইটেমের জন্য উপযুক্ত কলামে রাখতে পারেন।

আপনার শীটের আউটপুটটি এরকম কিছু দেখাবে:

স্ক্রিপ্টে আরএসএস ফিড ইউআরএল এম্বেড করার পরিবর্তে, ইউআরএল সহ আপনার শীটে একটি ক্ষেত্র থাকতে পারে এবং তারপরে একাধিক শীট থাকতে পারে – প্রতিটি ওয়েবসাইটের জন্য একটি যা আপনি নিরীক্ষণ করতে চান।

স্ট্রিংগুলি সংযুক্ত করুন এবং একটি ক্যারেজ রিটার্ন যোগ করুন

আপনি কিছু টেক্সট ম্যানিপুলেশন ফাংশন যোগ করে আরএসএস স্প্রেডশীটকে আরও এক ধাপ এগিয়ে নিতে পারেন, এবং তারপর সাইটের আরএসএস ফিডে সমস্ত নতুন পোস্টের সারাংশ সহ নিজেকে একটি ইমেল পাঠাতে ইমেল ফাংশন ব্যবহার করতে পারেন।

এটি করার জন্য, পূর্ববর্তী বিভাগে আপনি যে স্ক্রিপ্টটি তৈরি করেছেন তার অধীনে, আপনি কিছু স্ক্রিপ্টিং যুক্ত করতে চাইবেন যা স্প্রেডশীটের সমস্ত তথ্য বের করবে।

আপনি স্প্রেডশীটে RSS ডেটা লিখতে যে একই "আইটেম" অ্যারে থেকে সমস্ত তথ্য একসাথে পার্স করে বিষয় লাইন এবং ইমেল পাঠ্যের বডি তৈরি করতে চাইবেন৷

এটি করার জন্য, লুপের জন্য "আইটেম" এর আগে নিম্নলিখিত লাইনগুলি স্থাপন করে বিষয় এবং বার্তাটি শুরু করুন৷

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

তারপর, লুপের জন্য "আইটেম" এর শেষে (অ্যাপেন্ডরো ফাংশনের ঠিক পরে), নিম্নলিখিত লাইনটি যোগ করুন।

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

“+” চিহ্নটি চারটি আইটেমকে একত্রে সংযুক্ত করবে এবং তারপরে প্রতিটি লাইনের পরে ক্যারেজ রিটার্নের জন্য “\n” হবে। প্রতিটি শিরোনাম ডেটা ব্লকের শেষে, আপনি একটি সুন্দর ফর্ম্যাট করা ইমেল বডির জন্য দুটি ক্যারেজ রিটার্ন চাইবেন৷

একবার সমস্ত সারি প্রক্রিয়া করা হলে, "বডি" ভেরিয়েবলটি সম্পূর্ণ ইমেল বার্তা স্ট্রিং ধরে রাখে। এখন আপনি ইমেল পাঠাতে প্রস্তুত!

Google Apps স্ক্রিপ্টে কিভাবে ইমেল পাঠাবেন

আপনার Google স্ক্রিপ্টের পরবর্তী বিভাগটি ইমেলের মাধ্যমে "বিষয়" এবং "বডি" পাঠাতে হবে। Google স্ক্রিপ্টে এটি করা খুবই সহজ৷

var emailAddress = myemail@gmail.com;
MailApp.sendEmail(emailAddress, subject, message);

MailApp হল Google Apps স্ক্রিপ্টগুলির মধ্যে একটি খুব সুবিধাজনক শ্রেণী যা আপনাকে ইমেল পাঠাতে বা গ্রহণ করতে আপনার Google অ্যাকাউন্টের ইমেল পরিষেবাতে অ্যাক্সেস দেয়৷ এর জন্য ধন্যবাদ, sendEmail ফাংশন সহ একক লাইন আপনাকে শুধুমাত্র ইমেল ঠিকানা, বিষয় লাইন এবং বডি টেক্সট সহ যেকোনো ইমেল পাঠাতে দেয়।

এর ফলে ইমেল দেখতে কেমন হবে।

একটি ওয়েবসাইটের RSS ফিড বের করার ক্ষমতা, এটিকে একটি Google শীটে সঞ্চয় করার এবং URL লিঙ্কগুলি অন্তর্ভুক্ত করে নিজের কাছে পাঠানোর ক্ষমতা একত্রিত করা, যেকোনো ওয়েবসাইটের জন্য সাম্প্রতিক বিষয়বস্তু অনুসরণ করা খুব সুবিধাজনক করে তোলে৷

Google Apps স্ক্রিপ্টে ক্রিয়াগুলি স্বয়ংক্রিয় করতে এবং একাধিক ক্লাউড পরিষেবাগুলিকে একীভূত করার জন্য উপলব্ধ শক্তির এটি শুধুমাত্র একটি উদাহরণ৷