GAS: copy one data validation (dropdown) in N number of rows and set unique value in each dropdown

Edited for clarity and to add images

Using Google Apps Script, how can I:

1. copy a range from Section 2 sheet (G11:H11, with a checkbox & dropdown) into range G12:G25 N number of times (based on the number of non-empty rows in MASTER DROPDOWN sheet under same header title as 'Section 2'!A2) and then,

2. set a different value in each dropdown (each unique value listed in MASTER DROPDOWN sheet under the correct header).

For example, first image is "MASTER DROPDOWN" sheet.

This second image is "Section 2" sheet. The user can add or delete items on the list using the buttons on the right side of the page.

And this last image is "Section 2" sheet. I cannot understand how to write the code for this... When user presses "Reset list" button, I want to copy checkbox and dropdown menu (from G11:H11) N number of times (N=3 based on number of items from MASTER DROPDOWN under Section 2). In each dropdown, I want to set value with each item from the original list in the MASTER DROPDOWN sheet. This process should be dynamic and work on Section 1 and Section 3 sheet (not in worksheet currently).

Any advice on the script verbage to search/learn about this type of functionality, or some direction on the script for this is much appreciated. Here's a link to my code that I have so far...

https://docs.google.com/spreadsheets/d/1ZdlJdhA0ZJOIwLA9dw5-y5v1FyLfRSywjmQ543EwMFQ/edit?usp=sharing

function newListAlert (){
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Are you sure you want to delete your current list and create a new one?",ui.ButtonSet.YES_NO);
 
  if(response == ui.Button.YES) {
    newList();
  } else {
  }
}

function newList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = ss.getRange("G11:H25");
  var options = {contentsOnly: true, validationsOnly: true};
 
  //clear current list
  range.clear(options);

  //add new item to list in first row of range
  addNewItem();

  //copy new datavalidation row above based on number of non-empty rows in MASTER DROPDOWN with same header as active sheet (-1)
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MASTER DROPDOWN");
  var range = ss.getRange("A2");
    
    if (range.getCell(1,1)){

    var section = datass.getRange(1,1,1,datass.getLastColumn()).getValues();
    var sectionIndex = section[0].indexOf(range.getValue()) + 1;

    var validationRange = datass.getRange(4,sectionIndex,19);//19 columns: checklist has a maximum of 18 rows (+ 1 for "select option")
    }

    }

Solution 1:

In your situation, how about modifying newList() as follows?

Modified script:

function newList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getSheetName();
  sheet.getRange("G11:H25").clear({ contentsOnly: true, validationsOnly: true });
  var srcSheet = ss.getSheetByName("MASTER DROPDOWN");
  var values = srcSheet.getDataRange().getValues();
  var obj = values[0].map((_, c) => values.map(r => r[c])).reduce((o, [h, , , ...v], i) => {
    if (h != "") {
      v = v.filter(String);
      v.shift();
      o[h] = { values: v, range: srcSheet.getRange(4, i + 1, v.length + 1) };
    }
    return o;
  }, {});
  if (obj[sheetName]) {
    var validationRule = SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .setHelpText('Select an option from the menu. To add more options to the dropdown list, go to MASTER DROPDOWN tab.')
      .requireValueInRange(obj[sheetName].range, true)
      .build();
    var d = obj[sheetName].values.map(_ => [validationRule]);
    var v = obj[sheetName].values.map(e => [e]);
    sheet.getRange(sheet.getLastRow() + 1, 8, obj[sheetName].values.length).setDataValidations(d).setValues(v).offset(0, -1).insertCheckboxes();
  }
}

• When this script is run, the values for DataValidations are retrieved from the sheet "MASTER DROPDOWN", and using the sheet name, the dataValidation rules are created, and put to the column "H". And also, the checkboxes are put to the column "G" of the same rows of the dataValidations.

• In this case, for example, when you add a new sheet of "Section 1" and run newList(), the dropdown list including "Engineering" and "Design" is put to the column "H" and the checkboxes are also put to the column "G".

Note:

• In this modification, the sheet name like "Section 2" is used for searching the column of "MASTER DROPDOWN" sheet. So please be careful about this.

• And, from your current script, the last row is used for putting to the dropdown list and checkboxes. So when you want to modify this, please modify the above script.

• This sample script is for your sample Spreadsheet. So when your actual Spreadsheet is changed, this script might not be able to be used. Please be careful this.

References:

• setDataValidations(rules)
• insertCheckboxes()