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

Follow these steps in order

1. Set up the Orders tab

   Put headers in row 1 and a few test rows from row 2 down. Column C holds the amount as a number, and column D holds the status word, such as Done.

2. Run the summary by hand first

   Choose Extensions, then Apps Script. Paste both functions over the starter code and save. Choose sendWeeklyOrderSummary in the function dropdown above the code, then click Run. Approve 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 click Run once. It does nothing when a trigger for sendWeeklyOrderSummary already exists, so running it twice does not add a second trigger.

4. Check the trigger list

   Click Triggers, the clock icon in the left sidebar. sendWeeklyOrderSummary is 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 run stops with "Add a Sheet tab named Orders before running this function."

Rename the tab to Orders, or change SHEET_NAME in the code to the name of your tab. The name has to match exactly, including capital letters.

The total is 0 even though column C has values.

Remove any text from the amount cells, such as 1,200 USD, and leave plain numbers. A cell holding text is skipped. Use Format, Number, Currency if you want a currency sign.

Every row is counted as not Done.

Use the same spelling of Done in every row of column D, or change FINISHED_STATUS to the word you use. Spaces around the word are ignored, but done and Done are different.

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, the gear icon.