Dropdown list apply more than edited row in appscript-CodePudding

First I am so sorry for my English Skill I have to use my poor English Skill to make this question. I made a script to apply Depended Data Validation on Column E of Orders Sheet when new SKU added to Column D, like when I add 1 SKU to D2, E2 will show dropdown list with all Supplier that SKU have (ex: SKU00124 have Amazon, Walmart, Chewy as supplier if I input SKU00124 to D2, E2 will show dropdown list with Amazon, Walmart, Chewy option for user choice, and SKU00122 have only Walmart, Amazon when I input SKU00122 to D3, E3 will show Walmart, Amazon). SKU and Supplier Store at Item_List sheet with SKU at column B and supplier at Column E. My issue is in Orders sheet column D where to store SKU, it get data from Draft_Order column AA, I am using a formula to get values of AA input to D.

={"SKU";ARRAYFORMULA(if(B2:B="","",'Draft_Order'!AA2:AA))}

I see the script does not work correctly, I have to delete values in column D and when the formula gets values again the script runs. But it not set Data Validation to all range Ex: If SKU input from D2:D20, it not apply data validation to E2:E20, script just applies it row by row and only apply for 5 row (means only E2:E7 applied data validation), if I want more I have to delete the rest of SKU.

I use onEdit(e) to catch cell edited on column D

Here is my example spreadsheet: My Spreadsheet here

Here is my script:

function onEdit(e) {
  // Get the active sheet
  var sheet = e.source.getActiveSheet();

  // Check if the active sheet is the "Order" sheet
  if (sheet.getSheetName() == "Order") {
    // Get the range that was edited
    var range = e.range;

    // Get the column of the edited cell
    var col = range.getColumn();

    // Check if the edited cell is in column D
    if (col == 4) {
      // Get the values in the edited range
      var values = range.getValues();

      // Loop through the values in the range
      for (var i = 0; i < values.length; i  ) {
        for (var j = 0; j < values[i].length; j  ) {
          // Check if the value is not empty
          if (values[i][j] != "") {
            // Get the SKU
            var sku = values[i][j];

            // Get the sources for the SKU using the getSources function
            var sources = getSources(sku);

            // Check if there are any sources for the SKU
            if (sources.length > 0) {
              // Calculate the row and column of the current cell
              var row = range.getRow()   i;
              var col = range.getColumn()   1;

              // Get the cell in column E
              var cell = sheet.getRange(row, col);

              // Set the data validation for the cell using the sources list
              cell.setDataValidation(SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInList(sources).build());
            }
          }
        }
      }
    }
  }
}

function getSources(sku) {
  // Get the active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get the "Item List sheet
  var sheet = ss.getSheetByName("Item List");

  // Get the data in the sheet
  var data = sheet.getDataRange().getValues();

  // Create an array to store the sources
  var sources = [];

  // Loop through the data in the sheet
  for (var i = 0; i < data.length; i  ) {
    // Check if the SKU in column B matches the input SKU
    if (data[i][1] == sku) {
      // Add the source in column E to the sources array
      sources.push(data[i][4]);
    }
  }

  // Return the sources array
  return sources;
}

I am trying to fix it by using a range define and apply validation for range length by using edit values.length but script applies data validation for a range more than SKU range. Looks like if SKU values from D2:D20, the dropdown list will show on E2:E35 when D21:D35 is blank and does not have any SKU and issue I got in first script still meets (only E2:E7 is correct values of data validation, if I need more I have to delete it again.

Here is my try:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getSheetName() == "Order") {
    var range = e.range;
    var col = range.getColumn();
    if (col == 4) {
      var values = range.getValues();
      Logger.log("values.length: "   values.length);
      for (var i = 0; i < values.length; i  ) {
        for (var j = 0; j < values[i].length; j  ) {
          if (values[i][j] != "") {
            var sku = values[i][j];
            var sources = getSources(sku);
            if (sources.length > 0) {
              var row = range.getRow()   i;
              var col = range.getColumn()   1;
              var dataValidation = SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInList(sources).build();
              // Apply data validation to the range of cells in column E
              var numRows = values.length;
              for (var k = 0; k < numRows; k  ) {
                if (values[k][0] == "") {
                  numRows--;
                }
              }
              var validationRange = sheet.getRange(row, col, numRows, 1);
              validationRange.clearDataValidations().clearContent();
              validationRange.setDataValidation(dataValidation);
              Logger.log("numRows: "   numRows);
            }
          }
        }
      }
    }
  }
}

As you see, I try to Logger.log numRow and value.lenght but after script run nothing show

please help me:

1/ script will run each time when I input SKU to AA column at Draft_Orders sheet

2/ Script will run when I input many SKU in 1 times like more than 100 SKU

3/ Script will apply to correct range (SKU have in D2:D100 E2:E100 have data validation not E2:E250

CodePudding user response：

The onEdit() trigger works when the user edits the range. In this case you need to validate when edit has been done in the main sheet where you're getting the data from. You will need to validate whenever the user edits column AA in sheet Draft Order. To give you an idea, check this script (make sure the ranges and sheet name matches with yours):

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getSheetName() == "Draft Order") {
    var range = e.range;
    var col = range.getColumn();
    if (col == 27) {
      var values = range.getValues();
      Logger.log("values.length: "   values.length);
      for (var i = 0; i < values.length; i  ) {
        for (var j = 0; j < values[i].length; j  ) {
          if (values[i][j] != "") {
            var sku = values[i][j];
            var sources = getSources(sku);
            if (sources.length > 0) {
              var row = range.getRow()   i;
              var col = range.getColumn()   1;
              var dataValidation = SpreadsheetApp.newDataValidation().setAllowInvalid(true).requireValueInList(sources).build();
              // Apply data validation to the range of cells in column E
              var numRows = values.length;
              for (var k = 0; k < numRows; k  ) {
                if (values[k][0] == "") {
                  numRows--;
                }
              }
              var validationRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(row, 5);
              validationRange.clearDataValidations().clearContent();
              validationRange.setDataValidation(dataValidation);
              Logger.log("numRows: "   numRows);
            }
          }
        }
      }
    }
  }
}

References: onEdit()