1001fx - NoCode functions icon

1001fx - NoCode functions

1001fx is a set of functions for any automation platform. Our functions are simple, very well tested, scalable and offer features that most automation platforms do not offer.

Actions74

spreadsheet → Pivottable Spreadsheet Function for an Array

AI-generated

Overview

This node provides a "Pivottable Spreadsheet Function for an Array" as part of the 1001fx suite in n8n. It allows users to perform pivot table operations on an array of objects, aggregating data based on a specified index and applying custom aggregation functions. This is useful for summarizing, grouping, and analyzing tabular data within automation workflows.

Common scenarios:

  • Summarizing sales or survey data by category (e.g., total sales per city).
  • Grouping and aggregating metrics (e.g., average score per department).
  • Creating quick reports from raw data arrays.

Practical example:
Given an array of objects like [{city: "London", score: 10}, {city: "Paris", score: 20}], you can group by city and sum the score field to get total scores per city.

Properties

Name Meaning
Data Array of objects with at least two properties (e.g., [{"city": ..., "score": ...}]). This is the input dataset for the pivot operation.
Index Name of the field in the data to be used as the index for aggregation (case sensitive). For example, "city" if you want to group by city.
Aggregate functions Input Mode Choose how to specify aggregate functions:
- Expression: Provide aggregate functions as a JSON object.
- Manual: Enter each function and field manually.
Aggregate functions (Expression) (Shown when Input Mode is "Expression")
JSON object specifying the aggregate operator per field name. Example: { "score": ["sum", "avg"] }.
Aggregate functions (Manual) (Shown when Input Mode is "Manual")
List of fields and their aggregator functions. Each entry includes:
- Field: Name of the field
- Aggregators: List of functions (e.g., sum, avg).

Output

  • The output is a JSON object containing the result of the pivot table operation.
  • The structure typically includes:
    • The unique values of the index field as keys or rows.
    • Aggregated values for each specified field and function.
  • Example output:
    [
  {
    "city": "London",
    "score_sum": 30,
    "score_avg": 15
  },
  {
    "city": "Paris",
    "score_sum": 20,
    "score_avg": 20
  }
]
  • No binary data is produced by this operation.

Dependencies

  • External Service: Requires access to the 1001fx API.
  • API Key: You must configure the 1001fxApi credential in n8n.
  • n8n Configuration: Ensure your instance can reach external APIs and that the credential is set up.

Troubleshooting

Common issues:

  • Invalid Data Format: If the "Data" property is not a valid array of objects, the node may return an error or unexpected results.
  • Incorrect Index Field: If the "Index" does not match any property in the data objects (case sensitive), aggregation will fail or produce empty results.
  • Aggregate Functions Misconfiguration: If aggregate functions are not specified correctly (wrong field names or unsupported functions), the output may be incorrect or errors may occur.

Error messages:

  • "error": "Cannot read property '...' of undefined": Likely due to a missing or misspelled index or field name.
  • "error": "Unexpected token ... in JSON": Indicates invalid JSON in the "Data" or "Aggregate functions" fields.
  • "error": "Missing required parameter": One of the required properties was not provided.

How to resolve:

  • Double-check property names and ensure they match your data structure.
  • Validate all JSON inputs using a linter or online tool.
  • Ensure all required fields are filled in the node configuration.

Links and References

Discussion