الدوال المخصصة في جداول بيانات Google

تقدّم "جداول بيانات Google" مئات الدوالّ المضمّنة، مثل AVERAGE و SUM و VLOOKUP. عندما لا تكون هذه الدوالّ كافية لتلبية احتياجاتك، يمكنك استخدام "برمجة تطبيقات Google" لكتابة دوالّ مخصّصة ، مثل تحويل الأمتار إلى أميال أو جلب محتوى مباشر من الإنترنت، ثم استخدامها في "جداول بيانات Google" تمامًا مثل الدالة المضمّنة.

الخطوات الأولى

يتم إنشاء الدوال المخصّصة باستخدام لغة JavaScript العادية. إذا كنت حديث العهد باستخدام JavaScript، تقدّم Codecademy دورة تدريبية رائعة للمبتدئين. (ملاحظة: لم تطوّر Google هذه الدورة التدريبية وهي ليست مرتبطة بها).

في ما يلي دالة مخصّصة بسيطة باسم 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;
}

إذا لم تكن تعرف كيفية كتابة JavaScript ولم يكن لديك الوقت لمعرفة ذلك، اطّلِع على متجر الإضافات لمحاولة معرفة ما إذا كان شخص آخر قد أنشأ الوظيفة المخصّصة التي تحتاجها.

إنشاء دالة مخصّصة

لكتابة دالة مخصّصة:

1. أنشئ أو افتح جدول بيانات في "جداول بيانات Google".
2. اختَر رمز القائمة الإضافات > Apps Script.
3. احذف أي رمز برمجي في محرِّر النصوص البرمجية. بالنسبة إلى الدالة DOUBLE أعلاه، ما عليك سوى نسخ الرمز ولصقه في محرِّر النصوص البرمجية.
4. في أعلى الصفحة، انقر على رمز الحفظ save.

يمكنك الآن استخدام الدالة المخصّصة.

الحصول على دالة مخصّصة من Google Workspace Marketplace

Google Workspace Marketplace يقدّم العديد من الدوال المخصّصة كإضافات لـ "جداول بيانات Google". لاستخدام هذه الإضافات أو استكشافها، اتّبِع الخطوات التالية:

1. أنشئ أو افتح جدول بيانات في "جداول بيانات Google".
2. في أعلى الصفحة، انقر على الإضافات > الحصول على إضافات.
3. بعد فتح Google Workspace Marketplace ، انقر على مربّع البحث في أعلى يسار الشاشة.
4. اكتب "دالة مخصّصة" واضغط على مفتاح Enter.
5. إذا عثرت على إضافة دالة مخصّصة تهمّك، انقر على تثبيت لتثبيتها.
6. قد يُعلمك مربّع حوار بأنّ الإضافة تتطلّب تفويضًا. إذا كان الأمر كذلك، اقرأ الإشعار بعناية، ثم انقر على سماح.
7. تصبح الإضافة متاحة في جدول البيانات. لاستخدام الإضافة في جدول بيانات مختلف، افتح جدول البيانات الآخر وانقر على الإضافات > إدارة الإضافات في أعلى الصفحة. ابحث عن الإضافة التي تريد استخدامها وانقر على رمز الخيارات more_vert > استخدام في هذا المستند.

استخدام دالة مخصّصة

بعد كتابة دالة مخصّصة أو تثبيتها من 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))، ستكون الوسيطة صفيفًا بدورَين من قيم الخلايا. على سبيل المثال، في لقطة الشاشة أدناه، تفسِّر Apps Script المَعلمات في =DOUBLE(A1:B2) على أنّها double([[1,3],[2,4]]). يُرجى العِلم أنّه يجب تعديل رمز DOUBLE من الأعلى لقبول صفيف كإدخال.

  
  

• يجب أن تكون وسيطات الدوال المخصّصة حتمية. وهذا يعني أنّه لا يُسمح باستخدام الدوالّ المضمّنة في جدول البيانات التي تُعرِض نتيجة مختلفة في كل مرة تتم فيها عملية الحسابات، مثل NOW() أو RAND()، كوسيطات للدالة المخصّصة. إذا حاولت دالة مخصّصة عرض قيمة استنادًا إلى إحدى هذه الدوال المضمّنة المتقلبة، ستعرِض Loading... بشكلٍ غير محدّد.

القيم المعروضة

يجب أن تعرِض كلّ دالة مخصّصة قيمة، على النحو التالي:

• إذا كانت الدالة المخصّصة تعرِض قيمة، يتم عرض القيمة في الخلية التي تمّت من خلالها استدعاء الدالة.
• إذا كانت دالة مخصّصة تُرجع صفيفًا ثنائي الأبعاد من القيم، تَفيض القيم إلى الخلايا المجاورة ما دامت هذه الخلايا فارغة. إذا كان ذلك سيؤدي إلى استبدال الصفيف لمحتوى الخلايا الحالي، ستؤدي الدالة المخصّصة إلى عرض خطأ بدلاً من ذلك. على سبيل المثال، اطّلِع على القسم المخصّص لموضوع تحسين الدوال المخصّصة.
• لا يمكن لدالة مخصّصة التأثير في الخلايا غير تلك التي تعرض قيمة فيها. بعبارة أخرى، لا يمكن لدالة مخصّصة تعديل خلايا عشوائية، بل فقط الخلايا التي يتمّ استدعاؤها منها والخلايا المجاورة لها. لتعديل خلايا عشوائية، استخدِم قائمة مخصّصة لتشغيل دالة بدلاً من ذلك.
• يجب أن يتم عرض نتيجة طلب دالة مخصّصة خلال 30 ثانية. وإذا لم يكن الأمر كذلك، تعرِض الخلية #ERROR! وتكون ملاحظة الخلية Exceeded maximum execution time (line 0)..

أنواع البيانات

تخزِّن "جداول بيانات Google" البيانات بتنسيقات مختلفة استنادًا إلى طبيعة البيانات. عند استخدام هذه القيم في الدوال المخصّصة، يتعامل Apps Script معها على أنّها نوع البيانات المناسب في JavaScript. في ما يلي الحالات التي يحدث فيها التباس عادةً:

• تصبح الأوقات والتواريخ في "جداول بيانات Google" عناصر Date في Apps Script. إذا كان جدول البيانات والملف النصي يستخدمان مناطق زمنية مختلفة (وهي مشكلة نادرة)، يجب أن تُعوض الدالة المخصّصة عن ذلك.
• تصبح قيم المدة في "جداول بيانات Google" أيضًا كائنات Date، ولكن قد يكون التعامل معها معقّدًا.
• تصبح قيم النسبة المئوية في "جداول بيانات Google" أرقامًا عشرية في Apps Script. على سبيل المثال، تصبح الخلية التي تحتوي على القيمة 10% هي 0.1 في Apps Script.

الإكمال التلقائي

تتيح "جداول بيانات Google" ميزة الإكمال التلقائي للدوال المخصّصة تمامًا كما هو الحال مع الدوال المضمّنة. أثناء كتابة اسم دالة في خلية، ستظهر لك قائمة بالدوال المضمّنة والمخصّصة التي تتطابق مع ما تُدخله.

ستظهر الدوالّ المخصّصة في هذه القائمة إذا كان نصها البرمجي يتضمّن علامة 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
اللغة
قفل	تعمل هذه الطريقة، ولكنها ليست مفيدة بشكل خاص في الدوالّ المخصّصة.
خرائط Google	يمكنه احتساب الاتجاهات، ولكن لا يمكنه عرض الخرائط.
المواقع	لا يحصل "getUserProperties()" إلا على خصائص صاحب جدول البيانات. لا يمكن لمحرِّري جداول البيانات ضبط سمات المستخدِم في دالة مخصّصة.
جدول بيانات	للقراءة فقط (يمكن استخدام معظم طرق get*()، ولكن ليس set*()). لا يمكن فتح جداول بيانات أخرى (SpreadsheetApp.openById() أو SpreadsheetApp.openByUrl()).
جلب عنوان URL
برامج الخدمات
XML

إذا ظهرت رسالة الخطأ You do not have permission to call X service. في وظيفتك المخصّصة، يعني ذلك أنّ الخدمة تتطلّب تفويض المستخدم، وبالتالي لا يمكن استخدامها في وظيفة مخصّصة.

لاستخدام خدمة غير تلك المُدرَجة أعلاه، أنشئ قائمة مخصّصة تعمل على تنفيذ دالة برمجة تطبيقات Google بدلاً من كتابة دالة مخصّصة. إنّ الدالة التي يتم تفعيلها من قائمة ستطلب من المستخدم الحصول على إذن إذا لزم الأمر، ويمكنها بالتالي استخدام جميع خدمات "برمجة تطبيقات Google".

المشاركة

تبدأ الدوال المخصّصة مرتبطة بجدول البيانات الذي تم إنشاؤها فيه. وهذا يعني أنّه لا يمكن استخدام دالة مخصّصة مكتوبة في جدول بيانات واحد في جداول بيانات أخرى ما لم تستخدم إحدى الخطوات التالية:

• انقر على الإضافات > برمجة التطبيقات لفتح محرِّر النصوص البرمجية، ثم انسخ ملف برمجي من جدول البيانات الأصلي والصقه في محرِّر النصوص البرمجية لجدول بيانات آخر.
• أنشئ نسخة من جدول البيانات الذي يحتوي على الدالة المخصّصة بالنقر على ملف > إنشاء نسخة. عند نسخ جدول بيانات، يتم أيضًا نسخ أي نصوص برمجية مرفقة به. يمكن لأي مستخدم لديه إذن الوصول إلى جدول البيانات نسخ الرمز البرمجي. (لا يمكن للمتعاونين الذين لديهم إذن بالاطّلاع فقط فتح محرِّر النصوص البرمجية في جدول البيانات الأصلي. ومع ذلك، عند إنشاء نسخة، يصبح مالك النسخة ويمكنه الاطّلاع على النص.)
• انشر النص البرمجي كـ إضافة محرِّر في "جداول بيانات Google".

التحسين

في كل مرة يتم فيها استخدام دالة مخصّصة في جدول بيانات، تُجري "جداول بيانات Google" طلبًا منفصلاً إلى خادم 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 في JavaScript لبرمجة DOUBLE بشكل تسلسلي في كل قيمة في الصفيف ثنائي الأبعاد للخلايا. ويعرض الإجراء صفيفًا ثنائي الأبعاد يحتوي على النتائج. بهذه الطريقة، يمكنك استدعاء 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;
}

يمكن تطبيق هذه الأساليب على أي دالة مخصّصة تقريبًا يتم استخدامها بشكل متكرّر في جدول بيانات، على الرغم من أنّ تفاصيل التنفيذ ستختلف حسب سلوك الدالة.