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

گوگل شیت صدها تابع داخلی مانند AVERAGE ، SUM و VLOOKUP ارائه می‌دهد. وقتی این‌ها برای نیازهای شما کافی نیستند، می‌توانید از 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;
}

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

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

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

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

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

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

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

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

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

وقتی یک تابع سفارشی نوشتید یا یکی را از 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... indefine را نمایش می‌دهد.

• برای فعال کردن محاسبه مجدد، باید یک سلول یا محدوده سلولی ارجاع داده شده را مستقیماً به عنوان آرگومان به تابع سفارشی ارسال کنید. در غیر این صورت، تابع سفارشی تا زمانی که تابع را ویرایش نکنید یا مقدار یک سلول ارجاع داده شده را تغییر ندهید، محاسبه مجدد انجام نمی‌دهد. اگر از متد getValue در توابع سفارشی استفاده می‌کنید، توجه داشته باشید که محدوده ارجاع شده مستقیماً به عنوان آرگومان به تابع سفارشی ارسال نمی‌شود.

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

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

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

انواع داده

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

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

تکمیل خودکار

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

توابع سفارشی در صورتی در این لیست ظاهر می‌شوند که اسکریپت آنها شامل یک تگ JSDoc @customfunction باشد، مانند مثال DOUBLE() .

توجه: اگرچه Apps Script از JSDoc استاندارد برای تکمیل خودکار در ویرایشگر پشتیبانی می‌کند، رابط کاربری Google Sheets محدودیت‌های خاصی در مورد اینکه کدام برچسب‌ها و نحو در کمک‌کننده فرمول صفحه گسترده نمایش داده می‌شوند، دارد.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return {number} The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

پیشرفته

این بخش مباحث پیشرفته توابع سفارشی را پوشش می‌دهد.

از سرویس‌های اسکریپت گوگل اپس استفاده کنید

توابع سفارشی می‌توانند سرویس‌های Apps Script خاصی را برای انجام وظایف پیچیده‌تر فراخوانی کنند. برای مثال، یک تابع سفارشی می‌تواند سرویس Language را برای ترجمه یک عبارت انگلیسی به اسپانیایی فراخوانی کند.

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

خدمات پشتیبانی شده	یادداشت‌ها
حافظه پنهان	کار می‌کند، اما در توابع سفارشی به خصوص مفید نیست
اچ‌تی‌ام‌ال	می‌تواند HTML تولید کند، اما نمی‌تواند آن را نمایش دهد (به ندرت مفید است)
جی‌دی‌بی‌سی
زبان
قفل	کار می‌کند، اما در توابع سفارشی به خصوص مفید نیست
نقشه‌ها	می‌تواند مسیرها را محاسبه کند، اما نقشه‌ها را نمایش نمی‌دهد
خواص	getUserProperties() فقط ویژگی‌های مالک صفحه گسترده را دریافت می‌کند. ویرایشگرهای صفحه گسترده نمی‌توانند ویژگی‌های کاربر را در یک تابع سفارشی تنظیم کنند.
صفحه گسترده	فقط خواندنی (می‌تواند از اکثر متدهای get*() استفاده کند، اما نمی‌تواند set*() استفاده کند). نمی‌توان صفحات گسترده‌ی دیگر ( SpreadsheetApp.openById() یا SpreadsheetApp.openByUrl() ) را باز کرد.
دریافت URL	با دریافت URL ها به منابع وب دسترسی پیدا کنید.
خدمات رفاهی
XML

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

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

توابع سفارشی را به اشتراک بگذارید

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

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

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

بهینه‌سازی

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

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