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)), आर्ग्युमेंट दो डाइमेंशन वाला होगा सेल का अरे' वैल्यू. उदाहरण के लिए, नीचे दिए गए स्क्रीनशॉट में =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 स्क्रिप्ट उन्हें 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 सेवाएं टास्क. उदाहरण के लिए, कोई कस्टम फ़ंक्शन अंग्रेज़ी में अनुवाद करने के लिए भाषा सेवा वाक्यांश को स्पैनिश में बदलो.

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

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

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

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

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