الدوال المخصصة في جداول بيانات 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. انقر على عنصر القائمة الإضافات > برمجة تطبيقات Google.
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 ثانية. إذا لم يكن كذلك، تعرض الخلية #ERROR! وتكون ملاحظة الخلية Exceeded maximum execution time (line 0).

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

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

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

يمكن للدوال المخصّصة استدعاء بعض خدمات "برمجة تطبيقات 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" بدلاً من كتابة دالة مخصّصة. سيطلب من المستخدم منح الإذن إذا لزم الأمر، ويمكنه بالتالي استخدام جميع خدمات Apps Script.

المشاركة

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

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

تتضمّن جميع البرامج النصية المرتبطة بالحاويات قوائم الوصول نفسها التي تتضمّنها الحاويات. وهذا يعني أنّ أي مستخدم لديه إذن بتعديل جدول البيانات يمكنه تعديل أي رمز Apps Script مرفق به.

التحسين

في كل مرة يتم فيها استخدام دالة مخصّصة في جدول بيانات، ترسل "جداول بيانات 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).

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

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