Automate a Weekly Report Page in Notion for Remote Teams

Which Google Sheets rows belong in the weekly Notion report?

The weekly Notion report includes Monday through Sunday rows from the Updates tab, using the spreadsheet’s time zone.

Use these exact headers in columns A through E:

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.

Names are fictional. Use actual spreadsheet dates or plain text YYYY-MM-DD; ambiguous text such as 8/3/26 is rejected.

The script preserves sheet order, includes future-dated rows within the week, and excludes the following Monday. On August 7, 2026, only August 3 and August 6 qualify.

How do you connect Google Sheets to the Notion report parent?

Connect Google Sheets to Notion by granting an internal integration access to a regular parent page and storing its credentials in Apps Script.

1. Prepare Sheets. Create Updates with the headers above. Set the spreadsheet’s reporting time zone, such as America/New_York. Keep one update per row and unused rows empty.
2. Create the integration. In Notion’s integration settings, create an internal integration with insert-content capability. Copy its token without putting it in source code.
3. Grant page access. Create a regular page named Weekly Reports. Add the integration through that page’s Connections menu.
4. Copy identifiers. Get the spreadsheet ID between /d/ and /edit in its address. Copy the Notion page ID without query parameters; hyphenated and 32-character hexadecimal IDs are accepted.
5. Store configuration. Open Extensions, then Apps Script. Under Project Settings, add SPREADSHEET_ID, NOTION_PARENT_PAGE_ID, and NOTION_TOKEN as Script Properties. Project editors must be trusted with the token.
6. Save the code. Replace the starter code below and match the Apps Script project’s time zone to the spreadsheet’s before scheduling.

The destination must be a regular Notion page, not a database.

What Apps Script creates the weekly Notion report page?

The following Apps Script validates spreadsheet rows, selects the current week, and creates one Notion child page with a summary and update paragraphs.

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 } }]
    }
  };
}

Local calendar dates with UTC arithmetic avoid daylight saving boundary shifts. The summary plus 99 updates meets the request’s 100-child-block limit; each paragraph’s text object is limited to 2,000 characters.

What Notion page should the sample produce on August 7, 2026?

For an August 7, 2026 execution, the sample produces a child page under Weekly Reports titled Weekly report | 2026-08-03 to 2026-08-09.

Its three paragraph blocks contain:

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

Blank blockers become None. The previous Sunday and following Monday are excluded.

Select createWeeklyNotionReport and run it manually to authorize spreadsheet access and external requests. Qualifying rows create a real page; sample dates outside the current week create none.

Why does createWeeklyNotionReport select the correct week and create a Notion report?

createWeeklyNotionReport selects rows using spreadsheet-local calendar dates and Monday boundaries, then packages qualifying updates into one Notion child page.

First, LockService serializes executions, and PropertiesService supplies configuration rather than embedding credentials in source. After opening Updates, Utilities.formatDate derives today’s date in the spreadsheet’s time zone. UTC calendar arithmetic then calculates Monday, next Monday, and Sunday without relying on local midnight arithmetic.

A spreadsheet-parent-week marker is checked before reading rows. getValues retrieves the sheet together rather than cell by cell, and the header check establishes column meanings. The loop skips empty rows, then reportDateKey normalizes dates into sortable YYYY-MM-DD strings. Comparing date >= start and date < end expresses the complete week without calculating Sunday’s final instant.

Qualifying rows are trimmed, blank blockers become None, and reportParagraph constructs Notion paragraph blocks in sheet order. After checking update count, the payload combines a dated title, summary, and update blocks under the configured parent.

UrlFetchApp.fetch sends that payload as JSON. Only after confirming a successful response and page ID does the script save the weekly marker and remove older markers. Finally, releaseLock frees the lock even when execution exits early.

How do you schedule publication without routinely creating duplicates?

Schedule createWeeklyNotionReport with an Apps Script weekly time-driven trigger after the team’s submission deadline.

Open Triggers and add a Friday weekly timer. Confirm the project’s reporting time zone and allow a buffer: timers execute within a window, not at an exact minute.

After success, a SENT_... Script Property stores the page ID for that spreadsheet, parent, and week, and markers from earlier weeks are removed so old entries don’t pile up. Repeat executions stop; a script lock prevents overlapping executions within the project.

Reports are snapshots, so later edits are not synchronized. Empty weeks create neither a page nor a marker, allowing later publication.

How do you fix failed or missing weekly Notion reports?

Fix weekly report failures by correcting credentials, page permissions, or source data; check for an existing page before retrying uncertain requests.

• Notion HTTP 401: Replace NOTION_TOKEN with the active token only; the code adds Bearer .
• Notion HTTP 404 or object_not_found: Correct NOTION_PARENT_PAGE_ID and connect the same integration to that regular page. For 403, also enable insert-content capability.
• Row N: Date must be a real date or YYYY-MM-DD: Replace the missing or unsupported date with a spreadsheet date or ISO date text. Every populated row’s date is validated, even outside the reporting week.
• Missing required fields: Fill Owner, Project, and Update on the identified row.
• Text exceeds 2,000 characters: Shorten the identified paragraph, including its labels.
• Edited week produces no page: Edit the existing Notion page manually. To regenerate, archive it, delete only the matching week’s SENT_... property, and rerun during that week.
• Timeout with uncertain creation: Inspect the parent before retrying. If the report exists, restore its matching weekly marker with that page’s ID. Local markers cannot guarantee exactly-once delivery across network failures.

When should you replace this weekly snapshot workflow?

Replace this Apps Script workflow when reports require live updates, more than 99 weekly rows, or tighter 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.