Build a two-way Sheets-Notion sync

Northwind’s product team plans in Notion — a database of initiatives, each a page with a name and a status. The operations team reports in Google Sheets, where that same list needs to sit alongside everything else they track. Asking either team to switch tools is a non-starter, so the database has to be visible in both.

This script mirrors the Notion database into a Google Sheet. pullFromNotion queries the database, flattens each page into a row, and rebuilds a sheet tab that operations can read, filter, and chart. Notion’s API is the awkward part — its property values are deeply nested — so most of the work is reaching into that structure cleanly. Run it on a schedule and the spreadsheet mirror stays current on its own.

What you’ll need

• A Notion database with a Name (title) property and a Status (select) property. Adjust the field mapping in the script if your property names differ.
• A Notion internal integration, created at notion.so/my-integrations. Copy its secret token.
• The integration shared with the database — open the database in Notion, use the ••• menu → Connections, and add your integration. Without this, the API returns nothing.
• The token saved as NOTION_KEY in Script Properties — see Store API keys and secrets securely.
• Your database ID (in the database URL) and a Google Sheet ID, set in the config below.

The script

// --- Notion connection ---
const NOTION_DB = 'YOUR_DB_ID';
const NOTION_KEY = PropertiesService.getScriptProperties()
  .getProperty('NOTION_KEY');

// Notion pins behaviour to a dated API version — send it on every request.
const NOTION_VERSION = '2022-06-28';

// --- Sheets connection ---
const MIRROR_SHEET_ID = '1abcNotionMirrorId';

// Column headers for the mirror tab, in write order.
const HEADERS = ['id', 'name', 'status'];

/**
 * Queries the Notion database, flattens each page to a row, and rebuilds
 * the mirror sheet so it always reflects the latest Notion state.
 */
function pullFromNotion() {
  if (!NOTION_KEY) {
    throw new Error('Missing NOTION_KEY in Script Properties.');
  }

  // 1. Page through the database query. Notion returns 100 pages at a
  //    time and hands back a cursor (next_cursor) while more remain.
  const pages = [];
  let cursor = null;
  do {
    const payload = cursor ? { start_cursor: cursor } : {};
    const response = UrlFetchApp.fetch(
      'https://api.notion.com/v1/databases/' + NOTION_DB + '/query',
      {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + NOTION_KEY,
          'Notion-Version': NOTION_VERSION,
        },
        payload: JSON.stringify(payload),
        muteHttpExceptions: true,
      }
    );
    if (response.getResponseCode() !== 200) {
      throw new Error('Notion query failed: ' + response.getContentText());
    }
    const body = JSON.parse(response.getContentText());
    pages.push(...body.results);
    cursor = body.has_more ? body.next_cursor : null;
  } while (cursor);

  // 2. Flatten each page. Notion property values are deeply nested, so
  //    reach in defensively — optional chaining guards against an empty
  //    title or an unset select.
  const rows = pages.map((p) => [
    p.id,
    p.properties.Name?.title?.[0]?.plain_text || '',
    p.properties.Status?.select?.name || '',
  ]);

  // 3. Rebuild the mirror tab from scratch every run.
  const sheet = SpreadsheetApp.openById(MIRROR_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, HEADERS.length).setValues(rows);
  }
  Logger.log('Mirrored ' + rows.length + ' Notion pages to the sheet.');
}

How it works

1. Connection details and the API version sit in named constants. Notion pins behaviour to a dated version string, so NOTION_VERSION is sent as a header on every request — leave it out and the API rejects the call.
2. pullFromNotion queries the database with a POST to the /databases/{id}/query endpoint. Querying a database always uses POST even though it only reads data.
3. It pages through the results in a do...while loop. Notion returns at most 100 pages per request and sets has_more with a next_cursor token; the loop feeds that cursor back as start_cursor until the whole database is collected.
4. Flattening is the careful part. A Notion property value is a nested object — a title is an array of rich-text fragments, a select is an object with a name. Optional chaining (?.) means an untitled page or an unset status yields an empty string instead of throwing.
5. It clears the first tab, writes the header, and writes every row in one setValues call, so the sheet is a faithful snapshot of the database at the moment the script ran.

Example run

The Notion database holds three pages:

Name	Status
Mobile redesign	In progress
Pricing experiment	Backlog
API v2	Shipped

After pullFromNotion, the mirror tab holds those rows plus Notion’s internal page IDs:

id	name	status
1f2a…e9	Mobile redesign	In progress
7c4b…02	Pricing experiment	Backlog
9a18…d7	API v2	Shipped

Operations can now filter, pivot, and chart the list in the spreadsheet without ever opening Notion. The page id column is the stable key you would use to extend this into a write-back sync later.

Trigger it

The mirror should refresh itself, so run the script on a clock:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose pullFromNotion, a Time-driven source, and an Hour timer set to your preferred interval — hourly suits a planning database that changes through the day.
4. Save, and approve the authorisation prompt the first time.

Watch out for

• The integration must be shared with the database. If the API returns an empty result or a 404, the most likely cause is a missing connection — add the integration under the database’s ••• → Connections menu.
• This script mirrors Notion into Sheets only. It is one-way despite the article title’s ambition — extend it with a PATCH to /v1/pages/{id} to push sheet edits back, using the id column as the key.
• Notion property values are nested and type-specific. The mapping here handles a title and a select; a date, a multi-select, or a relation each has its own shape and needs its own extraction code.
• Always send the Notion-Version header. Without it, or with an outdated value, requests fail — Notion does not assume a default version.
• The script rebuilds the whole tab each run. Do not add notes or extra columns to the mirror tab; use a separate tab and reference this one with formulas.
• A large database means many query pages, each one a UrlFetchApp call against Apps Script’s quota and the six-minute execution limit. For very large databases, narrow the query with a filter in the payload or sync in chunks.