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

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

रिपोर्ट का इस्तेमाल करना

कस्टम फ़ंक्शन, स्टैंडर्ड JavaScript का इस्तेमाल करके बनाए जाते हैं. अगर आपने JavaScript का इस्तेमाल पहले कभी नहीं किया है, तो Codecademy नए उपयोगकर्ताओं के लिए एक बेहतरीन कोर्स उपलब्ध कराता है. (ध्यान दें: यह कोर्स 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 शीट में एक स्प्रैडशीट बनाएं या खोलें.
2. मेन्यू आइटम एक्सटेंशन > Apps Script चुनें.
3. स्क्रिप्ट एडिटर में मौजूद कोई भी कोड मिटाएं. ऊपर दिए गए DOUBLE फ़ंक्शन के लिए, बस कोड को कॉपी करके स्क्रिप्ट एडिटर में चिपकाएं.
4. सबसे ऊपर, सेव करें पर क्लिक करें.

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

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

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

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

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

कस्टम फ़ंक्शन लिखने या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 में तारीख के ऑब्जेक्ट के तौर पर दिखती है. अगर स्प्रेडशीट और स्क्रिप्ट अलग-अलग टाइम ज़ोन का इस्तेमाल करते हैं (बहुत कम समस्या), तो कस्टम फ़ंक्शन को इसकी भरपाई करनी होगी.
• 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 स्क्रिप्ट' की सेवाओं का इस्तेमाल करना

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

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

इस्तेमाल की जा सकने वाली सेवाएं	ज़रूरी जानकारी
कैश मेमोरी	काम करता है, लेकिन कस्टम फ़ंक्शन में खास तौर पर उपयोगी नहीं है
एचटीएमएल	एचटीएमएल जनरेट किया जा सकता है, लेकिन इसे दिखा नहीं सकता (बहुत कम काम का)
जेडीबीसी
भाषा
लॉक करें	काम करता है, लेकिन कस्टम फ़ंक्शन में खास तौर पर उपयोगी नहीं है
मैप	दिशा-निर्देशों का पता लगाया जा सकता है, लेकिन मैप नहीं दिखाए जा सकते
प्रॉपर्टी	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 के एडिटर ऐड-ऑन के तौर पर पब्लिश करें.

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

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

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