Email yourself a weekly sheet summary

1 · Prepare

Put these pieces in place

• Open the script from the spreadsheet with Extensions, then Apps Script.
• Create a tab named Orders with headers in row 1 and the first data row in row 2.
• Put a number in column C for the amount and a status word in column D, such as Done.
• Use an account that can send email through Gmail.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const SHEET_NAME = 'Orders'; // The tab the summary reads. It must already exist.
const SEND_DAY = ScriptApp.WeekDay.FRIDAY; // The weekday the trigger runs. Other values look like ScriptApp.WeekDay.MONDAY.
const SEND_HOUR = 16; // The hour the trigger runs, in the project time zone. 16 means some time between 4 PM and 5 PM.
const FINISHED_STATUS = 'Done'; // Rows whose column D does not match this word exactly are counted as still open.

function createWeeklySummaryTrigger() {
  const alreadyInstalled = ScriptApp.getProjectTriggers().some((trigger) => {
    return trigger.getHandlerFunction() === 'sendWeeklyOrderSummary';
  });
  if (alreadyInstalled) return;

  ScriptApp.newTrigger('sendWeeklyOrderSummary')
    .timeBased()
    .onWeekDay(SEND_DAY)
    .atHour(SEND_HOUR)
    .create();
}

function sendWeeklyOrderSummary() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);
  if (!sheet) throw new Error('Add a Sheet tab named ' + SHEET_NAME + ' before running this function.');

  let rowCount = 0;
  let total = 0;
  let notDone = 0;
  const lastRow = sheet.getLastRow();
  if (lastRow >= 2) {
    const rows = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
    const dataRows = rows.filter((row) => row.some((cell) => cell !== ''));
    rowCount = dataRows.length;
    dataRows.forEach((row) => {
      const amount = Number(row[2]);
      if (!isNaN(amount)) total = total + amount;
      if (String(row[3]).trim() !== FINISHED_STATUS) notDone = notDone + 1;
    });
  }

  const reportDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  const lines = [
    'Orders summary for ' + reportDate,
    'Rows with data: ' + rowCount,
    'Total amount: ' + total,
    'Not marked ' + FINISHED_STATUS + ': ' + notDone,
    spreadsheet.getUrl()
  ];

  GmailApp.sendEmail(Session.getEffectiveUser().getEmail(), 'Weekly orders summary', lines.join('\n'));
}

4 · Run safely

Follow these steps in order

1. Set up the Orders tab

   Row 1 holds the headers and the data starts in row 2. Column C holds the amount as a number, and column D holds the status word.

2. Run the summary by hand first

   Paste both functions and save. Choose sendWeeklyOrderSummary and press Run. Approve the access when Google asks, then compare the numbers in the email with the sheet.

3. Install the weekly trigger

   When the numbers look right, choose createWeeklySummaryTrigger and run it once. It skips the work when a trigger for sendWeeklyOrderSummary already exists, so running it twice does not add a second trigger.

4. Check the trigger list

   Open Triggers in the left sidebar. sendWeeklyOrderSummary should be listed as a week timer for Friday. The trigger runs as the account that created it, and that is the account the summary is sent to.

5 · Confirm

How to tell it worked

An email with the subject Weekly orders summary lists the report date, how many data rows the tab has, the total of column C, how many rows are not Done, and a link to the spreadsheet.

If the result is different

Troubleshooting

The script says Add a Sheet tab named Orders before running this function.

Rename the tab to Orders, or change the name in the code and in the editable values. The name has to match exactly, including capital letters.

The total is 0 even though column C has values.

The amounts have to be numbers. A cell holding text such as 1,200 USD is skipped. Remove the extra text and let the sheet format the number instead.

Every row is counted as not Done.

The check compares column D with the word Done. Spaces around the word are ignored, but done and Done are different, so use the same spelling in every row.

No email arrives on Friday.

Open Executions in the left sidebar to see whether the trigger ran and what it reported. The hour follows the project time zone, which you can check under Project Settings.