Calculating the Total Cost Using Google Apps Script

Google Apps Script is a powerful tool that allows you to automate tasks and enhance your Google Sheets capabilities. In this blog post, we will demonstrate how to use Google Apps Script to calculate the total cost from a sample data table. This script can be especially useful for financial calculations, budgeting, or any scenario where you need to sum values across multiple rows and columns.

Sample Data Table

Let’s start with a sample data table in a Google Sheet named CostsSheet. This table has the following columns:

A	B	C
Item	Quantity	Unit Price
Apples	10	0.50
Bananas	5	0.30
Oranges	8	0.70
Grapes	2	2.00
Mangoes	6	1.50

Goals

1. Calculate the total cost for each item.
2. Calculate the grand total cost of all items.

Google Apps Script Code

Below is the Google Apps Script code to accomplish this task:

function calculateTotalCost() {
  // Open the spreadsheet and the CostsSheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('CostsSheet');

  // Get the data from the sheet
  var data = sheet.getDataRange().getValues();

  // Initialize the total cost variable
  var totalCost = 0;

  // Loop through the data to calculate the total cost for each item
  for (var i = 1; i < data.length; i++) { // Skip the header row
    var quantity = data[i][1];
    var unitPrice = data[i][2];
    var itemTotal = quantity * unitPrice;

    // Write the total cost for each item in the next column
    sheet.getRange(i + 1, 4).setValue(itemTotal);

    // Add the item total to the grand total cost
    totalCost += itemTotal;
  }

  // Write the grand total cost at the bottom of the total cost column
  sheet.getRange(data.length + 1, 4).setValue('Grand Total:');
  sheet.getRange(data.length + 1, 5).setValue(totalCost);

  // Set the header for the total cost column
  sheet.getRange(1, 4).setValue('Total Cost');
}

Explanation

1. Open Spreadsheet and Sheet:var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('CostsSheet'); This opens the active spreadsheet and the specific sheet we are working with: CostsSheet.
2. Get Data from Sheet:var data = sheet.getDataRange().getValues(); This retrieves all the data from the CostsSheet.
3. Initialize the Total Cost Variable:var totalCost = 0; This initializes a variable to store the grand total cost.
4. Loop Through the Data to Calculate Total Cost for Each Item:for (var i = 1; i < data.length; i++) { // Skip the header row var quantity = data[i][1]; var unitPrice = data[i][2]; var itemTotal = quantity * unitPrice; // Write the total cost for each item in the next column sheet.getRange(i + 1, 4).setValue(itemTotal); // Add the item total to the grand total cost totalCost += itemTotal; } This loop calculates the total cost for each item by multiplying the quantity by the unit price. It then writes the total cost for each item in the next column and adds it to the grand total cost.
5. Write the Grand Total Cost:sheet.getRange(data.length + 1, 4).setValue('Grand Total:'); sheet.getRange(data.length + 1, 5).setValue(totalCost); This writes the grand total cost at the bottom of the total cost column.
6. Set the Header for the Total Cost Column:sheet.getRange(1, 4).setValue('Total Cost'); This sets the header for the total cost column.

Conclusion

This Google Apps Script efficiently calculates the total cost for each item and the grand total cost from a sample data table. By following the steps outlined in this blog post, you can automate and streamline your financial calculations in Google Sheets. Happy scripting!