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
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>;
Sheethuahua was designed to make an unknown Spreadsheet or CSV data structure become known with 2 steps:
- Define what kind of data structure we expected (the schema).
- 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 Column
s (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.
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()
.
- Same as a required column with an
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>;
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
});
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
});
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.