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. सबसे ऊपर, सेव करें पर क्लिक करें.

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

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

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

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

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

कस्टम फ़ंक्शन लिखने या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... को हमेशा के लिए दिखाएगा.

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

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

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

डेटा टाइप

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 सेवाओं को कॉल कर सकते हैं. उदाहरण के लिए, कोई कस्टम फ़ंक्शन, Language सेवा को कॉल करके, किसी अंग्रेज़ी वाक्यांश का स्पैनिश में अनुवाद कर सकता है.

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

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

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

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

शेयर करना

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

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

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

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

जब भी किसी स्प्रेडशीट में कस्टम फ़ंक्शन का इस्तेमाल किया जाता है, तब Google 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 फ़ंक्शन को सिर्फ़ एक बार कॉल करना होगा. हालांकि, यह एक साथ कई सेल के लिए कैलकुलेट किया जा सकता है. जैसा कि यहां दिए गए स्क्रीनशॉट में दिखाया गया है. (if कॉल के बजाय, नेस्ट किए गए if स्टेटमेंट का इस्तेमाल करके भी यही काम किया जा सकता है.)map

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

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