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 शीट में एक स्प्रेडशीट बनाएं या खोलें.
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)) के तौर पर कॉल किया है, तो सेल के वैल्यू की दो-डाइमेंशन वाला ऐरे होगा. उदाहरण के लिए, नीचे दिए गए स्क्रीनशॉट में, Apps Script में =DOUBLE(A1:B2) में दिए गए तर्कों को double([[1,3],[2,4]]) के तौर पर समझा गया है. ध्यान दें कि ऊपर दिए गए DOUBLE के सैंपल कोड में बदलाव करना होगा, ताकि इनपुट के तौर पर ऐरे स्वीकार किया जा सके.

  
  

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

नतीजे में मिलने वाली वैल्यू

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

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

डेटा टाइप

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

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

ऊपर दिया गया तरीका, सेल के दो-डाइमेंशन वाले ऐरे में हर वैल्यू पर बार-बार 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;
}

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