0
Jump into concepts and practice - no test required
Recommended
| 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 |
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]]);
}
}Run summarizeWeeklySales() function in Apps Script editor.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);
}In Apps Script editor, go to Triggers > Add Trigger > select summarizeWeeklySales and emailWeeklySummary > Time-driven > Week timer > choose day and time.Weekly Summary Sheet: +----------+--------------------+ | Product | Total Sales Amount | +----------+--------------------+ | Apples | 117 | | Bananas | 63 | | Oranges | 90 | +----------+--------------------+ Email sent to manager@example.com with this summary.
Modify the script to also calculate and include total quantity sold per product in the summary and email.
function fillCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange('A1:A3').setValue('Hello');
}function sendEmail() {
MailApp.sendEmail('user@example.com', 'Subject', 'Body text')
}