Google Apps Script का इस्तेमाल करके, Sheets की सुविधाओं को बढ़ाया जा सकता है. Sheets में कस्टम मेन्यू, डायलॉग, और साइडबार जोड़ें. Sheets के लिए कस्टम फ़ंक्शन लिखें और इसे Google Calendar, Google Drive, और Gmail जैसी अन्य Google सेवाओं के साथ इंटिग्रेट करें.
Sheets के लिए डिज़ाइन की गई ज़्यादातर स्क्रिप्ट, स्प्रेडशीट में मौजूद सेल, लाइनों, और कॉलम के साथ इंटरैक्ट करने के लिए, ऐरे में बदलाव करती हैं. अगर आपको JavaScript में ऐरे के बारे में जानकारी नहीं है, तो Codecademy, ऐरे के लिए बेहतरीन ट्रेनिंग मॉड्यूल उपलब्ध कराता है. इस कोर्स को Google ने डेवलप नहीं किया है और न ही यह Google से जुड़ा है.
Sheets के साथ Apps Script का इस्तेमाल करने के बारे में तुरंत जानकारी पाने के लिए, मैक्रो, मेन्यू, और कस्टम फ़ंक्शन के लिए, पांच मिनट की क्विकस्टार्ट गाइड देखें.
अपनी प्रोफ़ाइल बनाना शुरू करें
Apps Script में, प्रोग्राम के हिसाब से Sheets बनाने, पढ़ने, और उनमें बदलाव करने के लिए खास एपीआई शामिल होते हैं. Apps Script, Sheets के साथ दो तरीकों से इंटरैक्ट करती है: कोई भी स्क्रिप्ट, स्प्रेडशीट बना सकती है या उसमें बदलाव कर सकती है. हालांकि, इसके लिए ज़रूरी है कि स्क्रिप्ट के उपयोगकर्ता के पास इसके लिए ज़रूरी अनुमतियां हों. साथ ही, किसी स्क्रिप्ट को स्प्रेडशीट से बाउंड भी किया जा सकता है. बाउंड स्क्रिप्ट में, यूज़र इंटरफ़ेस में बदलाव करने या स्प्रेडशीट खोले जाने पर जवाब देने की खास सुविधाएं होती हैं. बाउंड स्क्रिप्ट बनाने के लिए, Sheets में जाकर एक्सटेंशन > Apps Script चुनें.
स्प्रेडशीट सेवा, Sheets को ग्रिड के तौर पर देखती है. यह दो डाइमेंशन वाले ऐरे के साथ काम करती है. स्प्रेडशीट से डेटा वापस पाने के लिए, उस स्प्रेडशीट का ऐक्सेस पाएं जिसमें डेटा सेव किया गया है. इसके बाद, उस रेंज का ऐक्सेस पाएं जिसमें डेटा मौजूद है. इसके बाद, सेल की वैल्यू पाएं. Apps Script, स्प्रेडशीट में मौजूद स्ट्रक्चर्ड डेटा को पढ़कर, डेटा ऐक्सेस करने में मदद करती है. साथ ही, उनके लिए JavaScript ऑब्जेक्ट बनाती है.
डेटा पढ़ने की अनुमति दें
मान लीजिए कि आपके पास प्रॉडक्ट के नामों और प्रॉडक्ट नंबर की एक सूची है, जिसे आपने स्प्रेडशीट में सेव किया है. इसे यहां दी गई इमेज में दिखाया गया है.
यहां दिए गए उदाहरण में, प्रॉडक्ट के नाम और प्रॉडक्ट नंबर को वापस पाने और लॉग करने का तरीका बताया गया है.
function logProductInfo() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}
लॉग देखें
लॉग किया गया डेटा देखने के लिए, स्क्रिप्ट एडिटर में सबसे ऊपर, एक्ज़ीक्यूशन लॉग पर क्लिक करें.
डेटा सेव करने की अनुमति दें
डेटा सेव करने के लिए, जैसे कि स्प्रेडशीट में नया प्रॉडक्ट का नाम और नंबर जोड़ने के लिए, स्क्रिप्ट के आखिर में यह कोड जोड़ें.
function addProduct() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
ऊपर दिए गए कोड से, स्प्रेडशीट में सबसे नीचे एक नई लाइन जुड़ जाती है. इसमें दी गई वैल्यू अपने-आप भर जाती हैं. इस फ़ंक्शन को चलाने पर, स्प्रेडशीट में एक नई लाइन जुड़ जाती है.
कस्टम मेन्यू और यूज़र इंटरफ़ेस
कस्टम मेन्यू, डायलॉग, और साइडबार जोड़कर, Sheets को पसंद के मुताबिक बनाएं. मेन्यू बनाने की बुनियादी जानकारी पाने के लिए, मेन्यू से जुड़ी गाइड देखें. डायलॉग के कॉन्टेंट को पसंद के मुताबिक बनाने के बारे में जानने के लिए, एचटीएमएल सेवा से जुड़ी गाइड देखें.
किसी स्प्रैडशीट में मौजूद इमेज या ड्राइंग में स्क्रिप्ट फ़ंक्शन अटैच करें. जब कोई उपयोगकर्ता इमेज या ड्राइंग पर क्लिक करता है, तब फ़ंक्शन काम करता है. ज़्यादा जानने के लिए, Sheets में इमेज और ड्रॉइंग लेख पढ़ें.
अगर आपको अपने कस्टम इंटरफ़ेस को ऐड-ऑन के तौर पर पब्लिश करना है, तो स्टाइल गाइड का पालन करें. इससे Sheets एडिटर के स्टाइल और लेआउट में एकरूपता बनी रहेगी.
Google Forms से कनेक्ट करना
Forms और Spreadsheet सेवाओं के ज़रिए, Google Forms को Sheets से कनेक्ट करें. इस सुविधा की मदद से, स्प्रेडशीट में मौजूद डेटा के आधार पर Google फ़ॉर्म अपने-आप बन जाता है.
Apps Script की मदद से, ट्रिगर का इस्तेमाल भी किया जा सकता है. जैसे, onFormSubmit. इनकी मदद से, उपयोगकर्ता के फ़ॉर्म का जवाब देने के बाद कोई खास कार्रवाई की जा सकती है. Sheets को Forms से कनेक्ट करने के बारे में ज़्यादा जानने के लिए, Forms के जवाब मैनेज करना 5 मिनट का क्विकस्टार्ट आज़माएं.
डेटा को फ़ॉर्मैट करना
Range क्लास में, सेल या सेल की रेंज के फ़ॉर्मैट को ऐक्सेस करने और उसमें बदलाव करने के लिए, setBackground जैसे तरीके होते हैं. यहां दिए गए उदाहरण में, किसी रेंज की फ़ॉन्ट स्टाइल सेट की गई है:
function formatMySpreadsheet() {
// Set the font style of the cells in the range of B2:C2 to be italic.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const cell = sheet.getRange('B2:C2');
cell.setFontStyle('italic');
}
डेटा सत्यापन
Sheets में डेटा की पुष्टि करने के मौजूदा नियमों को ऐक्सेस करना या नए नियम बनाना. उदाहरण के लिए, यहां दिए गए सैंपल में बताया गया है कि डेटा की पुष्टि करने का ऐसा नियम कैसे सेट किया जाए जिससे किसी सेल में सिर्फ़ 1 से 100 के बीच की संख्याएं डाली जा सकें.
function validateMySpreadsheet() {
// Set a rule for the cell B4 to be a number between 1 and 100.
const cell = SpreadsheetApp.getActive().getRange('B4');
const rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(1, 100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);
}
डेटा की पुष्टि करने के नियमों के साथ काम करने के बारे में ज़्यादा जानने के लिए, SpreadsheetApp.newDataValidation, DataValidationBuilder, और Range.setDataValidation देखें
चार्ट
किसी स्प्रेडशीट में ऐसे चार्ट एम्बेड करें जो किसी खास रेंज में मौजूद डेटा को दिखाते हों. नीचे दिए गए उदाहरण में, एम्बेड किया गया बार चार्ट जनरेट किया गया है. इसमें यह माना गया है कि आपके पास सेल A1:B15 में चार्ट बनाने लायक डेटा है:
function newChart() {
// Generate a chart representing the data in the range of A1:B15.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheet.getRange('A1:B15'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(chart);
}
अपनी स्प्रेडशीट में चार्ट एम्बेड करने के बारे में ज़्यादा जानने के लिए, EmbeddedChart और चार्ट बनाने वाले खास टूल देखें. जैसे, EmbeddedPieChartBuilder.
Google Sheets में कस्टम फ़ंक्शन
कस्टम फ़ंक्शन, स्प्रेडशीट में पहले से मौजूद फ़ंक्शन, जैसे कि =SUM(A1:A5) की तरह ही होता है. हालांकि, Apps Script की मदद से फ़ंक्शन के व्यवहार को तय किया जाता है. उदाहरण के लिए, in2mm() नाम का एक कस्टम फ़ंक्शन बनाया जा सकता है. यह फ़ंक्शन, वैल्यू को इंच से मिलीमीटर में बदलता है. इसके बाद, अपनी स्प्रेडशीट में फ़ॉर्मूला इस्तेमाल करने के लिए, किसी सेल में =in2mm(A1) या =in2mm(10) टाइप करें.
कस्टम फ़ंक्शन के बारे में ज़्यादा जानने के लिए, मेन्यू और कस्टम फ़ंक्शन के बारे में पांच मिनट में जानकारी देने वाला क्विकस्टार्ट लेख पढ़ें. इसके अलावा, कस्टम फ़ंक्शन के बारे में ज़्यादा जानकारी देने वाली गाइड भी देखें.
मैक्रो
मैक्रो, Sheets के यूज़र इंटरफ़ेस (यूआई) से Apps Script कोड को लागू करने का एक और तरीका है. कस्टम फ़ंक्शन के उलट, इन्हें कीबोर्ड शॉर्टकट या Sheets मेन्यू से चालू किया जाता है. ज़्यादा जानकारी के लिए, Sheets मैक्रो देखें.
Google Sheets के लिए ऐड-ऑन
ऐड-ऑन, खास तौर पर पैकेज किए गए Apps Script प्रोजेक्ट होते हैं. ये Sheets में चलते हैं और इन्हें Sheets के ऐड-ऑन स्टोर से इंस्टॉल किया जा सकता है. अगर आपने Sheets के लिए कोई स्क्रिप्ट डेवलप की है और उसे शेयर करना है, तो Apps Script की मदद से अपनी स्क्रिप्ट को ऐड-ऑन के तौर पर पब्लिश किया जा सकता है. इससे अन्य उपयोगकर्ता उसे इंस्टॉल कर पाएंगे.
परफ़ॉर्मेंस और स्केलिंग
डेटासेट बढ़ने पर, आपको परफ़ॉर्मेंस से जुड़ी समस्याएं आ सकती हैं. अपनी स्प्रेडशीट और स्क्रिप्ट को ऑप्टिमाइज़ करने के लिए:
- सबसे सही तरीके अपनाएं: सर्विस कॉल को कम करने और बैच ऑपरेशन का इस्तेमाल करने के बारे में सलाह पाने के लिए, सबसे सही तरीकों की गाइड पढ़ें.
- फ़ॉर्मूलों को ऑप्टिमाइज़ करें: अगर आपकी स्प्रेडशीट में जटिल फ़ॉर्मूलों (जैसे कि
VLOOKUP,ARRAYFORMULAयाIMPORTRANGE) की वजह से डेटा लोड होने में समय लग रहा है, तो Apps Script का इस्तेमाल करें. इससे ये कैलकुलेशन मेमोरी में की जा सकती हैं और नतीजों को बैच में वापस लिखा जा सकता है. - डेटाबेस के अन्य विकल्पों पर विचार करें: अगर डेटासेट बहुत बड़े हैं (लगभग 1 करोड़ सेल) या डेटा को बार-बार अपडेट किया जाता है (जैसे, कई कनेक्ट किए गए फ़ॉर्म), तो JDBC का इस्तेमाल करके Google Cloud SQL या BigQuery का इस्तेमाल करें.
ट्रिगर
Sheets की किसी फ़ाइल से बाइंड की गई स्क्रिप्ट, सिंपल ट्रिगर का इस्तेमाल कर सकती हैं. जैसे, onOpen() और onEdit() फ़ंक्शन. इससे, स्प्रेडशीट में बदलाव करने का ऐक्सेस रखने वाला कोई उपयोगकर्ता जब स्प्रेडशीट खोलता है या उसमें बदलाव करता है, तो स्क्रिप्ट अपने-आप जवाब दे सकती है.
सिंपल ट्रिगर की तरह, इंस्टॉल किए जा सकने वाले ट्रिगर की मदद से, Sheets में किसी फ़ंक्शन को अपने-आप चालू किया जा सकता है. ऐसा तब होता है, जब कोई खास इवेंट होता है.
हालांकि, इंस्टॉल किए जा सकने वाले ट्रिगर, सामान्य ट्रिगर के मुकाबले ज़्यादा फ़्लेक्सिबिलिटी देते हैं. साथ ही, ये इन इवेंट के साथ काम करते हैं: खोलना, बदलाव करना, बदलना, फ़ॉर्म सबमिट करना, और समय के हिसाब से ट्रिगर होना (घड़ी).