@mshindi-labs/gsheets

A fully-typed TypeScript wrapper around the Google Sheets v4 API. Every method returns a discriminated-union response — no thrown exceptions, no manual error parsing.

const result = await sheet.getHeaderRow('Customers');

if (result.success) {
  console.log(result.data); // string[]
} else {
  console.error(result.problem); // string
}

Table of Contents

• Installation
• Setup
  • Step 1 — Google Cloud credentials
  • Step 2 — Obtain a refresh token
  • Step 3 — Create a client
• Response shape
• Methods
  • Metadata & structure
  • Reading data
  • Writing data
  • Batch operations
  • Rows & columns
  • Sheet management
  • Formatting & styling
  • Data utilities
  • Filtering & sorting
  • Named ranges
• TypeScript types
• Requirements
• License

Installation

npm install @mshindi-labs/gsheets
# or
pnpm add @mshindi-labs/gsheets
# or
yarn add @mshindi-labs/gsheets

Setup

Step 1 — Google Cloud credentials

1. Go to the Google Cloud Console.
2. Create a project (or select an existing one).
3. Enable the Google Sheets API and Google Drive API.
4. Go to APIs & Services → Credentials → Create Credentials → OAuth 2.0 Client ID.
5. Choose Desktop app (for local/server scripts) or Web application.
6. Download the JSON — you need client_id, client_secret, and your chosen redirect_uri.

Step 2 — Obtain a refresh token

Run this once to exchange an authorization code for a long-lived refresh token.

import { createOAuth2Client, generateAuthUrl, retrieveRefreshToken } from '@mshindi-labs/gsheets';

const client = createOAuth2Client({
  clientId: process.env.GOOGLE_CLIENT_ID!,
  clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
  redirectUri: 'http://localhost',
});

// 1. Print the URL, open it in a browser, grant access.
console.log(generateAuthUrl(client));

// 2. Paste the code= query param from the redirect URL here.
const refreshToken = await retrieveRefreshToken(client, 'PASTE_CODE_HERE');
console.log('Save this refresh token:', refreshToken);

Store the refresh token securely (e.g. in an environment variable or secrets manager). It does not expire unless access is explicitly revoked.

Step 3 — Create a client

import { createSheetsClient } from '@mshindi-labs/gsheets';

const sheet = createSheetsClient(
  {
    clientId:     process.env.GOOGLE_CLIENT_ID!,
    clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
    redirectUri:  process.env.GOOGLE_REDIRECT_URI!,
    refreshToken: process.env.GOOGLE_REFRESH_TOKEN!,
  },
  'YOUR_SPREADSHEET_ID', // from the spreadsheet URL: /spreadsheets/d/<ID>/edit
);

createSheetsClient is a convenience factory. If you already manage your own OAuth2 client you can instantiate the class directly:

import { GoogleSpreadSheet } from '@mshindi-labs/gsheets';
import { google } from 'googleapis';

const auth = new google.auth.OAuth2(clientId, clientSecret, redirectUri);
auth.setCredentials({ refresh_token: refreshToken });

const sheet = new GoogleSpreadSheet({ auth, spreadsheetId: 'YOUR_ID' });

Response shape

Every method returns Promise<GoogleSheetResponse<T>>, a discriminated union:

type GoogleSheetResponse<T> =
  | { success: true;  data: T }
  | { success: false; problem: string };

Check result.success before accessing result.data. The problem string on failures includes both a human-readable context label and the underlying API error message.

Methods

Metadata & structure

getMetadata()

Returns the spreadsheet's top-level properties (title, locale, time zone) along with the properties of every sheet it contains.

const res = await sheet.getMetadata();
if (res.success) console.log(res.data.properties?.title);

getTabs()

Returns a lightweight summary of every tab: title, numeric sheetId, index, and grid dimensions. This is the cheapest way to discover available sheets.

const res = await sheet.getTabs();
if (res.success) {
  res.data.forEach(tab => console.log(tab.title, tab.sheetId));
}

getSheetByName(sheetTitle)

Finds a sheet by its tab title and returns the full Sheet resource.

const res = await sheet.getSheetByName('Inventory');
if (res.success) console.log(res.data.properties?.sheetId);

Reading data

getDataRange(props)

Reads a rectangular range and returns the full ValueRange resource.

| Prop | Type | Default | Description | |------|------|---------|-------------| | sheetTitle | string | — | Tab title (use this or sheetId) | | sheetId | number | — | Numeric sheet ID | | range | string | — | A1 notation, e.g. 'A1:D20' | | valueRenderOption | string | 'FORMATTED_VALUE' | How cell values are rendered |

const res = await sheet.getDataRange({ sheetTitle: 'Orders', range: 'A1:E100' });
if (res.success) console.log(res.data.values);

getLastRowWithData(sheetTitle, column?)

Returns the 1-based row number of the last row that contains data in the given column (defaults to column A).

const res = await sheet.getLastRowWithData('Sheet1', 'C');
if (res.success) console.log('Last row:', res.data); // e.g. 42

getRow({ sheetTitle, rowNumber, valueRenderOption? })

Reads all values from a single row and returns them as a flat string array. Row numbers are 1-based.

const res = await sheet.getRow({ sheetTitle: 'Sales', rowNumber: 5 });
if (res.success) console.log(res.data); // ['Alice', '2024-01-15', '1200']

getValuesBySheetTitle(sheetTitle, range, valueRenderOption?)

Reads a range and returns the raw 2-D values array.

const res = await sheet.getValuesBySheetTitle('Config', 'A1:B20');

getColumnValues(sheetTitle, column, valueRenderOption?)

Reads an entire column and returns a 2-D array where each element is [cellValue].

const res = await sheet.getColumnValues('Employees', 'B');
if (res.success) res.data.forEach(([name]: string[]) => console.log(name));

Writing data

appendData(props)

Appends rows to the end of the sheet's existing dataset.

| Prop | Type | Default | Description | |------|------|---------|-------------| | sheetTitle | string | — | Target tab | | data | string[][] | — | Rows to append | | valueInputOption | string | 'USER_ENTERED' | 'USER_ENTERED' parses formulas/dates; 'RAW' stores as-is | | insertDataOption | string | 'INSERT_ROWS' | 'INSERT_ROWS' shifts existing rows; 'OVERWRITE' fills blank cells |

await sheet.appendData({
  sheetTitle: 'Log',
  data: [['2024-06-01', 'Deploy', 'success']],
});

updateRange(props)

Overwrites a range with new values.

| Prop | Type | Default | Description | |------|------|---------|-------------| | sheetTitle | string | — | Target tab | | range | string | — | A1 notation start cell or range | | data | string[][] | — | Values to write | | valueInputOption | string | 'USER_ENTERED' | — |

await sheet.updateRange({
  sheetTitle: 'Summary',
  range: 'B2',
  data: [['Updated value']],
});

clearRange(sheetTitle, range)

Erases all values in a range while leaving formatting intact.

await sheet.clearRange('Temp', 'A1:Z1000');

Batch operations

Batch methods reduce quota consumption and round-trip latency by bundling multiple operations into a single API call.

batchGetRanges(props)

Fetches multiple ranges in one call.

const res = await sheet.batchGetRanges({
  sheetTitle: 'Dashboard',
  ranges: ['A1:A10', 'C1:C10', 'E1:E10'],
});
if (res.success) res.data.valueRanges?.forEach(vr => console.log(vr.values));

batchUpdateRanges(props)

Writes to multiple ranges in one call. Each entry has its own range and data matrix.

await sheet.batchUpdateRanges({
  sheetTitle: 'Config',
  entries: [
    { range: 'B2', data: [['v1.2.0']] },
    { range: 'B3', data: [['2024-06-01']] },
  ],
});

batchClearRanges(props)

Clears values from multiple ranges in one call. Formatting is preserved.

await sheet.batchClearRanges({
  sheetTitle: 'Cache',
  ranges: ['A2:A100', 'D2:D100'],
});

Rows & columns

All indices are 0-based and ranges are half-open [start, end) matching the Sheets API convention.

insertRows(sheetTitle, startIndex, endIndex)

Inserts blank rows, shifting existing rows down.

// Insert 3 blank rows before the current row 2
await sheet.insertRows('Sheet1', 1, 4);

insertColumns(sheetTitle, startIndex, endIndex)

Inserts blank columns, shifting existing columns right.

// Insert 1 blank column before column B
await sheet.insertColumns('Sheet1', 1, 2);

deleteRows(sheetTitle, startIndex, endIndex)

Permanently deletes rows. Irreversible.

// Delete rows 2–4 (0-based: start=1, end=4)
await sheet.deleteRows('Sheet1', 1, 4);

deleteColumns(sheetTitle, startIndex, endIndex)

Permanently deletes columns. Irreversible.

// Delete columns C and D (0-based: start=2, end=4)
await sheet.deleteColumns('Sheet1', 2, 4);

Sheet management

addSheet(props)

Creates a new empty sheet tab.

| Prop | Type | Default | Description | |------|------|---------|-------------| | title | string | — | Must be unique within the spreadsheet | | index | number | end | 0-based tab position | | rowCount | number | 1000 | Initial row count | | columnCount | number | 26 | Initial column count |

await sheet.addSheet({ title: 'Q3 Report', rowCount: 500 });

deleteSheet(sheetTitle)

Permanently deletes a sheet and all its data. Irreversible.

await sheet.deleteSheet('Old Data');

duplicateSheet(sheetId, newTitle?)

Creates a full copy of an existing sheet including data, formatting, and formulas.

const tabs = await sheet.getTabs();
const templateId = tabs.data?.find(t => t.title === 'Template')?.sheetId;
await sheet.duplicateSheet(templateId!, 'January Report');

renameSheet(currentTitle, newTitle)

Renames a sheet tab.

await sheet.renameSheet('Sheet1', 'Customers');

hideSheet(sheetTitle) / showSheet(sheetTitle)

Hides or shows a sheet tab. Data is preserved when hidden.

await sheet.hideSheet('Internal Notes');
await sheet.showSheet('Internal Notes');

moveSheet(sheetTitle, newIndex)

Moves a sheet to a new position in the tab bar (0 = leftmost).

await sheet.moveSheet('Summary', 0);

protectSheet(props)

Adds a protection rule to an entire sheet.

| Prop | Type | Default | Description | |------|------|---------|-------------| | sheetTitle | string | — | Sheet to protect | | description | string | — | Optional note explaining the protection | | warningOnly | boolean | false | Show a warning instead of blocking edits | | editorEmails | string[] | — | Emails allowed to edit despite protection |

await sheet.protectSheet({
  sheetTitle: 'Master Config',
  description: 'Do not edit without approval',
  editorEmails: ['[email protected]'],
});

Formatting & styling

All indices are 0-based and ranges are half-open [start, end).

setColumnWidth(props)

Sets the pixel width of one or more columns.

// Set columns A–C (indices 0–2) to 150 px
await sheet.setColumnWidth({
  sheetTitle: 'Sheet1',
  startColumnIndex: 0,
  endColumnIndex: 3,
  pixelSize: 150,
});

setRowHeight(props)

Sets the pixel height of one or more rows.

// Set the header row (index 0) to 40 px
await sheet.setRowHeight({
  sheetTitle: 'Sheet1',
  startRowIndex: 0,
  endRowIndex: 1,
  pixelSize: 40,
});

autoResizeColumns(props)

Fits column widths to their content automatically.

await sheet.autoResizeColumns({
  sheetTitle: 'Report',
  startColumnIndex: 0,
  endColumnIndex: 10,
});

freezeRowsAndColumns(props)

Freezes rows and/or columns so they stay visible while scrolling. Pass 0 to unfreeze.

// Freeze the first row and first column
await sheet.freezeRowsAndColumns({
  sheetTitle: 'Data',
  frozenRowCount: 1,
  frozenColumnCount: 1,
});

mergeCells(props)

Merges a rectangular block of cells.

| mergeType | Behaviour | |-------------|-----------| | 'MERGE_ALL' (default) | Merge entire range into one cell | | 'MERGE_COLUMNS' | Merge within each column independently | | 'MERGE_ROWS' | Merge within each row independently |

// Merge A1:C1 into a single header cell
await sheet.mergeCells({
  sheetTitle: 'Report',
  startRowIndex: 0, endRowIndex: 1,
  startColumnIndex: 0, endColumnIndex: 3,
});

setCellFormat(props)

Applies a CellFormat to every cell in a range. The fields mask controls which sub-properties are written — use a specific mask to avoid overwriting unrelated formatting.

// Bold the header row
await sheet.setCellFormat({
  sheetTitle: 'Report',
  range: 'A1:Z1',
  format: { textFormat: { bold: true } },
  fields: 'textFormat.bold',
});

// Set a red background on a range
await sheet.setCellFormat({
  sheetTitle: 'Alerts',
  range: 'B2:B50',
  format: {
    backgroundColor: { red: 1, green: 0, blue: 0 },
  },
  fields: 'backgroundColor',
});

Data utilities

getHeaderRow(sheetTitle)

Returns the values from row 1 — typically the column headers.

const res = await sheet.getHeaderRow('Customers');
if (res.success) console.log(res.data); // ['Name', 'Email', 'Plan']

updateCell(props)

Writes a single value into one cell identified by column letter and 1-based row number.

await sheet.updateCell({
  sheetTitle: 'Tasks',
  row: 5,
  column: 'C',
  value: 'DONE',
});

getDataAsObjects<T>(props)

Reads a range and maps each data row to a typed object keyed by the header row values. This is the most ergonomic way to consume tabular data.

| Prop | Type | Default | Description | |------|------|---------|-------------| | sheetTitle | string | — | Target tab | | range | string | 'A:Z' | A1 range to read | | headerRow | number | 1 | 1-based row number containing column headers |

type Order = { id: string; customer: string; amount: string };

const res = await sheet.getDataAsObjects<Order>({ sheetTitle: 'Orders' });
if (res.success) {
  res.data.forEach(o => console.log(o.customer, o.amount));
}

copyPasteRange(props)

Copies a range to a destination — optionally on a different sheet — using the Sheets API copyPaste request.

| pasteType | What is pasted | |-------------|----------------| | 'PASTE_NORMAL' (default) | Values, formulas, and formatting | | 'PASTE_VALUES' | Values only (no formulas or formatting) | | 'PASTE_FORMAT' | Formatting only | | 'PASTE_FORMULA' | Formulas only |

// Copy A1:D10 values-only from 'Live' to 'Archive'
await sheet.copyPasteRange({
  sheetTitle: 'Live',
  sourceRange: 'A1:D10',
  destinationRange: 'A1:D10',
  destinationSheetTitle: 'Archive',
  pasteType: 'PASTE_VALUES',
});

Filtering & sorting

sortRange(props)

Sorts rows within a range in-place. Multiple sort specs act as tie-breakers. columnIndex is 0-based relative to the start of the range.

// Sort by column B ascending, then column C descending
await sheet.sortRange({
  sheetTitle: 'Sales',
  range: 'A2:E500', // exclude header row
  sortSpecs: [
    { columnIndex: 1 },
    { columnIndex: 2, ascending: false },
  ],
});

setBasicFilter(props)

Attaches dropdown filter controls to a range. Only one basic filter can exist per sheet; clear it first if one already exists.

await sheet.setBasicFilter({ sheetTitle: 'Inventory', range: 'A1:G500' });

clearBasicFilter(sheetTitle)

Removes the basic filter and restores all hidden rows to visibility.

await sheet.clearBasicFilter('Inventory');

Named ranges

Named ranges provide stable, human-readable references that survive structural edits (inserted rows/columns automatically shift the range).

listNamedRanges()

Returns all named ranges defined in the spreadsheet.

const res = await sheet.listNamedRanges();
if (res.success) {
  res.data.forEach(nr => console.log(nr.name, nr.namedRangeId));
}

addNamedRange(props)

Creates a named range. The name must be unique and contain no spaces.

await sheet.addNamedRange({
  name: 'TAX_RATES',
  sheetTitle: 'Config',
  range: 'B2:B10',
});

deleteNamedRange(namedRangeId)

Deletes a named range by its API-assigned ID. The underlying cells are not affected. Use listNamedRanges() to find the ID.

const list = await sheet.listNamedRanges();
const id = list.data?.find(nr => nr.name === 'OLD_RANGE')?.namedRangeId;
if (id) await sheet.deleteNamedRange(id);

TypeScript types

All prop and response types are exported from the package root:

import type {
  // Core
  GoogleSheetResponse,
  GoogleSheetClient,
  GoogleSheetTab,
  GoogleSheet,
  GoogleSheetMetadata,
  // Read
  GetDataRangeProps,
  // Write
  AppendDataProps,
  UpdateRangeProps,
  // Batch
  BatchGetRangesProps,
  BatchUpdateRangesProps,
  BatchUpdateEntry,
  BatchClearRangesProps,
  // Sheet management
  AddSheetProps,
  ProtectSheetProps,
  ProtectedRange,
  // Formatting
  SetColumnWidthProps,
  SetRowHeightProps,
  AutoResizeColumnsProps,
  FreezeProps,
  MergeCellsProps,
  CellFormatProps,
  // Data utilities
  GetDataAsObjectsProps,
  UpdateCellProps,
  CopyPasteRangeProps,
  // Filter / sort
  SortRangeProps,
  SortSpec,
  SetBasicFilterProps,
  // Named ranges
  AddNamedRangeProps,
  NamedRange,
} from '@mshindi-labs/gsheets';

Requirements

| Requirement | Version | |-------------|---------| | Node.js | >= 18.0.0 | | TypeScript | >= 5.0 (peer, optional) | | googleapis | included as a dependency |

License

MIT — see LICENSE.