Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 2)

Данная статья является продолжением статьи Бесконечные Зависимые Выпадающие Списки в Google Sheets.

Здесь описаны изменения изменения и дополнения кода, а именно:

Автоматическое создание листов Номе Data_2;
Автоматическое добавление всех формул на листе Номе;
При редактировании созданной ранее строки зависимых (связанных списков), автоматически удаляются значения и формулы справа от редактируемой ячейки.

function onEdit(e) {

  var row = e.range.getRow();
  var col = e.range.getColumn();
  var list_name = e.source.getActiveSheet().getName();
  var name = e.value;
  var oldName = e.oldValue;
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var mask = JSON.stringify(sh.getRange(row, 1, 1, col).getValues()[0]);
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var colMax = sh.getLastColumn();
  
  if(list_name === "Home" && name !== oldName && col < colMax) {
    fillColumn(row, col, mask);
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Create sheets', 'createSheets')
      .addToUi();
}


function fillColumn(row, col, mask) {

// clear dataVal and Value
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var colMax = sh.getLastColumn();
  sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();

// find date
  var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var arrData = sd.getDataRange().getValues();
  var arrData_2 = [];
  arrData_2.push(arrData[0]);
  
  var iMax = arrData.length - 1;
  for(var i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col)) == mask) {
      arrData_2.push(arrData[i]);
    }
  }
   
// clear Data_2
  var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
  sd_2.getDataRange().clearContent();

// insert data
  sd_2.getRange(1, 1, arrData_2.length, arrData_2[0].length).setValues(arrData_2);
  
// add dataVal
  col++;
  var list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(sh.getRange('Data_2!$' + list[col - 1] + '$2:$' + list[col - 1] + '$1000'), true)
  .build());
}

function createSheets() {

// is exist Home?
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sd = ss.getSheetByName('Data')
  
// create if not exist
  if(!ss.getSheetByName('Home')) {
    ss.insertSheet('Home', 0);
// create Data Val
    var sh = ss.getSheetByName('Home');
    sh.getRange('Home!A2:A20').setDataValidation(SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(sh.getRange('Data!$A$2:$A'), true)
      .build());
    sh.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');

  };

// is exist Data_2?
  if(!ss.getSheetByName('Data_2')) {
   
// create if not exist
    var k = ss.getNumSheets();
    ss.insertSheet('Data_2', k + 1);
    var sd_2 = ss.getSheetByName('Data_2');
    sd_2.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');
  };
}

Теперь, в этой версии программы появилась возможность по нажатию всего лишь одной(!) кнопки из пользовательского меню автоматически создавать все листы файла, необходимые для работы скрипта (включая форматирование и валидацию данных).

Всё что для этого нужно: