كيفية الحصول على الصف الأخير في جداول بيانات Google عند استخدام ArrayFormula

لدينا هنا جدول بيانات لقائمة الموظفين مع عمود اسمه Employee Name وعمود اسمه Employee ID.

بمجرد إدخال اسم الموظف الجديد في Employee Name العمود، Employee ID سيتم ملء العمود تلقائيًا بمساعدة ARRAY FORMULA المتوفرة أدناه:

=ARRAYFORMULA(IF(ROW(A:A)=1, "Employee ID",
  IF(NOT(ISBLANK(A:A)), ROW(A:A)-1, "")))

تضيف الصيغة رقم الصف الحالي إلى Employee ID العمود إذا كان الصف الحالي ليس الصف الأول. إذا كان الصف الحالي هو الصف الأول، فسيتم إضافة العنوان إلى الخلية.

راجع أيضًا: صيغ جداول بيانات Google لنماذج Google

النظام يعمل ولكن هناك عيب رئيسي واحد في هذا النهج. اسمحوا لي أن أشرح:

لنفترض أن لديك العديد من الموظفين الجدد وترغب في إضافتهم إلى جدول البيانات برمجيًا بمساعدة Google Apps Script.

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

function addNewEmployees() {
  const employees = ['Richard', 'Elizabeth', 'Orli'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  Logger.log('Last row is %s', lastRow);
}

سوف يعود الرمز أعلاه 10 وليس 4 كما كنت تتوقع. والسبب هو أن ArrayFormula يؤثر على getLastRow() الطريقة لأنها تقوم بإخراج مجموعة من القيم الفارغة وصولاً إلى أسفل الورقة.

وبالتالي إخراج getLastRow() و getMaxRows() سيكون هو نفسه إذا لم تكن ArrayFormula مقيدة بحجم النطاق الذي يحتوي على بيانات فعلية.

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

=ARRAYFORMULA(IF(ROW(A:A)=1, "Employee ID",
  IF(NOT(ISBLANK(A:A)), ROW(A:A)-1,)))

إليك رمز العمل النهائي:

function addNewEmployees() {
  const employees = ['Richard', 'Elizabeth', 'Orli'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  Logger.log('Last row is %s', lastRow);
  sheet.getRange(lastRow + 1, 1, employees.length, 1).setValues(employees.map((e) => [e]));
}

يمكنك استخدام هذا الأسلوب لإضافة معرفات فريدة إلى جداول بيانات Google الخاصة بك.

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

نقوم بعكس المصفوفة للبحث في الصفوف الفارغة من الأسفل ونتوقف بمجرد العثور على أي صف يحتوي على بيانات.

function getLastRow() {
  const data = SpreadsheetApp.getActiveSheet()
    .getRange('A:A')
    .getValues()
    .reverse()
    .map(([employee]) => employee);

  const { length } = data;
  for (var d = 0; d < length; d++) {
    if (data[d]) {
      Logger.log('The last row is %s', length - d);
      return length - d;
    }
  }
  return 1;
}