Turn Google Sheets Status Updates into an Async Weekly Digest
How can I turn Google Sheets status updates into a project-grouped weekly Google Doc digest for a specific date range?
Set the reporting dates in Apps Script, then run it to group matching spreadsheet updates by project in a new Google Doc. Each entry includes its date, owner, accomplishments, next steps, and blockers. The script does not share the document or update it when the sheet changes.
Apps Script can turn Google Sheets status rows into a Google Doc organized by project and limited to an inclusive date range. Each update keeps its date and owner alongside accomplishments, next steps, and blockers. The script creates a fresh document each time, so check its contents and sharing settings before sending it.
How should the Google Sheets Updates tab be structured?
Set up an Updates tab with one row per person’s project update and six uniquely named headers in row 1.
Use the header names and capitalization shown here, in any column order. If one person is reporting on two projects, give each project its own row. Row 1 is for headers only, so do not put a title or other content above them.
| 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 |
The names and projects in the example are fictional. Enter dates as actual Sheets date values or plain text in YYYY-MM-DD form. The script rejects unclear text such as 4/8/2026; it cannot tell which date format you mean. Content fields may be left blank. Every row that falls within the reporting window must include both a project and an owner, or the script stops with an error.
How do you configure and generate the weekly digest?
Prepare the source tab, set the reporting dates in Apps Script, and run createWeeklyDigest from the script project bound to the spreadsheet.
- Prepare the sheet. Make an
Updatestab and enter the headers and sample rows shown above. Do not merge cells or place a title above row 1. - Set the reporting time zone. In Sheets, go to File > Settings and select the time zone to use for the report.
- Open Apps Script. Choose Extensions > Apps Script, then replace the starter code with the complete script below.
- Set Script Properties. In Apps Script’s Project Settings, add
START_DATE=2026-04-06andEND_DATE=2026-04-10. SetSOURCE_SHEET=Updates, or omit it to use the default. - Save and run. Select
createWeeklyDigest, click Run, and authorize an account with permission to read the spreadsheet and create Google Docs. - Open the document. Find its URL in the execution log, open it, and review the content before sharing.
The start and end properties set an inclusive reporting window. The Apps Script Properties Service guide explains how script properties hold configuration values. When actual date cells are converted to calendar dates, the script uses the spreadsheet’s time zone. Set that zone to match your reporting process. No API key or outside service is needed.
What Apps Script creates the project-grouped Google Doc?
The script validates the headers and dates, selects rows within the configured reporting window, and creates a Google Doc with updates grouped by project.
Projects are listed alphabetically. Within each project, updates are ordered by date, then owner. The script compares calendar-date strings and reads actual date cells using the spreadsheet’s time zone. It works with the spreadsheet and its time zone through the Apps Script Spreadsheet reference. For creating and editing the document, it uses the Apps Script DocumentApp reference.
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');
}
A successful run puts a new document in the executing account’s Drive. It leaves earlier digests untouched, sends no email, and gives no other users access.
What output should the April 6-10 sample produce?
With this sample range, the digest contains three updates from Comet Portal and Lantern Launch. The April 13 entry is outside the range and is left out.
The document title is Weekly status digest | 2026-04-06 to 2026-04-10. At the top, it shows the inclusive date range, the spreadsheet time zone, and Updates included: 3. The total comes from two matching Comet Portal rows and one matching Lantern Launch row, or 2 + 1 = 3. The later Lantern Launch update is outside the window, so it is not counted.
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 do not appear in the digest. When all entries for a project section are blank, the section says No entries supplied. If the owner has confirmed there are no blockers, enter None. A blank cell means no blocker text was supplied; it does not establish that the work has no blockers.
Why does createWeeklyDigest group the correct date-range updates into a Google Doc?
createWeeklyDigest puts dates in a consistent format, checks each row against the inclusive window, sorts the matching updates by project, and writes them into a new document.
First, the script calls PropertiesService.getScriptProperties() to read the reporting dates. It validates each value with requireIsoDate_() and confirms that the start date is on or before the end date. The Properties Service guide describes the service. Setting the dates explicitly makes the reporting window visible in the configuration and keeps it from shifting with the run date. The script then opens the bound spreadsheet, selects the source tab, and reads its values. It finds the column index for each required header, so you can change the column order without editing the code.
For each nonempty row, the script checks the date before deciding whether the row belongs in the report. With an actual date cell, sheetDateKey_() uses the spreadsheet time zone. With text, it calls requireIsoDate_(). Both paths return a fixed-width date string that sorts in calendar order. The range check skips dates before the start and dates after the end. For a matching row, the script trims the update fields and stores them in a Map under the project name. That keeps updates for the same project together even if their rows are scattered through the sheet.
Once it has counted the matching rows, the script calls DocumentApp.create() and adds the title and reporting details. It sorts project names alphabetically, then sorts updates within each project by date and owner. The section loops turn nonblank fields into bullets with the date and owner; the script does not copy whole spreadsheet rows. Finally, it gets the document URL, saves and closes the file, and logs and returns the URL. The DocumentApp reference describes the methods for creating and opening documents.
How do you fix date, header, and empty-report failures?
Correct the date, header, spreadsheet time zone, or reporting-window property named in the error, then run the script again.
| 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. |
Dates are checked in every nonempty row, including rows outside the requested window. A malformed date beyond the window can still stop the run. Timestamp interpretation follows the spreadsheet’s time zone, not the Apps Script project’s time zone. If there are no rows in the selected range, the script stops without making a document.
How should teammates review the generated digest asynchronously?
Give the intended readers access to the document, set a response deadline and time zone, and make clear that the digest is a snapshot, not a live report.
The script neither copies spreadsheet permissions nor grants access to other readers. Check the document’s sharing settings and give access to everyone who needs to read it. For remote review, say where people should leave comments and which project owner should handle a blocker. A deadline gives reviewers a clear point by which to respond. The broader remote operations guides cover ways to make handoffs clear.
Edits made to the spreadsheet later will not appear in a document that already exists. Running the script again creates a separate document, so mark which version is current after generating a replacement. Before setting up weekly runs, add automatic date-window calculation and protection against duplicate runs. With fixed START_DATE and END_DATE values, every run selects the same dates.
I would use a different method if each project needs its own reader list or if the report must stay current throughout the week. A process that accounts for permissions, or a live view, fits those needs better. For weekly reporting in Notion, see Automate a Weekly Report Page in Notion for Remote Teams. To focus on blocked work rather than the full weekly report, show only blocked tasks in a Google Sheets project tracker.