الدوال المخصصة في جداول بيانات 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 وليس لديك وقت للتعلّم، يمكنك مراجعة متجر الإضافات لمعرفة ما إذا كان مستخدم آخر قد أنشأ الدالة المخصصة التي تريدها أم لا.

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

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

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

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

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

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


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

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

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

  • إذا أرجعت دالة مخصصة قيمة، فسيتم عرض القيمة في الخلية التي تم استدعاء الدالة منها.
  • إذا عرضت دالة مخصّصة صفيفًا من القيم ثنائية الأبعاد، ستتجاوز القيم الخلايا المجاورة ما دامت تلك الخلايا فارغة. إذا كان ذلك يتسبب في أن يستبدل الصفيف محتويات الخلية الموجودة، فإن الدالة المخصصة ستعرض خطأ بدلاً من ذلك. للحصول على مثال، راجِع القسم الذي يتناول تحسين الدوال المخصّصة.
  • لا يمكن للدالة المخصصة أن تؤثر على الخلايا غير تلك التي تُرجع قيمة إليها. بمعنى آخر، لا يمكن للدالة المخصصة تحرير الخلايا العشوائية، بل فقط الخلايا التي تم استدعاؤها منها والخلايا المجاورة لها. لتعديل الخلايا العشوائية، استخدِم قائمة مخصّصة لتشغيل دالة بدلاً من ذلك.
  • يجب أن يتم إرجاع استدعاء الدالة المخصّصة في غضون 30 ثانية. وإذا لم يحدث ذلك، ستعرض الخلية رسالة الخطأ التالية: Internal error executing the custom function.

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

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

  • تصبح الأوقات والتواريخ في "جداول بيانات Google" عناصر التاريخ في "برمجة تطبيقات 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 The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

متقدّمة

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

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

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

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

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

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

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

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