জানতে হবে ৫টি গুগল শিটস স্ক্রিপ্ট ফাংশন

গুগল শিটস একটি শক্তিশালী ক্লাউড-ভিত্তিক স্প্রেডশীট টুল যা আপনাকে মাইক্রোসফট এক্সেল-এর প্রায় সব কাজ করতে দেয়। কিন্তু গুগল শিটসের প্রকৃত শক্তি তার সাথে আসা গুগল স্ক্রিপ্টিং ফিচারে নিহিত।

গুগল অ্যাপস স্ক্রিপ্টিং একটি ব্যাকগ্রাউন্ড স্ক্রিপ্টিং টুল যা শুধু গুগল শিটসেই নয়, গুগল ডক্স, জিমেইল, গুগল অ্যানালিটিক্স এবং প্রায় সব গুগল ক্লাউড সার্ভিসে কাজ করে। এটি আপনাকে সেসব অ্যাপগুলো স্বয়ংক্রিয় করতে এবং সেগুলোকে পারস্পরিকভাবে ইন্টিগ্রেট করতে সাহায্য করে।

গুগল অ্যাপস স্ক্রিপ্ট কীভাবে তৈরি করবেন

আপনি এখনই গুগল শিটসের ভেতর থেকে আপনার প্রথম গুগল অ্যাপস স্ক্রিপ্ট তৈরি শুরু করতে পারেন।

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

এটি স্ক্রিপ্ট এডিটর উইন্ডো খুলে দেবে এবং ডিফল্টভাবে myfunction() নামে একটি ফাংশন তৈরি করবে। এখানেই আপনি আপনার গুগল স্ক্রিপ্ট তৈরি ও টেস্ট করতে পারেন।

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

সেল থেকে ডেটা পাওয়ার ফাংশন হল getRange() এবং getValue()। আপনি সেলকে সারি ও কলাম দিয়ে চিহ্নিত করতে পারেন। যেন আপনার ২ নম্বর সারি এবং ১ নম্বর কলাম (A কলাম) এ কোনো মান থাকে, তাহলে আপনার স্ক্রিপ্টের প্রথম অংশ হবে এরকম:

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

এটি সেলটির মান data ভ্যারিয়েবলে সংরক্ষণ করবে। আপনি ডেটার ওপর ক্যালকুলেশন করে ফলাফল অন্য একটি সেলে লিখতে পারেন। তাই ফাংশনের শেষ অংশ হবে:

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

ফাংশন লিখা শেষ হলে ডিস্ক আইকনে ক্লিক করে সেভ করুন।

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

অনুমতি দিয়ে চালিয়ে যান। স্ক্রিপ্ট রান হলে আপনি দেখবেন টার্গেট সেলে ক্যালকুলেশন ফলাফল লেখা হয়েছে।

এখন আপনি বেসিক গুগল অ্যাপস স্ক্রিপ্ট ফাংশন লিখতে জানেন, চলুন কিছু অ্যাডভান্সড ফাংশন দেখি।

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

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

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

data ভ্যারিয়েবলটি একটি মাল্টি-ডাইমেনশনাল অ্যারে যা শিটের সকল ডেটা ধারণ করে। ডেটার ওপর ক্যালকুলেশন করতে আপনাকে for লুপ ব্যবহার করতে হবে। for লুপের কাউন্টার প্রতিটি সারি দিয়ে কাজ করবে, আর কলাম ثابت থাকবে, যেন আপনি যে কলাম থেকে ডেটা খুঁজছেন।

আমাদের উদাহরণ স্প্রেডশীটে, তিনটি সারির ডেটার ওপর ক্যালকুলেশন নিচের মত করে করা যায়:

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

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

আপনি লক্ষ্য করবেন অ্যারে ভ্যারিয়েবলে সেল ও সারি রেফারেন্স করা getRange ফাংশন থেকে আলাদা।

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

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

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

যদি আপনার স্প্রেডশীটে নতুন সারিয়ে ডেটা লিখতে হয় নাকি নতুন কলামে?

এটি appendRow ফাংশন দিয়ে সহজেই করা যায়। এই ফাংশন শিটের বিদ্যমান ডেটাকে কোনো ক্ষতি পৌঁছায় না। এটি শুধু শিটের শেষে একটি নতুন সারি যোগ করে দেয়।

উদাহরণস্বরূপ, একটি ফাংশন বানান যা ১ থেকে ১০ পর্যন্ত গণনা করবে এবং Counter কলামে ২-এর গুণফল দেখাবে।

ফাংশনটি হবে নিচের মত:

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

এই ফাংশন রান করলে নিচের ফলাফল আসবে।

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

আপনি আগের গুগল শিটস স্ক্রিপ্ট ফাংশন এবং 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"); // RSS ফিডের প্রতিটি আইটেম পার্স করা 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 RSS ফিড থেকে প্রতিটি আইটেম বের করে টাইটেল, লিংক, তারিখ ও ডেসক্রিপশন আলাদা করে।

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

আপনার শিটে আউটপুট দেখতে এরকম হবে।

স্ক্রিপ্টে RSS ফিড URL হার্ডকোড না করে আপনি শিটে একটি ফিল্ডে URL রাখতে পারেন, এবং প্রতিটি ওয়েবসাইট মনিটর করার জন্য আলাদা শিট ব্যবহার করতে পারেন।

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

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

এজন্য, আগের সেকশনে তৈরি স্ক্রিপ্টের নিচে কিছু স্ক্রিপ্ট যোগ করুন যা স্প্রেডশীট থেকে সকল তথ্য বের করবে।

আপনি সাবজেক্ট লাইন এবং ইমেইল বডি তৈরি করবেন "items" অ্যারে থেকে সকল তথ্য পার্স করে।

"items" for লুপের আগে নিচের লাইনগুলো যোগ করুন সাবজেক্ট ও মেসেজ ইনিশিয়ালাইজ করতে:

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

তারপর "items" for লুপের শেষে (appendRow ফাংশনের ঠিক পরে) নিচের লাইন যোগ করুন।

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

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

সকল সারি প্রসেস হলে "body" ভ্যারিয়েবলে পুরো ইমেইল মেসেজ স্ট্রিং থাকবে। এখন ইমেইল পাঠাতে প্রস্তুত!。

গুগল অ্যাপস স্ক্রিপ্টে ইমেইল পাঠানোর পদ্ধতি

আপনার গুগল স্ক্রিপ্টের পরবর্তী অংশ হবে "subject" এবং "body" ইমেইল দিয়ে পাঠানো। গুগল স্ক্রিপ্টে এটি খুব সহজ।

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

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

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

একটি ওয়েবসাইটের RSS ফিড বের করা, গুগল শিটে সংরক্ষণ করা, এবং URL লিংকসহ নিজের কাছে ইমেইল পাঠানো — এই ক্ষমতা গুগল অ্যাপস স্ক্রিপ্টে একশন ওটোমেট এবং একাধিক ক্লাউড সার্ভিস ইন্টিগ্রেট করার উদাহরণ।