Schedule and Send Weekly Reports with Google Apps Script

Weekly reports are one of the most common — and most time-consuming — recurring tasks. Apps Script can automate the entire process: pull data from your sheets, format it, and deliver it to inboxes every Monday morning without you lifting a finger.

Basic weekly report

function sendWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Weekly Data');
  const data = sheet.getDataRange().getValues();

  const now = new Date();
  const weekString = `Week of ${now.toLocaleDateString('en-US', { month: 'long', day: 'numeric', year: 'numeric' })}`;

  let html = `
    <div style="font-family:sans-serif;max-width:600px;margin:0 auto;">
      <h1 style="color:#1a73e8;">📊 Weekly Report</h1>
      <p style="color:#5f6368;">${weekString}</p>
      <table width="100%" border="1" cellpadding="8" cellspacing="0"
             style="border-collapse:collapse;font-size:14px;">
  `;

  data.forEach((row, i) => {
    const bg = i === 0 ? '#f1f3f4' : (i % 2 === 0 ? '#ffffff' : '#f8f9fa');
    const weight = i === 0 ? 'bold' : 'normal';
    html += `<tr style="background:${bg};">` +
      row.map(cell => `<td style="padding:8px 12px;font-weight:${weight};">${cell}</td>`).join('') +
      '</tr>';
  });

  html += `
      </table>
      <p style="color:#9aa0a6;font-size:12px;margin-top:24px;">
        This report was generated automatically by Google Apps Script.
      </p>
    </div>
  `;

  const recipients = ['[email protected]', '[email protected]'];

  recipients.forEach(email => {
    GmailApp.sendEmail(email, `Weekly Report – ${weekString}`, '', { htmlBody: html });
  });

  Logger.log('Weekly report sent to ' + recipients.length + ' recipients.');
}

Include multiple sections with KPIs

function sendFullWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const kpis = ss.getSheetByName('KPIs').getDataRange().getValues();
  const sales = ss.getSheetByName('Sales').getDataRange().getValues();
  const issues = ss.getSheetByName('Open Issues').getDataRange().getValues();

  function buildTable(data) {
    let t = '<table width="100%" border="1" cellpadding="8" cellspacing="0" style="border-collapse:collapse;font-size:13px;">';
    data.forEach((row, i) => {
      const bg = i === 0 ? '#e8f0fe' : (i % 2 === 0 ? '#fff' : '#f8f9fa');
      t += `<tr style="background:${bg};">` +
        row.map(cell => `<td style="padding:6px 10px;">${cell}</td>`).join('') +
        '</tr>';
    });
    return t + '</table>';
  }

  const html = `
    <div style="font-family:sans-serif;max-width:640px;margin:0 auto;color:#202124;">
      <h1 style="color:#1a73e8;border-bottom:2px solid #e8f0fe;padding-bottom:12px;">
        Weekly Business Report
      </h1>

      <h2 style="color:#3c4043;">Key Metrics</h2>
      ${buildTable(kpis)}

      <h2 style="color:#3c4043;margin-top:24px;">Sales This Week</h2>
      ${buildTable(sales)}

      <h2 style="color:#3c4043;margin-top:24px;">Open Issues</h2>
      ${buildTable(issues)}

      <p style="font-size:11px;color:#9aa0a6;margin-top:32px;">
        Auto-generated on ${new Date().toLocaleString()}
      </p>
    </div>
  `;

  GmailApp.sendEmail(
    '[email protected]',
    `Weekly Report – ${new Date().toDateString()}`,
    '',
    { htmlBody: html }
  );
}

Calculate week-over-week changes

function getWeekOverWeekChange(currentVal, previousVal) {
  if (!previousVal || previousVal === 0) return 'N/A';
  const change = ((currentVal - previousVal) / previousVal * 100).toFixed(1);
  const arrow = change >= 0 ? '▲' : '▼';
  const colour = change >= 0 ? '#34a853' : '#ea4335';
  return `<span style="color:${colour};">${arrow} ${Math.abs(change)}%</span>`;
}

Set up the Monday morning trigger

function createWeeklyTrigger() {
  ScriptApp.newTrigger('sendWeeklyReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(7)
    .create();
}

Tips

Set your script's timezone in Project Settings to ensure the trigger fires at the right local time.
Use SpreadsheetApp.openById(id) if your data lives in a different spreadsheet from the one your script is bound to.
Keep email HTML simple and inline-styled — many email clients strip <style> blocks.
Add a View Full Report link in the email pointing to the Google Sheet for recipients who want to dig deeper.
Check GmailApp daily quotas: 100 emails/day for Gmail, 1,500 for Google Workspace.