Turn Google Sheets Status Updates into an Async Weekly Digest

How should the Google Sheets Updates tab be structured?

The Google Sheets digest requires an Updates tab with one row per person’s project update and six uniquely named headers in row 1.

Keep header spelling and capitalization as shown; column order can change. Contributors working on two projects should submit two rows.

Date	Project	Owner	Accomplishments	Next steps	Blockers
2026-04-06	Comet Portal	Fictional Alex	Finished intake form	Review with support	None
2026-04-08	Comet Portal	Fictional Bea	Drafted help text	Add error messages	Waiting for field names
2026-04-10	Lantern Launch	Fictional Casey	Approved welcome email	Schedule internal review	None
2026-04-13	Lantern Launch	Fictional Casey	Revised onboarding checklist	Gather feedback	None

These people and projects are fictional. Dates must be actual Sheets date values or plain-text YYYY-MM-DD values; ambiguous text formats are rejected.

How do you configure and generate the weekly digest?

Configure the Google Sheets digest in a bound Apps Script project using explicit start and end dates, then run createWeeklyDigest.

1. Prepare the sheet. Create Updates with the headers and sample rows above. Avoid merged cells and title rows above row 1.
2. Set the reporting time zone. Open File > Settings in Sheets and select the intended time zone.
3. Open Apps Script. Choose Extensions > Apps Script and replace the starter code with the complete script below.
4. Set Script Properties. In Apps Script’s Project Settings, add START_DATE = 2026-04-06 and END_DATE = 2026-04-10. Set SOURCE_SHEET = Updates, or omit it for that default.
5. Save and run. Select createWeeklyDigest, click Run, and authorize an account permitted to read the spreadsheet and create Google Docs.
6. Open the document. Find its URL in the execution log and review it before sharing.

No API key or external service is required. Both reporting endpoints are included.

What Apps Script creates the project-grouped Google Doc?

The following Apps Script validates Google Sheets dates and headers, filters the configured reporting window, and creates a Google Doc grouped by project.

Projects sort alphabetically; updates sort by date, then owner. Date comparisons use calendar-date strings, with actual date cells interpreted in the spreadsheet’s time zone.

function createWeeklyDigest() {
  const properties = PropertiesService.getScriptProperties();
  const start = requireIsoDate_(
    properties.getProperty('START_DATE'), 'START_DATE'
  );
  const end = requireIsoDate_(
    properties.getProperty('END_DATE'), 'END_DATE'
  );

  if (start > end) {
    throw new Error('START_DATE must be on or before END_DATE.');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if (!spreadsheet) {
    throw new Error('Open this script from its source spreadsheet.');
  }

  const sheetName = properties.getProperty('SOURCE_SHEET') || 'Updates';
  const sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('Missing sheet: ' + sheetName);
  }

  const values = sheet.getDataRange().getValues();
  const headers = values[0].map(value => String(value).trim());
  const required = [
    'Date', 'Project', 'Owner',
    'Accomplishments', 'Next steps', 'Blockers'
  ];
  const columns = {};

  for (const name of required) {
    const matches = headers.filter(header => header === name).length;
    if (matches !== 1) {
      throw new Error('Expected exactly one header named: ' + name);
    }
    columns[name] = headers.indexOf(name);
  }

  const timeZone = spreadsheet.getSpreadsheetTimeZone();
  const projects = new Map();
  let updateCount = 0;

  for (let index = 1; index < values.length; index++) {
    const row = values[index];
    if (row.every(value => String(value).trim() === '')) {
      continue;
    }

    const rowNumber = index + 1;
    const rawDate = row[columns.Date];
    const date = rawDate instanceof Date
      ? sheetDateKey_(rawDate, timeZone, rowNumber)
      : requireIsoDate_(rawDate, 'Date at row ' + rowNumber);

    if (date < start || date > end) {
      continue;
    }

    const project = String(row[columns.Project]).trim();
    const owner = String(row[columns.Owner]).trim();

    if (!project || !owner) {
      throw new Error(
        'Row ' + rowNumber + ' needs both Project and Owner.'
      );
    }

    const update = {
      date: date,
      owner: owner,
      accomplishments: String(row[columns.Accomplishments]).trim(),
      nextSteps: String(row[columns['Next steps']]).trim(),
      blockers: String(row[columns.Blockers]).trim()
    };

    if (!projects.has(project)) {
      projects.set(project, []);
    }
    projects.get(project).push(update);
    updateCount++;
  }

  if (updateCount === 0) {
    throw new Error(
      'No updates found from ' + start + ' through ' + end +
      '. No document was created.'
    );
  }

  const title = 'Weekly status digest | ' + start + ' to ' + end;
  const document = DocumentApp.create(title);
  const body = document.getBody();

  body.appendParagraph(title)
    .setHeading(DocumentApp.ParagraphHeading.TITLE);
  body.appendParagraph(
    'Date range: ' + start + ' through ' + end +
    ' (inclusive; ' + timeZone + ')'
  );
  body.appendParagraph('Updates included: ' + updateCount);

  const sections = [
    ['Accomplishments', 'accomplishments'],
    ['Next steps', 'nextSteps'],
    ['Blockers', 'blockers']
  ];

  const projectNames = Array.from(projects.keys()).sort();

  for (const project of projectNames) {
    const updates = projects.get(project);
    updates.sort((a, b) =>
      a.date.localeCompare(b.date) || a.owner.localeCompare(b.owner)
    );

    body.appendParagraph(project)
      .setHeading(DocumentApp.ParagraphHeading.HEADING1);

    for (const [heading, field] of sections) {
      body.appendParagraph(heading)
        .setHeading(DocumentApp.ParagraphHeading.HEADING2);

      const entries = updates.filter(update => update[field] !== '');
      if (entries.length === 0) {
        body.appendParagraph('No entries supplied.');
        continue;
      }

      for (const update of entries) {
        body.appendListItem(
          update.date + ' | ' + update.owner + ': ' + update[field]
        ).setGlyphType(DocumentApp.GlyphType.BULLET);
      }
    }
  }

  const url = document.getUrl();
  document.saveAndClose();
  console.log('Weekly digest: ' + url);
  return url;
}

function requireIsoDate_(value, label) {
  const text = String(value == null ? '' : value).trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) {
    throw new Error(label + ' must use YYYY-MM-DD.');
  }

  const date = new Date(text + 'T00:00:00.000Z');
  if (
    Number.isNaN(date.getTime()) ||
    date.toISOString().slice(0, 10) !== text
  ) {
    throw new Error(label + ' is not a valid calendar date.');
  }
  return text;
}

function sheetDateKey_(date, timeZone, rowNumber) {
  if (Number.isNaN(date.getTime())) {
    throw new Error('Invalid date at row ' + rowNumber + '.');
  }
  return Utilities.formatDate(date, timeZone, 'yyyy-MM-dd');
}

Each successful run creates a new document in the executing account’s Drive; it does not update previous digests, email readers, or grant access.

What output should the April 6-10 sample produce?

The sample Google Sheets digest includes three updates across Comet Portal and Lantern Launch, excluding the April 13 row.

The title is Weekly status digest | 2026-04-06 to 2026-04-10. Opening lines show the inclusive range, spreadsheet time zone, and Updates included: 3.

Comet Portal

Accomplishments

• 2026-04-06 | Fictional Alex: Finished intake form
• 2026-04-08 | Fictional Bea: Drafted help text

Next steps

• 2026-04-06 | Fictional Alex: Review with support
• 2026-04-08 | Fictional Bea: Add error messages

Blockers

• 2026-04-06 | Fictional Alex: None
• 2026-04-08 | Fictional Bea: Waiting for field names

Lantern Launch

Accomplishments

• 2026-04-10 | Fictional Casey: Approved welcome email

Next steps

• 2026-04-10 | Fictional Casey: Schedule internal review

Blockers

• 2026-04-10 | Fictional Casey: None

Blank fields are omitted. An entirely blank project section says No entries supplied. Enter None explicitly for no blockers; missing text does not establish unblocked work.

Why does createWeeklyDigest group the correct date-range updates into a Google Doc?

createWeeklyDigest selects updates using normalized calendar dates, groups them by project, and writes sorted sections into a new Google Doc.

First, PropertiesService.getScriptProperties() supplies the reporting endpoints, and requireIsoDate_() validates them before their order is checked. This makes the reporting window explicit rather than dependent on today’s date. The script opens the bound spreadsheet, selects the source tab, and reads its values. Mapping header names to indexes allows column reordering instead of relying on fixed positions.

Next, each nonempty row’s date is validated. Actual date cells pass through sheetDateKey_() using the spreadsheet time zone; text dates pass through requireIsoDate_(). Fixed-width ISO strings sort chronologically, so comparisons include both endpoints without timestamp arithmetic. Matching rows become trimmed update objects stored in a Map keyed by project, keeping contributors’ entries together rather than preserving sheet order.

After counting matches, DocumentApp.create() creates the document and adds its title and reporting details. Projects sort alphabetically, then updates sort by date and owner. Section loops write nonblank fields as attributed bullets rather than copying entire rows. Finally, the script retrieves the URL, saves and closes the document, then logs and returns that URL.

How do you fix date, header, and empty-report failures?

Fix Google Sheets digest failures by correcting the reported source value, spreadsheet time zone, header, or reporting-window property before rerunning.

Failure	Exact fix
Date at row 3 must use YYYY-MM-DD.	Replace text such as 4/8/2026 with 2026-04-08, or enter an actual Sheets date. Display formatting alone does not convert text.
A final-day timestamp falls outside the range	Set Sheets File > Settings > Time zone to the reporting zone, verify the affected cell’s date, and rerun. Use YYYY-MM-DD text for date-only reporting without timestamp conversion.
Expected exactly one header named: Next steps	Put Next steps in row 1 exactly once. Rename Next Steps and remove or rename duplicate matching columns.
No updates found	Compare START_DATE and END_DATE with source dates and confirm SOURCE_SHEET identifies the intended tab. Correct the mismatch and rerun.

Date validation covers every nonempty row, including out-of-range rows. The spreadsheet’s time zone, not the Apps Script project’s time zone, controls timestamp interpretation. No matching updates means no document is created.

How should teammates review the generated digest asynchronously?

Share the generated Google Doc with intended reviewers and specify a response deadline with a time zone.

Spreadsheet access does not automatically carry over. Ask reviewers to comment on relevant blockers. The digest is a snapshot; later Sheets edits do not appear automatically, so clearly identify any regenerated replacement.

When should you avoid this Google Sheets digest workflow?

Avoid this static Google Docs digest when reporting must stay continuously current or matching updates require different audiences.

• Live reporting: Use a filtered Sheets view or dashboard.
• Restricted audiences: Separate source data by audience or build permission-aware reporting; this script combines all matching rows.
• Unattended weekly delivery: Add date-window calculation and duplicate-run protection before scheduling a trigger. Otherwise, unchanged Script Properties produce repeated documents for the same dates.