Google Sheets – Automatically Change Data Validation Rules After Adding Rows

data validationgoogle sheetsgoogle-apps-script

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.

Best Answer

OK, so you are just trying to implement dependent drop-down lists, and the question you are asking is just to solve an issue you bumped into while trying to find a way to do that.

You may want to try the three-level dependent drop-down lists script. You can make it work with two levels of drop-downs as well. Search this forum for [google-sheets] dependent drop-down to find several alternatives.

To answer your question, you can use mySourceRange.copyTo(myTargetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false) to copy cell formats and data validation rules from an existing row to a new row.

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.

Related Topic