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

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

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

يتم إنشاء الدوال المخصّصة باستخدام لغة 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 Sheets.
  2. حدد عنصر القائمة الإضافات > برمجة التطبيقات:
  3. احذف أي رمز في أداة تعديل النصوص البرمجية. بالنسبة للدالة DOUBLE أعلاه، ما عليك سوى انسخ الرمز والصقه في أداة تعديل النصوص البرمجية.
  4. في أعلى الصفحة، انقر على "حفظ" .

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

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

يقدم Google Workspace Marketplace العديد من خيارات يعمل كـ إضافات لـ "جداول بيانات Google". لاستخدام هذه الإضافات أو استكشافها:

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

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

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

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

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

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

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

الخدمات المتوافقة ملاحظات
ذاكرة التخزين المؤقت يعمل، ولكنه غير مفيد بشكل خاص في الدوال المخصصة
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 Sheets مكالمة منفصلة لخادم "برمجة تطبيقات 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;
}

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