Sheethuahua

Type-safe Google Sheets and CSV parser for TypeScript and JavaScript

Using TypeBox, d3-dsv and Web Fetch API under the hood, Sheethuahua should be supported by every modern browsers and back-end runtime.

Table of contents

• Quick Start
• Concepts
• Define Table and Column
• Using with Public Google Sheets
• Using with a CSV File
• Options

Quick Start

Install the package

npm i sheethuahua

Using with a public Google Sheets

import { Column, Spreadsheet, Table, type RowType } from 'sheethuahua';

// Define named table(s) schema
const userTable = Table('users', {
	name: Column.String(),
	age: Column.Number(),
	role: Column.OneOf(['Admin', 'Guest']),
});

// Define a Spreadsheet
const sheets = Spreadsheet('<sheetsId>', [userTable]);

// Get type-safe data from the table
const users = await sheets.get('users');

// Infer row type from the table schema
type User = RowType<typeof userTable>;

Using with URL or string of a CSV file

import {
	Column,
	parseCSVFromUrl,
	parseCSVFromString,
	Table,
	type RowType,
} from 'sheethuahua';

// Define anonymous table schema
const userTable = Table({
	name: Column.String(),
	age: Column.Number(),
	role: Column.OneOf(['Admin', 'Guest']),
});

// Get type-safe data from the URL
const usersFromUrl = await parseCSVFromUrl('some-url-to/data.csv', userTable);
// Or from string
const usersFromString = await parseCSVFromString('name,age\na,27', userTable);

// Can also infer row type from the table schema
type User = RowType<typeof userTable>;

Concepts

Sheethuahua was designed to make an unknown Spreadsheet or CSV data structure become known with 2 steps:

1. Define what kind of data structure we expected (the schema).
2. Try to parse it as an array of objects, and raise an error if it is not what we expected.

The basic data structure of both Spreadsheet and CSV is the Table which contains one or more Columns (Google Sheets's "Sheets" and "Sheet" are referred to as Sheethuahua's Spreadsheet and Table). Table can be either named or anonymous. A CSV represents exactly one AnonymousTable, while a Spreadsheet can contain one or more NamedTable.

An empty Table would have one header row defining each Column's name, and then each body row after that would represent one record of data.

Define Table and Column

We can define a table using Table and Column.

const userTable = Table('users', {
	name: Column.String(),
	age: Column.Number(),
	role: Column.OneOf(['Admin', 'Guest']),
});

From the example, we expect a table name "users" to have "name", "age", and "role" columns with coresponded type. Table name can be omitted to create AnonymousTable (Otherwise, NamedTable is created).

Every cell in a spreadsheet and CSV is a string by default. Sheethuahua will try to parse it into the expected Column type as defined in the Table. The following Column types are supported:

• Required column type: can't be empty.
  • String() expects anything except an empty string.
  • Number() expects any number including minus and decimal.
  • Boolean() expects case-insensitive true/false, or 0/1 (For Google Sheets, recommend using Checkbox).
  • Date() expect JavaScript's date time string format eg. YYYY-MM-DD or YYYY-MM-DDTHH:mm:ss.sssZ.
  • OneOf(values: TLiteralValue[]) expects one of the literal values (string, number, or boolean) in the given array. (For Google Sheets, recommend using Dropdown).
• Optional column type: can be empty, parsed as null.
  • Same as a required column with an Optional prefix eg. OptionalString().

TypeBox's schema options can be passed to the String, Number, Boolean, Date and their optional variations.

The body row type can be inferred from the Table schema using RowType.

// type User = {
//     name: string;
//     age: number;
//     role: "Admin" | "Guest";
// }
type User = RowType<typeof userTable>;

Using with Public Google Sheets

Important: Google Sheets has a very low rate limit for requesting data. It should be used with Static Site Generation (SSG), cache, or both.

A Spreadsheet can be defined with sheetsId (Can be found from the Sheets URL: docs.google.com/spreadsheets/d/{sheetsId}/) and one or more child NamedTable.

Note: The table's name must match the Google Sheets Sheet's name.

const userTable = Table('users', {
  name: Column.String(),
  age: Column.Number(),
  role: Column.OneOf(['Admin', 'Guest']),
});
const groupTable = Table('groups' {
  // ...
});

const sheets = Spreadsheet('<sheetsId>', [userTable, groupTable]);

Spreadsheet's .get() is used to fetch and parse the data as an array of objects of defined Column type. An error will be thrown if the data can not be parsed as expected type.

// const users: {
//     name: string;
//     age: number;
//     role: "Admin" | "Guest";
// }[]
const users = await sheets.get('users');
// const groups: {
//     ...
// }[]
const groups = await sheets.get('groups');

SheetOptions can be supplied to the Spreadsheet() as spreadsheet-wide options, or .get() for just once.

See more in Options

const sheets = Spreadsheet('<sheetsId>', [userTable, groupTable], {
	// SheetOptions
});

const users = await sheets.get('users', {
	// SheetOptions
});

Using with a CSV File

Sheethuahua also supports any CSV file from either a URL or string by supplying AnonymousTable into the parseCSVFromUrl() or parseCSVFromString(). Returned data will have an array of objects of defined Column type. An error will be thrown if the data can not be parsed as expected type.

const userTable = Table({
	name: Column.String(),
	age: Column.Number(),
	role: Column.OneOf(['Admin', 'Guest']),
});

// const users: {
//     name: string;
//     age: number;
//     role: "Admin" | "Guest";
// }[]
const usersFromUrl = await parseCSVFromUrl('some-url-to/data.csv', userTable);
const usersFromString = await parseCSVFromString('name,age\na,27', userTable);

The CSVFetcherOptions can be supplied to the parseCSVFromUrl() and CSVParserOptions can be supplied to the parseCSVFromString().

See more in Options

const usersFromUrl = await parseCSVFromUrl('some-url-to/data.csv', {
	// CSVFetcherOptions
});

const usersFromString = await parseCSVFromString('name,age\na,27', {
	// CSVParserOptions
});

Options

All options are optional but availability varies between each type.

| Name | SheetOptions | CSVFetcherOptions | CSVParserOptions | | --------------------- | -------------- | ------------------- | ------------------ | | range | ✅ | ❌ | ❌ | | headers | ✅ | ❌ | ❌ | | fetchRequestInit | ✅ | ✅ | ❌ | | trim | ✅ | ✅ | ✅ | | includeUnknownColumns | ✅ | ✅ | ✅ |

• range?: string - Which part of the sheet to use eg. "A1:B10" (see more)
• headers?: number - How many rows are header rows. If not specified, Google Sheets will guess from the header and body type. (see more)
• fetchRequestInit?: FetchRequestInit - Options for fetch() request (see more)
• trim?: boolean (default: true) - Trim whitespaces of each cell before parsing.
• includeUnknownColumns?: boolean (default: false) - Include columns that are not defined in the table.