Infinite Dropdown Lists: Personal Financial Plan (example # 1)

This article is a continuation of the article Infinite Dependent Dropdown Lists in Google Sheets (part 3) .

Here's an example of how Infinite Linked Lists can be used in a Personal Financial Plan.

In addition to adding several user-friendly functions (automatic insertion of the payment date and the formula for maintaining the balance of receipts and expenses), the script provided contains a number of significant improvements and improvements:

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

You can get more information from this video (RU voice!):