العمل مع البيانات في جداول بيانات جوجل من خلال Apps Script

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

وسوف نستمر في العمل مع فئات SpreadsheetApp، و Spreadsheet، و Sheet، و Range التي تعرفنا عليها في المقال السابق من هذا الدليل.

ماذا ستتعلم

1. كيفية استيراد البيانات من جدول بيانات شخصي أو مشترك في Google Drive.
2. كيفية إنشاء قائمة مخصصة باستخدام الدالة ()onOpen.
3. كيفية تحليل ومعالجة قيم سلسلة البيانات في خلايا ورقة جدول بيانات جوجل.
4. كيفية سحب بيانات كائن JSON ومعالجتها من مصدر API عام.

ماذا ستحتاج

• فهم موضوعات Apps Script الأساسية التي استكشفناها في المقاين السابقين من هذا الدليل.
• الإلمام الأساسي بمحرر الشيفرات البرمجية Apps Script.
• الإلمام الأساسي بجداول بيانات Google.
• الإلمام الأساسي بلغة البرمجة JavaScript وصنف 'String' الخاص به.

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

الإعداد للعمل

تتطلب التدريبات في هذا المقال وجود جدولين بيانات للعمل من خلالها.

• فالجدول الأول عبارة عن فهرس لمكتبة تحتوي على بيانات اسم الكتاب واسم المؤلف والرقم العالمي الموحد للكتاب، وستكون وظيفة هذا الجدول كقاعدة بيانات فقط لاستيراد هذه البيانات منه، لا تقلق لقد وفرنا لك هذا الجدول بالفعل ويمكنك الحصول عليه وتنزيله من المرفقات الخاصة بهذا المقال، ثم إعادة رفعه على حسابك بجوجل درايف بأحد الطريقتان اللتان ذكرناهما في المقال الأول من هذه السلسلة.

• والجدول الثاني سيكون جدول فارغ ستقوم أنت بإنشائه، وستكون وظيفة هذا الجدول الاتصال بجدول قاعدة البيانات لاستيراد البيانات التي بداخله ثم تنفيذ التدريبات الخاصة بهذا المقال عليه.

ولإنشاء جدول البيانات هذا اتبع الخطوات التالية:

أولاً، إنشاء جدول بيانات في Google Drive، ويمكنك عمل ذلك من واجهة جوجل درايف عن طريق النقر على زر "+ جديد" أعلى يمين النافذة، ثم النقر على "جداول بيانات Google"، ويؤدي هذا الإجراء إلى إنشاء وفتح جدول البيانات الجديد، وحفظه في مجلد Drive الخاص بك.

ثانيًا، انقر على عنوان جدول البيانات وغيره من "جدول بيانات بدون عنوان" إلى "معالجة البيانات والقوائم المخصصة"، يجب أن تبدو الورقة الخاصة بك كما يلي:

ثالثًا، افتح محرر النصوص البرمجية لـ Apps Script بالنقر على "الإضافات" ثم اختيار "محرر تطبيقات جوجل". رابعًا، انقر على عنوان مشروع برمجة التطبيقات وغيره من "مشروع بلا عنوان" إلى "Data Manipulation and Custom Menus"، ثم انقر على زر "إعادة تسمية" لحفظ تغيير العنوان.

الآن بحصولك على جدول بيانات ومشروع Apps Script فارغين فأنت على استعداد لبدء المقال، انتقل إلى القسم التالي من هذا المقال للبدء في التعرف على القوائم المخصصة.

اقتباس

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

نظرة عامة: استيراد البيانات باستخدام عنصر قائمة مخصص

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

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

ويتم تعيين عناصر القائمة المخصصة في دالة التشغيل البسيطة ()onOpen والتي ستتعرف عليها في القسم التالي.

الدالة ()onOpen

توفر المشغلات البسيطة في Apps Script طريقة لتشغيل شيفرة Apps Script معينة استجابةً لظروف أو أحداث معينة، وعند إنشاء مشغل فإنك تحدد الحدث الذي يتسبب في إطلاق هذا المشغل وتوفر له دالة Apps Script التي يتم تشغيلها لهذا الحدث.

فالدالة ()onOpen مثال على مشغل بسيط من السهل إعداده، كل ما عليك فعله هو كتابة دالة في Apps Script باسم ()onOpen ليعمل Apps Script على تشغيلها في كل مرة تفتح فيها أو تُعيد تحميل جدول البيانات المرتبط بهذه الدالة:

/**
دالة خاصة يتم تشغيلها عند فتح جدول البيانات لأول مرة أو إعادة تحميله *
تُستخدم ()onOpen لإضافة عناصر قائمة مخصصة إلى جدول البيانات *
*/
function onOpen() {
 /* ... */ 
}

التطبيق

دعنا نبدأ في إنشاء قائمة مخصصة، استبدل الشيفرة البرمجية في مشروع Apps Script الذي أسميناه "Data Manipulation and Custom Menus" بما يلي:

/**
دالة خاصة يتم تشغيلها عند فتح جدول البيانات لأول مرة أو إعادة تحميله *
تُستخدم ()onOpen لإضافة عناصر قائمة مخصصة إلى جدول البيانات *
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('قائمة الكتب')
    .addItem('تحميل قائمة الكتب', 'loadBookList')
    .addToUi();
}

احفظ مشروع نصك البرمجي.

مراجعة الشيفرة البرمجية

دعنا نراجع هذه الشيفرة البرمجية لفهم كيفية عمل الدالة ()onOpen.

يستخدم السطر الأول التابع()getUi للحصول على كائن "Ui" يمثل واجهة المستخدم لجدول البيانات النشط الذي يرتبط به هذا النص البرمجي أو السكربت.

تعمل الأسطر الثلاثة التالية على إنشاء قائمة مخصصة باسم "قائمة الكتب" ثم إضافة عنصر داخل هذه القائمة باسم "تحميل قائمة الكتب"، ثم إضافة هذه القائمة المخصصة إلى واجهة جدول البيانات، ونفذنا ذلك باستخدام ثلاث توابع متتالية على النحو التالي:

• أولاً: التابع ()createMenu والذي يأخذ معامل واحد فقط وهو "اسم القائمة المخصصة".
• ثانيًا: التابع ()addItem والذي يأخذ معاملين، الأول "اسم العنصر داخل القائمة المخصصة"، والثاني "اسم الدالة بالإنجليزية".
• ثالثًا: التابع ()addToUi وهو لا يأخذ أي معاملات.

ينشئ التابع ()addItem اتصالًا بين تسمية العنصر الموجود داخل القائمة المخصصة (تحميل قائمة الكتب) ودالة Apps Script المرتبطة بهذا العنصر loadBookList (التي لم نوفرها بعد) لتشغيلها.

النتائج

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

5. من خلال النقر على "قائمة الكتب"، يمكنك رؤية القائمة الفرعية "تحميل قائمة الكتب".

اقتباس

تنويه: يؤدي إعادة تحميل جدول البيانات عادةً إلى إغلاق علامة التبويب الخاصة بمحرر النص البرمجي.

في القسم التالي من هذا المقال سوف ننشىء الشيفرة البرمجية للدالة ()loadBookList التي تقدم طريقة واحدة يمكنك من خلالها التفاعل مع البيانات في Apps Script، وهي: قراءة البيانات من جداول البيانات الأخرى.

استيراد بيانات جدول البيانات

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

ويمكنك إصلاح هذا الخطأ عن طريق كتابة الشيفرة البرمجية للدالة ()loadBookList.

التطبيق

الآن أنت في حاجة إلى أن يملأ عنصر القائمة الجديد جدول البيانات الحالي ببيانات لتتمكن من العمل معها، لذلك يجب عليك توفير الشيفرة البرمجية للدالة ()loadBookList لتتمكن من تنفيذ قراءة بيانات الكتب من جدول بيانات آخر، ثم نسخها في هذا الجدول، افتح المشروع الذي أنشأناه في Apps Script باسم "Data Manipulation and Custom Menus"، ثم أضف الشيفرة البرمجية التالية بعد الدالة ()onOpen:

/** 
 دالة خاصة يتم تشغيلها عند الضغط على القائمة المخصصة "تحميل الكتب" *
 تنشئ قائمة كتب نموذجية مقدمة من فهرس مكتبة وفرناها لك *
 */
function loadBookList(){
  هذا السطر يجلب الورقة النشطة //
  var sheet = SpreadsheetApp.getActiveSheet();

الحصول على البيانات من جدول بيانات مختلف من Google Drive //
 باستخدام معرف جدول البيانات // 
  var bookSS = SpreadsheetApp.openById(
    "spreadsheet-id" 
  );

 الحصول على الورقة ونطاق البيانات وقيم جدول البيانات //
  المخزنة في المتغير bookSS //
  var bookSheet = bookSS.getSheetByName("فهرس المكتبة");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  إضافة هذه القيم إلى الورقة النشطة في جدول البيانات الحالي //
  سوف يؤدي هذا إلى الكتابة فوق أية قيم موجودة بالفعل //
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);

  إعادة تسمية الورقة الوجهة //
  وتغيير حجم أعمدة البيانات لتسهيل القراءة //
  sheet.setName("قائمة الكتب");
  sheet.autoResizeColumns(1, 3);
}

احفظ مشروع النص البرمجي الخاص بك.

انتبه إلى تبديل قيمة spreadsheet-id الذي يأخذه التابع openById()‎ بمعرّف جدول البيانات المرفق "فهرس المكتبة" بعد رفعه على حسابك بجوجل درايف، وقد شرحنا في المقال الثاني من هذه السلسلة كيفية جلب هذا المعرِّف.

مراجعة الشيفرة البرمجية

إذًا كيف تعمل هذه الدالة؟ تستخدم الدالة ()loadBookList توابع من أصناف Spreadsheet و Sheet و Range التي تعرفنا عليها في المقال السابق، فمع وضع هذه المفاهيم في الاعتبار يمكنك تقسيم الشيفرة البرمجية للدالة ()loadBookList إلى الأقسام الأربعة التالية:

1. تحديد الورقة الوجهة

يُستخدم السطر الأول ()SpreadsheetApp.getActiveSheet للحصول على مرجع إلى كائن الورقة الحالية، ثم تخزينه في المتغير sheet، وهذه هي الورقة التي سيتم نسخ البيانات إليها.

2. تحديد مصدر البيانات

تحدد الأسطر القليلة التالية أربعة متغيرات تشير إلى بيانات المصدر التي سوف تقوم باستردادها: يخزن المتغير bookSS مرجعًا لجدول البيانات الذي تقرأ الشيفرة البرمجية البيانات منه، فتعثر الشيفرة البرمجية على جدول البيانات من خلال مُعرف جدول البيانات الخاص به، وفي هذا المثال قدمنا مُعرف جدول بيانات للقراءة منه، وفتحنا جدول البيانات من خلال استخدام التابع (SpreadsheetApp.openById(id.

يخزن المتغير bookSheet مرجعًا إلى ورقة داخل المتغير bookSS تحتوي على البيانات التي تريدها، وتحدد الشيفرة البرمجية الورقة المراد قراءتها من خلال اسمها، وهو في مثالنا "فهرس المكتبة".

يخزن المتغير bookRange مرجعًا لمجموعة من البيانات في المتغير bookSheet، ويعمل التابع ()Sheet.getDataRange على إرجاع النطاق الذي يحتوي على جميع الخلايا غير الفارغة في الورقة، إنها طريقة سهلة للتأكد من حصولك على نطاق يغطي جميع البيانات في ورقة دون تضمين صفوف وأعمدة فارغة.

المتغير bookListValues عبارة عن مصفوفة ثنائية تحتوي على جميع القيم المأخوذة من الخلايا الموجودة في المتغير bookRange، وينشئ التابع ()Range.getValues هذه المصفوفة بقراءة البيانات من الورقة المصدر.

3. نسخ البيانات من المصدر إلى الوجهة

ينسخ القسم التالي من الشيفرة البرمجية بيانات المتغير bookListValues في الورقة، ثم يعيد تسمية الورقة أيضًا.

يُستخدم التابع ()Sheet.getRange وهو يأخذ أربع معاملات لتحديد مكان نسخ البيانات في الورقة وهم: الأول "صف البداية" والثاني "عمود البداية" والثالث "عدد الصفوف" والرابع "عدد الأعمدة".

يُستخدم التابعان ()Range.getHeight و ()Range.getWidth لقياس حجم البيانات وتحديد نطاق الوجهة بنفس الأبعاد.

يعمل التابع ()Range.setValues على نسخ قيم المصفوفة الثنائية الخاصة بالمتغير bookListValues في النطاق الوجهة، مما يؤدي إلى الكتابة فوق أي بيانات موجودة بالفعل.

4. تنسيق الورقة الوجهة

يُستخدم التابع ()Sheet.setName لتغيير اسم الورقة الوجهة، ونفعل ذلك بكتابة الاسم الذي نريده كمعامل للتابع، وفي مثالنا غيرنا الاسم إلى "قائمة الكتب".

ويستخدم السطر الأخير التابع ()Sheet.autoResizeColumns الذي يأخذ معاملين، الأول "عمود البداية" والثاني "عدد الأعمدة"، وذلك لتغيير حجم الأعمدة الثلاثة الأولى في ورقة الوجهة، مما يتيح لك قراءة البيانات الجديدة بسهولة أكبر.

النتائج

1. اذهب إلى جداول بيانات جوجل الذي نتدرب عليه "معالجة البيانات والقوائم المخصصة" ثم أعد تحميل جدول البيانات.
2. أعد فتح محرر النص البرمجي مرةً أخرى.
3. بعد إعادة تحميل جدول البيانات، انقر على القائمة "قائمة الكتب" في شريط القوائم، ثم اختر منها القائمة الفرعية "تحميل قائمة الكتب".
4. سوف تعمل الدالة ()loadBookList على جلب البيانات بنجاح.

تهانينا، أصبح لديك الآن ورقة بها قائمة بعناوين الكتب والمؤلفين ودور النشر، في القسم التالي من هذا المقال ستتعلم كيفية تعديل البيانات وتحديثها في "قائمة الكتاب" هذه باستخدام معالجة السلاسل النصية والقوائم المخصصة.

نظرة عامة: ترتيب البيانات داخل جدول البيانات

لديك الآن معلومات الكتاب في الورقة الخاصة بك، فيشير كل صف إلى كتاب معين، ويسرد عنوانه ومؤلفه ودار النشر التي نشرته في أعمدة منفصلة، ومع ذلك يمكنك أيضًا رؤية بعض المشكلات في هذه البيانات الأولية كالتالي:

1. بالنسبة لبعض الصفوف وضع عنوان الكتاب والمؤلف معًا في عمود "اسم الكتاب" مرتبطين إما بفاصلة أو سلسلة نصية "تأليف".
2. تفتقد بعض الصفوف إلى عنوان الكتاب أو مؤلفه.

في الأقسام التالية من هذا المقال سوف نعمل على تصحيح هذه المشكلات عن طريق ترتيب البيانات، فسوف نعمل أولاً على إنشاء دوال تقرأ عمود العنوان ثم تقسم النص عند العثور على فاصلة أو كلمة "تأليف"، ثم وضع العنوان واسم المؤلف في الأعمدة الصحيحة.

وثانيًا سنكتب شيفرة برمجية تبحث تلقائيًا عن معلومات الكتاب المفقودة باستخدام واجهة برمجة تطبيقات خارجية API، لتضيف هذه المعلومات إلى جدولك.

إضافة عناصر القائمة

أنت في حاجة إلى إنشاء ثلاثة عناصر قائمة للتحكم في عمليات ترتيب البيانات التي سوف تنفذها.

التطبيق

دعنا نحدث الدالة ()onOpen لتضمين العناصر الإضافية التي ستحتاجها إلى القائمة المخصصة "قائمة الكتب"، فقط اتبع الخطوات التالية:

أولًا، في مشروع Apps Script الذي باسم "Data Manipulation and Custom Menus" حدث الشيفرة البرمجية للدالة ()onOpen لتصبح مطابقة لما يلي:

/**
دالة خاصة يتم تشغيلها عند فتح جدول البيانات لأول مرة أو إعادة تحميله *
تُستخدم ()onOpen لإضافة عناصر قائمة مخصصة إلى جدول البيانات *
*/

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('قائمة الكتب')
    .addItem('تحميل قائمة الكتب', 'loadBookList')
    .addSeparator()
    .addItem(
      'افصل العنوان عن المؤلف عند أول فاصلة', 'splitAtFirstComma')
    .addItem(
      'افصل العنوان عن المؤلف عند كلمة "تأليف"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'ملء الخلايا الفارغة بالعنوان والمؤلف', 'fillInTheBlanks')
    .addToUi();
}

احفظ مشروع نصك البرمجي.

ثانيًا، في محرر النص البرمجي Apps Script حدد اسم الدالة "onOpen" من قائمة الدوال، ثم انقر على زر "Run"، سوف يعمل هذا الإجراء على تشغيل الدالة ()onOpen لإعادة إنشاء قائمة جدول البيانات، وذلك حتى لا تضطر إلى إعادة تحميل جدول البيانات.

في هذه الشيفرة البرمجية الجديدة تنشئ التابع ()Menu.addSeparator لعمل فاصل أفقي في القائمة المخصصة للحفاظ على مجموعات عناصر القائمة ذات الصلة منظمة، ثم إضافة عناصر القائمة الجديدة تحتها.

اقتباس

تنويه: تظهر عناصر القائمة المخصصة في شريط قوائم جدول البيانات بنفس الترتيب الذي تضيفه في الشيفرة البرمجية للدالة ()onOpen.

النتائج

اذهب إلى جداول بيانات جوجل الذي نتدرب عليه "معالجة البيانات والقوائم المخصصة"، ثم انقر على القائمة المخصصة "قائمة الكتب" من شريط القوائم لعرض عناصر القائمة المخصصة الجديدة.

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

تقسيم النص على محدد الفاصلة

تحتوي مجموعة البيانات التي جلبتها في جدول البيانات على بضع خلايا دُمج فيها المؤلف والعنوان بطريقة غير صحيحة في خلية واحدة باستخدام فاصلة.

يُعد تقسيم السلاسل النصية إلى أعمدة منفصلة مهمة شائعة في جدول البيانات، وتوفر جداول بيانات جوجل الدالة ()SPLIT التي تقسم السلاسل النصية إلى أعمدة، ومع ذلك غالبًا ما تواجه مجموعات البيانات مشكلات لا يمكن حلها بسهولة باستخدام الدوال المضمنة في جداول البيانات، وفي مثل هذه الحالات يمكنك كتابة شيفرة برمجية في Apps Script لتنفيذ العمليات المعقدة اللازمة لتنظيم بياناتك.

ابدأ في تنظيم بياناتك عن طريق تنفيذ الدالة التي تسمى ()splitAtFirstComma، فهي تفصل بين المؤلف والعنوان ثم تضعهم في خلايا كل منهما عند العثور على الفاصلة.

ويجب أن تتخذ الدالة ()splitAtFirstComma الخطوات التالية:

1. الحصول على النطاق الذي يمثل الخلايا المحددة حاليًا.
2. التحقق مما إذا كانت الخلايا الموجودة في النطاق تحتوي على فاصلة أم لا.
3. عند العثور على الفاصلات تُقسم الدالة السلسلة النصية إلى سلسلتين فرعيتين فقط في موقع الفاصلة الأولى هكذا: [العنوان]، [المؤلف] في حالتنا.
4. تعيين السلاسل الفرعية كمحتويات جديدة لخلية العنوان وخلية المؤلف.

التطبيق

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

/**
 دالة خاصة يتم تشغيلها عند الضغط على القائمة المخصصة "افصل العنوان عن المؤلف عند أول فاصلة" *
 عن طريق تقسيم عمود العنوان عند الفاصلة الأولى إن وجدت *
 */
function splitAtFirstComma(){
  الحصول على النطاق النشط (المحدد حاليًا) //
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

 الحصول على القيم الحالية لخلايا عمود العنوان المحدد //
  هذه مصفوفة ثنائية الاتجاه //
  var titleAuthorValues = titleAuthorRange.getValues();

  تحديث القيم حيث توجد الفواصل //
  يفترض أن وجود الفاصلة يشير إلى نمط "العنوان، الفاصلة، المؤلف" //
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf("، ");

    if(indexOfFirstComma >= 0){
      في حالة العثور على فاصلة //
      يتم تقسيم القيم وتحديثها بالمصفوفة //
      var titlesAndAuthors = titleAuthorValues[row][0];

      تحديث قيمة العنوان بالمصفوفة //
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      تحديث قيمة المؤلف بالمصفوفة //
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

 وضع القيم المحدثة مرة أخرى في جدول البيانات //
  titleAuthorRange.setValues(titleAuthorValues);
}

احفظ بعدها مشروع النص البرمجي الخاص بك.

مراجعة الشيفرة البرمجية

دعنا نراجع الشيفرة البرمجية الجديدة التي تتكون من ثلاثة أقسام رئيسية:

1. استرجاع قيم العنوان المميزة

تحدد الأسطر الثلاثة الأولى ثلاثة متغيرات تشير إلى البيانات الحالية في الورقة: يمثل المتغير activeRange النطاق الذي حدده المستخدم حاليًا عند استدعاء الدالة ()splitAtFirstComma.

يمثل المتغير titleAuthorRange نطاقًا جديدًا يغطي نفس الخلايا مثل المتغير activeRange، ولكنه يتضمن أيضًا عمودًا إضافيًا إلى اليسار، وتحتاج الشيفرة البرمجية إلى هذا النطاق الموسع لأنه يحتاج إلى مكان لوضع المؤلف الذي يعثر عليه في عمود العنوان.

يمثل المتغير titleAuthorValues مصفوفة ثنائية من البيانات المستخرجة من المتغير titleAuthorRange باستخدام التابع ()Range.getValues.

2. فحص كل عنوان وتقسيم النص عند أول فاصلة يُعثر عليها

يفحص القسم التالي القيم الموجودة في المتغير titleAuthorValues للعثور على الفواصل، ولتنفيذ ذلك يستخدم حلقة التكرار For بلغة البرمجة JavaScript لفحص كل القيم في العمود الأول من المتغير titleAuthorValues، وعندما يعثر على سلسلة فرعية فاصلة (" ،") باستخدام التابع ()indexOf من لغة JavaScript، تعمل الشيفرة البرمجية على تنفيذ ما يلي:

1. نسخ قيمة السلسلة النصية للخلية إلى المتغير titleAndAuthors.
2. تحديد موقع الفاصلة باستخدام التابع ()indexOf من لغة JavaScript.
3. استدعاء التابع ()slice من لغة JavaScript مرتين للحصول على السلسلة الفرعية قبل الفاصلة المحدِّد والسلسلة الفرعية بعد الفاصلة المحدِّد.
4. تُنسخ السلاسل الفرعية مرة أخرى إلى المصفوفة الثنائية داخل المتغير titleAuthorValues، ثم الكتابة فوق القيم الموجودة في هذا الموضع.

اقتباس

تنويه: عندما لا تعثر الشيفرة البرمجية على فاصلة، فإنها تترك البيانات الموجودة في الصف دون تغيير.

3. نسخ القيم الجديدة مرة أخرى إلى الورقة

بمجرد فحص جميع قيم خلية العنوان تنسخ المصفوفة المحدثة titleAuthorValues مرة أخرى إلى جدول البيانات باستخدام التابع ()Range.setValues.

النتائج

يمكنك الآن رؤية تأثيرات الدالة ()splitAtFirstCommaوهي تعمل، فقط حدد أحد الخلايا التي يوجد بها عنوان الكتاب واسم المؤلف مفصول بينهما بفاصلة، ثم اذهب إلى القائمة المخصصة "قائمة الكتب" واختر منها القائمة الفرعية "افصل العنوان عن المؤلف عند أول فاصلة" ومن المفترض أن تكون النتيجة لديك كما في الصورة التالية: تحديد خلية واحدة

تحديد عدة خلايا:

اقتباس

تنويه: بدلًا من استخدام هذه الدالة مع خلية منفردة أو عدة خلايا، يمكنك استخدامها مع العمود بالكامل مرة واحدة لتوفير الوقت والمجهود.

تهانينا، لقد أنشأت الآن دالة في Apps Script تعالج البيانات في جداول البيانات، في القسم التالي سنعمل على تنفيذ دالة التقسيم الثانية.

تقسيم النص على المحدد "تأليف"

بالنظر إلى البيانات الأصلية بالجدول، يمكنك رؤية مشكلة أخرى في هذه البيانات وهي اجتماع عنوان الكتاب واسم المؤلف في نفس الخلية ويفصل بينهما كملة "تأليف" هكذا: [العنوان] تأليف [المؤلف].

التطبيق

يمكنك حل هذه المشكلة باستخدام نفس الأسلوب الذي انتهجته في القسم السابق من هذا المقال، فالدالة ()splitAtLastBy لها وظيفة مماثلة للدالة ()splitAtFirstComma الاختلاف الحقيقي الوحيد هو أنها تبحث عن نمط نص مختلف، فقط نفذ هذه الدالة من خلال إضافة ما يلي في نهاية مشروع النص البرمجي في محرر Apps Script:

/**
 دالة خاصة يتم تشغيلها عند الضغط على القائمة المخصصة "افصل العنوان عن المؤلف عند كلمة تأليف" *
 عن طريق تقسيم عمود العنوان عند كلمة "تأليف" الأخيرة إن وجدت *
 */

function splitAtLastBy(){
  احصل على النطاق النشط (المحدد حاليًا) //
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  الحصول على القيم الحالية لخلايا عمود العنوان المحدد //
  هذه مصفوفة ثنائية الاتجاه //
  var titleAuthorValues = titleAuthorRange.getValues();

 تحديث القيم حيث توجد السلسلة النصية "تأليف" //
  يفترض أن وجود كلمة "تأليف" يشير إلى نمط "العنوان، تأليف، المؤلف" //
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" تأليف ");

    if(indexOfLastBy >= 0){
      في حالة العثور على كلمة "تأليف" //
      يتم تقسيم القيم وتحديثها بالمصفوفة //
      var titlesAndAuthors = titleAuthorValues[row][0];

      تحديث قيمة العنوان بالمصفوفة //
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);

      تحديث قيمة المؤلف بالمصفوفة //
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 7);
    }
  }

  وضع القيم المحدثة مرة أخرى في جدول البيانات //
  titleAuthorRange.setValues(titleAuthorValues);
}

احفظ مشروع النص البرمجي الخاص بك.

مراجعة الشيفرة البرمجية

هناك بعض الاختلافات الرئيسية بين هذا الشيفرة البرمجية لهذه الدالة ودالة ()splitAtFirstComma كما يلي:

أولاً، نحن نبحث عن السلسلة النصية "تأليف" كمحدد سلسلة بدلاً من الفاصلة "،".

ثانيًا، هنا نستخدم التابع ()lastindexOf من لغة JavaScript. بدلاً من التابع ()indexOf، وهذا يعني أنه إذا كانت هناك عدة سلاسل نصية "تأليف" في الخلية فمن المفترض أن تكون جميع السلاسل النصية "تأليف" جزءًا من العنوان وستعمل الشيفرة البرمجية على تقسيم النص عند كلمة "تأليف الأخيرة"، على سبيل المثال "كيفية تأليف كتاب تأليف مصطفى أمان" سوف تعتبر الشيفرة البرمجية كلمة "تأليف الأولى من ضمن عنوان الكتاب وتقسم النص بداية من كلمة "تأليف" الثانية.

ثالثًا، بعد تقسيم السلسلة النصية، تُعيَّن السلسلة النصية الأولى كعنوان والثانية كمؤلف.

النتائج

يمكنك الآن رؤية تأثيرات الدالة ()splitAtLastByوهي تعمل، فقط حدد أحد الخلايا التي يوجد بها عنوان الكتاب واسم المؤلف مفصول بينهما بكلمة "تأليف"، ثم اذهب إلى القائمة المخصصة "قائمة الكتب" واختر منها القائمة الفرعية "افصل العنوان عن المؤلف عند كلمة تأليف" وأخبرنا في التعليقات بالنتيجة التي حصلت عليها.

تهانينا، يمكنك الآن استخدام Apps Script لقراءة بيانات السلسلة النصية وتعديلها في ورقة جدول البيانات، واستخدام القوائم المخصصة لتنفيذ أوامر Apps Script المختلفة.

خاتمة

نكون إلى هنا قد وصلنا إلى نهاية هذا المقال من دليل أساسيات برمجة التطبيقات باستخدام جداول بيانات جوجل الذي تعلمنا فيه كيفية استيراد البيانات من جدول بيانات جوجل، كيفية إنشاء قائمة مخصصة من خلال الدالة ()onOpen، كيفية تحليل ومعالجة قيم بيانات سلسلة نصية.

وفي المقال القادم سوف نتعلم كيفية زيادة تحسين مجموعة البيانات هذه عن طريق ملء الخلايا الفارغة ببيانات مستمدة من واجهة برمجة تطبيقات عامة API.

نتمنى أن يكون هذا الدليل قد أضاف لكم معلومات جديدة ومفيدة، وفي حالة وجود أي استفسارات لا تترددوا في ذكرها لنا في التعليقات.

اقرأ أيضًا

• أساسيات برمجة التطبيقات Apps Script باستخدام جداول بيانات جوجل
• التعامل مع جداول البيانات والأوراق والنطاقات من خلال Apps Script