A Google Sheets event plugin for the xyOps Workflow Automation System. It can read, search, append, update, clear, and delete spreadsheet data from xyOps workflows using a simple Toolset interface.

This plugin is designed for common workflow automation jobs: append form submissions, look up rows by a customer ID, update status columns, export a row range for downstream jobs, clear staging ranges, or create worksheet tabs during a reporting pipeline.

Features

• Pure Node.js / npx plugin.
• Supports Google service accounts for read and write access.
• Row tools use a header row and return both object and array data.
• Range tools read and write plain two-dimensional arrays without requiring headers.
• Includes worksheet discovery and basic worksheet management.
• Sends structured data back to xyOps for downstream workflow steps.
• Sends compact tables to the xyOps job details page for easy review.

Requirements

• Node.js and npx
• Network access from the xyOps runner host to Google APIs
• A Google Cloud project with the Google Sheets API enabled

Installation

xyOps launches this plugin with:

npx -y @pixlcore/[email protected]

Authentication

This plugin uses Google service account authentication. All credentials are stored in one xyOps Secret Vault variable named GOOGLE_SERVICE_ACCOUNT_JSON.

Google Setup

Create a Google service account for private spreadsheets and read/write operations.

1. Open the Google Cloud Console.
2. Select an existing project or create a new project.
3. Go to APIs & Services and enable Google Sheets API.
4. Go to APIs & Services > Credentials.
5. Click Create Credentials and choose Service Account.
6. Give the service account a name and finish the wizard.
7. Open the new service account, click Keys, then click Add Key > Create New Key.
8. Choose JSON and download the key file.
9. Open the Google spreadsheet and share it with the service account email address from the JSON file. Give it Editor access for write tools, or Viewer access for read-only tools.

The downloaded JSON file is the value you will store in xyOps. It contains fields like these:

{
	"client_email": "[email protected]",
	"private_key": "-----BEGIN PRIVATE KEY-----\n..."
}

xyOps Secret Vault

Create a Secret Vault in xyOps and assign this plugin to it.

Add exactly one variable:

| Secret | Description | |--------|-------------| | GOOGLE_SERVICE_ACCOUNT_JSON | The entire downloaded service account JSON document. |

Tool Overview

The plugin exposes these tools:

| Tool | Purpose | |------|---------| | List Worksheets | Return metadata for all worksheet tabs. | | Get Rows | Read rows using the configured header row. | | Search Rows | Find rows by matching a header column value. | | Add Rows | Append one or more rows. | | Update Rows | Update one or more existing rows by row number. | | Delete Rows | Delete complete rows by row number. | | Get Range | Read an A1 range as a plain array. | | Update Range | Write a plain array into an A1 range. | | Clear Range | Clear values from an A1 range. | | Set Header Row | Set the header row used by row tools. | | Add Worksheet | Create a new worksheet tab. | | Delete Worksheet | Delete the selected worksheet tab. |

Header Rows and Data Shapes

Google Sheets can be used in two common ways, so this plugin supports both:

• Row tools treat a sheet like a table. The configured header row provides column names. Returned rows include:
  • rowNumber: The 1-based worksheet row number.
  • values: Ordered cell values as an array.
  • object: Cell values keyed by header name.
• Range tools ignore headers and operate on plain A1 ranges. Returned values are two-dimensional arrays.

If you want plain arrays only, use Get Range instead of a row-based tool.

Common Parameters

| Parameter | Required | Description | |-----------|----------|-------------| | Spreadsheet ID | Yes | The spreadsheet ID from the Google Sheets URL. | | Worksheet | No | Worksheet title, sheet ID, or zero-based tab index. Leave blank for the first worksheet. | | Header Row | No | 1-based row number containing headers for row tools. Defaults to 1. | | Verbose Logging | No | Sends setup details to STDERR for troubleshooting. |

Tool Reference

List Worksheets

Lists worksheet tabs in the spreadsheet.

Output:

• data.title: Spreadsheet title.
• data.sheets: Array of worksheet metadata.
• data.count: Number of worksheets.

Example response:

{
	"spreadsheetId": "1abcDEFghiJKLmnopQRstuVWxyz",
	"title": "Operations Tracker",
	"sheets": [
		{
			"title": "Sheet1",
			"sheetId": 0,
			"index": 0,
			"rowCount": 1000,
			"columnCount": 26,
			"hidden": false
		},
		{
			"title": "Archive",
			"sheetId": 123456789,
			"index": 1,
			"rowCount": 500,
			"columnCount": 10,
			"hidden": false
		}
	],
	"count": 2
}

Get Rows

Reads rows using the configured header row.

| Parameter | Required | Description | |-----------|----------|-------------| | Start Row | No | 1-based row number to start reading from. | | Maximum Rows | No | Maximum number of rows to return. |

Output:

• data.rows: Array of row objects containing rowNumber, values, and object.
• data.count: Number of rows returned.

Example response:

{
	"worksheet": "Sheet1",
	"headerRow": 1,
	"startRow": 2,
	"rows": [
		{
			"rowNumber": 2,
			"values": ["Ada Lovelace", "[email protected]", "active"],
			"object": {
				"name": "Ada Lovelace",
				"email": "[email protected]",
				"status": "active"
			}
		},
		{
			"rowNumber": 3,
			"values": ["Grace Hopper", "[email protected]", "pending"],
			"object": {
				"name": "Grace Hopper",
				"email": "[email protected]",
				"status": "pending"
			}
		}
	],
	"count": 2
}

If you fetch only one row, the response shape is identical: data.rows is still an array, but it contains one row object and count is 1.

Search Rows

Scans rows and returns matches based on a header column value.

| Parameter | Required | Description | |-----------|----------|-------------| | Search Column | Yes | Header name of the column to search. | | Search Value | No | Value to match. | | Search Mode | No | Equals, not equals, contains, starts with, ends with, or regular expression. | | Case Sensitive | No | Match letter case exactly. | | Rows To Scan | No | Maximum number of data rows to scan. | | Maximum Matches | No | Stop after this many matches. |

Example response:

{
	"worksheet": "Sheet1",
	"headerRow": 1,
	"searchColumn": "status",
	"searchValue": "active",
	"searchMode": "equals",
	"rows": [
		{
			"rowNumber": 2,
			"values": ["Ada Lovelace", "[email protected]", "active"],
			"object": {
				"name": "Ada Lovelace",
				"email": "[email protected]",
				"status": "active"
			}
		}
	],
	"count": 1
}

Add Rows

Appends one or more rows to the worksheet.

Rows can be:

{ "name": "Ada Lovelace", "email": "[email protected]" }

Or:

[
	{ "name": "Ada Lovelace", "email": "[email protected]" },
	{ "name": "Grace Hopper", "email": "[email protected]" }
]

Or array-style rows, ordered by the sheet headers:

[
	["Ada Lovelace", "[email protected]", "active"],
	["Grace Hopper", "[email protected]", "pending"]
]

| Parameter | Required | Description | |-----------|----------|-------------| | Rows | Yes | JSON row object, row array, or array of rows. | | Raw Values | No | Store values exactly as provided. | | Insert At End | No | Always insert new rows at the end, instead of overwriting empty rows. |

Update Rows

Updates one or more existing rows starting at Row Number.

| Parameter | Required | Description | |-----------|----------|-------------| | Row Number | Yes | 1-based worksheet row number of the first row to update. | | Updates | Yes | JSON object, row array, or array of updates. | | Raw Values | No | Store values exactly as provided. |

Object updates only modify named columns:

{ "status": "done" }

Array updates are applied from the first header column onward:

["Ada Lovelace", "[email protected]", "done"]

Delete Rows

Deletes complete worksheet rows. Rows below the deleted range shift upward.

| Parameter | Required | Description | |-----------|----------|-------------| | Start Row | Yes | 1-based worksheet row number to delete. | | Row Count | No | Number of consecutive rows to delete. | | Dry Run | No | Preview the operation without deleting rows. Enabled by default. |

Get Range

Reads an A1 range as a plain two-dimensional array.

| Parameter | Required | Description | |-----------|----------|-------------| | A1 Range | Yes | Range inside the selected worksheet, such as A1:D10. | | Value Render Mode | No | Formatted, unformatted, or formula values. | | Date/Time Render Mode | No | Serial number or formatted string. |

Output:

• data.values: Two-dimensional array of cells.
• data.rowCount: Number of returned rows.
• data.columnCount: Largest returned row width.

Example response:

{
	"worksheet": "Sheet1",
	"range": "A1:C3",
	"values": [
		["name", "email", "status"],
		["Ada Lovelace", "[email protected]", "active"],
		["Grace Hopper", "[email protected]", "pending"]
	],
	"rowCount": 3,
	"columnCount": 3
}

Update Range

Writes values to an A1 range.

| Parameter | Required | Description | |-----------|----------|-------------| | A1 Range | Yes | Top-left cell or range to update. | | Values | Yes | Two-dimensional JSON array of values. | | Raw Values | No | Store values exactly as provided. |

Example:

[
	["name", "email"],
	["Ada Lovelace", "[email protected]"]
]

Clear Range

Clears values from an A1 range without deleting rows or columns.

| Parameter | Required | Description | |-----------|----------|-------------| | A1 Range | Yes | Range to clear, such as A2:D100. |

Set Header Row

Sets the header row used by the row-based tools.

| Parameter | Required | Description | |-----------|----------|-------------| | Header Values | Yes | Comma-separated headers or a JSON array of strings. |

Add Worksheet

Creates a new worksheet tab.

| Parameter | Required | Description | |-----------|----------|-------------| | New Worksheet Title | Yes | Title for the new tab. | | Row Count | No | Initial row count. | | Column Count | No | Initial column count. | | Header Values | No | Optional headers for the new sheet. |

Delete Worksheet

Deletes the selected worksheet tab.

| Parameter | Required | Description | |-----------|----------|-------------| | Dry Run | No | Preview the operation without deleting the worksheet. Enabled by default. |

Local Testing

When invoked by xyOps, the plugin expects one JSON document on STDIN using the xyOps Wire Protocol. You can simulate this locally by piping JSON into node index.js.

Install dependencies first:

npm install

Read rows test:

{
	"xy": 1,
	"params": {
		"spreadsheet_id": "REPLACE_WITH_SPREADSHEET_ID",
		"worksheet": "Sheet1",
		"tool": "getRows",
		"header_row": 1,
		"start_row": 2,
		"max_rows": 5
	},
	"secrets": {
		"GOOGLE_SERVICE_ACCOUNT_JSON": "{\"client_email\":\"[email protected]\",\"private_key\":\"-----BEGIN PRIVATE KEY-----\\nREPLACE_ME\\n-----END PRIVATE KEY-----\\n\"}"
	}
}

Run it like this:

cat sample-get-rows.json | node index.js

Append rows test:

echo '{"xy":1,"params":{"spreadsheet_id":"REPLACE_WITH_SPREADSHEET_ID","worksheet":"Sheet1","tool":"addRows","header_row":1,"rows":[{"name":"Ada Lovelace","email":"[email protected]","status":"new"}]},"secrets":{"GOOGLE_SERVICE_ACCOUNT_JSON":"..."}}' | node index.js

Delete rows dry run:

echo '{"xy":1,"params":{"spreadsheet_id":"REPLACE_WITH_SPREADSHEET_ID","worksheet":"Sheet1","tool":"deleteRows","row_number":10,"row_count":2,"dry_run":true},"secrets":{"GOOGLE_SERVICE_ACCOUNT_JSON":"..."}}' | node index.js

Output Summary

Depending on the selected tool, the plugin returns structured job data such as:

• spreadsheet and worksheet metadata
• row objects keyed by headers
• ordered row value arrays
• range value arrays
• counts of rows, columns, cells, or matches
• dry-run status for destructive tools

These values are available to downstream xyOps workflow steps through the normal job output data path.

Data Collection

This plugin does not collect, store, or transmit telemetry, analytics, or usage metrics. It only connects to the Google spreadsheet you configure. Google may log API requests according to its own service policies.

License

MIT