Calculating Cost Per Item with Discount Using Google Apps Script

by

In this blog post, we will delve into a Google Apps Script function that calculates the cost per item, considering any applicable discounts. This script is especially useful for e-commerce platforms or any other scenarios where discounts and coupon codes are used to determine the final cost of products. We will explain the code in detail and provide insights into how it works.

Function Overview

The main function, COST_PER_ITEM, calculates the final cost of an item after applying a discount. The discount is determined based on a coupon code and a discount table. The discount table is a 2D array where each row contains a coupon code and its corresponding discount percentage.

Code Breakdown

Let’s break down the code into its constituent parts and understand how each part contributes to the overall functionality.

Main Function: COST_PER_ITEM

function COST_PER_ITEM(item, quantity, price, couponCode, discountTable) {

  const discount = getDiscount(couponCode, discountTable);

  let cost = quantity * price;

  cost = cost – (cost * discount);

  return cost;

}

Helper Function: getDiscount

function getDiscount(couponCode, discountTable) {

  for(let i = 0; i < discountTable.length; i++) {

    if(discountTable[i][0] === couponCode) {

      return discountTable[i][1];

    }

  }

  return 0;

}

Detailed Explanation

1. COST_PER_ITEM Function

The COST_PER_ITEM function is the main function that calculates the final cost of an item after applying any applicable discount. It takes the following parameters:

  • item: The name or identifier of the item (not used in the calculation, but useful for logging or display purposes).
  • quantity: The number of units of the item being purchased.
  • price: The price per unit of the item.
  • couponCode: The coupon code that may provide a discount.
  • discountTable: A 2D array where each row contains a coupon code and its corresponding discount percentage.

Steps within COST_PER_ITEM:

Get the Discount:
const discount = getDiscount(couponCode, discountTable);

  1. This line calls the getDiscount function to determine the discount percentage associated with the provided coupon code.

Calculate Initial Cost:
let cost = quantity * price;

  1. This calculates the initial cost of the item by multiplying the quantity by the price per unit.

Apply Discount:
cost = cost – (cost * discount);

  1. This applies the discount to the initial cost. The discount is subtracted from the initial cost to get the final cost.

Return Final Cost:
return cost;

  1. This returns the final cost after applying the discount.

2. getDiscount Function

The getDiscount function determines the discount percentage associated with a given coupon code. It takes the following parameters:

  • couponCode: The coupon code for which the discount is to be determined.
  • discountTable: A 2D array where each row contains a coupon code and its corresponding discount percentage.

Steps within getDiscount:

Loop Through Discount Table:
for(let i = 0; i < discountTable.length; i++) {

  if(discountTable[i][0] === couponCode) {

    return discountTable[i][1];

  }

}

  1. This loop iterates through each row in the discount table. If the coupon code matches the first element of the row (i.e., the coupon code in the discount table), it returns the second element of the row (i.e., the discount percentage).

Return Default Discount:
return 0;

  1. If the coupon code is not found in the discount table, the function returns a default discount of 0%.

Example Usage

Let’s consider an example to see how the COST_PER_ITEM function works in practice.

Sample Data

  • Item: “Laptop”
  • Quantity: 2
  • Price: $1000
  • Coupon Code: “DISCOUNT10”
  • Discount Table:

Coupon Code Discount

DISCOUNT10 0.10

SAVE15 0.15

Calculation

var item = “Laptop”;

var quantity = 2;

var price = 1000;

var couponCode = “DISCOUNT10”;

var discountTable = [

  [“DISCOUNT10”, 0.10],

  [“SAVE15”, 0.15]

];

var finalCost = COST_PER_ITEM(item, quantity, price, couponCode, discountTable);

Logger.log(finalCost); // Outputs: 1800

Explanation:

  1. The discount for the coupon code “DISCOUNT10” is 0.10 (or 10%).
  2. The initial cost is 2 * 1000 = $2000.
  3. The discount amount is 2000 * 0.10 = $200.
  4. The final cost is 2000 – 200 = $1800.

Conclusion

In this blog post, we have explored how to use Google Apps Script to calculate the cost per item, considering any applicable discounts based on a coupon code and a discount table. This script is a powerful tool for automating pricing calculations in Google Sheets, making it easier to manage and apply discounts to your data.

By understanding and utilizing the COST_PER_ITEM and getDiscount functions, you can streamline your financial calculations and ensure accurate pricing for your products.