Google Sheets get Data from two sheets to use within a custom function

How to use data from two sheets to create a custom Apps Script function 

CALC_TOTAL_COST_WITH_TAX

function getTaxRate(category) {

const sheets = SpreadsheetApp.getActive().getSheetByName(‘TaxTable’);

const taxTable = sheets.getDataRange().getValues();

let tax = 0

taxTable.forEach(val =>{

if (val[0] == category) {

tax = val[1];

}

})

return tax;

}

function test(){

Logger.log(getTaxRate(‘Category 1’))

}

Category	Tax Rate
Category 1	0.1
Category 2	0.15
Category 3	0.08

Item	Quantity	Price	Category	Cost
Item 1	5	10	Category 3	54
Item 2	3	15	Category 2	51.75
Item 3	2	20	Category 1	44
Total Cost	149.75

getTaxRate(category): This function retrieves the tax rate for a specific category from a sheet named ‘TaxTable’ within the active spreadsheet. Here’s a detailed breakdown of how this function works:

• const sheets = SpreadsheetApp.getActive().getSheetByName(‘TaxTable’);: This line gets the active spreadsheet and fetches the sheet named ‘TaxTable’ using the getSheetByName method. The variable sheets now holds a reference to this sheet.
• const taxTable = sheets.getDataRange().getValues();: Here, the getDataRange() method is used to get the entire range of data in the ‘TaxTable’ sheet. The getValues() method is then used to retrieve the values within that range. This produces a two-dimensional array called taxTable, where each row represents a row in the sheet, and each column represents a cell value in that row.
• let tax = 0;: A variable named tax is initialized to 0. This variable will store the tax rate for the specified category.
• taxTable.forEach(val => {…}: This line initiates a loop that iterates through each row of the taxTable array. For each row (represented by the array val), the following code block is executed:
  • if (val[0] == category) { tax = val[1]; }: This condition checks whether the value in the first column of the current row (val[0]) matches the provided category. If there’s a match, it means the tax rate for the specified category has been found in the second column of that row (val[1]). The tax rate is assigned to the tax variable.
• Finally, the function returns the value of the tax variable, which is either the tax rate found for the specified category or the default value of 0 if no match was found.
• test(): This function is a test function that logs the result of calling the getTaxRate(category) function. It’s meant to showcase how the getTaxRate function works by providing an example usage.
  • Logger.log(getTaxRate(‘Category 1’)): This line calls the getTaxRate function with the argument ‘Category 1’. The result, which is the tax rate associated with this category, is then logged to the execution log using the Logger.log() method.

In summary, the code retrieves tax rates from a ‘TaxTable’ sheet based on specified categories and provides a test function to demonstrate its functionality. The getTaxRate(category) function looks up the tax rate associated with a given category, and the test() function showcases this behavior by logging the tax rate for the category ‘Category 1’.

In this example, we’ll create a custom formula that calculates the total cost of items based on their categories, with different tax rates applied to each category.

Scenario: You have a list of items with their quantities, prices, and categories in columns A, B, and C. Additionally, you have a separate table of categories and their corresponding tax rates in columns F and G. You want to create a custom formula that calculates the total cost for each item, considering the tax rates based on their categories.

Data Table:

A	B	C	D	E	F	G
Item	Quantity	Price	Category	Cost
Item 1	5	10	Category 1
Item 2	3	15	Category 2
Item 3	2	20	Category 1
Total Cost

Tax Rate Table:

F	G
Category	Tax Rate
Category 1	0.1
Category 2	0.15
Category 3	0.08

Step 1: Setting up the Spreadsheet

• Create a new Google Sheets document.
• Enter your data in columns A to C starting from row 2.
• Leave a blank cell in column D for the calculated cost for each item.
• Leave another blank cell in column E for the custom formula that calculates the total cost.
• Enter the tax rate table data in columns F and G.

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:

function CALC_TOTAL_TAX(item,qty,price,cat){

const taxRate = getTaxRate(cat);

const subTotal = qty * price;

const total = subTotal * taxRate + subTotal;

return total;

}

function CALC_FINAL_TOTAL(qty,price,cat){

let total = 0;

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

const taxRate = getTaxRate(cat[i]);

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

total += subTotal * taxRate + subTotal;

}

return total;

}

Step 3: Using the Custom Formula in Google Sheets

• Go back to your Google Sheets document.
• In a cell where you want the total cost to appear (let’s say cell E6), enter the following formula:

CALC_TOTAL_TAX(item, qty, price, cat): This function calculates the total cost of an item, including tax, based on its quantity, price, and category. Here’s a detailed breakdown of how this function works:

• const taxRate = getTaxRate(cat);: This line calls the getTaxRate(category) function to retrieve the tax rate associated with the given category.
• const subTotal = qty * price;: Calculates the subtotal by multiplying the quantity and the price of the item.
• const total = subTotal * taxRate + subTotal;: Calculates the total cost by adding the tax amount to the subtotal. The tax amount is obtained by multiplying the subtotal by the tax rate.
• Finally, the function returns the calculated total cost.

CALC_FINAL_TOTAL(qty, price, cat): This function calculates the total cost for multiple items, considering their quantities, prices, and categories. It iterates through each item using a for loop, and for each item, it calculates the total cost including tax using a similar approach to the CALC_TOTAL_TAX function. The calculated costs for each item are accumulated to calculate the final total cost. The function returns this final total.

In summary, these functions work together to calculate the total cost of items, considering their quantities, prices, and tax rates based on categories. The CALC_TOTAL_TAX function calculates the total cost for a single item, and the CALC_FINAL_TOTAL function calculates the total cost for multiple items. The getTaxRate function retrieves the tax rate for a given category from a spreadsheet, and the test function is a demonstration of how to use the getTaxRate function.

The provided code and explanation outline how to use data from two sheets to create a custom Google Apps Script function called CALC_TOTAL_COST_WITH_TAX. This function calculates the total cost of items based on their categories while considering tax rates. Here’s a summary:

1. getTaxRate(category) Function:
   • This function retrieves the tax rate for a specific category from a sheet named ‘TaxTable’ within the active spreadsheet.
   • The function uses the getActive() method to get the active spreadsheet and fetches the ‘TaxTable’ sheet using getSheetByName('TaxTable').
   • It then uses getDataRange() to retrieve the entire range of data in the ‘TaxTable’ sheet and getValues() to retrieve the values within that range, creating a two-dimensional array called taxTable.
   • The function initializes the tax variable to 0 and iterates through each row of taxTable. If the category matches, it assigns the corresponding tax rate to the tax variable.
   • The function returns the value of the tax variable.
   • The test() function demonstrates the getTaxRate(category) function’s usage by logging the tax rate for a specific category.
2. Custom Formula Calculation (CALC_TOTAL_COST_WITH_TAX):
   • This custom formula calculates the total cost of items based on their categories, with different tax rates applied to each category.
   • The CALC_TOTAL_COST_WITH_TAX function takes item details including item name, quantity, price, and category as arguments.
   • Within the function, it uses the getTaxRate(category) function to obtain the tax rate associated with the provided category.
   • The subtotal for the item is calculated by multiplying quantity and price.
   • The total cost for the item is then calculated by adding the tax amount (obtained by multiplying the subtotal by the tax rate) to the subtotal.
   • The function returns the calculated total cost for the item.
3. Using the Custom Formula in Google Sheets:
   • In a cell where you want the total cost to appear (e.g., cell E6), enter the formula =CALC_TOTAL_COST_WITH_TAX(item, qty, price, cat) to calculate the total cost for a specific item.

In summary, the code showcases how to leverage Google Apps Script to calculate the total cost of items considering tax rates from a separate table. The custom formula CALC_TOTAL_COST_WITH_TAX integrates the getTaxRate(category) function to efficiently calculate costs for each item based on their categories.