sheet/appendOrUpdate FormID SH02

When to Use

• HR — employee record sync: An HRIS integration runs hourly to sync employee status changes. Using appendOrUpdate with EmployeeId as the lookup key ensures that existing records are updated (e.g. status changed from "Active" to "On Leave") while genuinely new employees are added as new rows — without duplicates regardless of how many times the sync runs.
• Marketing — campaign performance sync: A daily workflow pulls campaign metrics from the ad platform API and calls appendOrUpdate keyed on CampaignId + Date. If today's row already exists (e.g. the workflow ran twice), it updates impressions, clicks, and spend. If it's a new day, a new row is appended.
• Operations — order status tracking: Order status updates arrive via webhook throughout the day. appendOrUpdate keyed on OrderId updates the status, last-updated timestamp, and shipping details in the "Orders" sheet — no duplicate rows, no lost updates.
• Finance — invoice reconciliation: A payment processor webhook fires on each payment received. The workflow calls appendOrUpdate keyed on InvoiceNumber to mark invoices as Paid and record the payment date, idempotently handling retry webhook deliveries.
• Inventory — stock level update: A POS integration updates product quantities in the "Inventory" sheet. appendOrUpdate keyed on SKU ensures each product has exactly one row — updated on every stock change, inserted on first encounter of a new SKU.

Configuration

Validation Errors

Output Fields

Sample Configuration

{
  "resource": "sheet",
  "operation": "appendOrUpdate",
  "CredentialId": 12,
  "SpreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
  "SheetName": "Employees",
  "LookupFilters": [
    { "Column": "EmployeeId", "Operator": "Equals", "Value": "{{ $json.employeeId }}" }
  ],
  "CombineFilters": "And",
  "DataMode": "DefineBelow",
  "ColumnMappings": [
    { "Column": "EmployeeId",   "Value": "{{ $json.employeeId }}" },
    { "Column": "FirstName",    "Value": "{{ $json.firstName }}" },
    { "Column": "LastName",     "Value": "{{ $json.lastName }}" },
    { "Column": "Department",   "Value": "{{ $json.department }}" },
    { "Column": "Title",        "Value": "{{ $json.jobTitle }}" },
    { "Column": "Salary",       "Value": "{{ $json.salary }}" },
    { "Column": "Status",       "Value": "{{ $json.status }}" },
    { "Column": "LastSyncedAt", "Value": "{{ $now | date('YYYY-MM-DD HH:mm:ss') }}" }
  ],
  "CellFormat": "UserEntered",
  "HeaderRow": 1,
  "FirstDataRow": 2,
  "HandlingExtraData": "IgnoreIt"
}

Sample Output

{
  "action": "update",
  "updatedRange": "Employees!A23:H23",
  "updatedRows": 1,
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
  "sheetName": "Employees",
  "status": "success",
  "resource": "sheet",
  "operation": "appendOrUpdate"
}

Expression Reference

Assuming this node is named syncEmployee in the workflow:

Node Policies & GuardRails

• Lookup is a full sheet scan: The node reads all rows in the sheet to find the matching row before writing. For sheets with thousands of rows, this read adds latency and consumes read quota. Consider using a dedicated lookup column with a short, indexed value (e.g. a numeric ID) rather than a free-text string.
• Multiple matches: If more than one row matches the LookupFilters, only the first match (by row order, top to bottom) is updated. All others are left unchanged. Ensure your lookup key is truly unique to avoid silently updating the wrong row.
• Concurrent safety: Do not run two parallel branches calling appendOrUpdate on the same sheet simultaneously. The read-then-write pattern is not atomic — concurrent executions can result in both branches appending (creating duplicates) instead of one updating.
• Formula injection: Sanitize any user-supplied text before it enters ColumnMappings values. Text starting with =, +, -, or @ is treated as a formula in UserEntered mode.
• Credential scope: The Google credential must have Edit (not just View) permission on the spreadsheet. Read-only credentials will cause a 403 Forbidden error.
• action field usage: Always inspect {{ $output.nodeName.action }} in downstream nodes when your workflow cares whether a record was new or existing — for example, to send a "new employee" welcome email only on append, not on update.

Workflow Examples

Example 1 — HRIS Employee Sync (Hourly)

// Trigger: Scheduled every hour
// Node 1 — HttpRequest: GET /hris/employees/modified?since={{ $lastRun }}
// Node 2 — Loop over modified employees
// Node 3 — sheet/appendOrUpdate (syncEmployee)
{
  "resource": "sheet", "operation": "appendOrUpdate",
  "CredentialId": 12,
  "SpreadsheetId": "{{ $env.HR_SHEET_ID }}",
  "SheetName": "Employees",
  "LookupFilters": [
    { "Column": "EmployeeId", "Operator": "Equals", "Value": "{{ $item.id }}" }
  ],
  "DataMode": "AutoMap"
}

// Node 4 — IF: action == "append"
//   True branch: send welcome email to new employee
//   False branch: continue (no action needed for updates)

Example 2 — Campaign Metrics Daily Upsert

// Trigger: Scheduled daily at 6 AM
// Node 1 — HttpRequest: GET /ads/campaigns/performance?date={{ $today }}
// Node 2 — Loop over campaigns
// Node 3 — sheet/appendOrUpdate (upsertCampaign)
{
  "resource": "sheet", "operation": "appendOrUpdate",
  "CredentialId": 15,
  "SpreadsheetId": "{{ $env.MARKETING_SHEET_ID }}",
  "SheetName": "Campaign Performance",
  "LookupFilters": [
    { "Column": "CampaignId", "Operator": "Equals", "Value": "{{ $item.campaignId }}" },
    { "Column": "Date",       "Operator": "Equals", "Value": "{{ $today | date('YYYY-MM-DD') }}" }
  ],
  "CombineFilters": "And",
  "DataMode": "DefineBelow",
  "ColumnMappings": [
    { "Column": "CampaignId",   "Value": "{{ $item.campaignId }}" },
    { "Column": "CampaignName", "Value": "{{ $item.name }}" },
    { "Column": "Date",         "Value": "{{ $today | date('YYYY-MM-DD') }}" },
    { "Column": "Impressions",  "Value": "{{ $item.impressions }}" },
    { "Column": "Clicks",       "Value": "{{ $item.clicks }}" },
    { "Column": "Spend",        "Value": "{{ $item.spend }}" },
    { "Column": "Conversions",  "Value": "{{ $item.conversions }}" }
  ]
}