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

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

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

Автоматическое создание листа Номе ;
Автоматическое добавление всех формул на листе Номе;
При редактировании созданной ранее строки зависимых (связанных списков), автоматически удаляются значения и формулы справа от редактируемой ячейки.
Возможность сдвига таблицы на листе Номе вправо на произвольное число столбцов
01.10.2021 Добавлена проверка наличия листов Home и Data в рабочем файле (книге)

var colShift = 0;

function onEdit(e) {

  let row = e.range.getRow();
  let col = e.range.getColumn();
  let sheetName = e.source.getActiveSheet().getName();
  let name = e.value;
  let oldName = e.oldValue;
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let mask = JSON.stringify(sh.getRange(row, colShift+1, 1, col-colShift).getValues()[0]);
  if (!doesSheetExist('Home', true)) return;
  if (!doesSheetExist('Data', true)) return;

  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").getLastColumn();
  
  if(sheetName === "Home" && name !== oldName && col < colMax+colShift) {
    fillColumn(row, col, mask);
  }
}

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

function fillColumn(row, col, mask) {

  let col_data = col - colShift;

// clear dataVal and Value
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  let colMax = sh.getLastColumn();
  sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();
 
// find date
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  let arrData = sd.getDataRange().getValues();
  let arrData_2 = [];
  
  let iMax = arrData.length - 1;
  for(let i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col_data)) == mask) {
      arrData_2.push(arrData[i].slice(0, col_data+1));
    }
  }
  arrData_2 = arrData_2.map(item => item.pop())
  let uniqArrDate_2 = arrData_2.filter(uniqValues); 

// add dataVal
  col++;
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInList(uniqArrDate_2, true)
  .build());
}

function createSheets() {

// is exist Home?
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sd;
  if (doesSheetExist('Data', true)) {
    sd = ss.getSheetByName('Data');
  } else {
    return;
  }
  
  
// 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');

  };

}

function uniqValues(item, index, arr) {
  return arr.indexOf(item) === index;
}


function doesSheetExist(sheetName, needAlert) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let allSheets = ss.getSheets().map(sheet => sheet.getName())
  if (allSheets.includes(sheetName)) return true;
  if (needAlert) Browser.msgBox(`Error! Could not find sheet "${sheetName}"!`);
  
  return false;
}

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

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