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

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

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

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

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

انظر أيضًا: صيغ Google Sheets لنماذج Google

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

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

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

=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;
}