n a m n a m نوشته‌هایی درباره‌ی هر چیزی به ویژه حقوق بشر و امنیت دیجیتال

چند روزی روی فرمی در گوگل کار می‌کردم که سوآلی ذهنم را مشغول کرد: «آیا می‌شود فهرست (منویی) از داده‌ها داشت که وابسته به انتخاب قبلی داده‌ها باشد؟». در واقع امکان ساده‌ی این کار Data Validation است (با یا بدون درست کردن رسته یا Range از داده‌ها) اما پرسش این‌جاست که اگر محصولات یک فروشگاه را در چهار دسته‌ی سبزی، میوه، لبنیات و حبوبات تعریف کنیم و ۲۰۰ نوع محصول را در این چهار دسته خلاصه کنیم به چه شیوه‌ای می‌شود این دسته‌بندی وابسته (Dependent) را در گوگل شیتز (Google Sheets) به کار بست.

به طبع با اندکی جست‌وجو در گوگل به راه‌های مختلفی برمی‌خورید. من شیوه‌ی زیر را به کار بستم اما جواب نیاز مرا نداد و مجبور شدم تغییرهایی در آن اعمال کنم تا با نیاز من سازگار شود.

مشکل در این بود که نیاز به چند سری از دسته‌بندی داشتم. برای نمونه در فروشگاه بالا علاوه بر دسته‌بندی محصولات نیاز به دسته‌بندی مشتریان هم دارم. جست‌وجوهای من نتیجه‌ای نداشت و مجبور شدم اسکریپت نوشته‌شده را بخوانم و تغییرهای لازم را اعمال کنم. اما پیش توضیح گفته‌های ویدئو را به صورت خلاصه می‌نویسم:

1. یک برگه‌ی جدید باز کنید و گروه محصول‌ها را در یک ستون بنویسید.
2. ستون‌های بعدی را اختصاص دهید به زیرگروه‌ها. شکل زیر به صورت گرافیکی مسئله را توضیح می‌دهد.
   
3. مرحله‌ی بعد درست کردن رسته (Range) از داده‌هاست. برای درست کردن رسته از فهرست بالا گزینه‌ی Data و سپس Named Range را بزنید و به هر ستون یک اسم بدهید (برای جزئیات چگونگی انجام این کار ویدئو را ببینید).
4. در آخرین مرحله کافی است که اسکریپتی بسازید. برای این کار از فهرست بالا گزینه‌ی Tools را انتخاب کنید و سپس Scripts Editor را بزنید تا صفحه‌ی ویرایش اسکریپت باز شود. کدهای زیر را به آن منتقل کنید و پس از ذخیره کردن، آن را اجرا کنید.

function depDrop_(range, sourceRange){
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}
function depDrop1_(range1, sourceRange1){
  var rule1 = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange1, true).build();
  range1.setDataValidation(rule1);
}

if (SpreadsheetApp.getActiveSheet().getActiveCell().getColumn() == 13){
  function onEdit (){
    var GROUP1 = 13;
    var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
    var aColumn = aCell.getColumn();
    if (aColumn == Category && SpreadsheetApp.getActiveSheet()){
      var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
      var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
      depDrop_(range, sourceRange);
    }
    else if (aColumn == (GROUP1 + 1) && SpreadsheetApp.getActiveSheet()){
      var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
      var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
      depDrop_(range, sourceRange);
    }
  }
}
else if (SpreadsheetApp.getActiveSheet().getActiveCell().getColumn() == 16){
  function onEdit (){
    var GROUP2 = 16;
    var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
    var aColumn = aCell.getColumn();
    if (aColumn == Project && SpreadsheetApp.getActiveSheet()){
      var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
      var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
      depDrop_(range, sourceRange);
    }
    else if (aColumn == (GROUP2 + 1) && SpreadsheetApp.getActiveSheet()){
      var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
      var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
      depDrop_(range, sourceRange);
    }
  }
}

چند نکته درباره‌ی کد:

• اگر این کد را با کد اصلی مقایسه کنید، تغییرهایی در آن می‌بینید. نخست آن که فرض من بر آن بوده است که نیاز به دو سطح است و برای همین کدهای اضافی را پاک کردم و دوم این که یک شرط (در خط‌های ۱۰ و ۲۷) به کد اضافه کردم که توضیح آن در زیر خواهد آمد، عددها را با متغییرهای GRUOP1 و GROUP2 عوض کردم که میزان تغییرها کم شود و آخر این که تابع OnEdit دو بار تکرار شده است.
• برای کاری که انجام می‌دادم نیاز به دو فهرست کشویی وابسته داشتم که ساده‌ترین راه تکرار تابع بود اما از آن‌جایی که برنامه تابع دوم را مد نظر قرار می‌داد تصمیم گرفتم از یک شرط استفاده کنم تا وقتی به ستون مد نظر من رسید تابع مربوط به آن را اجرا کند. واضح است که برای اضافه کردن سطح‌های بعدی باید پس از خط ۲۴ و/یا خط ۴۱ یک بار دیگر محتوای خط‌های ۲۰ تا ۲۴ و/یا خط‌های ۳۷ تا ۴۱ را تکرار کنید. پس از آن باید مقدارهای GROUP2 + 1 و GROUP1 + 1 را با GROUP2 + 2 و GROUP1 + 2 را تغییر دهید. به همین شیوه می‌توان برای سطح‌های بعدی نیز اقدام کرد.
• نکته‌ای که در ویدئو هم آمده مربوط به شماره‌ی ستون است؛ عددهایی که در سطرهای ۱۰ و ۱۲ و ۲۷ و ۲۹ آمده‌اند. در شمردن آن‌ها دقیق باشید. برای نمونه ستون A معادل یک است و ستون S معادل ۱۹ است.

در گشت و گذارم به شیوه‌ی جالبی از این کار برخوردم که جواب‌گوی نیاز من نبود اما شاید به کار بیاید. بازی جالبی است با تابع Filter.