# 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:

### Goals

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

``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!