-1

I'm working on a Google Sheets document in which I calculate profits based on two columns: Price and payment method. I'd like help with a script that checks the values of said columns in each row, and based on those two columns sets a certain value in another column (net profit).

So: If row 2 column E (price) = €47,00 && column H (payment method) = "iDEAL" then set the value in column K (net profit) of row 2 to €42,98.

My table:

Example of my table


Text version of sample data

Date Time Order_nr Product Price Vat Email Payment method Couponcode Couponamount Net profit
12/12/2020 01:02:03 order_wc_order_nr_2 Awesome Product $47.00 3.88 customer@email.com IDEAL
14/12/2020 02:05:04 order_wc_order_nr_3 Awesome Product $0.00 0 customer@email.com 43.12
16/12/2020 03:10:05 order_wc_order_nr_4 Awesome Product $28.20 2.33 customer@email.com Bancontact 17.25
17/12/2020 04:15:06 order_wc_order_nr_5 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 05:20:07 order_wc_order_nr_6 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 06:25:08 order_wc_order_nr_7 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 07:30:09 order_wc_order_nr_8 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 08:35:10 order_wc_order_nr_9 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 09:40:11 order_wc_order_nr_10 Awesome Product $47.00 3.88 customer@email.com PayPal
23/12/2020 10:45:12 order_wc_order_nr_11 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 11:50:13 order_wc_order_nr_12 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 12:55:14 order_wc_order_nr_13 Awesome Product $47.00 3.88 customer@email.com IDEAL
23/12/2020 14:00:15 order_wc_order_nr_14 Awesome Product $47.00 3.88 customer@email.com IDEAL

This table has 3 different price values: €47, €37,9 and €28,20. We offer four different payment methods: "iDEAL", "PayPal", "Bancontact" and "Creditcard".

For each combination of price value and payment method, there is a different net profit.
Net profit values:


Table2 data as text

$47.00 Net Profit
IDEAL $42.89
PayPal $41.09
CreditCard $42.25
BanConnect $42.81
$37.60 Net Profit
IDEAL $34.27
PayPal $32.47
CreditCard $33.63
BanConnect $34.19
$28.20 Net Profit
IDEAL $25.64
PayPal $23.84
CreditCard $25.00
BanConnect $25.56

I tried to edit the following script:

function onEdit() {
  var sheetActive = SpreadsheetApp.openById("mySheetID");
  var s = sheetActive.getSheetByName("mySheetName");
  var r = s.getActiveCell();
      if( r.getColumn() == 7 && r.getValue() == 47)
      var nextCell = r.offset(0, 2);
      nextCell.setValue(42.89);
}

Based on the script in this link: How do I update one column based on the value of another using a script?

But I didn't get it to work and it also doesn't take the values of two columns into account.

I also found the following script: Use `setValue` to update a column based on values of two others that match their respective values in reference cells

But I haven't been able to find out how to edit the script to fit my needs.

I have minimal experience with scripting and programming but I think I need a for-loop that iterates through my rows in combination with a case/switch based on two column values.

Please help me out and thanks in advance!

0

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.

Revised Table2


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.

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.