Sheetlog
Sheetlog is a a logging system that turns any Google Sheet into a database.
It's very helpful for logging console data, record form responses, or store LLM outputs for evaluation outputs.
It also supports authentication and full CRUD operations, turning your Google Sheet into a full-fledged database.
Since Sheets supports up to roughly 200,000 cells per sheet, this makes Sheetlog perfect for toy projects and MVPs that need a "faux", persisted "database". Think of it as a lighter alternative to Notion Database or Airtable, as opposed to something like Firebase or Supabase.
The Google Sheets API is prone to rate limiting and slow cold starts, so is not ideal for high-traffic or fast applications.
Sheetlog is built on top of the excellent SpreadAPI library, which is a Google Apps Script library that turns any Google Sheets into a data store.
Here's a live demo and usage guide
Installation
- Install package:
yarn add --dev @yawnxyz/sheetlog
- Create a Google Sheet
- Follow the installation instructions for SpreadAPI
- Replace the default script with the custom script (
sheetlog.js
) in this repo - Make sure to change the appropriate authentication for your app — the default is fully open.
- Deploy the app per installation instructions, and get the custom URL.
- Set that URL to .env.SHEET_URL to your deployed SpreadAPI Apps Script, or with
sheet.setup({sheetUrl: "some url"})
- Create a new Google Sheets tab named "Logs"
- Now you can log any object to your sheet, with
sheet.log({some: "data"})
to your code, and it'll log to the Logs
sheet!
Usage
Quick Start
To start logging data to your Google Sheet:
import sheet from '@yawnxyz/sheetlog';
const testSheet = new Sheetlog({
sheetUrl: "https://script.google.com/macros/s/AKfycbwVqIg9YzOSaa92I6z4Q9VBTSFeNUrT5GfmsWfNKqL9SU4ZQeZ2Rl1FfdE3cjrNqllV/exec",
sheet: "testSheet"
});
let result = await testSheet.log({
Name: "test"
})
This adds the data to the Name
column of the testSheet
.
Basic Methods
.log(payload, options)
The .log
function is the core method used to interact with the sheet. It accepts:
payload
: The data to be logged (object or array)
- Can be a single object or array of objects with any schema
- Nested objects/arrays will be JSON stringified into a single cell, not spread across columns
- Example:
{ name: "John", details: { age: 30, city: "NY" } }
→ "details" column will contain the stringified object
options
: Configuration object including:
sheet
: Sheet name (defaults to "Logs")sheetUrl
: Optional custom sheet URLmethod
: Request method (defaults to "POST")id
: Row ID for operations like GET/PUTidColumn
: Column name for UPSERT operations- Additional parameters specific to each method
Example:
const payload = { name: 'John', age: 30 };
await sheet.log(payload, {
sheet: 'Users'
});
.get(id, options)
Retrieves data from the sheet. Can fetch single rows or multiple with pagination.
Example of single row:
const row = await sheet.get(123);
Example of multiple rows:
const rows = await sheet.get(null, {
method: "GET",
limit: 10,
start_id: 100,
order: "desc"
});
.post(payload, options)
Creates new rows in the sheet. Automatically adds timestamp in "Date Modified" column.
const result = await sheet.post({
name: 'Jane',
email: 'jane@example.com'
}, {
sheet: 'Users'
});
.upsert(idColumn, id, payload, options)
Updates existing row or creates new one if not found. Uses specified column as unique identifier.
const result = await sheet.upsert(
'email',
'jane@example.com',
{ status: 'active' },
{ sheet: 'Users' }
);
.find(idColumn, id, returnAllMatches, options)
Searches for rows matching the specified criteria. Returns single object or array based on returnAllMatches.
const user = await sheet.find(
'email',
'jane@example.com',
false
);
const users = await sheet.find(
'status',
'active',
true
);
.dynamicPost(payload, options)
Adds new rows and automatically creates new columns for any new fields in the payload.
const result = await sheet.dynamicPost({
name: 'Alice',
newField: 'value',
nested: { foo: 'bar' }
}, {
sheet: 'Users'
});
.paginatedGet(options)
Provides cursor-based pagination for large datasets.
const results = await sheet.paginatedGet({
cursor: "100",
limit: 20,
sortBy: "Date Modified",
sortDir: "desc",
sheet: "Users"
});
.rangeUpdate(data, options)
Efficiently updates multiple cells in a range.
const result = await sheet.rangeUpdate([
["A1", "B1", "C1"],
["A2", "B2", "C2"]
], {
sheet: "Users",
startRow: 2,
startCol: 3
});
.aggregate(column, operation, options)
Performs calculations on numeric columns.
const result = await sheet.aggregate(
"amount",
"sum",
{
sheet: "Sales",
where: { status: "completed" }
}
);
.getSheets()
Returns information about all sheets in the spreadsheet.
const sheets = await sheet.getSheets();
.getCSV(options)
Exports a specific sheet as CSV format.
const csvData = await sheet.getCSV({
sheet: "Users"
});
.getColumns(options)
Retrieves specific columns from the sheet. Can fetch single or multiple columns, including formulas and formatting.
Parameters:
sheet
: The name of the sheetstartColumn
: The starting column identifier (e.g., "A", "G", or 1)endColumn
: (Optional) The ending column identifierincludeFormulas
: (Optional) Get the actual formulas in cells instead of computed valuesincludeFormatting
: (Optional) Include cell formatting information
const columnsWithFormulas = await sheet.getColumns({
sheet: "Users",
startColumn: "B",
endColumn: "D",
includeFormulas: true
});
const columnsWithFormatting = await sheet.getColumns({
sheet: "Users",
startColumn: "B",
endColumn: "D",
includeFormulas: true,
includeFormatting: true
});
.getAllCells(options)
Retrieves all cells and data from a specified sheet, with comprehensive formatting information.
Parameters:
sheet
: The name of the sheetincludeFormulas
: (Optional, defaults to true) Include cell formulasincludeFormatting
: (Optional, defaults to true) Include full cell formatting
const allData = await sheet.getAllCells({
sheet: "testSheet",
includeFormulas: true,
includeFormatting: true
});
{
values: [...],
formulas: [...],
backgrounds: [...],
fontColors: [...],
numberFormats: [...],
fontFamilies: [...],
fontSizes: [...],
fontStyles: [...],
horizontalAlignments: [...],
verticalAlignments: [...],
wraps: [...],
lastColumn: 10,
lastRow: 100
}
.getRows(options)
Retrieves specific rows from the sheet. Can fetch single or multiple rows.
const rows = await sheet.getRows({
sheet: "Users",
startRow: 2,
endRow: 10
});
.getRange(payload, options)
Retrieves data from a specified range with flexible empty cell handling.
const result = await sheet.getRange({}, {
sheet: "Sheet1",
startRow: 2,
startCol: 3
});
const result = await sheet.getRange({}, {
sheet: "Sheet1",
startRow: 2,
startCol: 3,
stopAtEmptyRow: true,
stopAtEmptyColumn: true
});
const result = await sheet.getRange({}, {
sheet: "Sheet1",
startRow: 2,
startCol: 3,
skipEmptyRows: true,
skipEmptyColumns: true
});
.getDataBlock(payload, options)
Automatically finds and retrieves a contiguous block of data within a search range. Useful for finding data that's not aligned to A1.
const result = await sheet.getDataBlock({}, {
sheet: "Sheet1",
searchRange: {
startRow: 1,
startCol: 1,
endRow: 99,
endCol: 26
}
});
Column Management
sheet.log(null, {
method: "ADD_COLUMN",
columnName: "newColumn"
});
sheet.log(null, {
method: "EDIT_COLUMN",
oldColumnName: "oldName",
newColumnName: "newName"
});
sheet.log(null, {
method: "REMOVE_COLUMN",
columnName: "columnToRemove"
});
Features
- Simple logging interface with
sheet.log()
- Automatic timestamp tracking with "Date Modified" column
- Dynamic column creation
- JSON object serialization for nested data
- Multiple authentication methods
- Pagination support
- Bulk operations
- Aggregation functions
- Data export options
- Custom sheet management
Authentication
Sheetlog supports flexible authentication patterns:
const logger = new Sheetlog({
users: [
{
name: "admin",
key: "myStr0ng!Pass",
permissions: "*"
},
{
name: "poweruser",
key: "P0wer!User",
permissions: {
logs: ["GET", "POST"],
analytics: "GET",
config: ["PUT", "DELETE"]
}
},
{
name: "viewer",
key: "V1ewer!Pass",
permissions: {
public: "GET",
reports: "GET"
}
}
]
});
Authentication Requirements
Keys must be:
- 8+ characters long
- Include lowercase, uppercase, number, and special character
- Can be marked as
UNSAFE()
to bypass security requirements (development only)
Available Methods
To run these folllowing methods, set the method
key in the payload for the desired method when using sheet.log()
.
For example, to run GET_ROWS
:
sheet.log(null, {
method: "GET_ROWS",
sheet: "testSheet",
startRow: 1,
endRow: 9099
});
Method | Description |
---|
GET | Fetch single or multiple rows |
POST | Create new rows |
PUT | Update existing rows |
DELETE | Remove rows |
UPSERT | Create or update based on ID |
DYNAMIC_POST | Create rows with dynamic columns |
ADD_COLUMN | Add new columns |
EDIT_COLUMN | Rename columns |
REMOVE_COLUMN | Delete columns |
FIND | Search for specific values |
BULK_DELETE | Remove multiple rows |
PAGINATED_GET | Get rows with pagination |
EXPORT | Export data in different formats |
AGGREGATE | Perform calculations on columns |
BATCH_UPDATE | Update multiple rows efficiently |
GET_SHEETS | Get information about all sheets in the spreadsheet |
GET_CSV | Export a specific sheet as CSV |
Retrieve Specific Rows
You can retrieve specific rows from a Google Sheet using the GET_ROWS
method. This method allows you to specify a range of rows to fetch.
Parameters:
sheet
: The name of the sheet.startRow
: The starting row number (1-indexed).endRow
: (Optional) The ending row number. If not provided, only the startRow
will be retrieved.
Example:
{
method: "GET_ROWS",
sheet: "logs",
startRow: 2,
endRow: 5
}
Retrieve Specific Columns
You can retrieve specific columns from a Google Sheet using the GET_COLUMNS
method. This method allows you to specify a range of columns to fetch by their letter or number.
Parameters:
sheet
: The name of the sheet.startColumn
: The starting column identifier (e.g., "A", "G", or 1).endColumn
: (Optional) The ending column identifier. If not provided, only the startColumn
will be retrieved.
Example:
{
method: "GET_COLUMNS",
sheet: "logs",
startColumn: "A",
endColumn: "C"
}
Method Index & Examples
{
"method": "GET",
"sheet": "testSheet"
}
{
"method": "GET",
"sheet": "testSheet",
"id": 2
}
{
"method": "GET",
"sheet": "links",
"start_id": 2,
"limit": 5
}
{
"method": "POST",
"sheet": "users",
"payload": {
"name": "John Doe",
"email": "john@example.com"
}
}
{
"method": "UPSERT",
"sheet": "users",
"idColumn": "userId",
"id": 123,
"payload": {
"name": "Jane Doe",
"email": "jane@example.com"
}
}
{
"method": "DYNAMIC_POST",
"sheet": "data",
"payload": {
"newField": "newValue",
"anotherField": "anotherValue"
}
}
{
"method": "PUT",
"sheet": "users",
"id": 123,
"payload": {
"email": "newemail@example.com"
}
}
{
"method": "DELETE",
"sheet": "users",
"id": 123
}
{
"method": "ADD_COLUMN",
"sheet": "users",
"columnName": "newColumn"
}
{
"method": "EDIT_COLUMN",
"sheet": "users",
"oldColumnName": "oldName",
"newColumnName": "newName"
}
{
"method": "REMOVE_COLUMN",
"sheet": "users",
"columnName": "columnToRemove"
}
{
"method": "FIND",
"sheet": "users",
"idColumn": "email",
"id": "john@example.com",
"returnAllMatches": true
}
{
"method": "BULK_DELETE",
"sheet": "users",
"ids": [1, 2, 3, 4]
}
{
"method": "PAGINATED_GET",
"sheet": "logs",
"limit": 20,
"cursor": "100",
"sortBy": "timestamp",
"sortDir": "desc"
}
{
"method": "EXPORT",
"sheet": "logs",
"format": "csv"
}
{
"method": "AGGREGATE",
"sheet": "sales",
"column": "amount",
"operation": "sum"
}
{
"method": "BATCH_UPDATE",
"sheet": "logs",
"payload": [
{ "_id": 1, "status": "complete" },
{ "_id": 2, "status": "pending" }
]
}
{
"method": "GET_ROWS",
"sheet": "testSheet",
"startRow": 2,
"endRow": 10
}
{
"method": "GET_COLUMNS",
"sheet": "testSheet",
"startColumn": "B",
"endColumn": "D"
}
{
"method": "GET_COLUMNS",
"sheet": "testSheet",
"startColumn": "G"
}
{
"method": "GET_ALL_CELLS",
"sheet": "testSheet"
}
{
"method": "RANGE_UPDATE",
"sheet": "dynamicSheet",
"startRow": 2,
"startCol": 3,
"data": [
["A1", "B1", "C1"],
["A2", "B2", "C2"],
["A3", "B3", "C3"]
]
}
{
"method": "GET_SHEETS"
}
{
"method": "GET_CSV",
"sheet": "testSheet"
}
{
"method": "GET_RANGE",
"sheet": "Sheet1",
"startRow": 2,
"startCol": 3,
"stopAtEmptyRow": true,
"stopAtEmptyColumn": true,
"skipEmptyRows": true,
"skipEmptyColumns": true
}
{
"method": "GET_DATA_BLOCK",
"sheet": "Sheet1",
"searchRange": {
"startRow": 1,
"startCol": 1,
"endRow": 99,
"endCol": 26
}
}
{
"method": "GET_COLUMNS",
"sheet": "testSheet",
"startColumn": "B",
"endColumn": "D",
"includeFormulas": true
}
{
"method": "GET_COLUMNS",
"sheet": "testSheet",
"startColumn": "B",
"endColumn": "D",
"includeFormulas": true,
"includeFormatting": true
}
{
"method": "GET_ALL_CELLS",
"sheet": "testSheet",
"includeFormulas": true,
"includeFormatting": true
}
{
"method": "GET_ALL_CELLS",
"sheet": "testSheet",
"includeFormulas": false,
"includeFormatting": false
}
### Authentication & Security
#### Strong Password Requirements
Keys (passwords) must meet the following criteria:
- Minimum 8 characters
- Include at least one lowercase letter
- Include at least one uppercase letter
- Include at least one number
- Include at least one special character
- Can be marked as `UNSAFE()` to bypass security requirements (development only)
```javascript
const logger = new Sheetlog({
users: [
// Strong password example
{
name: "admin",
key: "myStr0ng!Pass", // Meets all requirements
permissions: "*"
},
// Unsafe password example (development only)
{
name: "dev",
key: { __unsafe: "simple" }, // Bypasses requirements
permissions: "*"
}
]
});
Data Aggregation
.aggregate(column, operation, options)
Performs calculations on numeric columns.
Operations:
sum
: Total of all valuesavg
: Average of valuesmin
: Minimum valuemax
: Maximum valuecount
: Count of numeric values
const result = await sheet.aggregate(
"amount",
"sum",
{
sheet: "Sales",
where: { status: "completed" }
}
);
Batch Operations
.batchUpdate(updates, options)
Efficiently updates multiple rows in a single operation.
const result = await sheet.batchUpdate([
{ _id: 1, status: "complete", amount: 100 },
{ _id: 2, status: "pending", amount: 200 }
], {
sheet: "Orders"
});
Column Management
.addColumn(columnName, options)
Adds a new column to the sheet.
const result = await sheet.addColumn("newColumn", {
sheet: "Users"
});
.editColumn(oldName, newName, options)
Renames an existing column.
const result = await sheet.editColumn(
"oldName",
"newName",
{ sheet: "Users" }
);
.removeColumn(columnName, options)
Removes a column from the sheet.
const result = await sheet.removeColumn("columnToRemove", {
sheet: "Users"
});
Bulk Operations
.bulkDelete(ids, options)
Removes multiple rows in a single operation.
const result = await sheet.bulkDelete([1, 2, 3, 4], {
sheet: "Users"
});