---
title: "Google Sheets, Excel and Airtable"
description: "Link a chart to a Google Sheet, an Excel workbook or an Airtable table with a Data Source provider of your own."
canonical_url: "https://tiptap-docs.lin52025i.qzz.io/diagrams/guides/spreadsheets"
---

# Google Sheets, Excel and Airtable

Link a chart to a Google Sheet, an Excel workbook or an Airtable table with a Data Source provider of your own.

## How it fits together

The provider calls the service's API and returns Chart Data. The document stores the address
and a snapshot, as for every [Data Source](https://tiptap-docs.lin52025i.qzz.io/diagrams/data/sources.md). A provider gets its token from your
sign-in or your server when you create it, never from the address.

| Service       | Provider id     | Address                                               | Credentials                         |
| ------------- | --------------- | ----------------------------------------------------- | ----------------------------------- |
| Google Sheets | `google-sheets` | `{ spreadsheetId, range }`                            | The user's Google sign-in (OAuth)   |
| Excel         | `excel`         | `{ itemId, table }` or `{ itemId, worksheet, range }` | The user's Microsoft sign-in (MSAL) |
| Airtable      | `airtable`      | `{ baseId, table, fields }`                           | A token on your server              |

You choose the provider ids. [Writing a provider](https://tiptap-docs.lin52025i.qzz.io/diagrams/data/sources.md#writing-a-provider) says what
a provider must have.

## Google Sheets

### A published sheet, without credentials

A sheet published as CSV needs no provider of your own. The built-in `url-csv` provider 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.

```ts
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`](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/get):

```ts
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.

```ts
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, and the first column holds the labels. Numbers arrive as numbers,
because `UNFORMATTED_VALUE` returns `1200.5` instead of `$1,200.50`. Dates arrive as the sheet
shows them, through `FORMATTED_STRING`. The API leaves out empty cells at the end of a row, and
the chart shows them as no value.

The Sheets API allows 300 read requests per minute per project and 60 per minute per user. Past
that, it 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 provider only reads, but Microsoft Graph lists
  `Files.ReadWrite` as the permission 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](https://learn.microsoft.com/en-us/graph/api/table-range?view=graph-rest-1.0)
or a [worksheet range](https://learn.microsoft.com/en-us/graph/api/range-get?view=graph-rest-1.0):

```ts
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:

```ts
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`:

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

editor.commands.linkChartSource({
  provider: 'excel',
  address: { itemId: '01BYE5RZ6QN3ZWBTUFOFD3GSPGOHDJD36K', table: 'Sales' },
  readOnly: true,
})
```

Prefer a table as the address. A table grows with new rows, and a range like `A1:D13` does not.
A workbook in SharePoint, or one shared with the user, also needs its `driveId` in the address.

Numbers come from `values`, and labels from `text`, as the workbook shows them.

Microsoft Graph allows 1,500 requests per 10 seconds per app in one tenant. Past that, it
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.

Anyone who opens your app's web page could read a token there, and the token reaches every base
it has access to. The route sends only `GET` requests, and only to the bases you allow:

```ts
/**
 * 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 that the signed-in user may read the base.

The provider reads
[the records of a table](https://airtable.com/developers/web/api/list-records), 100 per page,
and follows `offset` to the next page:

```ts
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:

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

editor.commands.linkChartSource({
  provider: 'airtable',
  address: { baseId: 'appXXXXXXXXXXXXXX', table: 'Deals', fields: ['Stage', 'Count'] },
  readOnly: true,
})
```

The `fields` in the address 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. The provider reads text and numbers. Any other field, such as an attachment or a linked
record, shows as no value.

Airtable allows 5 requests per second per base. Past that, it 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 you synchronize it.

```ts
// 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' },
})
```

A failed read goes to the `onSourceError` option. The providers above throw a `ChartSourceError`
for a bad address or an error answer from the service. Other failures, such as a lost connection
or a failed token request, arrive with their own error type. [When a chart synchronizes](https://tiptap-docs.lin52025i.qzz.io/diagrams/data/sources.md#when-a-chart-synchronizes) has the
rules for failed reads, schedules with `requestSync` and `setInterval`, and service limits.

## What to watch for

- Never put a token in an address, because the document shares it with everyone who opens it.
- The rules for every Data Source apply: link with `readOnly: true`
  ([Read-only](https://tiptap-docs.lin52025i.qzz.io/diagrams/data/sources.md#read-only)), set `encoding` to show only some columns
  ([Showing only some columns](https://tiptap-docs.lin52025i.qzz.io/diagrams/data/sources.md#showing-only-some-columns)), and use
  `detachChartSource` and `relinkChartSource` to edit by hand
  ([Linked or detached](https://tiptap-docs.lin52025i.qzz.io/diagrams/data/sources.md#linked-or-detached)).
- For Google Sheets and Excel, each person signs in with their own account. A person without
  access still sees the snapshot, but a synchronization fails for them.