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

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

शुरू करना

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

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

/**
 * 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 सेकंड के अंदर रिटर्न करना होगा. अगर ऐसा नहीं होता है, तो सेल में #ERROR! दिखता है और सेल का नोट Exceeded maximum execution time (line 0). होता है

डेटा टाइप

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 Script की सेवाओं का इस्तेमाल करना

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

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

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

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

शेयर करें

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

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

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

जब भी किसी स्प्रेडशीट में कस्टम फ़ंक्शन का इस्तेमाल किया जाता है, तो 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 ऑब्जेक्ट के 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;
}

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