الدوال المخصصة في "جداول بيانات 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().
  • لا يمكن أن ينتهي اسم الدالة المخصصة بشرطة سفلية (_)، والتي تشير إلى دالة خاصة في "برمجة التطبيقات".
  • يجب الإعلان عن اسم الدالة المخصّصة باستخدام البنية function myFunction()، وليس var myFunction = new Function().
  • لا تهم الكتابة بالأحرف الكبيرة، على الرغم من أن أسماء دوال جدول البيانات عادةً ما تكون بأحرف كبيرة.

الوسيطات

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

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

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


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

عرض القيم

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

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

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

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

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

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

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

ستظهر الدوال المخصصة في هذه القائمة إذا كان نصها البرمجي يتضمن علامة @customfunctionJsDoc، كما في مثال 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 لترجمة عبارة إنجليزية إلى الإسبانية.

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

الخدمات المتوافقة ملاحظات
ذاكرة التخزين المؤقت تعمل، ولكنها ليست مفيدة بشكل خاص في الدوال المخصصة
HTML يمكنه إنشاء HTML، ولكن لا يمكنه عرضه (نادرًا ما يكون مفيدًا)
JDBC
اللغة
قفل تعمل، ولكنها ليست مفيدة بشكل خاص في الدوال المخصصة
الخرائط إمكانية حساب الاتجاهات، ولكن لا يمكن عرض الخرائط
المواقع لا يحصل getUserProperties() إلا على سمات مالك جدول البيانات. لا يمكن لمحرّري جداول البيانات ضبط خصائص المستخدمين في دالة مخصّصة.
جدول البيانات للقراءة فقط (يمكن استخدام معظم طُرق get*()، ولكن ليس set*()).
لا يمكن فتح جداول البيانات الأخرى (SpreadsheetApp.openById() أو SpreadsheetApp.openByUrl()).
جلب عنوان 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 لاستدعاء DOUBLE بشكل متكرر على كل قيمة في صفيف الخلايا ثنائي الأبعاد. حيث تقوم بإرجاع صفيف ثنائي الأبعاد يحتوي على النتائج. بهذه الطريقة، يمكنك استدعاء 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;
}

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