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

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

للاطّلاع على أمثلة للدوال المخصّصة، راجِع البرامج التعليمية التالية:

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

يتم إنشاء الدوال المخصّصة باستخدام JavaScript العادية. إذا كنت مبتدئًا في JavaScript، يقدّم موقع 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;
}

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

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

لكتابة دالة مخصّصة، اتّبِع الخطوات التالية:

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

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

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

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

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

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

بعد كتابة دالة مخصّصة أو تثبيت دالة من Google Workspace Marketplace، يمكنك استخدامها تمامًا مثل دالة مضمّنة:

  1. انقر على الخلية التي تريد استخدام الدالة فيها.
  2. اكتب علامة يساوي (=) متبوعة باسم الدالة وأي قيمة إدخال، مثل =DOUBLE(A1)، ثم اضغط على Enter.
  3. تعرض الخلية الرمز Loading... للحظة، ثم تعرض النتيجة.

إرشادات حول الدوال المخصّصة

قبل كتابة دالة مخصّصة، هناك بعض الإرشادات التي يجب معرفتها.

تسمية الدوال

بالإضافة إلى الاصطلاحات العادية لتسمية دوال JavaScript، يجب الانتباه إلى ما يلي:

  • يجب أن يكون اسم الدالة المخصّصة مختلفًا عن أسماء الدوال المضمّنة، مثل SUM().
  • لا يمكن أن ينتهي اسم دالة مخصّصة بعلامة تسطير سفلي (_)، لأنّ هذه العلامة تشير إلى دالة خاصة في "برمجة تطبيقات Google".
  • يجب تعريف اسم الدالة المخصّصة باستخدام البنية function myFunction()، وليس var myFunction = new Function().
  • لا يهم استخدام الأحرف الكبيرة أو الصغيرة، مع أنّ أسماء دوال جداول البيانات تكون عادةً بأحرف كبيرة.

الوسيطات

مثل الدالة المضمّنة، يمكن أن تستخدم الدالة المخصّصة وسيطات كقيم إدخال:

  • إذا استدعيت الدالة مع مرجع إلى خلية واحدة كوسيطة (مثل =DOUBLE(A1))، تكون الوسيطة هي قيمة الخلية.

  • إذا استدعيت الدالة مع مرجع إلى نطاق من الخلايا كوسيطة (مثل =DOUBLE(A1:B10))، تكون الوسيطة عبارة عن مصفوفة ثنائية الأبعاد لقيم الخلايا. على سبيل المثال، في لقطة الشاشة التالية، تفسّر برمجة تطبيقات الوسيطات في =DOUBLE(A1:B2) على أنّها double([[1,3],[2,4]]). يُرجى العِلم أنّه يجب تعديل رمز المثال الخاص بـ DOUBLE الموضّح سابقًا لكي يقبل مصفوفة كإدخال.


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

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

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

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

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

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

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

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

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

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

تظهر الدوال المخصّصة في هذه القائمة إذا كان النص البرمجي يتضمّن علامة JSDoc @customfunction، كما هو الحال في مثال DOUBLE().

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

إعدادات متقدمة

يتناول هذا القسم مواضيع متقدّمة حول الدوال المخصّصة.

استخدام خدمات "برمجة تطبيقات Google"

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

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

الخدمات المتوافقة ملاحظات
ذاكرة التخزين المؤقت تعمل، ولكنّها ليست مفيدة بشكل خاص في الدوالّ المخصّصة
HTML يمكنه إنشاء HTML، ولكن لا يمكنه عرضه (غير مفيد في حالات نادرة)
JDBC
اللغة
قفل تعمل، ولكنّها ليست مفيدة بشكل خاص في الدوالّ المخصّصة
خرائط Google يمكنه حساب الاتجاهات، ولكن لا يمكنه عرض الخرائط
المواقع لا يحصل getUserProperties() إلا على خصائص مالك جدول البيانات. لا يمكن لمحرّري جداول البيانات ضبط خصائص المستخدمين في دالة مخصّصة.
جدول بيانات إذن القراءة فقط (يمكن استخدام معظم طرق get*()، ولكن ليس set*()).
لا يمكن فتح جداول بيانات أخرى (SpreadsheetApp.openById() أو SpreadsheetApp.openByUrl()).
استرجاع عنوان URL الوصول إلى المراجع على الويب من خلال جلب عناوين URL
برامج الخدمات
XML

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

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

مشاركة الدوال المخصّصة

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

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

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

تحسين

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

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

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

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

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