New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More
Socket
Sign inDemoInstall
Socket

@yawnxyz/sheetlog

Package Overview
Dependencies
Maintainers
0
Versions
29
Alerts
File Explorer

Advanced tools

Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@yawnxyz/sheetlog

a google sheets logging system

  • 0.1.11
  • npm
  • Socket score

Version published
Weekly downloads
47
decreased by-60.5%
Maintainers
0
Weekly downloads
 
Created
Source

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

  1. Install package: yarn add --dev @yawnxyz/sheetlog
  2. Create a Google Sheet
  3. Follow the installation instructions for SpreadAPI
  4. Replace the default script with the custom script (sheetlog.js) in this repo
  5. Make sure to change the appropriate authentication for your app — the default is fully open.
  6. Deploy the app per installation instructions, and get the custom URL.
  7. Set that URL to .env.SHEET_URL to your deployed SpreadAPI Apps Script, or with sheet.setup({sheetUrl: "some url"})
  8. Create a new Google Sheets tab named "Logs"
  9. 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';

// initialize the sheet
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 URL
    • method: Request method (defaults to "POST")
    • id: Row ID for operations like GET/PUT
    • idColumn: 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:

// Get row with ID 123
const row = await sheet.get(123);

Example of multiple rows:

// Get multiple rows with options
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',                    // idColumn
  'jane@example.com',        // id
  { status: 'active' },      // payload
  { sheet: 'Users' }         // options
);
.find(idColumn, id, returnAllMatches, options)

Searches for rows matching the specified criteria. Returns single object or array based on returnAllMatches.

// Find single match
const user = await sheet.find(
  'email',                    // idColumn
  'jane@example.com',        // id
  false                      // returnAllMatches
);

// Find all matches
const users = await sheet.find(
  'status',                  // idColumn
  'active',                  // id
  true                      // returnAllMatches
);
.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',      // Creates column if doesn't exist
  nested: { foo: 'bar' }  // Will be JSON stringified
}, {
  sheet: 'Users'
});
.paginatedGet(options)

Provides cursor-based pagination for large datasets.

const results = await sheet.paginatedGet({
  cursor: "100",           // Starting point
  limit: 20,              // Items per page
  sortBy: "Date Modified", // Sort column
  sortDir: "desc",        // Sort direction
  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",           // column
  "sum",             // operation: sum, avg, min, max, count
  {
    sheet: "Sales",
    where: { status: "completed" }
  }
);
.getSheets()

Returns information about all sheets in the spreadsheet.

const sheets = await sheet.getSheets();
// Returns array of sheet names and metadata
.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 sheet
  • startColumn: The starting column identifier (e.g., "A", "G", or 1)
  • endColumn: (Optional) The ending column identifier
  • includeFormulas: (Optional) Get the actual formulas in cells instead of computed values
  • includeFormatting: (Optional) Include cell formatting information
// Get columns with formulas
const columnsWithFormulas = await sheet.getColumns({
  sheet: "Users",
  startColumn: "B",
  endColumn: "D",
  includeFormulas: true
});

// Get columns with full formatting
const columnsWithFormatting = await sheet.getColumns({
  sheet: "Users",
  startColumn: "B",
  endColumn: "D",
  includeFormulas: true,
  includeFormatting: true  // Includes backgrounds, fonts, alignments, etc.
});
.getAllCells(options)

Retrieves all cells and data from a specified sheet, with comprehensive formatting information.

Parameters:

  • sheet: The name of the sheet
  • includeFormulas: (Optional, defaults to true) Include cell formulas
  • includeFormatting: (Optional, defaults to true) Include full cell formatting
// Get all cells with complete information
const allData = await sheet.getAllCells({
  sheet: "testSheet",
  includeFormulas: true,
  includeFormatting: true
});

// Response includes:
{
  values: [...],           // Cell values
  formulas: [...],        // Original formulas
  backgrounds: [...],     // Cell background colors
  fontColors: [...],      // Text colors
  numberFormats: [...],   // Number formatting
  fontFamilies: [...],    // Font families
  fontSizes: [...],       // Font sizes
  fontStyles: [...],      // Font styles (bold, italic, etc.)
  horizontalAlignments: [...],  // Text alignment
  verticalAlignments: [...],    // Vertical alignment
  wraps: [...],           // Text wrapping
  lastColumn: 10,         // Last used column
  lastRow: 100           // Last used row
}
.getRows(options)

Retrieves specific rows from the sheet. Can fetch single or multiple rows.

const rows = await sheet.getRows({
  sheet: "Users",
  startRow: 2,    // Row 1 is typically headers
  endRow: 10
});
.getRange(payload, options)

Retrieves data from a specified range with flexible empty cell handling.

// Basic range retrieval
const result = await sheet.getRange({}, {
  sheet: "Sheet1",
  startRow: 2,
  startCol: 3
});

// Stop at first empty row or column
const result = await sheet.getRange({}, {
  sheet: "Sheet1",
  startRow: 2,
  startCol: 3,
  stopAtEmptyRow: true,    // Stop when encountering an empty row
  stopAtEmptyColumn: true  // Stop when encountering an empty column
});

// Skip empty rows and columns
const result = await sheet.getRange({}, {
  sheet: "Sheet1",
  startRow: 2,
  startCol: 3,
  skipEmptyRows: true,     // Skip any empty rows in the range
  skipEmptyColumns: true   // Skip any empty columns in the range
});
.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.

// Find data block anywhere in first 99 rows and 26 columns (A-Z)
const result = await sheet.getDataBlock({}, {
  sheet: "Sheet1",
  searchRange: {
    startRow: 1,
    startCol: 1,
    endRow: 99,
    endCol: 26
  }
});

Column Management

// Add new column
sheet.log(null, {
  method: "ADD_COLUMN",
  columnName: "newColumn"
});

// Rename column
sheet.log(null, {
  method: "EDIT_COLUMN",
  oldColumnName: "oldName",
  newColumnName: "newName"
});

// Remove column
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: [
    // Admin with full access
    {
      name: "admin",
      key: "myStr0ng!Pass",  // Strong password required
      permissions: "*"       // ALL access
    },

    // Power user with mixed permissions
    {
      name: "poweruser",
      key: "P0wer!User",
      permissions: {
        logs: ["GET", "POST"],     // Multiple methods for one sheet
        analytics: "GET",          // Single method for one sheet
        config: ["PUT", "DELETE"]  // Multiple methods for another sheet
      }
    },

    // Read-only user
    {
      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
});
MethodDescription
GETFetch single or multiple rows
POSTCreate new rows
PUTUpdate existing rows
DELETERemove rows
UPSERTCreate or update based on ID
DYNAMIC_POSTCreate rows with dynamic columns
ADD_COLUMNAdd new columns
EDIT_COLUMNRename columns
REMOVE_COLUMNDelete columns
FINDSearch for specific values
BULK_DELETERemove multiple rows
PAGINATED_GETGet rows with pagination
EXPORTExport data in different formats
AGGREGATEPerform calculations on columns
BATCH_UPDATEUpdate multiple rows efficiently
GET_SHEETSGet information about all sheets in the spreadsheet
GET_CSVExport 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

// Fetch all
{
  "method": "GET",
  "sheet": "testSheet"
}

// Fetch the first row of data (row 1 is the header row since Sheets is 1-indexed)
{
  "method": "GET",
  "sheet": "testSheet",
  "id": 2
}

// Fetch multiple rows with pagination
{
  "method": "GET",
  "sheet": "links",
  "start_id": 2,
  "limit": 5
}

// Create a new row
{
  "method": "POST",
  "sheet": "users",
  "payload": {
    "name": "John Doe",
    "email": "john@example.com"
  }
}

// Create or update a row based on ID
{
  "method": "UPSERT",
  "sheet": "users",
  "idColumn": "userId",
  "id": 123,
  "payload": {
    "name": "Jane Doe",
    "email": "jane@example.com"
  }
}

// Create rows with dynamic columns
{
  "method": "DYNAMIC_POST",
  "sheet": "data",
  "payload": {
    "newField": "newValue",
    "anotherField": "anotherValue"
  }
}

// Update specific fields in a row
{
  "method": "PUT",
  "sheet": "users",
  "id": 123,
  "payload": {
    "email": "newemail@example.com"
  }
}

// Delete a specific row
{
  "method": "DELETE",
  "sheet": "users",
  "id": 123
}

// Add a new column to the sheet
{
  "method": "ADD_COLUMN",
  "sheet": "users",
  "columnName": "newColumn"
}

// Rename an existing column
{
  "method": "EDIT_COLUMN",
  "sheet": "users",
  "oldColumnName": "oldName",
  "newColumnName": "newName"
}

// Remove a column from the sheet
{
  "method": "REMOVE_COLUMN",
  "sheet": "users",
  "columnName": "columnToRemove"
}

// Find rows by a specific column value
{
  "method": "FIND",
  "sheet": "users",
  "idColumn": "email",
  "id": "john@example.com",
  "returnAllMatches": true
}

// Bulk delete multiple rows
{
  "method": "BULK_DELETE",
  "sheet": "users",
  "ids": [1, 2, 3, 4]
}

// Get rows with pagination
{
  "method": "PAGINATED_GET",
  "sheet": "logs",
  "limit": 20,
  "cursor": "100",
  "sortBy": "timestamp",
  "sortDir": "desc"
}

// Export data in a specific format
{
  "method": "EXPORT",
  "sheet": "logs",
  "format": "csv"
}

// Perform aggregation on a column
{
  "method": "AGGREGATE",
  "sheet": "sales",
  "column": "amount",
  "operation": "sum"
}

// Batch update multiple rows
{
  "method": "BATCH_UPDATE",
  "sheet": "logs",
  "payload": [
    { "_id": 1, "status": "complete" },
    { "_id": 2, "status": "pending" }
  ]
}

// Fetch specific rows
{
  "method": "GET_ROWS",
  "sheet": "testSheet",
  "startRow": 2,
  "endRow": 10
}

// Fetch specific columns
{
  "method": "GET_COLUMNS",
  "sheet": "testSheet",
  "startColumn": "B",
  "endColumn": "D"
}

// Fetch a single column
{
  "method": "GET_COLUMNS",
  "sheet": "testSheet",
  "startColumn": "G"
}

// Fetch all cells and data in the sheet
{
  "method": "GET_ALL_CELLS",
  "sheet": "testSheet"
}

// Update a range of cells
{
  "method": "RANGE_UPDATE",
  "sheet": "dynamicSheet", 
  "startRow": 2,
  "startCol": 3,
  "data": [
    ["A1", "B1", "C1"],
    ["A2", "B2", "C2"],
    ["A3", "B3", "C3"]
  ]
}

// Get information about all sheets
{
  "method": "GET_SHEETS"
}

// Get CSV export of a specific sheet
{
  "method": "GET_CSV",
  "sheet": "testSheet"
}

// Get range with empty handling
{
  "method": "GET_RANGE",
  "sheet": "Sheet1",
  "startRow": 2,
  "startCol": 3,
  "stopAtEmptyRow": true,
  "stopAtEmptyColumn": true,
  "skipEmptyRows": true,
  "skipEmptyColumns": true
}

// Find data block
{
  "method": "GET_DATA_BLOCK",
  "sheet": "Sheet1",
  "searchRange": {
    "startRow": 1,
    "startCol": 1,
    "endRow": 99,
    "endCol": 26
  }
}

// Get columns with formulas
{
  "method": "GET_COLUMNS",
  "sheet": "testSheet",
  "startColumn": "B",
  "endColumn": "D",
  "includeFormulas": true
}

// Get columns with full formatting
{
  "method": "GET_COLUMNS",
  "sheet": "testSheet",
  "startColumn": "B",
  "endColumn": "D",
  "includeFormulas": true,
  "includeFormatting": true
}

// Get all cells with complete information
{
  "method": "GET_ALL_CELLS",
  "sheet": "testSheet",
  "includeFormulas": true,
  "includeFormatting": true
}

// Get all cells (values only)
{
  "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 values
  • avg: Average of values
  • min: Minimum value
  • max: Maximum value
  • count: Count of numeric values
const result = await sheet.aggregate(
  "amount",           // column
  "sum",             // operation: sum, avg, min, max, count
  {
    sheet: "Sales",
    where: { status: "completed" }  // Optional filter
  }
);

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"
});

Keywords

FAQs

Package last updated on 16 Jan 2025

Did you know?

Socket

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

Packages

npm

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc