توابع سفارشی در Google Sheets

گوگل شیت صدها تابع داخلی مانند AVERAGE ، SUM و VLOOKUP ارائه می‌دهد. وقتی این‌ها برای نیازهای شما کافی نیستند، می‌توانید از Google Apps Script برای نوشتن توابع سفارشی استفاده کنید و سپس آن‌ها را درست مانند یک تابع داخلی در گوگل شیت به کار ببرید.

برای مثال‌هایی از توابع سفارشی، به آموزش‌های زیر مراجعه کنید:

• محاسبه قیمت فروش اقلام تخفیف‌دار (شروع سریع)
• تخفیف قیمت‌گذاری پلکانی را محاسبه کنید
• محاسبه مسافت رانندگی و تبدیل متر به مایل
• خلاصه کردن داده‌ها از چندین شیت
• بررسی صحت اظهارات با یک عامل هوش مصنوعی ADK و مدل Gemini

شروع به کار

توابع سفارشی با استفاده از جاوا اسکریپت استاندارد ایجاد می‌شوند. اگر در جاوا اسکریپت تازه‌کار هستید، Codecademy یک دوره عالی برای مبتدیان ارائه می‌دهد. (توجه: این دوره توسط گوگل توسعه داده نشده و به آن وابسته نیست.)

در اینجا یک تابع سفارشی ساده به نام 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;
}

اگر نمی‌دانید چگونه جاوا اسکریپت بنویسید و وقت یادگیری هم ندارید، فروشگاه افزونه‌ها را بررسی کنید تا ببینید آیا شخص دیگری قبلاً تابع سفارشی مورد نیاز شما را ساخته است یا خیر.

ایجاد یک تابع سفارشی

برای نوشتن یک تابع سفارشی:

1. یک صفحه گسترده در Google Sheets ایجاد یا باز کنید.
2. گزینه منو Extensions > Apps Script را انتخاب کنید.
3. هر کدی را در ویرایشگر اسکریپت حذف کنید. برای تابع DOUBLE در بالا، کافیست کد را کپی کرده و در ویرایشگر اسکریپت جایگذاری کنید.
4. در بالا، روی ذخیره save کلیک کنید.

حالا می‌توانید از تابع سفارشی استفاده کنید .

دریافت یک عملکرد سفارشی از بازار Google Workspace

بازار Google Workspace چندین عملکرد سفارشی را به عنوان افزونه برای Google Sheets ارائه می‌دهد. برای استفاده یا بررسی این افزونه‌ها:

1. یک صفحه گسترده در Google Sheets ایجاد یا باز کنید.
2. در بالا، روی افزونه‌ها > دریافت افزونه‌ها کلیک کنید.
3. پس از باز شدن بازار کار گوگل ، روی کادر جستجو در گوشه بالا سمت راست کلیک کنید.
4. عبارت "تابع سفارشی" را تایپ کرده و Enter را فشار دهید.
5. اگر افزونه‌ی کاربردی سفارشی‌ای را که به آن علاقه دارید پیدا کردید، برای نصب آن روی نصب کلیک کنید.
6. ممکن است یک کادر محاوره‌ای به شما بگوید که افزونه نیاز به مجوز دارد. در این صورت، اطلاعیه را با دقت بخوانید، سپس روی «مجاز» کلیک کنید.
7. افزونه در صفحه‌گسترده در دسترس قرار می‌گیرد. برای استفاده از افزونه در یک صفحه‌گسترده دیگر، صفحه‌گسترده دیگر را باز کنید و در بالا، روی افزونه‌ها > مدیریت افزونه‌ها کلیک کنید. افزونه‌ای را که می‌خواهید استفاده کنید پیدا کنید و روی گزینه‌ها more_vert > استفاده در این سند کلیک کنید.

با استفاده از یک تابع سفارشی

وقتی یک تابع سفارشی نوشتید یا یکی را از Google Workspace Marketplace نصب کردید، استفاده از آن به آسانی یک تابع داخلی است:

1. روی سلولی که می‌خواهید از تابع در آن استفاده کنید، کلیک کنید.
2. یک علامت مساوی ( = ) و به دنبال آن نام تابع و هر مقدار ورودی - برای مثال، =DOUBLE(A1) - را تایپ کنید و Enter را فشار دهید.
3. سلول به طور موقت Loading... نمایش می‌دهد، سپس نتیجه را برمی‌گرداند.

دستورالعمل‌های مربوط به توابع سفارشی

قبل از نوشتن تابع سفارشی خودتان، چند نکته وجود دارد که باید بدانید.

نامگذاری

علاوه بر قراردادهای استاندارد برای نامگذاری توابع جاوا اسکریپت، از موارد زیر نیز آگاه باشید:

• نام یک تابع سفارشی باید با نام توابع داخلی مانند 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... indefinely را نمایش می‌دهد.

مقادیر بازگشتی

هر تابع سفارشی باید مقداری را برای نمایش برگرداند، به طوری که:

• اگر یک تابع سفارشی مقداری را برگرداند، آن مقدار در سلولی که تابع از آن فراخوانی شده است نمایش داده می‌شود.
• اگر یک تابع سفارشی یک آرایه دوبعدی از مقادیر را برگرداند، تا زمانی که آن سلول‌ها خالی باشند، این مقادیر به سلول‌های مجاور سرریز می‌شوند. اگر این باعث شود آرایه محتوای سلول‌های موجود را بازنویسی کند، تابع سفارشی به جای آن خطا می‌دهد. برای مثال، به بخش بهینه‌سازی توابع سفارشی مراجعه کنید.
• یک تابع سفارشی نمی‌تواند سلول‌هایی غیر از سلول‌هایی که به آنها مقدار برمی‌گرداند را تحت تأثیر قرار دهد. به عبارت دیگر، یک تابع سفارشی نمی‌تواند سلول‌های دلخواه را ویرایش کند، فقط سلول‌هایی که از آنها فراخوانی شده و سلول‌های مجاور آنها را ویرایش می‌کند. برای ویرایش سلول‌های دلخواه، به جای آن از یک منوی سفارشی برای اجرای یک تابع استفاده کنید.
• یک فراخوانی تابع سفارشی باید ظرف 30 ثانیه مقدار بازگشتی داشته باشد. در غیر این صورت، سلول #ERROR! را نمایش می‌دهد و یادداشت سلول Exceeded maximum execution time (line 0).

انواع داده

گوگل شیت بسته به ماهیت داده‌ها، داده‌ها را در قالب‌های مختلفی ذخیره می‌کند. وقتی این مقادیر در توابع سفارشی استفاده می‌شوند، Apps Script آنها را به عنوان نوع داده مناسب در جاوا اسکریپت در نظر می‌گیرد. موارد زیر رایج‌ترین موارد سردرگمی هستند:

• زمان‌ها و تاریخ‌ها در Sheets به اشیاء Date در Apps Script تبدیل می‌شوند. اگر صفحه گسترده و اسکریپت از مناطق زمانی متفاوتی استفاده کنند (یک مشکل نادر)، تابع سفارشی باید این مشکل را جبران کند.
• مقادیر مدت زمان در Sheets نیز به اشیاء Date تبدیل می‌شوند، اما کار با آنها می‌تواند پیچیده باشد .
• مقادیر درصد در Sheets در Apps Script به اعداد اعشاری تبدیل می‌شوند. برای مثال، سلولی با مقدار 10% در Apps Script به 0.1 تبدیل می‌شود.

تکمیل خودکار

گوگل شیت از تکمیل خودکار توابع سفارشی، بسیار شبیه به توابع داخلی، پشتیبانی می‌کند. وقتی نام یک تابع را در یک سلول تایپ می‌کنید، لیستی از توابع داخلی و سفارشی را مشاهده خواهید کرد که با آنچه وارد می‌کنید مطابقت دارند.

توابع سفارشی در صورتی در این لیست ظاهر می‌شوند که اسکریپت آنها شامل یک تگ 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) خاصی را برای انجام وظایف پیچیده‌تر فراخوانی کنند. برای مثال، یک تابع سفارشی می‌تواند سرویس زبان (Language) را برای ترجمه یک عبارت انگلیسی به اسپانیایی فراخوانی کند.

برخلاف اکثر انواع دیگر اسکریپت‌های برنامه‌ها، توابع سفارشی هرگز از کاربران اجازه دسترسی به داده‌های شخصی را نمی‌خواهند. در نتیجه، آنها فقط می‌توانند سرویس‌هایی را فراخوانی کنند که به داده‌های شخصی دسترسی ندارند، به ویژه موارد زیر:

اگر تابع سفارشی شما پیام خطای You do not have permission to call X service. را نمایش دهد، این سرویس نیاز به مجوز کاربر دارد و بنابراین نمی‌توان از آن در یک تابع سفارشی استفاده کرد.

برای استفاده از سرویسی غیر از موارد ذکر شده در بالا، به جای نوشتن یک تابع سفارشی، یک منوی سفارشی ایجاد کنید که یک تابع Apps Script را اجرا کند. تابعی که از یک منو فعال می‌شود، در صورت لزوم از کاربر درخواست مجوز می‌کند و در نتیجه می‌تواند از تمام سرویس‌های Apps Script استفاده کند.

اشتراک‌گذاری

توابع سفارشی در ابتدا به صفحه‌گسترده‌ای که در آن ایجاد شده‌اند، محدود می‌شوند . این بدان معناست که یک تابع سفارشی که در یک صفحه‌گسترد نوشته شده است، نمی‌تواند در صفحات‌گسترده‌ی دیگر استفاده شود، مگر اینکه از یکی از روش‌های زیر استفاده کنید:

• برای باز کردن ویرایشگر اسکریپت، روی Extensions > Apps Script کلیک کنید، سپس متن اسکریپت را از صفحه گسترده اصلی کپی کرده و در ویرایشگر اسکریپت صفحه گسترده دیگری جایگذاری کنید.
• با کلیک روی File > Make a copy، یک کپی از صفحه‌گسترده‌ای که حاوی تابع سفارشی است، تهیه کنید. وقتی یک صفحه‌گسترده‌ای کپی می‌شود، هر اسکریپتی که به آن متصل است نیز کپی می‌شود. هر کسی که به صفحه‌گسترده‌ای دسترسی داشته باشد می‌تواند اسکریپت را کپی کند. (همکارانی که فقط دسترسی مشاهده دارند، نمی‌توانند ویرایشگر اسکریپت را در صفحه‌گسترده‌ای اصلی باز کنند. با این حال، وقتی یک کپی تهیه می‌کنند، مالک کپی می‌شوند و می‌توانند اسکریپت را ببینند.)
• اسکریپت را به عنوان افزونه‌ای برای ویرایشگر گوگل شیت منتشر کنید.

بهینه‌سازی

هر بار که یک تابع سفارشی در یک صفحه گسترده استفاده می‌شود، 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;
}

رویکرد فوق از متد map از شیء Array جاوا اسکریپت برای متد روی آرایه دو بعدی سلول‌ها برای دریافت هر ردیف استفاده می‌کند، سپس برای هر ردیف، دوباره map برای برگرداندن مقدار دو برابر هر سلول استفاده می‌کند. این یک آرایه دو بعدی را برمی‌گرداند که شامل نتایج است. به این ترتیب، می‌توانید DOUBLE فقط یک بار فراخوانی کنید، اما همانطور که در تصویر زیر نشان داده شده است، آن را برای تعداد زیادی سلول به طور همزمان محاسبه کنید. (شما می‌توانید همین کار را با دستورات if تو در تو به جای فراخوانی map انجام دهید.)

به طور مشابه، تابع سفارشی زیر به طور موثر محتوای زنده را از اینترنت دریافت می‌کند و از یک آرایه دو بعدی برای نمایش دو ستون از نتایج تنها با یک فراخوانی تابع استفاده می‌کند. اگر هر سلول به فراخوانی تابع خاص خود نیاز داشته باشد، این عملیات زمان بسیار بیشتری طول می‌کشد، زیرا سرور Apps Script باید هر بار فید XML را دانلود و تجزیه کند.

/**
 * 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;
}

این تکنیک‌ها را می‌توان تقریباً برای هر تابع سفارشی که به طور مکرر در سراسر یک صفحه گسترده استفاده می‌شود، اعمال کرد، اگرچه جزئیات پیاده‌سازی بسته به رفتار تابع متفاوت خواهد بود.