Skip to content
Browse all functions

Pivottable Spreadsheet Function for an Array

spreadsheet/pivottable

pivottable (Pivottable Spreadsheet Function for an Array) is a 1001fx API function in the group Spreadsheet: POST https://api.1001fx.com/spreadsheet/pivottable, 2 credits per call. It works as a ready-made module in Make and n8n or as a plain HTTP call.

2 credits per callHTTP APIn8nMake

Implements a pivottable function similar to GSheet and Excel functions. Aggregate fields with given operator. Multiple fields can be aggregated. Aggregate functions are: count, sum, min, max and mean.

Aggregate functions :

  • count: calculates the count of all values in a set
  • sum: calculates the sum of values.
  • min: minimum gets the minimum value in a set of values
  • max: aximum gets the maximum value in a set of values
  • mean: calculates the average in a set of values — not rounded

Try it in the 1001fx app

Run pivottable in your browser with your API key – fill the form, click Run and get the answer or the file.

pivottable in the 1001fx app: the form with example values
Pivottable Spreadsheet Function for an Array in the 1001fx app

Endpoint

POSThttps://api.1001fx.com/spreadsheet/pivottable
Header
X-API-KEY: YOURSECRETAPIKEY How to
Content type
application/json

Parameters

  • dataarray<object>required

    Array of objects with at least two properties e.g. [{city,score}] each.

  • indexstringrequired

    Name of the field in data to be used as index for aggregation. Case sensitive.

    Example:
    name
  • aggregateFunctionsarray<object>required

    Aggregate operator per name attribute.

    • fieldstringrequired

      Name of field

    • aggregatorsarray<enum>required

      Aggregator functions

      Values:
      count
      sum
      min
      max
      mean

Examples

Single aggregation

Creates pivottable with total line

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1,
      "highest": 10
    },
    {
      "name": "Bob",
      "score": 2,
      "highest": 20
    },
    {
      "name": "Alice",
      "score": 4,
      "highest": 40
    },
    {
      "name": "Charlie",
      "score": 8,
      "highest": 80
    },
    {
      "name": "Bob",
      "score": 16,
      "highest": 160
    }
  ],
  "index": "name",
  "aggregateFunctions": [
    {
      "field": "score",
      "aggregators": [
        "sum"
      ]
    }
  ]
}

Output · HTTP 200

Response
{
  "result": [
    {
      "Name": "Alice",
      "Sum of score": 5
    },
    {
      "Name": "Bob",
      "Sum of score": 18
    },
    {
      "Name": "Charlie",
      "Sum of score": 8
    },
    {
      "Name": "Grand Total",
      "Sum of score": 31
    }
  ]
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/pivottable' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1,"highest":10},{"name":"Bob","score":2,"highest":20},{"name":"Alice","score":4,"highest":40},{"name":"Charlie","score":8,"highest":80},{"name":"Bob","score":16,"highest":160}],"index":"name","aggregateFunctions":[{"field":"score","aggregators":["sum"]}]}'

Multiple aggregations

Creates pivottable with total line and multiple aggregations

Input

Expects a json body
{
  "data": [
    {
      "name": "Alice",
      "score": 1,
      "highest": 10
    },
    {
      "name": "Bob",
      "score": 2,
      "highest": 20
    },
    {
      "name": "Alice",
      "score": 4,
      "highest": 40
    },
    {
      "name": "Charlie",
      "score": 8,
      "highest": 80
    },
    {
      "name": "Bob",
      "score": 16,
      "highest": 160
    }
  ],
  "index": "name",
  "aggregateFunctions": [
    {
      "field": "score",
      "aggregators": [
        "sum"
      ]
    },
    {
      "field": "highest",
      "aggregators": [
        "max",
        "mean"
      ]
    }
  ]
}

Output · HTTP 200

Response
{
  "result": [
    {
      "Name": "Alice",
      "Sum of score": 5,
      "Max of highest": 40,
      "Mean of highest": 25
    },
    {
      "Name": "Bob",
      "Sum of score": 18,
      "Max of highest": 160,
      "Mean of highest": 90
    },
    {
      "Name": "Charlie",
      "Sum of score": 8,
      "Mean of highest": 80,
      "Max of highest": 80
    },
    {
      "Name": "Grand Total",
      "Max of highest": 160,
      "Mean of highest": 62,
      "Sum of score": 31
    }
  ]
}
Show curl command
curl -X POST 'https://api.1001fx.com/spreadsheet/pivottable' \
  -H 'X-API-KEY: YOURSECRETAPIKEY' \
  -H 'Content-Type: application/json' \
  -d '{"data":[{"name":"Alice","score":1,"highest":10},{"name":"Bob","score":2,"highest":20},{"name":"Alice","score":4,"highest":40},{"name":"Charlie","score":8,"highest":80},{"name":"Bob","score":16,"highest":160}],"index":"name","aggregateFunctions":[{"field":"score","aggregators":["sum"]},{"field":"highest","aggregators":["max","mean"]}]}'

Try pivottable now

Run it in your browser or create a free account and get 100 credits every month.

Changelog

  • 1.0.017.01.2023created
Ask 1001fx