Tiptap Docs

Google Sheets, Excel and Airtable

A chart can follow a range in Google Sheets, a table in an Excel workbook or the records of an Airtable table. For each service you write a small provider, register it with Diagrams, and link a chart to an address in that service.
Pro ExtensionAlphaPilot program

How it fits together

  • The provider reads. It calls the service's API and returns Chart Data.
  • The document stores the address and a snapshot. The chart renders from the snapshot, offline, in an export and for a reader who has no access to the service.
  • Credentials stay with your application. A provider gets its token from your sign-in or your server when you create it; the address never holds one.
  • A chart updates when you synchronize it. See Keeping a chart up to date.
ServiceProvider idAddressCredentials
Google Sheetsgoogle-sheets{ spreadsheetId, range }The user's Google sign-in (OAuth)
Excelexcel{ itemId, table } or { itemId, worksheet, range }The user's Microsoft sign-in (MSAL)
Airtableairtable{ baseId, table, fields }A token on your server

The provider ids are yours to choose. Data Sources describes the provider contract and the Commands used below.

Google Sheets

A published sheet, without credentials

A sheet published as CSV needs no provider: the built-in url-csv reads it.

  1. In Google Sheets, choose File, Share, Publish to web.
  2. Pick the sheet and Comma-separated values (.csv), then Publish.
  3. Link a chart to the URL it shows.
editor.commands.linkChartSource({
  provider: 'url-csv',
  address: {
    url: 'https://docs.google.com/spreadsheets/d/e/2PACX-…/pub?gid=0&single=true&output=csv',
  },
  readOnly: true,
})
  • Anyone who has the URL can read the published sheet.
  • Google updates a published sheet a few minutes after a change, not at once.

A private sheet, through the Sheets API

What to set up:

  • A Google Cloud project with the Google Sheets API enabled.
  • An OAuth client ID of type Web application, with your site as an authorized JavaScript origin.
  • The scope https://www.googleapis.com/auth/spreadsheets.readonly.
  • A sign-in that returns an access token, such as the token client of Google Identity Services.

The provider reads a range with spreadsheets.values.get:

import {
  ChartSourceError,
  gridToChartData,
  type ChartSourceProvider,
} from '@tiptap-pro/extension-diagrams/core'

function sheetAddress(address: unknown): { spreadsheetId: string; range: string } {
  if (
    typeof address === 'object' &&
    address !== null &&
    'spreadsheetId' in address &&
    typeof address.spreadsheetId === 'string' &&
    'range' in address &&
    typeof address.range === 'string'
  ) {
    return { spreadsheetId: address.spreadsheetId, range: address.range }
  }
  throw new ChartSourceError('The address needs a spreadsheetId and a range.')
}

/** Reads a range of a Google Sheet. `getAccessToken` comes from your own Google sign-in. */
export function googleSheetsSource(options: {
  getAccessToken: () => Promise<string>
}): ChartSourceProvider {
  return {
    id: 'google-sheets',
    read: async (address, { fetch }) => {
      const { spreadsheetId, range } = sheetAddress(address)
      const url =
        `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}` +
        `/values/${encodeURIComponent(range)}` +
        '?valueRenderOption=UNFORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING'
      const response = await fetch(url, {
        headers: { Authorization: `Bearer ${await options.getAccessToken()}` },
      })
      if (!response.ok) throw new ChartSourceError(`Google Sheets answered ${response.status}.`)

      const { values = [] }: { values?: unknown[][] } = await response.json()
      return gridToChartData(
        values.map((row) => row.map((cell) => String(cell ?? ''))),
        { decimalSeparator: '.' },
      )
    },
    describe: (address) => `Google Sheet, ${sheetAddress(address).range}`,
  }
}

Register it and link a chart:

  • getGoogleAccessToken is your function that returns the signed-in user's access token.
  • spreadsheetId is the long id in the sheet's URL.
  • range is in A1 notation, with the sheet's name.
import { Diagrams } from '@tiptap-pro/extension-diagrams'

Diagrams.configure({
  sources: [googleSheetsSource({ getAccessToken: getGoogleAccessToken })],
})

editor.commands.linkChartSource({
  provider: 'google-sheets',
  address: { spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms', range: 'Sales!A1:D13' },
  readOnly: true,
})
  • The first row is the header. The first column holds the labels.
  • Numbers arrive as numbers. UNFORMATTED_VALUE keeps 1200.5 instead of $1,200.50.
  • Dates arrive as the sheet shows them, through FORMATTED_STRING.
  • Empty cells at the end of a row are left out by the API; the chart shows them as no value.
  • Limits: 300 read requests per minute per project and 60 per minute per user. Past that, the API answers 429.

Excel

The provider reads an Excel workbook in OneDrive or SharePoint through Microsoft Graph.

What to set up:

  • An app registration in Microsoft Entra ID, with a single-page application redirect URI.
  • The delegated permission Files.ReadWrite, the one Microsoft Graph lists for reading a range.
  • MSAL (@azure/msal-browser) to sign the user in and get a token.
  • The workbook as an .xlsx file. Its itemId is the drive item id that Microsoft Graph returns for the file.

The provider reads a table's range or a worksheet range:

import {
  ChartSourceError,
  gridToChartData,
  type ChartSourceProvider,
} from '@tiptap-pro/extension-diagrams/core'

const text = (value: unknown): string => (typeof value === 'string' ? value : '')

/** `{ itemId, table }` or `{ itemId, worksheet, range }`, with a `driveId` for SharePoint. */
function rangePath(address: unknown): string {
  const field = (key: string) =>
    typeof address === 'object' && address !== null && key in address
      ? text(Reflect.get(address, key))
      : ''
  if (!field('itemId')) throw new ChartSourceError('The address needs an itemId.')

  const drive = field('driveId') ? `drives/${field('driveId')}` : 'me/drive'
  const workbook = `${drive}/items/${field('itemId')}/workbook`
  if (field('table')) return `${workbook}/tables/${encodeURIComponent(field('table'))}/range`
  const sheet = encodeURIComponent(field('worksheet'))
  return `${workbook}/worksheets/${sheet}/range(address='${field('range')}')`
}

/** Reads an Excel workbook in OneDrive or SharePoint. `getAccessToken` comes from MSAL. */
export function excelSource(options: {
  getAccessToken: () => Promise<string>
}): ChartSourceProvider {
  return {
    id: 'excel',
    read: async (address, { fetch }) => {
      const url = `https://graph.microsoft.com/v1.0/${rangePath(address)}?$select=values,text`
      const response = await fetch(url, {
        headers: { Authorization: `Bearer ${await options.getAccessToken()}` },
      })
      if (!response.ok) throw new ChartSourceError(`Microsoft Graph answered ${response.status}.`)

      const range: { values: unknown[][]; text: string[][] } = await response.json()
      // A date is a number in Excel, so the label column is read as the workbook shows it
      return gridToChartData(
        range.values.map((row, r) =>
          row.map((value, c) =>
            c > 0 && typeof value === 'number' ? String(value) : text(range.text[r]?.[c]),
          ),
        ),
        { decimalSeparator: '.' },
      )
    },
  }
}

A token from MSAL, signed in silently when it can, with a popup when it must:

import { PublicClientApplication } from '@azure/msal-browser'

const msal = new PublicClientApplication({ auth: { clientId: 'your-client-id' } })
await msal.initialize()

async function getAccessToken(): Promise<string> {
  const request = { scopes: ['Files.ReadWrite'], account: msal.getAllAccounts()[0] }
  try {
    return (await msal.acquireTokenSilent(request)).accessToken
  } catch {
    return (await msal.acquireTokenPopup(request)).accessToken
  }
}

Register it and link a chart to a table named Sales:

Diagrams.configure({ sources: [excelSource({ getAccessToken })] })

editor.commands.linkChartSource({
  provider: 'excel',
  address: { itemId: '01BYE5RZ6QN3ZWBTUFOFD3GSPGOHDJD36K', table: 'Sales' },
  readOnly: true,
})
  • A table is the better address. It grows with new rows; a range like A1:D13 does not.
  • A workbook in SharePoint or shared with the user needs its driveId in the address too.
  • Numbers come from values, labels from text. Excel stores a date as a number, so the label column is read as the workbook shows it.
  • Limits: 1,500 requests per 10 seconds per app in one tenant. Past that, Microsoft Graph answers 429.

Airtable

The provider reads the records of an Airtable table through a route on your server, which adds the token.

What to set up:

  • A personal access token with the scope data.records:read and access to the base. It stays on your server.
  • The base id (app…) and the table name or id (tbl…), both in the URL of the table.
  • The route below on your server.

Why a server route: a token in the page can be read by everyone who opens it, and it reaches every base the token can reach. The route sends only GET requests, and only to the bases you allow:

/**
 * Runs on your server. Forwards `GET /api/airtable/<baseId>/<table>?…` to Airtable with a
 * personal access token, for the bases you allow.
 */
export function airtableProxy(options: {
  token: string
  bases: string[]
}): (request: Request) => Promise<Response> {
  return async (request) => {
    const url = new URL(request.url)
    const path = url.pathname.replace(/^\/api\/airtable\//, '')
    if (request.method !== 'GET' || !options.bases.includes(path.split('/')[0] ?? '')) {
      return new Response('Not allowed', { status: 403 })
    }

    const response = await fetch(`https://api.airtable.com/v0/${path}${url.search}`, {
      headers: { Authorization: `Bearer ${options.token}` },
    })
    return new Response(response.body, {
      status: response.status,
      headers: { 'Content-Type': 'application/json' },
    })
  }
}

Mount it where your server takes requests, for example app.get('/api/airtable/*', …). Check there, too, that the signed-in user may read the base.

The provider reads the records of a table, 100 per page, and follows offset to the next page:

import {
  ChartSourceError,
  gridToChartData,
  type ChartSourceProvider,
} from '@tiptap-pro/extension-diagrams/core'

function tableAddress(address: unknown): { baseId: string; table: string; fields: string[] } {
  if (
    typeof address === 'object' &&
    address !== null &&
    'baseId' in address &&
    typeof address.baseId === 'string' &&
    'table' in address &&
    typeof address.table === 'string' &&
    'fields' in address &&
    Array.isArray(address.fields)
  ) {
    return { baseId: address.baseId, table: address.table, fields: address.fields.map(String) }
  }
  throw new ChartSourceError('The address needs a baseId, a table and its fields.')
}

/** Reads the records of an Airtable table through `proxyUrl`, a route of your server. */
export function airtableSource(options: { proxyUrl: string }): ChartSourceProvider {
  return {
    id: 'airtable',
    read: async (address, { fetch }) => {
      const { baseId, table, fields } = tableAddress(address)
      const records: Record<string, unknown>[] = []
      let offset = ''
      do {
        const query = new URLSearchParams(offset ? { offset } : {})
        for (const field of fields) query.append('fields[]', field)
        const path = `${encodeURIComponent(baseId)}/${encodeURIComponent(table)}`
        const response = await fetch(`${options.proxyUrl}/${path}?${query}`)
        if (!response.ok) throw new ChartSourceError(`Airtable answered ${response.status}.`)

        const page: { records: { fields: Record<string, unknown> }[]; offset?: string } =
          await response.json()
        records.push(...page.records.map((record) => record.fields))
        offset = page.offset ?? ''
      } while (offset)

      // Airtable leaves out empty fields, so the columns are the fields the address names
      const cell = (value: unknown) =>
        typeof value === 'string' || typeof value === 'number' ? String(value) : ''
      return gridToChartData(
        [fields, ...records.map((record) => fields.map((field) => cell(record[field])))],
        { decimalSeparator: '.' },
      )
    },
    describe: (address) => `Airtable, ${tableAddress(address).table}`,
  }
}

Register it and link a chart to two fields of a table:

Diagrams.configure({ sources: [airtableSource({ proxyUrl: '/api/airtable' })] })

editor.commands.linkChartSource({
  provider: 'airtable',
  address: { baseId: 'appXXXXXXXXXXXXXX', table: 'Deals', fields: ['Stage', 'Count'] },
  readOnly: true,
})
  • fields are the columns, in their order. Airtable leaves empty fields out of a record, so the records alone do not say which columns there are.
  • The first field holds the labels. Text and numbers are read; any other field, such as an attachment or a linked record, shows as no value.
  • Limits: 5 requests per second per base. Past that, Airtable answers 429 and accepts requests again after 30 seconds. A table of 1,000 records takes 10 requests.

Keeping a chart up to date

A linked chart shows its snapshot until it is synchronized.

// The selected chart
editor.commands.syncChartData()

// Every chart that reads this range
await editor.storage.diagrams.requestSync({
  provider: 'google-sheets',
  address: { spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms', range: 'Sales!A1:D13' },
})
  • On a schedule: call requestSync from setInterval, and stay within the service's limits.
  • When a read fails, the chart keeps its snapshot and the option onSourceError gets the error and { provider, address }. A provider throws a ChartSourceError with a message a person can read.
  • A synchronization is not undone with the user's edits: it is left out of the undo history.

What to watch for

  • No token in an address. The address is stored in the Tiptap Document and shared with everyone who opens it.
  • Link with readOnly: true. While linked, Commands that change the data return false, so a local edit is never overwritten by the next synchronization.
  • The chart's settings stay yours. A synchronization replaces the data, never the Chart Spec. To show only some columns, set encoding; see Showing only some columns.
  • Every client that synchronizes registers the provider. A client without it shows the snapshot and cannot read the source.
  • Each reader signs in to Google or Microsoft. A user without access to the sheet or the workbook still sees the snapshot; a synchronization fails for them.
  • Detach to edit the data by hand. detachChartSource keeps the snapshot and the address, and relinkChartSource follows the source again.
PreviouslyExport