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

संग्रह की मदद से व्यवस्थित रहें अपनी प्राथमिकताओं के आधार पर, कॉन्टेंट को सेव करें और कैटगरी में बांटें.

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

शुरू करना

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

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

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. ऐड-ऑन स्प्रेडशीट में उपलब्ध हो जाता है. किसी दूसरी स्प्रेडशीट में ऐड-ऑन का इस्तेमाल करने के लिए, दूसरी स्प्रेडशीट खोलें और सबसे ऊपर जाकर, ऐड-ऑन और gt; ऐड-ऑन मैनेज करें पर क्लिक करें. वह ऐड-ऑन ढूंढें जिसका इस्तेमाल करना है और विकल्प &gt पर क्लिक करें. इस दस्तावेज़ में इस्तेमाल करें.

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

जब आप कोई कस्टम फ़ंक्शन लिखते हैं याGoogle Workspace Marketplaceसे कोई फ़ंक्शन इंस्टॉल करते हैं, तो यह पहले से मौजूद फ़ंक्शन की तरह इस्तेमाल करने में आसान होता है:

  1. उस सेल पर क्लिक करें जहां आप फ़ंक्शन का इस्तेमाल करना चाहते हैं.
  2. बराबर का निशान (=) लिखें और उसके बाद फ़ंक्शन का नाम और इनपुट का कोई भी मान डालें — उदाहरण के लिए, =DOUBLE(A1) — और Enter दबाएं.
  3. सेल तुरंत ही Loading... दिखाएगा, फिर नतीजे दिखाएगा.

कस्टम फ़ंक्शन के लिए दिशा-निर्देश

अपना कस्टम फ़ंक्शन लिखने से पहले, जानने के लिए कुछ दिशा-निर्देश उपलब्ध हैं.

इन्हें

JavaScript फ़ंक्शन को नाम देने से जुड़े मानक तरीकों के अलावा, इन बातों का भी ध्यान रखें:

  • कस्टम फ़ंक्शन का नाम, SUM() में मौजूद बिल्ट-इन फ़ंक्शन के नाम से अलग होना चाहिए.
  • पसंद के मुताबिक बनाए गए फ़ंक्शन का नाम, अंडरस्कोर (_) से खत्म नहीं हो सकता. यह ऐप्लिकेशन की स्क्रिप्ट में निजी फ़ंक्शन को दिखाता है.
  • कस्टम फ़ंक्शन के नाम की जानकारी सिंटैक्स के साथ दी जानी चाहिए function myFunction(), न कि var myFunction = new Function().
  • बड़े अक्षरों का इस्तेमाल करने से कोई फ़र्क़ नहीं पड़ता है. हालांकि, स्प्रेडशीट के फ़ंक्शन के नाम आम तौर पर अंग्रेज़ी के बड़े अक्षरों में होते हैं.

तर्क

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

  • अगर आप अपने फ़ंक्शन को किसी एक सेल के रेफ़रंस के तौर पर आर्ग्युमेंट (जैसे कि =DOUBLE(A1)) के तौर पर कॉल करते हैं, तो तर्क को सेल की वैल्यू माना जाएगा.
  • अगर आप फ़ंक्शन को किसी आर्ग्युमेंट (जैसे =DOUBLE(A1:B10)) के तौर पर सेल की रेंज के संदर्भ के साथ कॉल करते हैं, तो तर्क, सेल और #39; वैल्यू के दो-डाइमेंशन वाले कलेक्शन का होगा. उदाहरण के लिए, नीचे दिए गए स्क्रीनशॉट में, =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 में Date ऑब्जेक्ट बन जाते हैं. अगर स्प्रेडशीट और स्क्रिप्ट में अलग-अलग समय क्षेत्रों (बहुत कम होने वाली समस्या) का इस्तेमाल किया जाता है, तो कस्टम फ़ंक्शन को इसकी भरपाई करनी होगी.
  • Sheets में, वैल्यू की अवधि भी Date ऑब्जेक्ट बन जाती है. हालांकि, उनके साथ काम करना मुश्किल हो सकता है.
  • Apps Script में Sheets की प्रतिशत वैल्यू, दशमलव में बदल जाती है. उदाहरण के लिए, Apps Script में 10% वैल्यू वाला सेल, 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;
}

बेहतर सुविधाएं

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

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

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

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

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

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

शेयर करें

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

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

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

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

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

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

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