Google Sheetsspreadsheet~15 mins

Why Apps Script automates Google Sheets - Business Case Study

Choose your learning style9 modes available

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

Scenario Mode

👤 Your Role: You are a data analyst at a small retail company.

📋 Request: Your manager wants you to automate repetitive tasks in Google Sheets to save time and reduce errors.

📊 Data: You have a sales data sheet with daily sales records including Date, Product, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create an automated script that summarizes total sales per product each week and emails the summary report to your manager.

Progress0 / 6 steps

Sample Data

Date	Product	Quantity Sold	Sales Amount
2024-06-01	Apples	10	30
2024-06-01	Bananas	5	15
2024-06-02	Apples	8	24
2024-06-02	Oranges	12	36
2024-06-03	Bananas	7	21
2024-06-03	Apples	6	18
2024-06-04	Oranges	10	30
2024-06-04	Bananas	9	27
2024-06-05	Apples	15	45
2024-06-05	Oranges	8	24

Step 1: Open the Google Sheets file with the sales data.

Expected Result

Step 2: Create a new sheet named 'Weekly Summary' to store the summarized data.

Expected Result

Step 3: Write an Apps Script to calculate total sales amount per product for the week.

function summarizeWeeklySales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('Sheet1');
  const summarySheet = ss.getSheetByName('Weekly Summary');
  const data = dataSheet.getDataRange().getValues();

  // Clear previous summary
  summarySheet.clear();

  // Create a map to hold product totals
  const productTotals = {};

  // Skip header row (index 0)
  for (let i = 1; i < data.length; i++) {
    const product = data[i][1];
    const salesAmount = data[i][3];
    if (productTotals[product]) {
      productTotals[product] += salesAmount;
    } else {
      productTotals[product] = salesAmount;
    }
  }

  // Write headers
  summarySheet.appendRow(['Product', 'Total Sales Amount']);

  // Write totals
  for (const product in productTotals) {
    summarySheet.appendRow([product, productTotals[product]]);
  }
}

Expected Result

Step 4: Run the script to generate the weekly sales summary.

Run summarizeWeeklySales() function in Apps Script editor.

Expected Result

Step 5: Add code to send the summary report by email to your manager.

function emailWeeklySummary() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheet = ss.getSheetByName('Weekly Summary');
  const data = summarySheet.getDataRange().getValues();

  let message = 'Weekly Sales Summary:\n\n';
  for (let i = 1; i < data.length; i++) {
    message += `${data[i][0]}: $${data[i][1]}\n`;
  }

  MailApp.sendEmail('manager@example.com', 'Weekly Sales Summary', message);
}

Expected Result

Step 6: Set a time-driven trigger to run the summarize and email functions every week automatically.

In Apps Script editor, go to Triggers > Add Trigger > select summarizeWeeklySales and emailWeeklySummary > Time-driven > Week timer > choose day and time.

Expected Result

Final Result

Weekly Summary Sheet:

+----------+--------------------+
| Product  | Total Sales Amount  |
+----------+--------------------+
| Apples   | 117                |
| Bananas  | 63                 |
| Oranges  | 90                 |
+----------+--------------------+

Email sent to manager@example.com with this summary.

✓Apples have the highest total sales amount this week.

✓Bananas have the lowest total sales amount.

✓Automating this report saves time and ensures consistent updates.

Bonus Challenge

Modify the script to also calculate and include total quantity sold per product in the summary and email.

Show Hint