Two-way sync with Google Sheets

In this tutorial, you will put a Handsontable DataGrid on top of a Google Sheet without moving the data. The grid reads the sheet, writes the user’s edits back, and picks up edits other people make in Google Sheets. The app runs in the browser only: the user signs in with Google, and the grid calls the Google Sheets API directly, with no backend.

Open in the demo runner

The grid above syncs with the sheet next to it. Edit a cell in the grid, and the sheet shows it within a second. Edit the sheet, or select Colleague edits a cell, and the grid picks the change up on its next poll. Select Sort sheet by Product or Insert blank row, then edit the grid: the edit still lands in the right row. The log under the panels lists every API call, and the toolbar counts them against the quota.

Note: the demo above simulates the Google Sheets API in the page, so it works without a Google account. The recipe code calls the real API: to run it, you need your own Google Cloud project, OAuth client ID, and spreadsheet, as set up in Step 1.

Overview

Difficulty: Intermediate
Time: ~30 minutes
Stack: Google Sheets API v4, Google Identity Services, Handsontable, Vite, TypeScript

A grid on top of a shared sheet breaks in predictable ways if it syncs naively:

Someone sorts the sheet, and the grid’s next edit lands in the wrong row.
Two people edit the same cell, and the later write silently overwrites the earlier one.
A change from the sheet arrives while the user is typing, and wipes what they typed.
Every keystroke becomes a request, and the app runs into the API quota.
A user types =SUM(A:A), and the sheet turns it into a formula.

This recipe handles each of these. Rows are matched by an ID column, never by position. Each edit is checked against the sheet before it’s written. Remote changes wait until the editor closes. Edits are batched, and values are written as plain values.

What you’ll build

A sign-in button that gets a short-lived access token from Google, with no client secret and no server.
A grid whose columns come from the sheet’s first row, with a hidden id column.
Edits in the grid that reach the sheet within a second, in one request per batch.
Edits in Google Sheets that reach the grid within about 5 seconds.
Rows added and removed in either place.
Conflict detection: if someone changed a cell in the sheet after the grid last read it, the grid keeps their value and shows a toast.
Retries with exponential backoff when Google returns 429 Too Many Requests or a server error, and a request counter against the quota.

Before you begin

Requirement	Version
Node.js	20 or later
`handsontable`	18.1 or later
Vite	any current version
A Google account	with access to Google Cloud console

Create the app:

1
npm create vite@latest sheets-grid -- --template vanilla-ts
2
cd sheets-grid
3
npm install handsontable

Set up Google Cloud and the sheet

The browser needs an OAuth client ID to ask the user for access, and the Sheets API must be enabled for the project the ID belongs to.
1. In the Google Cloud console, create a project, or pick an existing one.
2. Go to APIs & Services > Library, find Google Sheets API, and select Enable.
3. Go to APIs & Services > OAuth consent screen (Google Auth Platform in newer versions of the console) and configure it. If your Google Workspace allows it, pick the Internal user type, so anyone in your organization can sign in. Otherwise pick External, keep the app in Testing mode, and add each tester’s Google account as a test user.
4. Go to APIs & Services > Credentials > Create credentials > OAuth client ID. Pick Web application, and under Authorized JavaScript origins add http://localhost:5173, the address Vite serves the app on. The origin must match exactly: http://127.0.0.1:5173 is a different origin. The token flow needs no redirect URI. Copy the client ID.
Then prepare the sheet:
1. Create a Google Sheet. Put the column names in row 1, and name one column id.
2. Give every row a unique value in the id column. Any text works, for example 1, 2, 3, or a UUID.
3. Copy the spreadsheet ID from its URL: https://docs.google.com/spreadsheets/d/<spreadsheet-id>/edit.
Create .env.local in the app folder:
Terminal window
```
1
VITE_GOOGLE_CLIENT_ID=your-client-id.apps.googleusercontent.com
2
VITE_SPREADSHEET_ID=your-spreadsheet-id
3
VITE_SHEET_NAME=Sheet1
```
The client ID isn’t a secret: it identifies your app to Google, and Google only accepts it from the origins you listed. Keep .env.local out of version control anyway, so each developer uses their own sheet.

Add the configuration

Create src/config.ts:

1
// Values come from .env.local. The OAuth client ID identifies your
2
// app to Google; it is not a secret, and the browser needs it.
3
export const CLIENT_ID = import.meta.env.VITE_GOOGLE_CLIENT_ID as string;
4
export const SPREADSHEET_ID = import.meta.env.VITE_SPREADSHEET_ID as string;
5
export const SHEET_NAME = (import.meta.env.VITE_SHEET_NAME as string | undefined) ?? 'Sheet1';
6

7
// The column that holds each row's stable ID. Rows are matched by this value, never by
8
// position, so sorting or inserting rows in Google Sheets can't send an edit to the wrong row.
9
export const ID_HEADER = 'id';
10

11
// 12 reads a minute, well under the Sheets API limit of 60 per user per minute.
12
export const POLL_MS = 5_000;

What’s happening: ID_HEADER names the column that identifies rows, and POLL_MS sets how often the grid reads the sheet. Every 5 seconds is 12 reads a minute. The Sheets API allows 60 read requests and 60 write requests a minute per user per project, so the grid stays well under the limit even with the extra reads its writes make.

Sign the user in

Create src/auth.ts:

1
import { CLIENT_ID } from './config';
2

3
// The parts of the Google Identity Services library this app uses. The library loads from
4
// https://accounts.google.com/gsi/client (see index.html); Google doesn't support self-hosting it.
5
interface TokenResponse {
6
  access_token: string;
7
  expires_in: number;
8
  error?: string;
9
}
10

11
interface TokenClient {
12
  requestAccessToken(overrides?: { prompt?: string }): void;
13
}
14

15
declare global {
16
  interface Window {
17
    google?: {
18
      accounts: {
19
        oauth2: {
20
          initTokenClient(config: {
21
            client_id: string;
22
            scope: string;
23
            callback: (response: TokenResponse) => void;
24
            error_callback?: (error: { type: string }) => void;
25
          }): TokenClient;
26
        };
27
      };
28
    };
29
  }
30
}
31

32
// Read and write access to the user's spreadsheets.
33
const SCOPE = 'https://www.googleapis.com/auth/spreadsheets';
34

35
let client: TokenClient | null = null;
36

37
// Opens Google's consent popup and resolves with an access token. Call it from a click
38
// handler: browsers block the popup otherwise. The token is short-lived and there is no
39
// refresh token in this flow, so when it expires the user signs in again.
40
export function signIn(): Promise<string> {
41
  return new Promise((resolve, reject) => {
42
    const oauth2 = window.google?.accounts.oauth2;
43

44
    if (!oauth2) {
45
      reject(new Error('Google Identity Services has not loaded yet.'));
46

47
      return;
48
    }
49

50
    client = oauth2.initTokenClient({
51
      client_id: CLIENT_ID,
52
      scope: SCOPE,
53
      callback: (response) => (response.error ? reject(new Error(response.error)) : resolve(response.access_token)),
54
      error_callback: (error) => reject(new Error(`Sign-in failed: ${error.type}`)),
55
    });
56
    client.requestAccessToken();
57
  });
58
}

What’s happening:

The token model needs no server. initTokenClient from Google Identity Services opens Google’s consent popup and returns an access token to the page. There is no client secret and no refresh token, so nothing sensitive lives in the browser beyond the token itself.
Sign-in must come from a click. Browsers block popups that a page opens on its own, so signIn runs in the button’s click handler.
Ignore the popup warning in the console. During sign-in, the browser console can log Cross-Origin-Opener-Policy policy would block the window.closed call. Google’s library checks whether its popup has closed, and the browser blocks that check. Sign-in still works.
Tokens expire. An access token lasts about an hour. When it expires, the API returns 401, and the app asks the user to sign in again.
The spreadsheets scope covers every sheet the user can open. That is what lets the app open a sheet it didn’t create. The narrower drive.file scope only covers files the app created or the user picked with the Google Picker. See Production considerations.

Call the Sheets API

Create src/sheets.ts:

1
import { SHEET_NAME, SPREADSHEET_ID } from './config';
2

3
export type Cell = string | number | boolean;
4

5
const API = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(SPREADSHEET_ID)}`;
6
const MAX_TRIES = 5;
7

8
let accessToken = '';
9
let sheetId: number | null = null;
10
// Request timestamps from the last minute, for the quota readout.
11
const requestLog: number[] = [];
12

13
export class SessionExpiredError extends Error {}
14

15
// A write that hit the quota (429) or a server error (5xx). Run the whole task again with `retrying`.
16
export class RetryableError extends Error {}
17

18
export function setAccessToken(token: string) {
19
  accessToken = token;
20
}
21

22
export function requestsLastMinute() {
23
  const cutoff = Date.now() - 60_000;
24

25
  while (requestLog.length > 0 && requestLog[0] < cutoff) {
26
    requestLog.shift();
27
  }
28

29
  return requestLog.length;
30
}
31

32
const sleep = (ms: number) => new Promise((resolve) => setTimeout(resolve, ms));
33

34
// Truncated exponential backoff with jitter, as Google recommends: about 1, 2, 4, and 8 seconds.
35
const backoff = (attempt: number) => Math.min(2 ** attempt * 1000 + Math.random() * 1000, 32_000);
36

37
// Every Sheets API call goes through here. A read that hits the quota (429) or a server error
38
// (5xx) waits and tries again. A write doesn't: it addresses rows by number, and rows can move
39
// in the sheet while it waits, so it throws RetryableError instead.
40
async function sheetsFetch<T>(path: string, init: RequestInit = {}): Promise<T> {
41
  const isRead = (init.method ?? 'GET') === 'GET';
42

43
  for (let attempt = 0; ; attempt++) {
44
    requestLog.push(Date.now());
45

46
    const response = await fetch(`${API}${path}`, {
47
      ...init,
48
      headers: { Authorization: `Bearer ${accessToken}`, 'Content-Type': 'application/json' },
49
    });
50

51
    if (response.status === 401) {
52
      throw new SessionExpiredError('Your Google session expired. Sign in again.');
53
    }
54

55
    if (response.status === 429 || response.status >= 500) {
56
      if (!isRead) {
57
        throw new RetryableError(`Sheets API error ${response.status}`);
58
      }
59

60
      if (attempt < MAX_TRIES - 1) {
61
        await sleep(backoff(attempt));
62
        continue;
63
      }
64
    }
65

66
    if (!response.ok) {
67
      const body = await response.json().catch(() => null);
68

69
      throw new Error(body?.error?.message ?? `Sheets API error ${response.status}`);
70
    }
71

72
    return response.json() as Promise<T>;
73
  }
74
}
75

76
// Runs a write task, and runs it again with backoff when a write in it throws RetryableError. The
77
// task reads the sheet before it writes, so each attempt finds the rows where they are now.
78
export async function retrying(task: () => Promise<void>) {
79
  for (let attempt = 0; ; attempt++) {
80
    try {
81
      await task();
82

83
      return;
84
    } catch (error) {
85
      if (!(error instanceof RetryableError) || attempt >= MAX_TRIES - 1) {
86
        throw error;
87
      }
88

89
      await sleep(backoff(attempt));
90
    }
91
  }
92
}
93

94
// 'Sheet1' -> "'Sheet1'": quoting makes names with spaces or apostrophes safe in a range.
95
const quotedSheet = `'${SHEET_NAME.replace(/'/g, "''")}'`;
96

97
// 0 -> A, 25 -> Z, 26 -> AA.
98
function columnLetter(index: number): string {
99
  let letters = '';
100

101
  for (let n = index + 1; n > 0; n = Math.floor((n - 1) / 26)) {
102
    letters = String.fromCharCode(65 + ((n - 1) % 26)) + letters;
103
  }
104

105
  return letters;
106
}
107

108
// Reads the whole tab. UNFORMATTED_VALUE returns numbers as numbers; FORMATTED_STRING
109
// returns dates as the text the sheet shows instead of serial numbers.
110
export async function readSheet(): Promise<Cell[][]> {
111
  const range = encodeURIComponent(quotedSheet);
112

113
  try {
114
    const result = await sheetsFetch<{ values?: Cell[][] }>(
115
      `/values/${range}?valueRenderOption=UNFORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING`,
116
    );
117

118
    return result.values ?? [];
119
  } catch (error) {
120
    // A tab that doesn't exist comes back as "Unable to parse range". Name the tabs instead.
121
    if (error instanceof Error && error.message.startsWith('Unable to parse range')) {
122
      const tabs = await listTabs();
123

124
      throw new Error(`No tab named "${SHEET_NAME}". Tabs in this spreadsheet: ${tabs.map(({ title }) => title).join(', ')}.`);
125
    }
126

127
    throw error;
128
  }
129
}
130

131
// Writes single cells in one atomic request. `sheetRow` is the 1-based row in the sheet.
132
// RAW stores each value exactly as sent: text that starts with `=` stays text and never
133
// becomes a formula.
134
export async function writeCells(cells: { sheetRow: number; column: number; value: Cell }[]) {
135
  if (cells.length === 0) {
136
    return;
137
  }
138

139
  await sheetsFetch('/values:batchUpdate', {
140
    method: 'POST',
141
    body: JSON.stringify({
142
      valueInputOption: 'RAW',
143
      data: cells.map(({ sheetRow, column, value }) => ({
144
        range: `${quotedSheet}!${columnLetter(column)}${sheetRow}`,
145
        values: [[value]],
146
      })),
147
    }),
148
  });
149
}
150

151
// Adds rows after the table that starts at A1. A blank row ends that table, so rows can land
152
// above it instead of at the bottom; the sync matches rows by ID, so that is fine.
153
export async function appendRows(rows: Cell[][]) {
154
  if (rows.length === 0) {
155
    return;
156
  }
157

158
  const range = encodeURIComponent(`${quotedSheet}!A1`);
159

160
  await sheetsFetch(`/values/${range}:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS`, {
161
    method: 'POST',
162
    body: JSON.stringify({ values: rows }),
163
  });
164
}
165

166
// The spreadsheet's tabs, with the numeric IDs the values API doesn't use.
167
async function listTabs() {
168
  const result = await sheetsFetch<{ sheets: { properties: { sheetId: number; title: string } }[] }>(
169
    '?fields=sheets.properties(sheetId,title)',
170
  );
171

172
  return result.sheets.map(({ properties }) => properties);
173
}
174

175
// Deleting rows needs the tab's numeric ID.
176
async function getSheetId(): Promise<number> {
177
  if (sheetId === null) {
178
    const tab = (await listTabs()).find(({ title }) => title === SHEET_NAME);
179

180
    if (!tab) {
181
      throw new Error(`No tab named "${SHEET_NAME}" in the spreadsheet.`);
182
    }
183

184
    sheetId = tab.sheetId;
185
  }
186

187
  return sheetId;
188
}
189

190
// Deletes whole rows (1-based sheet row numbers). Bottom-up, so earlier deletions don't
191
// shift the rows still to be deleted.
192
export async function deleteRows(sheetRows: number[]) {
193
  if (sheetRows.length === 0) {
194
    return;
195
  }
196

197
  const id = await getSheetId();
198

199
  await sheetsFetch(':batchUpdate', {
200
    method: 'POST',
201
    body: JSON.stringify({
202
      requests: [...sheetRows].sort((a, b) => b - a).map((row) => ({
203
        deleteDimension: { range: { sheetId: id, dimension: 'ROWS', startIndex: row - 1, endIndex: row } },
204
      })),
205
    }),
206
  });
207
}

What’s happening:

A missing tab is named. If the spreadsheet has no tab called VITE_SHEET_NAME, the API answers Unable to parse range. readSheet turns that into a message that lists the tabs the spreadsheet does have.
One wrapper for every request. sheetsFetch adds the bearer token. A read that gets 429 or a 5xx response is retried with truncated exponential backoff, as Google recommends: about 1, 2, 4, and 8 seconds, plus random jitter, capped at 32 seconds, and at most five tries. A write isn’t retried on its own: it addresses rows by number, and rows can move in the sheet during the wait. It throws RetryableError, and retrying runs the whole write task again, read included, so each try finds the rows where they are. A 401 throws SessionExpiredError instead of retrying.
Numbers come back as numbers. readSheet asks for UNFORMATTED_VALUE, so a price is 12.5, not "$12.50", and the grid can sort and edit it as a number. dateTimeRenderOption=FORMATTED_STRING returns dates as the text the sheet shows, instead of serial numbers.
RAW keeps text as text. writeCells sends valueInputOption: 'RAW', so the sheet stores each value exactly as sent. With USER_ENTERED, the sheet would parse it the way it parses typing, and a value that starts with = would become a formula.
One request per batch. values:batchUpdate writes any number of cells in one request, and either all of them are written or none is. It counts as one request against the quota.
Appended rows go after the first table Google finds. values:append looks for a table starting at A1 and inserts the new rows right after it. A blank row ends that table, so in a sheet with a blank row in the middle, a row added in the grid lands above the blank row instead of at the bottom. The sync matches rows by ID, so the position doesn’t matter to it.
Rows are deleted bottom-up. Deleting a row shifts every row below it up by one. deleteRows sorts the row numbers from the bottom, so each deletion leaves the rows still to delete where they were.
Sheet names are quoted. quotedSheet wraps the tab name in single quotes, so a name with a space or an apostrophe still works in a range.

Sync the sheet and the grid

Create src/sync.ts:

1
import type Handsontable from 'handsontable/base';
2
import type { CellChange, ChangeSource } from 'handsontable/settings';
3
import { ID_HEADER, POLL_MS } from './config';
4
import { createGrid } from './grid';
5
import { appendRows, deleteRows, readSheet, retrying, SessionExpiredError, writeCells, type Cell } from './sheets';
6

7
// Changes the sync makes itself. Only a user's change goes back to the sheet.
8
const OWN_SOURCES = new Set<string>(['sync', 'loadData', 'updateData']);
9
const EDIT_DEBOUNCE_MS = 300;
10
const COLUMNS_CHANGED = 'The sheet\'s columns changed. Reload the page to pick them up.';
11

12
// A value the grid sent, or is about to send, to the sheet. writtenAt is set once the sheet
13
// has it.
14
interface Pending {
15
  value: Cell;
16
  writtenAt?: number;
17
}
18

19
interface Edit {
20
  id: string;
21
  column: number;
22
  base: Cell;
23
  value: Cell;
24
  pending: Pending;
25
}
26

27
// Sheets leaves out empty cells at the end of a row, and an empty grid cell is null.
28
// Both become '' so an untouched empty cell compares equal on both sides.
29
const normalize = (value: unknown): Cell => (value === null || value === undefined ? '' : (value as Cell));
30
const key = (id: string, column: number) => `${id}\u0000${column}`;
31

32
export function startSync(container: HTMLElement, onStatus: (text: string, isError?: boolean) => void) {
33
  let hot: Handsontable | null = null;
34
  let header: string[] = [];
35
  let idColumn = -1;
36
  // The grid's data. Handsontable keeps a reference to it, so a physical row index is a
37
  // position in this array.
38
  let rows: Cell[][] = [];
39
  const indexById = new Map<string, number>();
40

41
  // Cells the grid is writing. A read that started before the write landed must not overwrite
42
  // them; a read that started after it is the sheet's word, even if someone changed the cell.
43
  const pendingCells = new Map<string, Pending>();
44
  // Rows added or removed in the grid whose change hasn't reached the sheet yet.
45
  const pendingAppends = new Set<string>();
46
  const pendingDeletes = new Set<string>();
47

48
  // Set once the sheet's first row no longer matches the grid's columns. From then on, nothing
49
  // syncs: a write by column index could land in the wrong column.
50
  let columnsChanged = false;
51
  // IDs that more than one sheet row has. Each is reported once.
52
  const warnedDuplicates = new Set<string>();
53
  // Set while polls fail, so an outage shows one toast, not one every POLL_MS.
54
  let pollFailing = false;
55

56
  let editBatch = new Map<string, Edit>();
57
  let editTimer = 0;
58
  let pollTimer = 0;
59
  // All writes run one after another, so an append lands before an edit to that row.
60
  let writeQueue = Promise.resolve();
61

62
  // The message can quote a sheet value, so it goes in as text, never as HTML.
63
  function notify(title: string, message: string) {
64
    const text = document.createElement('span');
65

66
    text.textContent = message;
67
    hot?.getPlugin('notification').showMessage({ variant: 'warning', title, message: text });
68
  }
69

70
  // Clears an edit's pending entry, unless a newer edit of the same cell replaced it.
71
  function settle(edit: Edit) {
72
    if (pendingCells.get(key(edit.id, edit.column)) === edit.pending) {
73
      pendingCells.delete(key(edit.id, edit.column));
74
    }
75
  }
76

77
  function reindex() {
78
    indexById.clear();
79
    rows.forEach((row, index) => indexById.set(idOf(row), index));
80
  }
81

82
  // Splits a sheet read into the header and the data rows. Each row is padded to the header's
83
  // width and keeps its 1-based sheet row number, taken before blank rows are skipped, so a
84
  // blank row in the middle of the sheet can't shift a write to the wrong row.
85
  function parse(values: Cell[][]) {
86
    const [first = [], ...rest] = values;
87
    const names = first.map(String);
88
    const body: { sheetRow: number; row: Cell[] }[] = [];
89

90
    rest.forEach((raw, index) => {
91
      const row = Array.from({ length: names.length }, (_, column) => normalize(raw[column]));
92

93
      if (row.some((value) => value !== '')) {
94
        body.push({ sheetRow: index + 2, row });
95
      }
96
    });
97

98
    return { names, body };
99
  }
100

101
  // A row the grid just created holds null, which normalize turns into ''.
102
  const idOf = (row: Cell[]) => String(normalize(row[idColumn]));
103

104
  // The first sheet row with each ID. A row copied in Google Sheets with its ID is left out, with a
105
  // warning, so an edit or a delete in the grid reaches one sheet row only.
106
  function byId(body: { sheetRow: number; row: Cell[] }[]) {
107
    const entries = new Map<string, { sheetRow: number; row: Cell[] }>();
108

109
    for (const entry of body) {
110
      const id = idOf(entry.row);
111

112
      if (id === '') {
113
        continue;
114
      }
115

116
      if (!entries.has(id)) {
117
        entries.set(id, entry);
118
      } else if (hot && !warnedDuplicates.has(id)) {
119
        warnedDuplicates.add(id);
120
        notify('Duplicate ID', `More than one row in the sheet has the ID "${id}". Only the first one syncs.`);
121
      }
122
    }
123

124
    return entries;
125
  }
126

127
  function checkColumns(names: string[]) {
128
    if (columnsChanged || names.join('\u0000') !== header.join('\u0000')) {
129
      columnsChanged = true;
130
      throw new Error(COLUMNS_CHANGED);
131
    }
132
  }
133

134
  // Sorting and filtering don't re-run when data changes, so re-apply them after rows are
135
  // added or removed. New rows then land in sorted position and filtered-out rows stay hidden.
136
  function reapplyView() {
137
    if (!hot) {
138
      return;
139
    }
140

141
    const filters = hot.getPlugin('filters');
142
    const sorting = hot.getPlugin('columnSorting');
143
    const sortConfig = sorting.getSortConfig();
144

145
    if (filters.exportConditions().length > 0) {
146
      filters.filter();
147
    }
148

149
    if (Array.isArray(sortConfig) && sortConfig.length > 0) {
150
      sorting.sort(sortConfig);
151
    }
152
  }
153

154
  // Applies a fresh read of the sheet to the grid. readStartedAt is when the read was sent.
155
  function applyRemote(values: Cell[][], readStartedAt: number) {
156
    const { names, body } = parse(values);
157

158
    if (!hot) {
159
      header = names;
160
      idColumn = header.indexOf(ID_HEADER);
161

162
      if (idColumn === -1) {
163
        throw new Error(`The first row of the sheet needs a column named "${ID_HEADER}".`);
164
      }
165

166
      // A row typed into the sheet without an ID isn't synced. Give it one there.
167
      rows = [...byId(body).values()].map(({ row }) => row);
168
      reindex();
169
      hot = createGrid(container, header, rows, idColumn);
170
      attachHooks(hot);
171

172
      return;
173
    }
174

175
    checkColumns(names);
176

177
    // Setting a cell's source data refreshes an open editor and would wipe what the user is
178
    // typing. Skip this round; the next poll catches up.
179
    if (hot.getActiveEditor()?.isOpened()) {
180
      return;
181
    }
182

183
    const remoteIds = new Set<string>();
184
    const cellChanges: [number, number, Cell][] = [];
185
    let structural = false;
186

187
    for (const [id, { row: remoteRow }] of byId(body)) {
188
      remoteIds.add(id);
189
      pendingAppends.delete(id);
190

191
      if (pendingDeletes.has(id)) {
192
        continue;
193
      }
194

195
      const index = indexById.get(id);
196

197
      if (index === undefined) {
198
        rows.push(remoteRow);
199
        structural = true;
200
        continue;
201
      }
202

203
      remoteRow.forEach((value, column) => {
204
        const pending = pendingCells.get(key(id, column));
205

206
        if (pending) {
207
          const landed = pending.writtenAt !== undefined && readStartedAt > pending.writtenAt;
208

209
          if (!landed && pending.value !== value) {
210
            return; // This read predates the grid's write. Keep the grid's value.
211
          }
212

213
          pendingCells.delete(key(id, column));
214
        }
215

216
        if (rows[index][column] !== value) {
217
          cellChanges.push([index, column, value]);
218
        }
219
      });
220
    }
221

222
    for (const id of pendingDeletes) {
223
      if (!remoteIds.has(id)) {
224
        pendingDeletes.delete(id); // The sheet has our deletion.
225
      }
226
    }
227

228
    // Physical row indexes: a sort or filter in the grid can't misdirect a value.
229
    if (cellChanges.length > 0) {
230
      hot.setSourceDataAtCell(cellChanges, undefined, undefined, 'sync');
231
    }
232

233
    const removed = rows.filter((row) => {
234
      const id = idOf(row);
235

236
      return !remoteIds.has(id) && !pendingAppends.has(id);
237
    });
238

239
    if (removed.length > 0) {
240
      rows = rows.filter((row) => !removed.includes(row));
241
      structural = true;
242
    }
243

244
    if (structural) {
245
      reindex();
246
      hot.updateData(rows, 'sync');
247
      reapplyView();
248
    }
249
  }
250

251
  function enqueueWrite(task: () => Promise<void>) {
252
    writeQueue = writeQueue.then(task).catch(handleError);
253
  }
254

255
  // Writes a batch of grid edits. Reads the sheet first to find each row's current position
256
  // and to catch edits someone else made to the same cell since the grid last synced.
257
  async function flushEdits(batch: Map<string, Edit>) {
258
    try {
259
      await retrying(() => writeEdits(batch));
260
    } catch (error) {
261
      // Nothing from this batch reached the sheet: put the grid back to what the sheet holds.
262
      for (const edit of batch.values()) {
263
        settle(edit);
264

265
        const index = indexById.get(edit.id);
266

267
        if (index !== undefined) {
268
          hot!.setSourceDataAtCell(index, edit.column, edit.base, 'sync');
269
        }
270
      }
271

272
      throw error;
273
    }
274
  }
275

276
  async function writeEdits(batch: Map<string, Edit>) {
277
    const { names, body } = parse(await readSheet());
278

279
    checkColumns(names);
280

281
    const remoteById = byId(body);
282
    const toWrite: { sheetRow: number; column: number; value: Cell }[] = [];
283
    const written: Edit[] = [];
284

285
    // An edit that is settled here leaves the batch, so a retry doesn't handle it twice.
286
    for (const [editKey, edit] of batch) {
287
      const remote = remoteById.get(edit.id);
288

289
      if (!remote) {
290
        settle(edit);
291
        batch.delete(editKey);
292
        continue; // The row was deleted in the sheet; the next poll removes it here too.
293
      }
294

295
      const { sheetRow } = remote;
296
      const current = remote.row[edit.column];
297

298
      if (current !== edit.base && current !== edit.value) {
299
        // Someone changed this cell in the sheet after the grid last read it. Keep their value.
300
        settle(edit);
301
        batch.delete(editKey);
302

303
        const index = indexById.get(edit.id);
304

305
        if (index !== undefined) {
306
          hot!.setSourceDataAtCell(index, edit.column, current, 'sync');
307
        }
308

309
        notify('Edit not saved', `Someone changed "${header[edit.column]}" in the sheet first. Showing their value: ${current}`);
310
        continue;
311
      }
312

313
      written.push(edit);
314

315
      if (current !== edit.value) {
316
        toWrite.push({ sheetRow, column: edit.column, value: edit.value });
317
      }
318
    }
319

320
    await writeCells(toWrite);
321

322
    const now = Date.now();
323

324
    written.forEach((edit) => {
325
      edit.pending.writtenAt = now;
326
    });
327
  }
328

329
  // Appends rows created in the grid. A row that undo brought back keeps its ID, so it is the same
330
  // row again; an empty new row gets a stable ID first.
331
  function sendNewRows(created: Cell[][]) {
332
    const newRows: Cell[][] = [];
333
    const newIds: string[] = [];
334

335
    for (const row of created) {
336
      const index = rows.indexOf(row);
337

338
      if (index === -1) {
339
        continue; // Removed again before it could be sent.
340
      }
341

342
      let id = idOf(row);
343

344
      if (id === '') {
345
        id = crypto.randomUUID();
346
        hot!.setSourceDataAtCell(index, idColumn, id, 'sync');
347
      }
348

349
      pendingAppends.add(id);
350
      pendingDeletes.delete(id);
351
      newIds.push(id);
352
      newRows.push(row.map(normalize));
353
    }
354

355
    reindex();
356

357
    if (newRows.length === 0) {
358
      return;
359
    }
360

361
    enqueueWrite(async () => {
362
      try {
363
        // Each try reads the sheet first. A 5xx can come back after Google added the rows, so a
364
        // retry skips the ones the sheet already has.
365
        await retrying(async () => {
366
          const { names, body } = parse(await readSheet());
367

368
          checkColumns(names);
369

370
          const present = byId(body);
371

372
          await appendRows(newRows.filter((row) => !present.has(idOf(row))));
373
        });
374
      } catch (error) {
375
        // Every try failed: the rows never reached the sheet, so the next poll removes them here.
376
        newIds.forEach((id) => pendingAppends.delete(id));
377
        throw error;
378
      }
379
    });
380
  }
381

382
  function attachHooks(grid: Handsontable) {
383
    // A user's edit: remember the value the grid had before (the base), batch for 300 ms.
384
    grid.addHook('afterChange', (changes: CellChange[] | null, source: ChangeSource) => {
385
      if (!changes || OWN_SOURCES.has(source)) {
386
        return;
387
      }
388

389
      for (const [visualRow, column, oldValue, newValue] of changes) {
390
        const row = rows[grid.toPhysicalRow(visualRow)];
391
        const id = idOf(row);
392
        const value = normalize(newValue);
393
        const existing = editBatch.get(key(id, column as number));
394

395
        if (normalize(oldValue) === value) {
396
          continue;
397
        }
398

399
        const pending: Pending = { value };
400

401
        editBatch.set(key(id, column as number), {
402
          id,
403
          column: column as number,
404
          base: existing?.base ?? normalize(oldValue),
405
          value,
406
          pending,
407
        });
408
        pendingCells.set(key(id, column as number), pending);
409
      }
410

411
      clearTimeout(editTimer);
412
      editTimer = window.setTimeout(() => {
413
        const batch = editBatch;
414

415
        editBatch = new Map();
416
        enqueueWrite(() => flushEdits(batch));
417
      }, EDIT_DEBOUNCE_MS);
418
    });
419

420
    // A new row goes to the sheet. Undo and redo create a row first and refill it after this hook,
421
    // so for them the row is read once the restore has finished.
422
    grid.addHook('afterCreateRow', (visualIndex: number, amount: number, source?: string) => {
423
      if (source && OWN_SOURCES.has(source)) {
424
        return;
425
      }
426

427
      const created = Array.from({ length: amount }, (_, offset) => rows[grid.toPhysicalRow(visualIndex + offset)]);
428

429
      if (source?.startsWith('UndoRedo')) {
430
        queueMicrotask(() => sendNewRows(created));
431
      } else {
432
        sendNewRows(created);
433
      }
434
    });
435

436
    // Read the IDs before Handsontable removes the rows from the data.
437
    grid.addHook('beforeRemoveRow', (_index: number, _amount: number, physicalRows: number[], source?: string) => {
438
      if (source && OWN_SOURCES.has(source)) {
439
        return;
440
      }
441

442
      const ids = physicalRows.map((physical) => idOf(rows[physical])).filter((id) => id !== '');
443

444
      ids.forEach((id) => pendingDeletes.add(id));
445
      enqueueWrite(async () => {
446
        try {
447
          // Each try reads the sheet first, so it deletes the rows where they are now.
448
          await retrying(async () => {
449
            const { names, body } = parse(await readSheet());
450

451
            checkColumns(names);
452

453
            const remote = byId(body);
454
            const sheetRows = ids.flatMap((id) => remote.get(id)?.sheetRow ?? []);
455

456
            await deleteRows(sheetRows);
457
          });
458
        } catch (error) {
459
          // Every try failed. The rows are still in the sheet, so the next poll brings them back
460
          // to the grid.
461
          ids.forEach((id) => pendingDeletes.delete(id));
462
          throw error;
463
        }
464
      });
465
    });
466

467
    grid.addHook('afterRemoveRow', () => reindex());
468

469
    // The sync's own writes stay off the undo stack, so Ctrl+Z can't revert a colleague's change.
470
    grid.addHook('beforeUndoStackChange', (_doneActions: unknown[], source?: string) => source !== 'sync');
471
  }
472

473
  function handleError(error: unknown) {
474
    if (error instanceof SessionExpiredError) {
475
      clearTimeout(pollTimer);
476
      onStatus(error.message, true);
477

478
      return;
479
    }
480

481
    notify('Sync error', (error as Error).message);
482
  }
483

484
  // Reads the sheet every POLL_MS while the tab is visible.
485
  async function poll() {
486
    try {
487
      if (!document.hidden) {
488
        const readStartedAt = Date.now();
489

490
        applyRemote(await readSheet(), readStartedAt);
491
        pollFailing = false;
492
        onStatus(`Synced at ${new Date().toLocaleTimeString()}`);
493
      }
494
    } catch (error) {
495
      // A first read that failed, an expired session, or changed columns: stop and say why.
496
      if (error instanceof SessionExpiredError || !hot || columnsChanged) {
497
        onStatus((error as Error).message, true);
498

499
        return;
500
      }
501

502
      // Offline, for example. Say so once, and keep polling until a read works again.
503
      onStatus(`Can't reach the sheet. Retrying every ${POLL_MS / 1000} seconds.`);
504

505
      if (!pollFailing) {
506
        pollFailing = true;
507
        handleError(error);
508
      }
509
    }
510

511
    pollTimer = window.setTimeout(poll, POLL_MS);
512
  }
513

514
  poll();
515

516
  return {
517
    // Restarts polling after the user signs in again.
518
    resume: () => {
519
      clearTimeout(pollTimer);
520
      poll();
521
    },
522
  };
523
}

What’s happening:

The grid polls. Google can notify a server about file changes through Drive’s files.watch, but the notification goes to a public HTTPS endpoint, which a browser-only app doesn’t have. So poll reads the sheet every POLL_MS, and pauses while the browser tab is hidden. If a read fails, for example while the user is offline, the grid shows one toast and keeps polling.
Rows are matched by ID. applyRemote compares each sheet row with the grid row that has the same ID. A row that moved in the sheet, because someone sorted it or inserted a row above it, still updates the right grid row. A sheet row with an empty id isn’t synced. If a row was copied in Google Sheets with its ID, only the first row with that ID syncs, and the grid warns about the copy. parse skips blank rows but keeps each row’s sheet row number, so a blank row in the middle of the sheet can’t shift a write to the row below it.
Empty cells are normalized on both sides. The Sheets API leaves out empty cells at the end of a row, and an empty grid cell is null. parse pads every row to the header’s width, and normalize turns null and missing cells into ''. Without this, every untouched empty cell would look like a change.
Changed values go in by physical row. setSourceDataAtCell takes physical row indexes, the positions in the rows array, so a sort or filter in the grid can’t send a value to the wrong row. It takes all of a poll’s changes in one call and renders once. The 'sync' source tells the afterChange hook not to send the change back.
Added and removed rows replace the data. When the sheet has rows the grid doesn’t, or the other way round, updateData loads the new array. Sorting and filtering don’t re-run on their own when data changes, so reapplyView re-applies them, and new rows land in sorted position.
An open editor isn’t interrupted. Setting a cell’s source data refreshes the active editor’s value, which would wipe what the user is typing. While an editor is open, applyRemote skips the round, and the next poll catches up.
The grid’s own writes aren’t undone by a poll. A read can return before a write reaches the sheet. pendingCells, pendingAppends, and pendingDeletes hold what the grid sent, and applyRemote leaves those cells and rows alone until the sheet has them. Each pending cell records when its write finished. A read that started after that is the sheet’s current state, so if someone changed the cell in the meantime, their value wins instead of the grid holding on to its own.
Edits are batched and checked. afterChange collects a user’s edits for 300 ms and remembers each cell’s value before the edit, the base. writeEdits then reads the sheet once, finds each row’s current position by ID, and compares the sheet’s value with the base. If they differ, someone changed the cell in Google Sheets after the grid last read it: the grid keeps their value, and the Notification plugin says so. The other edits go out in one values:batchUpdate.
New rows get an ID in the grid. afterCreateRow gives each new row a crypto.randomUUID() ID and appends it to the sheet. A row that undo brings back keeps its old ID: undo creates the row before it refills it, so sendNewRows reads the row in a microtask, once the refill is done. beforeRemoveRow reads the IDs of the rows being removed while they still exist, then deletes those rows from the sheet by their current row numbers. If either write fails, the pending entry is dropped, and the next poll puts the grid back to what the sheet holds.
Writes run in order. enqueueWrite chains every write on one promise, so a new row reaches the sheet before an edit to that row does.
Undo covers the user’s own edits. The sync writes with the 'sync' source, and beforeUndoStackChange keeps those writes off the undo stack, so Ctrl+Z can’t revert a colleague’s change.

Build the grid

Create src/grid.ts:

1
import Handsontable from 'handsontable/base';
2
import { registerAllModules } from 'handsontable/registry';
3
import type { ColumnSettings } from 'handsontable/settings';
4
import 'handsontable/styles/handsontable.min.css';
5
import 'handsontable/styles/ht-theme-main.min.css';
6
import type { Cell } from './sheets';
7

8
registerAllModules();
9

10
// A column whose filled cells are all numbers gets the numeric cell type.
11
function columnFor(rows: Cell[][], column: number): ColumnSettings {
12
  const filled = rows.map((row) => row[column]).filter((value) => value !== '');
13
  const numeric = filled.length > 0 && filled.every((value) => typeof value === 'number');
14

15
  return numeric ? { type: 'numeric' } : { type: 'text' };
16
}
17

18
// The grid uses arrays, not objects keyed by header: a header such as "Unit.price" would
19
// otherwise be read as a nested path.
20
export function createGrid(container: HTMLElement, header: string[], rows: Cell[][], idColumn: number) {
21
  return new Handsontable(container, {
22
    data: rows,
23
    colHeaders: header,
24
    // The ID column is read-only, so a paste or Delete over a selected row can't change an ID.
25
    columns: header.map((_, column) => (column === idColumn ? { readOnly: true } : columnFor(rows, column))),
26
    // The ID column stays in the data, so edits can be matched to sheet rows, but users never see it.
27
    hiddenColumns: { columns: [idColumn] },
28
    rowHeaders: true,
29
    columnSorting: true,
30
    filters: true,
31
    dropdownMenu: true,
32
    contextMenu: ['row_above', 'row_below', 'remove_row', '---------', 'undo', 'redo'],
33
    notification: true,
34
    height: 520,
35
    stretchH: 'all',
36
    themeName: 'ht-theme-main',
37
    // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/
38
  });
39
}

What’s happening:

The data is arrays, not objects. Handsontable reads a column’s data property with dots as a nested path, so a header such as Unit.price would bind to the wrong place. The grid keeps each row as an array, uses the column index as the column, and shows the sheet’s headers through colHeaders.
Column types come from the data. columnFor makes a column numeric when every filled cell in it is a number, and text otherwise.
The ID stays in the data. hiddenColumns hides the id column from users. The value is still in each row, so edits can be matched to sheet rows. Hidden columns still take part in copy and paste, so the column is also readOnly: pasting rows or pressing Delete on a selected row can’t change an ID. The sync sets new IDs with setSourceDataAtCell, which writes the data directly.

Run the app

Replace src/main.ts:

1
import { signIn } from './auth';
2
import { requestsLastMinute, setAccessToken } from './sheets';
3
import { startSync } from './sync';
4
import './styles.css';
5

6
const button = document.querySelector<HTMLButtonElement>('#sign-in')!;
7
const status = document.querySelector('#status')!;
8
const requests = document.querySelector('#requests')!;
9

10
function showStatus(text: string, isError = false) {
11
  status.textContent = text;
12
  status.classList.toggle('is-error', isError);
13

14
  if (isError) {
15
    button.hidden = false;
16
  }
17
}
18

19
let sync: ReturnType<typeof startSync> | null = null;
20

21
button.addEventListener('click', async () => {
22
  try {
23
    setAccessToken(await signIn());
24
    button.hidden = true;
25

26
    if (sync) {
27
      sync.resume();
28
    } else {
29
      sync = startSync(document.querySelector('#grid')!, showStatus);
30
    }
31
  } catch (error) {
32
    showStatus((error as Error).message, true);
33
  }
34
});
35

36
setInterval(() => {
37
  requests.textContent = String(requestsLastMinute());
38
}, 1000);

Add the styles in src/styles.css:

1
body {
2
  margin: 0;
3
  font-family: Inter, system-ui, -apple-system, sans-serif;
4
}
5

6
main {
7
  max-width: 1100px;
8
  margin: 0 auto;
9
  padding: 20px 16px;
10
}
11

12
h1 {
13
  margin: 0 0 8px;
14
  font-size: 22px;
15
}
16

17
.toolbar {
18
  display: flex;
19
  gap: 20px;
20
  align-items: center;
21
  font-size: 14px;
22
}
23

24
.is-error {
25
  color: #b3261e;
26
}
27

28
button {
29
  padding: 6px 12px;
30
  font: inherit;
31
  cursor: pointer;
32
}

Replace index.html. Google Identity Services must load from accounts.google.com. Google doesn’t support self-hosting it.

1
<!doctype html>
2
<html lang="en">
3
  <head>
4
    <meta charset="UTF-8" />
5
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
6
    <title>Two-way sync with Google Sheets</title>
7
    <script src="https://accounts.google.com/gsi/client" async></script>
8
  </head>
9
  <body>
10
    <main>
11
      <h1>Two-way sync with Google Sheets</h1>
12
      <p class="toolbar">
13
        <button id="sign-in" type="button">Sign in with Google</button>
14
        <span id="status">Not connected</span>
15
        <span>Requests in the last minute: <strong id="requests">0</strong> / 60</span>
16
      </p>
17
      <div id="grid"></div>
18
    </main>
19
    <script type="module" src="/src/main.ts"></script>
20
  </body>
21
</html>

Start the app:

1
npm run dev

Open http://localhost:5173, select Sign in with Google, and allow access. If the app reports a missing tab, rename the tab in Google Sheets or set VITE_SHEET_NAME to its name, then restart npm run dev: Vite reads .env.local only when it starts. The grid loads the sheet. Open the sheet in a second window and try the following:

Edit a cell in the grid. The sheet shows the new value within a second.
Edit a cell in the sheet. The grid shows it within about 5 seconds.
Sort the sheet by any column, then edit a cell in the grid. The right row changes.
Change a cell in the sheet, then change the same cell in the grid before the next poll. The grid shows a toast and the sheet’s value.
Type =1+1 in a grid cell. The sheet stores the text =1+1, not 2.
Add and remove rows in both places.
Start editing a cell and wait for a poll. What you typed stays.

How it works — complete flow

The user signs in, and Google returns an access token to the page.
The grid reads the whole tab, takes the headers from row 1, and loads the rows that have an ID.
Every 5 seconds, the grid reads the tab again, matches rows by ID, and applies the changed values with one setSourceDataAtCell call, or replaces the data with updateData when rows were added or removed.
The user edits cells. After 300 ms without another edit, the grid reads the tab, drops each edit whose cell someone else changed, and writes the rest with one values:batchUpdate request.
The next poll sees the written values and clears them from the pending list.

Production considerations

Publish the consent screen. In Testing mode, only the test users you added can sign in. To let anyone sign in, publish the app. The spreadsheets scope is sensitive, so Google reviews the app before it shows the consent screen without a warning. An app for one Google Workspace organization can use the Internal user type, which needs no review.
Consider a narrower scope. With drive.file, the app only gets the files a user created in it or picked with the Google Picker. Users grant access file by file, and because drive.file isn’t a sensitive scope, it doesn’t need Google’s review.
Add every origin. Google only accepts the client ID from the Authorized JavaScript origins you listed. Add your production origin, and each preview origin you use.
Close the gap between reading and writing. Before each write, the grid reads the sheet to find each row’s current position, then writes by row number. If someone sorts the sheet or inserts a row in the fraction of a second between the two requests, the write can land in the wrong row. To remove that window, tag each row with developer metadata that holds its ID, and write with values:batchUpdateByDataFilter, which finds rows by their metadata instead of their position.
Keep the sheet’s shape stable. The grid takes its columns from row 1 once. If someone renames, adds, or removes a column, the grid stops syncing, and the status line asks the user to reload the page. Protect row 1 and the id column in Google Sheets, so only the people who own the layout can change them.
Store dates as text, or convert them. With RAW, a date typed in the grid is stored as text, and the sheet doesn’t treat it as a date. To keep real dates, convert the value to a Sheets serial number before writing, or write that column with USER_ENTERED.
Know the limits. The recipe reads the whole tab on every poll. That suits sheets with up to a few thousand rows. A Google Sheet holds at most 10 million cells. For larger data, keep it in a database and use the dataProvider plugin.
Use a server for push updates. To get changes in seconds instead of polling, run a small server that receives Drive files.watch notifications and forwards them to the browser, as the Kafka recipe does with Server-Sent Events.

What you learned

How to sign a user in with Google Identity Services and call the Sheets API from the browser, with no server.
Why rows should be matched by an ID column, not by position.
How to detect a conflicting edit by comparing the sheet’s value with the value the grid had before the edit.
Why RAW keeps typed text from becoming a formula.
How to apply remote changes to Handsontable by physical row with setSourceDataAtCell, without interrupting an open editor.
How to stay under the Sheets API quota with polling, batched writes, and exponential backoff.

Next steps

Stream Kafka topics into the grid — push updates from a server with Server-Sent Events.
Saving data — the hooks and sources behind the write-back.
Notification — the toasts the grid shows for conflicts and errors.
Google Sheets API usage limits — the quota and the recommended backoff.
Google Identity Services token model — how the browser gets an access token.

Google Sheets is a trademark of Google LLC.

Two-way sync with Google Sheets

Overview

What you’ll build

Before you begin

Set up Google Cloud and the sheet

Add the configuration

Sign the user in

Call the Sheets API

Sync the sheet and the grid

Build the grid

Run the app

How it works — complete flow

Production considerations

What you learned

Next steps