I am completely new to scripts. I have created a complex template on Google Sheets for accounting. I am using dependent drop-down lists. As it is a template and plan is to add rows for the items in the tab that I will call ItemTab. To make dependent drop-down lists work I have created a new tab (Let's call it HelperTab) with ArrayFormula. To be able to add rows to the helper tab when I add rows to ItemTab I have managed to find a script formula that works.
// Global variables
var LEADER_SHEET = 'ItemTab';
var FOLLOWER_SHEET = 'HelperTab';
function replicateRowInsertion(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
if(e.changeType == 'INSERT_ROW'){
var leaderRange = ss.getActiveRange();
var rows = leaderRange.getNumRows();
if(leaderRange.getSheet().getName() == 'ItemTab'){
var sheet = ss.getSheetByName('HelperTab');
sheet.insertRows(leaderRange.getRow(),rows);
}
}
}
Problem is that when you add rows with script Google sheet does not update data validation to reflect the new sheet even when you use relative references.
So my question is that, how do you create a script that updates the column of data validation but only either below the new row or from the new row? I am also open to any other working solution to the problem.
Edit: Here is a spreadsheet to show what I am working with. https://docs.google.com/spreadsheets/d/131Y975fQzDHPbliMPWR1Rkwkp_08HVMoUXGThVsPIUg/edit?usp=sharing
It works apart from when you add a new row where the items are going to be going. Script adds a new row on the correct place on the VFG Expenses tab. But the data validations won't change on "Total Grain, Fruit and Vegetable costs" tab like they would if I added the rows on hand.
