I have a 2 columns 1 column with data and the other with with drop-down lists in every cell.
When I make a choice on the dropdown list I want google sheet to auto select the same items the the other dropdown lists based on the value of the cell from the first row.

(I dont want to do the same thing over and over)

Currently I am struggeling on when I found a cell with a specific value, how do I then replace the value of another cell. I found some examples using textFinder, but now it replaces the value of the current cell.

When I found the value I need to target the cell that I want to modify (the cell on the second column)

I have the following script:

function onEdit() {
    var activesheet = SpreadsheetApp.getActiveSheet()
    var Cell = SpreadsheetApp.getActiveSheet().getActiveCell();
    var Column = Cell.getColumn();
    
    if (activesheet.getName()=='Transacties'){
    if (Column == 5 && SpreadsheetApp.getActiveSheet()){
      var Target = SpreadsheetApp.getActiveSheet().getRange(Cell.getRow(), Column   1); 
      var Reknr = SpreadsheetApp.getActiveSheet().getRange(Cell.getRow(), Column   -1); //this is the bill column
      var Juistecat = SpreadsheetApp.getActiveSheet().getRange(Cell.getRow(), Column   0); //this is the drop-list column
      var Options = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(Cell.getValue()); 
      var Valrek = Reknr.getValue(); //waarde huidig rekening nr
      var Valcat = Juistecat.getValue(); //waarde huidig rekening nr
      SubcategoryDropdown(Target, Options);
    }
       
        //Reknr.setValue("Hello"); //werkt, juiste cell iig
        // var vv = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
    
    SpreadsheetApp.getUi().alert("The active cell  value is " Valrek); 
    
    SpreadsheetApp.getUi().alert("The active cell  value is " Valcat); 
    
    
    
      var textFinder = Reknr.createTextFinder(Valrek);
     var range = SpreadsheetApp.getActive().getRangeByName("Reknr");
     var values = range.getValues();
     SpreadsheetApp.getUi().alert("The active cell  value is " valrek); 
    
     
     values.forEach(function(row) {
       row.forEach(function(col) {
            textFinder.replaceAllWith(Valrek); // But I dont want to replace the current, I need to replace 
            //Target.setValue("Hello");
            });
            });
          }

CodePudding user response：

I made a sample code which should do what you need. However, since I do not know how the information is arranged in your Google Sheet, I cannot customize the code to match exactly with the information on your sheet and some changes will be necessary.

I tried to comment on the code as much as possible so it can be easily changed and customized to your data.

This is how I arrange my data:

function onEdit(e) {

  // you need to add the name of your sheet 
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); 

  // Automatically gets the range that was edited 
  const range = e.range;

  // Gets the A1 notation of the cell example 'B2'
  let current_cell =range.getA1Notation();

  // Gets the value in the range that was modify 
  // In this case the value inside the drop-down 

  let value_selected = sheet.getRange(current_cell).getValue();
  
  // Gets the column that was edited 
  col = range.getColumn();

  // My drop-down was in column B
  // This will filter only the changes on column B, and excludes the rest
  // you can change this to the column where you drop-down is located
  if (col == 2){

    //Gets the number of row where the value in column B was change
    let row = range.getRow();   

    // The data I was searching was in column 1, so I use 'A' row
    // You can replace the A for the column where the search will be done
    new_cell = sheet.getRange('A' row).getValue();
    
    // Searches all the values in column A
    // that match the one next to the value edited
    list = sheet.createTextFinder(new_cell).findAll();
    
    // Loops inside the list of array that was created with the search
    for (let i = 0; i < list.length; i  ){

      let ranges_info = list[i];
      
      // Gets the row in each iteration of the range inside 'list'
      let ranges_row = ranges_info.getRow();

      // set the value, I selected in the first drop-down
      // to the rest of the cells in B that match the same value in A
      sheet.getRange('B'   ranges_row).setValue(value_selected)
    };

  }
}

And here is a gif with the code running: