Бесконечные Связанные Списки: Личный Финансовый План (пример #1)

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

Здесь рассмотрен пример практического применения Бесконечных связанных списков при составлении Личного Финансового Плана.

Помимо добавления нескольких удобных для пользователя функций (автоматическая вставка даты внесения платежа и формулы для ведения баланса приходов-расходов) скрипт, приведённый содержит целый ряд существенных доработок и улучшений:

// global variable
const excludedSheets = ['Summary', 'Category'];
const dataSheet = 'Category';

var colShift = 3;
var rowMin = 4;

function onEdit(e) {

  let row = e.range.getRow();
  let col = e.range.getColumn();
  let sheetName = e.source.getActiveSheet().getName();
  if (excludedSheets.includes(sheetName)) return;
  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]);
  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet).getLastColumn();
  
  if(name !== oldName && row >= rowMin && col < colMax+colShift) {
    insertDateFormulasAndDataValidation(row, col, sheetName)
    fillColumn(row, col, mask, sheetName);
  }
}

function insertDateFormulasAndDataValidation(row, col, sheetName) {

  if (col == colShift+1) {
    let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    let dateValue = sh.getRange(row, 2).getValue();
    if (dateValue == '') {
      let today = new Date();
      let timeZone = Session.getScriptTimeZone();
      let todayStr = Utilities.formatDate(today, timeZone, 'dd.MM.yyyy');
      sh.getRange(row, 2).setValue(todayStr);
    };
    let formulaStr = '=I' + (row-1) + '+H' + row + '-C' + row; 
    sh.getRange(row, 9).setFormula(formulaStr);
    let conN = String.fromCharCode(65+colShift) 
    let adress = sheetName + '!' + conN + (row+1) + ':' + conN + (row+3);
    sh.getRange(adress).setDataValidation(SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(sh.getRange(dataSheet+'!$A$2:$A'), true)
      .build());    
  }
}

function tmp() {
  insertDateFormulasAndDataValidation(7, 4, 'Transactions');
}

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

function fillColumn(row, col, mask, sheetName) {

  let col_data = col - colShift;

// clear dataVal and Value
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet).getLastColumn();
  sh.getRange(row, col + 1, 1, colMax-col_data).clearDataValidations().clearContent();
 
// find date
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet);
  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 uniqValues(item, index, arr) {
  return arr.indexOf(item) === index;
}

Дополнительную информацию вы можете получить из этого видео: