Create Apps Script custom functions How to get the total amount of items including the tax rate

How to get the total amount of items including the tax rate

CALC_TOTAL_AMOUNT

Product	Price	Quantity	Tax Rate	Subtotal	Total Amount
Product A	10	5	0.08	4	54
Product B	20	3	0.05	3	63
Product C	15	2	0.1	3	33
				Total	150

In this example, we’ll create a custom formula that calculates the total amount of a sales transaction based on different products and their quantities.

Scenario: You want to create a custom formula that calculates the total amount of a sales transaction based on different products and their quantities, considering the tax rate.

Data Table:

A	B	C	D	E	F
Product	Price	Quantity	Tax Rate	Subtotal	Total Amount
Product A	10	5	0.08
Product B	20	3	0.05
Product C	15	2	0.10

Total

Step 1: Setting up the Spreadsheet

Create a new Google Sheets document.
Enter the product names, prices, quantities, and tax rates in columns A to D starting from row 2.
Leave cells in columns E and F 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:

Step 3: Using the Custom Formula in Google Sheets

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

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

function CALC_TAX_TOTAL(price,qty,tax){

const total = price * qty * tax;

return total;

}

function CALC_TAX_TOTAL_AMOUNT(items,price,qty,tax){

let total = 0;

const arr = [];

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

const tot = price[i] * qty[i];

const subTotal = tot * tax[i];

arr.push([subTotal+tot]);

total += subTotal+tot;

}

arr.push(total);

return arr;

}

CALC_TAX_TOTAL(price, qty, tax): This function calculates the total tax amount for a given price, quantity, and tax rate. It takes three parameters: price (individual price), qty (quantity), and tax (tax rate as a decimal between 0 and 1).
Inside the function, a constant named total is calculated by multiplying the price and qty, and then further multiplying the result by the tax. This calculates the tax amount for the specified quantity of items at the given price.
The calculated total tax amount is returned by the function.

function CALC_TAX_TOTAL(price, qty, tax) {

const total = price * qty * tax;

return total;

}

Example usage:

const taxAmount = CALC_TAX_TOTAL(10, 5, 0.1); // Calculates (10 * 5 * 0.1) = 5

console.log(taxAmount); // Output: 5

CALC_TAX_TOTAL_AMOUNT(items, price, qty, tax): This function calculates the total amount, including tax, for a list of items with their respective prices, quantities, and tax rates. It takes four arrays as parameters: items (item names), price (individual prices), qty (quantities of each item), and tax (tax rates for each item).
The function initializes two variables: total to keep track of the cumulative total amount, and an array arr to store the subtotals and the final total.
It then iterates over each item using a for loop. Inside the loop, it calculates the total amount for the current item by multiplying its price and quantity. It also calculates the subtotal by multiplying the total amount with the tax rate.
The subtotal, when added to the total amount, gives the total amount including tax for the item. This value is added to the arr array, and the total variable is updated.
After iterating through all items, the final total is appended to the arr array. The function then returns the arr array containing the subtotals for each item and the final total.

function CALC_TAX_TOTAL_AMOUNT(items, price, qty, tax) {

let total = 0;

const arr = [];

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

const tot = price[i] * qty[i];

const subTotal = tot * tax[i];

arr.push([subTotal + tot]);

total += subTotal + tot;

}

arr.push(total);

return arr;

}

Example usage:

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

const prices = [1, 0.5, 0.8];

const quantities = [5, 3, 2];

const taxes = [0.1, 0.15, 0.08];

const totalAmountsArray = CALC_TAX_TOTAL_AMOUNT(items, prices, quantities, taxes);

console.log(totalAmountsArray);

// Output: [[5.5], [0.975], [1.36], 7.835]

The CALC_TAX_TOTAL function calculates the tax amount for a specified price and quantity. The CALC_TAX_TOTAL_AMOUNT function calculates the total amount including tax for each item in a list, considering their prices, quantities, and tax rates, and returns an array containing subtotals and the final total.

Explanation of the Code:

The function CALC_TOTAL_AMOUNT calculates the total amount of a sales transaction based on different products, their prices, quantities, and tax rates.
It takes four parameters: products, prices, quantities, and taxRates.
- products: The range of cells containing the product names.
- prices: The range of cells containing the prices of each product.
- quantities: The range of cells containing the quantities of each product.
- taxRates: The range of cells containing the tax rates for each product.
Inside the function, a loop iterates through each product.
For each product, it calculates the subtotal by multiplying the price and quantity.
It then calculates the tax amount by multiplying the subtotal with the tax rate.
The item total is calculated by adding the subtotal and tax.
The calculated item totals are accumulated to calculate the total amount of the sales transaction.
The function returns the total amount of the sales transaction.

Step 4: Testing the Custom Formula

Enter product names, prices, quantities, and tax rates in columns A to D starting from row 2.
Use the custom formula in cell F2 to calculate the total amount of the sales transaction.

For example, if you have entered the product details as shown in the data table, the calculated total amount of the sales transaction should appear in cell F2.

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

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

📊 Seamlessly Calculate Total Amounts with Tax Rates Using Custom Google Apps Script Functions! 📊

Ever wished there was an easier way to handle complex calculations involving tax rates? Say hello to efficiency with our cutting-edge solution! 🚀

Introducing CALC_TOTAL_AMOUNT Function: Imagine effortlessly obtaining the total amount of items, factoring in their prices, quantities, and tax rates. With this powerful custom formula, you can make those calculations in a snap! 📈💸

💡 Scenario: Picture this: a sales transaction involving diverse products, each with its price, quantity, and unique tax rate. Instead of juggling manual calculations, use CALC_TOTAL_AMOUNT to instantly compute the total amount for each item, taxes included.

🌐 How it Works:

Calculate subtotals based on product prices and quantities.
Incorporate tax rates to calculate tax amounts.
Combine subtotals and tax amounts to get the total amount for each product.

💼 Business Advantages:

Save time and effort: Bid farewell to tedious manual calculations and let automation do the heavy lifting.
Ensure accuracy: Eliminate human errors and ensure precise calculations for your financial transactions.
Handle varying tax rates: Seamlessly accommodate different tax rates for different products.

🚀 Get Started with Efficiency:

Create a new Google Sheets document.
Enter product names, prices, quantities, and tax rates.
Utilize the CALC_TOTAL_AMOUNT formula in a cell to instantly compute the total amounts.

Supercharge your spreadsheet skills with the ability to effortlessly calculate total amounts, taxes included! 📊💰

#GoogleAppsScript #FinancialCalculations #Automation #DataAccuracy #ProductivityHacks

How to get the total amount of items including the tax rate

CALC_TOTAL_AMOUNT

Share this:

Related