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

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

कस्टम फ़ंक्शन के उदाहरणों के लिए, ये ट्यूटोरियल देखें:

• छूट वाले आइटम की सेल वाली कीमत का हिसाब लगाना (क्विकस्टार्ट)
• टियर के हिसाब से तय की गई कीमत पर छूट का हिसाब लगाना
• ड्राइविंग की दूरी का हिसाब लगाना और मीटर को मील में बदलना
• एक से ज़्यादा शीट से डेटा की खास जानकारी पाना
• ADK के एआई एजेंट और Gemini मॉडल की मदद से, तथ्यों की जांच करना

शुरू करना

कस्टम फ़ंक्शन, स्टैंडर्ड 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 लिखना नहीं आता और आपके पास इसे सीखने का समय नहीं है, तो Google Workspace ऐड-ऑन स्टोर देखें. इससे आपको यह पता चलेगा कि क्या किसी और ने पहले से ही आपकी ज़रूरत के हिसाब से कस्टम फ़ंक्शन बनाया है.

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

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

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

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

Google Workspace Marketplace से कस्टम फ़ंक्शन पाना

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

1. 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 में इसका मतलब प्राइवेट फ़ंक्शन होता है.
• कस्टम फ़ंक्शन के नाम का एलान, function myFunction() सिंटैक्स के साथ किया जाना चाहिए, न कि var myFunction = new Function() के साथ.
• कैपिटलाइज़ेशन से कोई फ़र्क़ नहीं पड़ता. हालांकि, स्प्रेडशीट फ़ंक्शन के नाम आम तौर पर अपरकेस में होते हैं.

तर्क

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

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

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

  
  

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

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

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

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

• अगर कोई कस्टम फ़ंक्शन वैल्यू दिखाता है, तो वह वैल्यू उस सेल में दिखती है जहां से फ़ंक्शन को कॉल किया गया था.
• अगर कोई कस्टम फ़ंक्शन, वैल्यू का दो डाइमेंशन वाला ऐरे दिखाता है, तो वैल्यू आस-पास की सेल में तब तक दिखती हैं, जब तक वे सेल खाली हों. अगर इससे ऐरे, सेल में मौजूद कॉन्टेंट को ओवरराइट कर देता है, तो कस्टम फ़ंक्शन इसके बजाय गड़बड़ी दिखाता है. उदाहरण के लिए, कस्टम फ़ंक्शन को ऑप्टिमाइज़ करने के बारे में जानकारी देने वाला सेक्शन देखें.
• कस्टम फ़ंक्शन, उन सेल पर असर नहीं डाल सकता जिनमें वह वैल्यू दिखाता है. दूसरे शब्दों में कहें, तो कस्टम फ़ंक्शन किसी भी सेल में बदलाव नहीं कर सकता. वह सिर्फ़ उन सेल में बदलाव कर सकता है जिनसे उसे कॉल किया गया है और उनसे जुड़ी सेल में बदलाव कर सकता है. किसी भी सेल में बदलाव करने के लिए, फ़ंक्शन चलाने के लिए कस्टम मेन्यू का इस्तेमाल करें.
• कस्टम फ़ंक्शन कॉल को 30 सेकंड के अंदर जवाब देना होगा. अगर ऐसा नहीं होता है, तो सेल में #ERROR! दिखता है और सेल नोट Exceeded maximum execution time (line 0). होता है

डेटा टाइप

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

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

अपने-आप पूरा होना

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

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

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

बेहतर

इस सेक्शन में, कस्टम फ़ंक्शन से जुड़े बेहतर विषयों के बारे में बताया गया है.

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

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

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

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

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

कस्टम फ़ंक्शन शेयर करना

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

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

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

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

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

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

उदाहरण के लिए, ऊपर दिखाए गए 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 ऑब्जेक्ट के map तरीके का इस्तेमाल किया जाता है, ताकि हर लाइन मिल सके. इसके बाद, हर लाइन के लिए, यह map का फिर से इस्तेमाल करता है, ताकि हर सेल की वैल्यू को दोगुना किया जा सके. यह दो डाइमेंशन वाला ऐसा अरे दिखाता है जिसमें नतीजे शामिल होते हैं. इस तरह, 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;
}

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