Automate a Weekly Report Page in Notion for Remote Teams
How can I automatically create a weekly Notion report page from Google Sheets updates for a remote team?
Have Apps Script select this week's Google Sheets updates and publish a child page under a regular Notion page. Keep credentials in Script Properties and schedule the run after the submission deadline. The report is a weekly snapshot, not a live sync.
Apps Script can gather the current week’s Google Sheets updates and publish one dated report through Notion’s create-page endpoint. The script below produces a Monday-through-Sunday snapshot. Once publication succeeds, it saves the returned page ID so routine later runs do not publish the report again.
Which Google Sheets rows belong in the weekly Notion report?
The weekly report includes rows from the Updates tab dated within the current Monday-through-Sunday week, calculated in the spreadsheet’s time zone.
Set up columns A through E with these exact headers:
| Date | Owner | Project | Update | Blocker |
|---|---|---|---|---|
| 2026-08-02 | Pip Sample | Help center | Archived old draft. | |
| 2026-08-03 | Mira Example | Help center | Published onboarding draft. | Needs review. |
| 2026-08-06 | Pip Sample | Release notes | Finished release notes. | |
| 2026-08-10 | Mira Example | Help center | Start FAQ revisions. |
All names in the sample are fictional. For dates, use actual spreadsheet dates or plain text formatted as YYYY-MM-DD. The date helper rejects ambiguous entries such as 8/3/26. Put each update on a separate row, and keep unused rows empty.
The selection covers a calendar week rather than the preceding seven days. On August 7, 2026, a run includes August 3 and August 6, leaving out August 2 and August 10. That gives the report two updates: four sample rows minus two outside the week.
Updates appear in the same order as their sheet rows. A date later in the current week still qualifies, which means the Date column cannot enforce a publication embargo. I prefer completed status statements for this report. If an update describes planned work, say so explicitly in the Update text.
Set the submission rules before automating publication so contributors know what to provide. The guide to turning status updates into an async weekly digest covers that part of the process.
How do you connect the spreadsheet to the Notion parent page?
To connect the spreadsheet to Notion, grant an integration access to a regular parent page and save the three configuration values outside the script.
- Add an
Updatestab using the headers above. Set the spreadsheet’s reporting time zone before contributors enter dated updates. - Set up an internal Notion integration with insert-content capability, then give it access to a regular page named
Weekly Reports. The create-page documentation specifies the parent access and capability required. - Copy both the spreadsheet ID and the Notion parent page ID. Exclude any URL query parameters.
- Open Project Settings in the spreadsheet’s Apps Script project. Add
SPREADSHEET_ID,NOTION_PARENT_PAGE_ID, andNOTION_TOKENas Script Properties. - Save the project after pasting the code below. Do not put the token in the source code or in spreadsheet cells.
Script Properties are shared configuration for project editors, not a private credential store. Limit access to the project accordingly. Google’s Properties Service guide explains the scope of these stored values.
This request needs a regular page as its destination. A database destination calls for a different parent configuration and properties that match the database schema. The page-creation reference covers both parent types.
What script creates the weekly Notion report?
The createWeeklyNotionReport function checks the source rows, selects updates for the current week, and sends one page-creation request with a summary and update paragraphs.
The date calculation begins with a local calendar date, keeping reporting dates separate from timestamp arithmetic. It then uses UTC arithmetic to find the Monday boundaries and compares normalized date strings. Every populated row needs a valid date, even when that row falls outside the week being published.
function createWeeklyNotionReport() {
const lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
const props = PropertiesService.getScriptProperties();
const required = [
'SPREADSHEET_ID',
'NOTION_PARENT_PAGE_ID',
'NOTION_TOKEN'
];
const config = {};
for (const key of required) {
config[key] = (props.getProperty(key) || '').trim();
if (!config[key]) {
throw new Error(`Missing Script Property: ${key}`);
}
}
const spreadsheet = SpreadsheetApp.openById(
config.SPREADSHEET_ID
);
const sheet = spreadsheet.getSheetByName('Updates');
if (!sheet) {
throw new Error('Create a sheet tab named Updates.');
}
const tz = spreadsheet.getSpreadsheetTimeZone();
const today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
const monday = new Date(`${today}T00:00:00Z`);
const daysSinceMonday = (monday.getUTCDay() + 6) % 7;
monday.setUTCDate(monday.getUTCDate() - daysSinceMonday);
const nextMonday = new Date(monday.getTime());
nextMonday.setUTCDate(nextMonday.getUTCDate() + 7);
const sunday = new Date(monday.getTime());
sunday.setUTCDate(sunday.getUTCDate() + 6);
const start = monday.toISOString().slice(0, 10);
const end = nextMonday.toISOString().slice(0, 10);
const last = sunday.toISOString().slice(0, 10);
const sentKey = [
'SENT',
config.SPREADSHEET_ID,
config.NOTION_PARENT_PAGE_ID,
start
].join('_');
if (props.getProperty(sentKey)) {
console.log(`Report already recorded for ${start}.`);
return;
}
const values = sheet.getDataRange().getValues();
const headers = ['Date', 'Owner', 'Project', 'Update', 'Blocker'];
if (!headers.every((header, index) =>
String(values[0][index] || '').trim() === header
)) {
throw new Error(
'A1:E1 must be Date, Owner, Project, Update, Blocker.'
);
}
const updates = [];
for (let index = 1; index < values.length; index++) {
const row = values[index].slice(0, 5);
if (row.every(value => String(value).trim() === '')) {
continue;
}
const rowNumber = index + 1;
const date = reportDateKey(row[0], tz, rowNumber);
if (date < start || date >= end) {
continue;
}
const owner = String(row[1] ?? '').trim();
const project = String(row[2] ?? '').trim();
const update = String(row[3] ?? '').trim();
const blocker = String(row[4] ?? '').trim() || 'None';
if (!owner || !project || !update) {
throw new Error(
`Row ${rowNumber}: fill Owner, Project, and Update.`
);
}
updates.push(reportParagraph(
`Date: ${date}\nOwner: ${owner}\nProject: ${project}\n` +
`Update: ${update}\nBlocker: ${blocker}`,
`Row ${rowNumber}`
));
}
if (updates.length === 0) {
console.log(`No updates for ${start}; no page created.`);
return;
}
if (updates.length > 99) {
throw new Error(
'More than 99 weekly rows; use a batched-block workflow.'
);
}
const title = `Weekly report | ${start} to ${last}`;
const payload = {
parent: {
type: 'page_id',
page_id: config.NOTION_PARENT_PAGE_ID
},
properties: {
title: {
title: [{ type: 'text', text: { content: title } }]
}
},
children: [
reportParagraph(
`${updates.length} updates for ${start} through ${last}.`,
'Summary'
),
...updates
]
};
const response = UrlFetchApp.fetch(
'https://api.notion.com/v1/pages',
{
method: 'post',
contentType: 'application/json',
headers: {
Authorization: `Bearer ${config.NOTION_TOKEN}`,
'Notion-Version': '2022-06-28'
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
}
);
const status = response.getResponseCode();
const body = response.getContentText();
if (status < 200 || status >= 300) {
throw new Error(`Notion HTTP ${status}: ${body}`);
}
const page = JSON.parse(body);
if (!page.id) {
throw new Error('Notion response did not contain a page ID.');
}
props.setProperty(sentKey, page.id);
// Remove markers for earlier weeks so they don't pile up in Script Properties.
props.getKeys()
.filter(key => key.startsWith('SENT_') && key.slice(-10) < start)
.forEach(key => props.deleteProperty(key));
console.log(`Created ${title}`);
} finally {
lock.releaseLock();
}
}
function reportDateKey(value, tz, rowNumber) {
if (value instanceof Date && !isNaN(value.getTime())) {
return Utilities.formatDate(value, tz, 'yyyy-MM-dd');
}
const text = String(value ?? '').trim();
if (/^\d{4}-\d{2}-\d{2}$/.test(text)) {
const parsed = new Date(`${text}T00:00:00Z`);
if (!isNaN(parsed.getTime()) &&
parsed.toISOString().slice(0, 10) === text) {
return text;
}
}
throw new Error(
`Row ${rowNumber}: Date must be a real date or YYYY-MM-DD.`
);
}
function reportParagraph(text, label) {
if (text.length > 2000) {
throw new Error(
`${label}: report text exceeds 2000 characters; shorten it.`
);
}
return {
object: 'block',
type: 'paragraph',
paragraph: {
rich_text: [{ type: 'text', text: { content: text } }]
}
};
}
The request uses UrlFetchApp’s JSON POST options. Enabling muteHttpExceptions lets the code read unsuccessful HTTP responses and include their response bodies in the errors it reports.
Each run allows up to 99 update paragraphs, with a limit of 2,000 characters per paragraph. That character count includes the generated labels, not just the text contributors enter.
What should the sample report look like?
For a sample run on August 7, 2026, the expected report title is Weekly report | 2026-08-03 to 2026-08-09.
Expect three blocks in the body: one summary followed by the two update paragraphs.
2 updates for 2026-08-03 through 2026-08-09.
Date: 2026-08-03
Owner: Mira Example
Project: Help center
Update: Published onboarding draft.
Blocker: Needs review.
Date: 2026-08-06
Owner: Pip Sample
Project: Release notes
Update: Finished release notes.
Blocker: None
An empty blocker field becomes None; it does not cause the script to skip the row. The summary counts updates. It does not count projects, owners, or completed tasks.
Before scheduling anything, run createWeeklyNotionReport manually with test data. Choose dates in the actual current week unless a run with no output is what you want to check. The historical sample will not produce a page in every week if you copy its dates unchanged.
Qualifying rows cause a real Notion page-creation request, even during a test. Point the authorization run at a test parent page so test data stays out of the report archive.
How do you schedule reports without routine duplicates?
To avoid routine duplicates, schedule the report after the submission deadline and retain the weekly marker once the script records a successful publication.
Add a weekly time-driven trigger for createWeeklyNotionReport and set the project’s time zone to match the reporting time zone. I would leave a buffer after the deadline rather than depend on a last-minute spreadsheet edit. Make publication a defined step in your remote operations process, with someone assigned to handle missed submissions.
The publication marker is a SENT_... key made from the spreadsheet ID, parent ID, and Monday date. Script Properties retain it between executions. The script saves that marker only when it receives a successful response containing a page ID.
Later edits to the source sheet will not change a published report because this code sends no update request. If the week has no qualifying rows, it creates no page and saves no marker. Add qualifying rows within that same week, and a later run can still publish them.
Keep deadline reminders separate from report publication if you need both. The guide to sending one daily task reminder without repeat emails explains how to set up the reminders.
How do you recover a failed or missing report?
Before rerunning a failed or missing report, read the execution error and inspect the destination page in Notion.
- Missing configuration: Add the Script Property named in the error.
NOTION_TOKENshould contain only the token; the script supplies the authorization prefix. - Rejected page request: Verify the token, parent ID, integration access, and insert-content capability using the Notion creation requirements.
- Invalid date: Correct the date on the row named in the error, including when that row belongs to another week. The script validates dates before filtering by week.
- Missing fields: Complete Owner, Project, and Update on qualifying rows. Blocker can be blank, but every other field is required.
- Oversized paragraph: Reduce the length of the full generated paragraph. Count the owner, project, and labels as well as the update text.
- No qualifying updates: Verify the reporting week and sheet dates first. Do not change credentials to fix a date-selection problem.
A timeout needs more care than a validation error. The page may already exist in Notion even though the script never received its ID or recorded success. Inspect the parent page before retrying the request. If the report is there, restore the matching weekly marker using that page’s ID.
The lock and marker do not guarantee exactly-once delivery. I would not delete markers as a routine repair: that erases the script’s record of a successful publication.
When should you replace this weekly snapshot workflow?
Move beyond this script when you need synchronized report edits, more than 99 weekly rows, or a different model for credential access.
| Requirement | Replacement |
|---|---|
| Editable, synchronized reports | Use a Notion database, stable source-row IDs, and an update workflow. |
| More than 99 weekly rows | Create one page, save its ID, and append blocks in batches; track completed batches to prevent repeated content. |
| Untrusted project editors | Use a controlled backend with managed secret storage; Script Properties do not hide tokens from project editors. |
Changing the parent ID alone will not implement these replacements. Notion’s creation documentation explains how page-parent properties differ from database-backed properties. Any replacement also needs to account for Script Properties being shared across the script.
For an occasional correction, I prefer a manual edit to the published snapshot over building synchronization. If you want to regenerate the report, archive the existing page, remove only its matching weekly marker, and rerun within that same week. A run next week selects next week’s rows, not the rows from the report you meant to repair.