0

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.

3
  • It is very possible that it is XY problem. I added an edit on the post with a sample sheet and an explanation of the problem.
    – Aure
    May 19 at 9:08
  • I am trying to implement a three-level dropdown list that does not break when new rows are added in the middle without fear of basic users of the sheet seeing the work there. This is a template that will be copied and copy edited. Our user team is even less good at spreadsheets as I am so it needs to happen in a way that can be hidden. I will see the three-level dependent drop-down list script. Hopefully, it works for our purposes.
    – Aure
    May 19 at 11:10
  • I probably made the mistake of not starting with a script but as I am such a novice with them, scripts came into the picture very late. And I just noticed that I said I was trying to do a three-level dropdown instead of a two-level.
    – Aure
    May 19 at 11:36
0

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 pagejavascript.infoMozilla Developer Network and Apps Script at Stack Overflow.

2
  • The issue is that the range that data validation changes by row. There is a normal drop-down created from range and a second dependent drop down that is created through data validation with ArrayFormula. Each row on Array Formula changes.
    – Aure
    May 19 at 8:35
  • Thank you so much for your help with this. And taking your time to figure out what I actually wanted to accomplish instead of what I was asking. I will work to create a script for this situation. I might need extra help at some point but that is for another question.
    – Aure
    May 19 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.