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

• تصبح الأوقات والتواريخ في "جداول بيانات Google" عناصر Date في 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 Apps Script

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

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

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

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

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

المشاركة

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

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

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

التحسين

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

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

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