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:
Text version of sample data
Date | Time | Order_nr | Product | Price | Vat | 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!
Best Answer
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.
Processing Logic
The following may help you understand the aspects of the script that fit your needs.
Table2
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.onEdit
, this link described the event objects.if(edittedsheet == nametable1 && edittedcol == watchcol && edittedrow >1){
&&
which means "AND. FYI,||
means "OR" but that doesn't apply here.getValues()
indexOf
Table 2 - Search Selling prices.
var sellprices = table2values.map(function(e){return e[0];});
Table 2 - Search Payment Methods
var methodidx = table2values[0].indexOf(edittedvalue);