توابع سفارشی در Google Sheets (original) (raw)

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

شروع کردن

توابع سفارشی با استفاده از جاوا اسکریپت استاندارد ایجاد می شوند. اگر با جاوا اسکریپت تازه کار هستید، Codecademy یک دوره عالی برای مبتدیان ارائه می دهد. (توجه: این دوره توسط 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;
}

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

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

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

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

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

گرفتن یک تابع سفارشی از Google Workspace Marketplace

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

یک صفحه گسترده در Google Sheets ایجاد یا باز کنید.
در بالا، روی افزونه‌ها > دریافت افزونه‌ها کلیک کنید.
یک بارGoogle Workspace Marketplace باز می شود، روی کادر جستجو در گوشه بالا سمت راست کلیک کنید.
"عملکرد سفارشی" را تایپ کرده و Enter را فشار دهید.
اگر یک افزونه تابع سفارشی پیدا کردید که به آن علاقه دارید، روی Install کلیک کنید تا نصب شود.
یک کادر محاوره ای ممکن است به شما بگوید که افزونه نیاز به مجوز دارد. اگر چنین است، اعلان را با دقت بخوانید، سپس روی اجازه کلیک کنید.
افزونه در صفحه گسترده در دسترس می شود. برای استفاده از برافزا در صفحه‌گسترده دیگری، صفحه‌گسترده دیگر را باز کنید و در بالا، روی «افزونه‌ها» > مدیریت برافزاها کلیک کنید. افزونه ای را که می خواهید استفاده کنید پیدا کنید و روی Options > Use in this document کلیک کنید.

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

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

روی سلولی که می خواهید از تابع استفاده کنید کلیک کنید.
یک علامت تساوی ( = ) و به دنبال آن نام تابع و هر مقدار ورودی - برای مثال =DOUBLE(A1) - تایپ کنید و Enter را فشار دهید.
سلول به صورت لحظه ای 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... به طور نامحدود نمایش می دهد.

مقادیر برگرداندن

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

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

انواع داده ها

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

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

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

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

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

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

اشتراک گذاری

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

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

بهینه سازی

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

روش فوق از روش نقشه Array آرایه به روش جاوا اسکریپت روی آرایه دو بعدی سلول ها برای بدست آوردن هر سطر استفاده می کند، سپس برای هر سطر، دوباره map استفاده می کند تا دو برابر مقدار هر سلول را برگرداند. یک آرایه دو بعدی را برمی گرداند که حاوی نتایج است. به این ترتیب، می‌توانید فقط یک بار DOUBLE تماس بگیرید، اما آن را برای تعداد زیادی سلول به طور همزمان محاسبه کنید، همانطور که در تصویر زیر نشان داده شده است. (شما می توانید همین کار را با دستورات Nested 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;
}

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