Google Sheets में कस्टम फ़ंक्शन

Google Sheets में AVERAGE, SUM, और VLOOKUP जैसे सैकड़ों पहले से मौजूद फ़ंक्शन उपलब्ध हैं. जब ये आपकी ज़रूरतों के लिए काफ़ी न हों, तब कस्टम फ़ंक्शन लिखने के लिए Google Apps Script का इस्तेमाल करें. उदाहरण के लिए, मील को मील में बदलें या इंटरनेट से लाइव कॉन्टेंट फ़ेच करें. इसके बाद, इन फ़ंक्शन का इस्तेमाल बिल्ट-इन फ़ंक्शन की तरह Google Sheets में करें.

प्रारंभ करना

कस्टम फ़ंक्शन, स्टैंडर्ड JavaScript का इस्तेमाल करके बनाए जाते हैं. अगर आपने JavaScript का इस्तेमाल पहले कभी नहीं किया है, तो Codecademy नए उपयोगकर्ताओं के लिए बेहतरीन कोर्स उपलब्ध कराता है. (ध्यान दें: इस कोर्स को न तो Google ने बनाया है और न ही यह Google से जुड़ा है.)

यहां DOUBLE नाम का एक आसान कस्टम फ़ंक्शन दिया गया है, जो इनपुट की वैल्यू को 2 से गुणा करता है:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

अगर आपको JavaScript लिखने का तरीका नहीं पता है और आपके पास इसके बारे में जानने का समय नहीं है, तो ऐड-ऑन स्टोर देखें और देखें कि किसी दूसरे व्यक्ति ने पहले से ही आपकी ज़रूरत के हिसाब से कस्टम फ़ंक्शन बना लिया है या नहीं.

कस्टम फ़ंक्शन बनाना

कस्टम फ़ंक्शन लिखने के लिए:

1. Google Sheets में स्प्रेडशीट बनाएं या खोलें.
2. मेन्यू आइटम एक्सटेंशन > Apps Script चुनें.
3. स्क्रिप्ट एडिटर में मौजूद सभी कोड मिटाएं. ऊपर दिए गए DOUBLE फ़ंक्शन के लिए, कोड को कॉपी करके स्क्रिप्ट एडिटर में चिपकाएं.
4. सबसे ऊपर, सेव करें save पर क्लिक करें.

अब कस्टम फ़ंक्शन का इस्तेमाल किया जा सकता है.

Google Workspace Marketplaceसे कस्टम फ़ंक्शन मिल रहा है

Google Workspace Marketplace में, Google Sheets के लिए ऐड-ऑन के तौर पर कई कस्टम फ़ंक्शन उपलब्ध हैं. इन ऐड-ऑन का इस्तेमाल करने या एक्सप्लोर करने के लिए:

1. Google Sheets में स्प्रेडशीट बनाएं या खोलें.
2. सबसे ऊपर, ऐड-ऑन > ऐड-ऑन पाएं पर क्लिक करें.
3. Google Workspace Marketplace खुलने के बाद, सबसे ऊपर दाएं कोने में मौजूद खोज बॉक्स पर क्लिक करें.
4. "कस्टम फ़ंक्शन" टाइप करें और Enter दबाएं.
5. अगर आपको अपनी पसंद का कस्टम फ़ंक्शन ऐड-ऑन मिलता है, तो उसे इंस्टॉल करने के लिए इंस्टॉल करें पर क्लिक करें.
6. आपको किसी डायलॉग बॉक्स से पता चल सकता है कि ऐड-ऑन के लिए अनुमति की ज़रूरत है. अगर ऐसा है, तो सूचना को ध्यान से पढ़ें और अनुमति दें पर क्लिक करें.
7. ऐड-ऑन, स्प्रेडशीट में उपलब्ध हो जाता है. ऐड-ऑन को किसी दूसरी स्प्रेडशीट में इस्तेमाल करने के लिए, दूसरी स्प्रेडशीट खोलें और सबसे ऊपर, ऐड-ऑन > ऐड-ऑन मैनेज करें पर क्लिक करें. वह ऐड-ऑन ढूंढें जिसका आपको इस्तेमाल करना है और विकल्प more_vert > इस दस्तावेज़ में इस्तेमाल करें पर क्लिक करें.

कस्टम फ़ंक्शन का इस्तेमाल करना

कोई कस्टम फ़ंक्शन लिखने याGoogle Workspace Marketplaceसे कोई फ़ंक्शन इंस्टॉल करने के बाद, उसे किसी बिल्ट-इन फ़ंक्शन की तरह इस्तेमाल करना आसान हो जाता है:

1. उस सेल पर क्लिक करें जहां आपको फ़ंक्शन का इस्तेमाल करना है.
2. बराबर का निशान (=) टाइप करें. इसके बाद, फ़ंक्शन का नाम और इनपुट की कोई भी वैल्यू डालें. उदाहरण के लिए, =DOUBLE(A1). इसके बाद, Enter दबाएं.
3. सेल कुछ समय के लिए, Loading... दिखाएगा. इसके बाद, नतीजा दिखाएगा.

कस्टम फ़ंक्शन के लिए दिशा-निर्देश

अपने कस्टम फ़ंक्शन को लिखने से पहले, कुछ दिशा-निर्देशों के बारे में जान लें.

इन्हें

JavaScript फ़ंक्शन को नाम देने के स्टैंडर्ड कन्वेंशन के अलावा, इन बातों का भी ध्यान रखें:

• कस्टम फ़ंक्शन का नाम पहले से मौजूद फ़ंक्शन, जैसे कि SUM() के नाम से अलग होना चाहिए.
• कस्टम फ़ंक्शन का नाम, अंडरस्कोर (_) पर खत्म नहीं हो सकता. यह अंडरस्कोर, Apps Script में किसी निजी फ़ंक्शन को दिखाता है.
• कस्टम फ़ंक्शन का नाम, var myFunction = new Function() के बजाय function myFunction() सिंटैक्स के साथ देना ज़रूरी है.
• कैपिटल लेटर का इस्तेमाल करने से कोई फ़र्क़ नहीं पड़ता. हालांकि, स्प्रेडशीट फ़ंक्शन के नाम में आम तौर पर कैपिटल लेटर का इस्तेमाल होता है.

तर्क

किसी बिल्ट-इन फ़ंक्शन की तरह, कस्टम फ़ंक्शन इनपुट वैल्यू के तौर पर आर्ग्युमेंट को ले सकता है:

• अगर अपने फ़ंक्शन को किसी एक सेल के रेफ़रंस के साथ कॉल किया जाता है, तो आर्ग्युमेंट के तौर पर (जैसे =DOUBLE(A1)), सेल की वैल्यू को आर्ग्युमेंट कहा जाएगा.

• अगर फ़ंक्शन को सेल की रेंज के रेफ़रंस के साथ कॉल किया जाता है (जैसे कि =DOUBLE(A1:B10)), तो आर्ग्युमेंट सेल की वैल्यू की दो डाइमेंशन वाला कलेक्शन होगा. उदाहरण के लिए, नीचे दिए गए स्क्रीनशॉट में, =DOUBLE(A1:B2) में दिए गए तर्क को Apps Script में double([[1,3],[2,4]]) के तौर पर समझा जाता है. ध्यान दें कि ऊपर से DOUBLE के सैंपल कोड को इनपुट के तौर पर किसी अरे को स्वीकार करने के लिए, उसे बदलना होगा.

  
  

• कस्टम फ़ंक्शन के आर्ग्युमेंट तय करने वाले होने चाहिए. इसका मतलब है कि पहले से मौजूद स्प्रेडशीट फ़ंक्शन जो हर बार हिसाब लगाने पर अलग नतीजा देते हैं, जैसे कि NOW() या RAND() उन्हें कस्टम फ़ंक्शन में आर्ग्युमेंट के तौर पर इस्तेमाल नहीं किया जा सकता. अगर कोई कस्टम फ़ंक्शन, बार-बार अपडेट होने वाले इन बिल्ट-इन फ़ंक्शन में से किसी एक पर आधारित वैल्यू देने की कोशिश करता है, तो वह Loading... हमेशा के लिए दिखाएगा.

रिटर्न वैल्यू

हर कस्टम फ़ंक्शन को दिखाने के लिए एक वैल्यू देनी चाहिए, जैसे कि:

• अगर कोई कस्टम फ़ंक्शन कोई वैल्यू दिखाता है, तो वह वैल्यू उस सेल में दिखती है जिससे फ़ंक्शन को कॉल किया गया था.
• अगर कोई कस्टम फ़ंक्शन, वैल्यू का दो डाइमेंशन वाला अरे दिखाता है, तो वैल्यू आस-पास की सेल में तब तक ओवरफ़्लो होती हैं, जब तक वे सेल खाली नहीं रहतीं. अगर इसकी वजह से अरे मौजूदा सेल के कॉन्टेंट को ओवरराइट करता है, तो कस्टम फ़ंक्शन इसके बजाय गड़बड़ी दिखाएगा. उदाहरण के लिए, कस्टम फ़ंक्शन ऑप्टिमाइज़ करने से जुड़ा सेक्शन देखें.
• कस्टम फ़ंक्शन, उन सेल पर असर नहीं डाल सकता जिन पर वह वैल्यू दिखाता है. दूसरे शब्दों में, कस्टम फ़ंक्शन आर्बिट्ररी सेल में बदलाव नहीं कर सकता. सिर्फ़ वे सेल जिनमें उसे कॉल किया जाता है और उनके आस-पास की सेल में बदलाव किया जा सकता है. आर्बिट्रेरी सेल में बदलाव करने के लिए, फ़ंक्शन को चलाने के लिए कस्टम मेन्यू का इस्तेमाल करें.
• कस्टम फ़ंक्शन कॉल, 30 सेकंड के अंदर दिखना चाहिए. अगर ऐसा नहीं होता है, तो सेल में एक गड़बड़ी दिखेगी: Internal error executing the custom function.

डेटा टाइप

डेटा किस तरह का है, इसके आधार पर Google Sheets डेटा को अलग-अलग फ़ॉर्मैट में सेव करता है. जब इन वैल्यू का इस्तेमाल कस्टम फ़ंक्शन में किया जाता है, तो Apps Script में इन्हें JavaScript में सही डेटा टाइप के तौर पर देखा जाता है. आम तौर पर, इन चीज़ों में भ्रम होता है:

• Sheets में मौजूद समय और तारीख, Apps Script में Date ऑब्जेक्ट बन जाते हैं. अगर स्प्रेडशीट और स्क्रिप्ट अलग-अलग टाइम ज़ोन (कभी-कभी आने वाली समस्या) का इस्तेमाल करती हैं, तो कस्टम फ़ंक्शन को
• Sheets में अवधि की वैल्यू भी Date ऑब्जेक्ट बन जाती हैं. हालांकि, उनके साथ काम करना मुश्किल हो सकता है.
• Sheets में मौजूद प्रतिशत की वैल्यू, Apps Script में दशमलव वाली संख्या में बदल जाती हैं. उदाहरण के लिए, 10% वैल्यू वाली सेल, Apps Script में 0.1 बन जाती है.

ऑटोकंप्लीट

Google Sheets में, पसंद के मुताबिक बनाए गए फ़ंक्शन के लिए, ऑटोकंप्लीट की सुविधा उपलब्ध है. जैसे, पहले से मौजूद फ़ंक्शन. किसी सेल में फ़ंक्शन का नाम टाइप करने पर, आपको उन बिल्ट-इन और कस्टम फ़ंक्शन की सूची दिखेगी जो डाले गए फ़ंक्शन से मेल खाते हैं.

कस्टम फ़ंक्शन इस सूची में तब दिखेंगे, जब उनकी स्क्रिप्ट में JsDoc @customfunction टैग शामिल हो, जैसा कि नीचे DOUBLE() के उदाहरण में बताया गया है.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

ऐडवांस फ़िल्टर

Google Apps Script सेवाओं का इस्तेमाल करना

कस्टम फ़ंक्शन, ज़्यादा मुश्किल काम करने के लिए कुछ Google Apps Script सेवाओं को कॉल कर सकते हैं. उदाहरण के लिए, कोई कस्टम फ़ंक्शन किसी अंग्रेज़ी वाक्यांश का स्पैनिश में अनुवाद करने के लिए भाषा सेवा को कॉल कर सकता है.

दूसरी तरह की Apps Script के मुकाबले, कस्टम फ़ंक्शन कभी भी उपयोगकर्ताओं से निजी डेटा के ऐक्सेस की अनुमति नहीं मांगते. ऐसे में, वे सिर्फ़ उन सेवाओं को कॉल कर सकते हैं जिनके पास निजी डेटा का ऐक्सेस नहीं है, खास तौर पर इन सेवाओं पर:

इस्तेमाल की जा सकने वाली सेवाएं	ज़रूरी जानकारी
कैश मेमोरी	काम करता है, लेकिन कस्टम फ़ंक्शन में खास तौर पर फ़ायदेमंद नहीं है
एचटीएमएल	एचटीएमएल को जनरेट किया जा सकता है, लेकिन उसे दिखाया नहीं जा सकता (ऐसा बहुत कम होता है)
जेडीबीसी
Language
लॉक करें	काम करता है, लेकिन कस्टम फ़ंक्शन में खास तौर पर फ़ायदेमंद नहीं है
Maps	दिशा-निर्देशों का पता लगा सकता है, लेकिन मैप नहीं दिखा सकता
प्रॉपर्टी	getUserProperties() को सिर्फ़ स्प्रेडशीट के मालिक की प्रॉपर्टी मिलती हैं. स्प्रेडशीट के एडिटर, कस्टम फ़ंक्शन में उपयोगकर्ता प्रॉपर्टी सेट नहीं कर सकते.
स्प्रेडशीट	रीड ओनली (ज़्यादातर get*() तरीकों का इस्तेमाल किया जा सकता है, लेकिन set*() का नहीं). अन्य स्प्रेडशीट (SpreadsheetApp.openById() या SpreadsheetApp.openByUrl()) नहीं खोली जा सकतीं.
यूआरएल फ़ेच करना
काम की सेवाएं
XML

अगर आपके कस्टम फ़ंक्शन से गड़बड़ी का मैसेज You do not have permission to call X service. मिलता है, तो इस सेवा के लिए उपयोगकर्ता की अनुमति ज़रूरी है. इसलिए, इसे कस्टम फ़ंक्शन में इस्तेमाल नहीं किया जा सकता.

ऊपर बताई गई सेवा के अलावा किसी दूसरी सेवा का इस्तेमाल करने के लिए, ऐसा कस्टम मेन्यू बनाएं जो कस्टम फ़ंक्शन लिखने के बजाय, Apps Script फ़ंक्शन को चलाता है. मेन्यू से ट्रिगर होने वाला फ़ंक्शन, ज़रूरत पड़ने पर उपयोगकर्ता से अनुमति मांगेगा. साथ ही, वह सभी Apps Script सेवाओं का इस्तेमाल कर सकेगा.

शेयर करने की अनुमतियाँ

कस्टम फ़ंक्शन उसी स्प्रेडशीट से बाउंड होते हैं जिसमें वे बनाए गए थे. इसका मतलब है कि एक स्प्रेडशीट में लिखे गए कस्टम फ़ंक्शन का इस्तेमाल दूसरी स्प्रेडशीट में तब तक नहीं किया जा सकता, जब तक कि आप इनमें से किसी एक तरीके का इस्तेमाल न करें:

• स्क्रिप्ट एडिटर खोलने के लिए, एक्सटेंशन > Apps Script पर क्लिक करें. इसके बाद, ओरिजनल स्प्रेडशीट से स्क्रिप्ट का टेक्स्ट कॉपी करके, उसे किसी दूसरी स्प्रेडशीट के स्क्रिप्ट एडिटर में चिपकाएं.
• फ़ाइल > कॉपी बनाएं पर क्लिक करके कस्टम फ़ंक्शन वाली स्प्रेडशीट की एक कॉपी बनाएं. जब कोई स्प्रेडशीट कॉपी की जाती है, तो उससे जुड़ी स्क्रिप्ट भी कॉपी हो जाती हैं. जिसके पास भी स्प्रेडशीट का ऐक्सेस है वह स्क्रिप्ट को कॉपी कर सकता है. (जिन सहयोगियों के पास सिर्फ़ देखने का ऐक्सेस है वे मूल स्प्रेडशीट में स्क्रिप्ट एडिटर नहीं खोल सकते. हालांकि, जब वे कॉपी बनाते हैं, तो वे कॉपी का मालिक बन जाते हैं और स्क्रिप्ट देख सकते हैं.)
• स्क्रिप्ट को Google Sheets एडिटर ऐड-ऑन के तौर पर पब्लिश करें.

ऑप्टिमाइज़ेशन

जब भी स्प्रेडशीट में कस्टम फ़ंक्शन का इस्तेमाल किया जाता है, तब Google Sheets, Apps Script सर्वर पर एक अलग कॉल करता है. अगर आपकी स्प्रेडशीट में कस्टम फ़ंक्शन कॉल के दर्जनों (या सैकड़ों या हज़ारों!) हैं, तो यह प्रोसेस काफ़ी धीमी हो सकती है.

ऐसे में, अगर आपको डेटा की बड़ी रेंज पर कस्टम फ़ंक्शन को कई बार इस्तेमाल करना है, तो फ़ंक्शन में बदलाव करें, ताकि यह 2-डाइमेंशन वाले अरे के रूप में इनपुट के तौर पर रेंज को स्वीकार कर सके. इसके बाद, 2-डाइमेंशन वाला अरे लौटाता है, जो सही सेल में ओवरफ़्लो हो सकता है.

उदाहरण के लिए, किसी एक सेल या सेल की रेंज को इस तरह से स्वीकार करने के लिए, ऊपर दिखाए गए DOUBLE() फ़ंक्शन को फिर से लिखा जा सकता है:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

ऊपर दिया गया तरीका, JavaScript के Array ऑब्जेक्ट के मैप तरीके का इस्तेमाल करता है. इस तरीके का इस्तेमाल करके, सेल के दो-डाइमेंशन वाले कलेक्शन में मौजूद हर वैल्यू पर DOUBLE को बार-बार कॉल किया जाता है. यह एक द्वि-आयामी सरणी देता है, जिसमें परिणाम होते हैं. इस तरह, DOUBLE को सिर्फ़ एक बार कॉल किया जा सकता है. हालांकि, इससे एक साथ कई सेल का हिसाब लगाया जा सकता है, जैसा कि नीचे दिए गए स्क्रीनशॉट में दिखाया गया है. (आप map कॉल के बजाय, नेस्ट किए गए if स्टेटमेंट की मदद से ऐसा ही कर सकते हैं.)

इसी तरह, नीचे दिया गया कस्टम फ़ंक्शन इंटरनेट से लाइव कॉन्टेंट को बेहतर तरीके से फ़ेच करता है और सिर्फ़ एक फ़ंक्शन कॉल से, नतीजों के दो कॉलम दिखाने के लिए दो डाइमेंशन वाले अरे का इस्तेमाल करता है. अगर हर सेल को खुद के फ़ंक्शन कॉल की ज़रूरत होती है, तो कार्रवाई में ज़्यादा समय लगेगा, क्योंकि Apps Script सर्वर को हर बार एक्सएमएल फ़ीड को डाउनलोड और पार्स करना होगा.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

इन तकनीकों को करीब-करीब ऐसे किसी भी कस्टम फ़ंक्शन पर लागू किया जा सकता है जिसे स्प्रेडशीट में बार-बार इस्तेमाल किया जाता है. हालांकि, लागू करने से जुड़ी जानकारी, फ़ंक्शन के काम करने के तरीके के आधार पर अलग-अलग होगी.