Custom Google Apps Script for Sheets create and apply discount percentages

by

CALC_DIS(qty, cost, discount): This function calculates the total cost for a particular quantity of items, given their individual cost and a discount rate. It takes three parameters: qty (quantity), cost (individual cost of each item), and discount (discount rate as a decimal between 0 and 1).
Inside the function, a variable named total is initialized to 0. Then, the total cost is calculated by multiplying the qty with the cost, and then further reducing the result by the product of qty, cost, and discount. The 1 – discount factor is used to apply the discount rate.
The calculated total is returned by the function.

function CALC_DIS(qty,cost,discount){

let total = 0;

total = qty * cost * (1-discount);

return total;

}

const totalCost = CALC_DIS(5, 10, 0.1); // Calculates (5 * 10 * 0.9) = 45

console.log(totalCost); // Output: 45

CALC_TOTAL_AMOUNT(items, quantities, prices, discounts): This function calculates the total amount for each item in a list, considering their quantities, individual prices, and discount rates. It takes four arrays as parameters: items (item names), quantities (quantities of each item), prices (individual prices of each item), and discounts (discount rates for each item).
The function iterates over each item using a for loop. Inside the loop, it extracts the corresponding quantity, price, and discount for the current item. The total amount for the item is then calculated by multiplying quantity, price, and 1 – discount. This amount is added to the totalAmounts array.
After iterating through all items, the function returns an array (totalAmounts) containing the calculated total amounts for each item.

function CALC_TOTAL_AMOUNT(items,quantities,prices,discounts){

let totalAmounts = [];

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

let quantity = quantities[i];

let price = prices[i];

let discount = discounts[i];

const itemAmount = quantity * price * (1-discount);

totalAmounts.push([itemAmount]);

}

return totalAmounts;

}

const items = [‘Apple’, ‘Banana’, ‘Orange’];

const quantities = [5, 3, 2];

const prices = [1, 0.5, 0.8];

const discounts = [0.1, 0.2, 0.15];

const totalAmountsArray = CALC_TOTAL_AMOUNT(items, quantities, prices, discounts);

console.log(totalAmountsArray);

// Output: [[4.5], [1.2], [1.36]]

The CALC_DIS function calculates the total cost considering the quantity, individual cost, and discount. The CALC_TOTAL_AMOUNT function calculates the total amount for each item in a list, accounting for quantities, prices, and discounts, and returns an array of these amounts.

In this example, we’ll create a custom formula that calculates the total amount of a purchase with discounts based on the quantity of items purchased.

Scenario: You want to create a custom formula that calculates the total amount of a purchase with discounts based on the quantity of items purchased.

Data Table:

ABCDE
ItemQuantityPriceDiscountTotal Amount
Item 15100.1
Item 22150.05
Item 33200.2
Total

Step 1: Setting up the Spreadsheet

  • Create a new Google Sheets document.
  • Enter the item names, quantities, prices, and discounts in columns A to D starting from row 2.
  • Leave cells in column E empty for now.

Step 2: Writing the Google Apps Script Code

  • Click on “Extensions” in the top menu, then select “Apps Script”.
  • Delete any default code and replace it with the following script:

// Custom formula to calculate the total amount with discounts

function CALC_TOTAL_AMOUNT(items, quantities, prices, discounts) {

var totalAmounts = [];

for (var i = 0; i < items.length; i++) {

var quantity = quantities[i];

var price = prices[i];

var discount = discounts[i];

var itemAmount = quantity * price * (1 – discount);

totalAmounts.push([itemAmount]);

}

return totalAmounts;

}

Step 3: Using the Custom Formula in Google Sheets

  • Go back to your Google Sheets document.
  • In cell E2, enter the following formula:

=CALC_TOTAL_AMOUNT(A2:A, B2:B, C2:C, D2:D)

Explanation of the Code:

  • The function CALC_TOTAL_AMOUNT calculates the total amount of a purchase with discounts based on the quantity of items purchased.
  • It takes four parameters: items, quantities, prices, and discounts.
    • items: The range of cells containing the item names.
    • quantities: The range of cells containing the quantities of items purchased.
    • prices: The range of cells containing the prices per item.
    • discounts: The range of cells containing the discounts for each item.
  • Inside the function, a loop iterates through each item.
  • The total amount for each item is calculated by multiplying the quantity, price, and the complement of the discount.
  • The calculated total amounts are stored in the totalAmounts array.
  • The function returns the array of calculated total amounts.

Step 4: Testing the Custom Formula

  • Enter item names, quantities, prices, and discounts in columns A to D starting from row 2.
  • Use the custom formula in cell E2 to calculate the total amount of the purchase with discounts for each item.

For example, if you have entered the item details as shown in the data table, the calculated total amounts after applying discounts should appear in column E.

Remember to enable the “Google Apps Script” extension and use the exact function name (CALC_TOTAL_AMOUNT) in your formula.

The provided code includes two functions, CALC_DIS and CALC_TOTAL_AMOUNT, that perform calculations related to costs, quantities, prices, and discounts. It also explains how to use these functions to create a custom formula in Google Sheets. Here’s a summary of the steps and the code’s functionality:

  1. CALC_DIS(qty, cost, discount):
    • This function calculates the total cost for a given quantity of items considering their individual cost and a discount rate.
    • Parameters: qty (quantity), cost (individual cost of each item), and discount (discount rate as a decimal between 0 and 1).
    • Inside the function, the total variable is initialized to 0.
    • The total cost is calculated by multiplying the qty by the cost, and then further reducing the result by the product of qty, cost, and (1 - discount).
    • The calculated total is returned by the function.
  2. CALC_TOTAL_AMOUNT(items, quantities, prices, discounts):
    • This function calculates the total amount for each item in a list, considering quantities, individual prices, and discount rates.
    • Parameters: items (item names), quantities (quantities of each item), prices (individual prices of each item), and discounts (discount rates for each item).
    • The function iterates over each item using a for loop.
    • Inside the loop, it calculates the total amount for the item by multiplying quantity, price, and (1 - discount).
    • The calculated amount is added to the totalAmounts array.
    • After iterating through all items, the function returns an array (totalAmounts) containing the calculated total amounts for each item.
  3. Using the Custom Formula in Google Sheets:
    • Create a new Google Sheets document.
    • Enter item names, quantities, prices, and discounts in columns A to D.
    • Write the custom formula in cell E2 using the CALC_TOTAL_AMOUNT function, passing the respective ranges as arguments.
    • The custom formula calculates the total amount with discounts for each item based on the provided quantities, prices, and discounts.

By using the custom formula in Google Sheets, you can automatically calculate and update the total amounts for each item with the given quantities, prices, and discounts. The provided script outlines how to set up this custom formula and demonstrates its application in a practical scenario.

Google-Apps-Script-Sheets-Formulas-Part-1Download