@yawnxyz/sheetlog - npm Package Compare versions

Comparing version 0.0.17 to 0.1.0

package.json

 {
   "name": "@yawnxyz/sheetlog",
-  "version": "0.0.17",
+  "version": "0.1.00",
   "description": "simple console.log to google sheets",
@@ -5,0 +5,0 @@ "main": "index.mjs",

readme.md

@@ -1,2 +0,1 @@
-
 # Sheet.log
@@ -12,18 +11,14 @@
 
-
-
-
 ## Installation
 
 1. Install package: `yarn add --dev @yawnxyz/sheetlog`
-1. Create a Google Sheet for logging
-1. Follow the [installation instructions for SpreadAPI](https://spreadapi.roombelt.com)
-1. Replace the default script with the custom script (spreadapi-custom.js) in this repo
-1. Make sure to change the appropriate authentication for your app!!
-1. Deploy the app per installation instructions, and get the custom URL.
-1. Set that URL to .env.SHEET_URL to your deployed SpreadAPI Apps Script, or with `sheet.setup({sheetUrl: "some url"})`
-1. Create a new Google Sheets tab named "Logs"
-1. 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!
+2. Create a Google Sheet
+3. Follow the [installation instructions for SpreadAPI](https://spreadapi.roombelt.com/setup)
+4. Replace the default script with the custom script (spreadapi-custom.js) in this repo
+5. Make sure to change the appropriate authentication for your app!!
+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
@@ -35,5 +30,5 @@
 
-```
+```javascript
 import sheet from '@yawnxyz/sheetlog';
-sheet.log({Name: "First Name"})
+sheet.log({Name: "First Name"});
 ```
@@ -43,12 +38,11 @@
 
+### Basic Methods
 
-### Logging Data
-
 #### .log(payload, options)
-The .log function is used to log data to the specified sheet. It accepts the following parameters:
-- payload: The data to be logged.
-- options: An object containing additional options such as sheet, sheetUrl, sqid, method, id, and idColumn.
+The `.log` function is used to log data to the specified sheet. It accepts the following parameters:
+- `payload`: The data to be logged.
+- `options`: An object containing additional options such as `sheet`, `sheetUrl`, `sqid`, `method`, `id`, and `idColumn`.
 
 Example:
-```
+```javascript
 const payload = { name: 'John', age: 30 };
@@ -59,7 +53,25 @@ const options = { sheet: 'Users', method: 'POST' };
 
+#### .get(options)
+The `.get` function is used to retrieve data from the specified sheet. It supports various query options including pagination, sorting, and filtering.
+
+Example of basic get:
+```javascript
+const options = {
+  limit: 10,
+  start_id: 100,
+  order: "desc"
+};
+sheet.get(options);
+```
+
+Example of get with `_id`:
+```javascript
+sheet.get(123); // Fetches the row with ID 123
+```
+
 #### .update(payload, options)
-The .update function is used to update existing data in the specified sheet. It accepts the same parameters as .log and sets the method to "UPSERT".
+The `.update` function is used to update existing data in the specified sheet. It accepts the same parameters as `.log` and sets the method to "UPSERT".
 
 Example:
-```
+```javascript
 const payload = { id: 123, name: 'Jane', age: 25 };
@@ -71,6 +83,6 @@ const options = { sheet: 'Users' };
 #### .add(payload, options)
-The .add function is used to add new data to the specified sheet. It accepts the same parameters as .log, but turns any new keys in the object into columns
+The `.add` function is used to add new data to the specified sheet. It accepts the same parameters as `.log`, but turns any new keys in the object into columns.
 
 Example:
-```
+```javascript
 const payload = { name: 'Alice', age: 28 };
@@ -82,3 +94,3 @@ const options = { sheet: 'Users' };
 #### .find(idColumn, id, returnAllMatches)
-The .find function is used to find data in the specified sheet based on the provided idColumn and id. It accepts the idColumn, id, and returnAllMatches parameters.
+The `.find` function is used to find data in the specified sheet based on the provided `idColumn` and `id`. It accepts the `idColumn`, `id`, and `returnAllMatches` parameters.
 
@@ -88,3 +100,3 @@ This method returns an object, but setting `returnAllMatches=true` will instead return an array of all matches.
 Example:
-```
+```javascript
 const idColumn = 'id';
@@ -96,77 +108,377 @@ const id = 123;
 
+#### Pagination
+```javascript
+sheet.log(payload, {
+  method: "PAGINATED_GET",
+  limit: 20,
+  cursor: "100",
+  sortBy: "timestamp",
+  sortDir: "desc"
+});
+```
 
+#### Bulk Operations
+```javascript
+// Batch Update
+sheet.log(payload, {
+  method: "BATCH_UPDATE",
+  payload: [
+    { _id: 1, status: "complete" },
+    { _id: 2, status: "pending" }
+  ]
+});
 
+// Bulk Delete
+sheet.log(null, {
+  method: "BULK_DELETE",
+  ids: [1, 2, 3, 4]
+});
+```
 
+#### Aggregation
+```javascript
+sheet.log(null, {
+  method: "AGGREGATE",
+  sheet: "sales",
+  column: "amount",
+  operation: "sum" // Available: sum, avg, min, max, count
+});
+```
 
+#### Data Export
+```javascript
+sheet.log(null, {
+  method: "EXPORT",
+  format: "csv" // or "json"
+});
+```
 
-### Using Custom Sheets
+### Column Management
 
-To use a custom sheet, you can do this:
+```javascript
+// 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"
+});
 ```
-import sheet from '@yawnxyz/sheetlog';
-sheet.log({Name: "First Name"}, 
-  {
-    sheet: "Signups", // custom sheet name
-  })
-```
 
-This will work as long as you have the appropriate Google Sheets tabs set up — with the correct spelling of the sheet name and column names.
+### 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.
 
-### Custom Sheets
+**Example:**
+```javascript
+{
+  method: "GET_ROWS",
+  sheet: "logs",
+  startRow: 2,
+  endRow: 5
+}
+```
 
-You can also setup the custom sheet, then log data to the new configuation: 
+### Retrieve Specific Columns
 
-```
-sheet.setup({
-  sheet: "sheetName", // custom default sheet name
-  sheetUrl: "some custom sheet url",
-  logPayload: false, // log the payload back to console?
-  useSqid: false, // creates a sqid based on timestamp for easy referencing
-  method: "POST", // default method is POST, but can use "DYNAMIC_POST" for adding more columns, etc.
-})
+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.
 
-sheet.log({ Name: "Test Name" });
-```
+**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.
 
-You can also create and set up multiple Sheets:
+**Example:**
+```javascript
+{
+  method: "GET_COLUMNS",
+  sheet: "logs",
+  startColumn: "A",
+  endColumn: "C"
+}
 ```
-import { Sheet } from '@yawnxyz/sheetlog';
 
-const errorSheet = new Sheet();
-errorSheet.setup({
-  sheetUrl: "https://googleappscriptlink",
-  sheet: "Errors"
-})
 
-const signupSheet = new Sheet();
-signupSheet.setup({
-  sheetUrl: "https://googleappscriptlink",
-  sheet: "Signups"
-})
+## Features
 
-errorSheet.log({ Message: error.message });
-signupSheet.log({ Name: "New Signup Name" });
+- 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
+
+SheetLogs supports flexible authentication patterns:
+
+```javascript
+const logger = new SheetLogs({
+  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
 
+| 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 |
 
+## Method Index & Examples
 
+```javascript
+// Fetch all
+{
+  "method": "GET",
+  "sheet": "testSheet"
+}
 
-## Notes
+// Fetch the first row of data (row 1 is the header row since Sheets is 1-indexed)
+{
+  "method": "GET",
+  "sheet": "testSheet",
+  "id": 2
+}
 
-- Originally, [sqids](https://github.com/sqids) was used to create short IDs based on request time to quickly identify/search for batches of data in lengthy, inscrutable logs. It's very useful, but adds another dep, so I decided to make it optional. 
+// Fetch multiple rows with pagination
+{
+  "method": "GET",
+  "sheet": "links",
+  "start_id": 2,
+  "limit": 5
+}
 
-### Additions to SpreadAPI
+// Create a new row
+{
+  "method": "POST",
+  "sheet": "users",
+  "payload": {
+    "name": "John Doe",
+    "email": "[e-mail]"
+  }
+}
 
-Many thanks to [SpreadAPI](https://spreadapi.roombelt.com) for sharing the code for free (and even letting everyone know you CAN do this at all, without any expensive third party tools or the official API!). All credit goes to them.
+// Create or update a row based on ID
+{
+  "method": "UPSERT",
+  "sheet": "users",
+  "idColumn": "userId",
+  "id": 123,
+  "payload": {
+    "name": "Jane Doe",
+    "email": "[e-mail]"
+  }
+}
 
-1. Added column management / dynamic column support
-1. Adding data to "DYNAMIC_POST" creates columns that otherwise don't exist; this lets us log anything we want (but creates clutter in the sheet)
-1. Added date support for every log: the first column is always "Date Modified"
-1. Added better JSON handling; if you have a nested JSON it'll display it properly in each cell
-1. Find, Upsert, and a few other helpers
+// 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": "[e-mail]"
+  }
+}
+
+// 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": "[e-mail]",
+  "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"]
+  ]
+}
+
+```

New alerts

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Manifest confusion

Supply chain risk

This package has inconsistent metadata. This could be malicious or caused by an error when publishing the package.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

78770

increased by186.83%

Number of package files

7

increased by16.67%

Lines of code

2195

increased by262.81%

Number of lines in readme file

476

increased by188.48%

Worsened metrics

Number of medium supply chain risk alerts

3

increased by200%

No dependency changes