Script setValue in column based on the values of two columns with a case/switch and for-loop

When the "Payment method" is updated on Table1, you want to lookup the net profit on Table2 based on the combination of the Table 1 Price and Payment method. Then you want to update the "Net profit" value on Table 1.

Layout of Table 2

The design of Table 2 is inefficient and impossible to normalise. This solution redesigns the Table 2 as shown in this image.

function onEdit(e) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // define the sheets
  var nametable1 = "Table1";
  var nametable2 = "Table2";
  var table1 = ss.getSheetByName(nametable1);
  var table2 = ss.getSheetByName(nametable2);
  
  // variables to watch
  var watchsheet = nametable1; // sheet
  var watchcol = 8; // trigger columncolumn H-Payment method
  var pricecol = 5;   // column for price
  var npcol = 10; // net profit column: Column K (zero-based)
  var headerrows=1;   // number of header rows
  var table1spcol = 4; // var table1 sell prices are in Column E zero-based
  var table2sprow=2; // table 2 sell porices are on row 2
  
  // editted parameters - index-based, not zero-based
  var edittedcol = e.range.getColumn();
  var edittedrow = e.range.getRow();
  var edittedsheet = e.source.getSheetName();
  var edittedvalue = e.value;
  // Logger.log("DEBUG: Edited details: column = "+edittedcol+", row =  "+edittedrow+", sheet = "+edittedsheet+", value = "+edittedvalue);
  
  // test sheet, column and row
  if(edittedsheet == nametable1 && edittedcol == watchcol && edittedrow >1){
    // there was a change on table 1 in Column H below row 1
    
    var table1values = table1.getDataRange().getValues(); // get the data for table1
    var table2values = table2.getDataRange().getValues(); // get the data for table2
    var sp = table1values[edittedrow][table1spcol];  // Table1 sell price
    // Logger.log("DEBUG: the sell price is "+sp);
    
    // get the column of selli prices from Table 2
    var sellprices = table2values.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]

    // lookup table1 sp on table2 
    // result is index number or -1 if not found.
    var result = sellprices.indexOf(sp);
    // Logger.log("DEBUG: the index number for the sell price is "+result)
    
    // lookup paymentmethoid from table 1 on table2 
    // result is index number or -1 if not found.
    var methodidx = table2values[0].indexOf(edittedvalue);
    // Logger.log("DEBUG: the index number for the payment type is  "+methodidx);
    
    // get the net profit from Table 2
    var netp = table2values[result][methodidx];
    // Logger.log("DEBUG: the net profit is "+netp);
    
    // update the net profit on Table1       
    table1.getRange(edittedrow,npcol+1).setValue(netp);
    SpreadsheetApp.flush();  
  }
  
  return;
}

Processing Logic

The following may help you understand the aspects of the script that fit your needs.

• Table2

  • As mentioned this is simply a very poor layout for a database (which is how we are using Sheets). The new design brings all the data into a single table that can be searched efficiently.

• onEdit(e)

  • onEdit is a special script that is automatically triggered when a user edits a cell. It is one of a group known as a Simple Trigger. There are also Installable triggers that you should learn about.
  • the "e" argument refers to Event Objects; these are specific values and parameters that the system returns relating to the trigger. In the case of onEdit, this link described the event objects.

• if(edittedsheet == nametable1 && edittedcol == watchcol && edittedrow >1){

  • The script needs to ensure that the edit is on Table1, in Column H, and in a row below the header(s).
  • the three conditions are joined by && which means "AND. FYI, || means "OR" but that doesn't apply here.

• getValues()

  • note that the script gets ALL the data for Table1 and Table2.
  • values in specific rows and columns can be obtained by applying the various row and column parameters that have already been identified.

• indexOf

  • indexOf is a Javascript method that searches for a value in an array.

• Table 2 - Search Selling prices.

  • var sellprices = table2values.map(function(e){return e[0];});
  • we need to search the column 1 of Table2. Map lets you do this by returning just the first column of the 2D Array.

• Table 2 - Search Payment Methods

  • var methodidx = table2values[0].indexOf(edittedvalue);
  • this enables the script to search the first row of the 2D array.