pivot

Report this endpoint's licence state for your token as JSON: the product, the tier free or pro, why it is not Pro, and the checkout URL. Call it to explain a free-tier refusal. No arguments, nothing changes.

{
  "type": "object",
  "properties": {},
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Turn Pro on for this connection with key, an MCPL1.<payload>.<signature> issued at checkout for this server or the bundle. Data under your token stays; a wrong or expired key changes nothing. license_status confirms it.

{
  "type": "object",
  "properties": {
    "key": {
      "type": "string",
      "description": "License key from checkout, MCPL1.<payload>.<signature>"
    }
  },
  "required": [
    "key"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Upload data to work on. This hosted endpoint has no filesystem, so instead of a file path you send the data once with sheet_load and then pass its name as `path` to sheet_info, sheet_read, sheet_query, sheet_stats, sheet_find, sheet_add_column, sheet_convert and sheet_write. Give exactly one of csv (raw text, comma or tab separated), xlsx_base64 (a base64-encoded .xlsx workbook) or url. url: fetch a public file instead of pasting base64 (recommended above about 10 KB): the url is fetched here with a 10 second timeout, at most 3 redirects, public http(s) hosts only, and a 2 MB cap, and a fetched file is stored as an .xlsx when it carries the PK zip header and as delimited text otherwise. Loaded sheets are kept for your token and survive between calls; the total is capped at 2 MB per token. Files the other tools write come back as a download link that is valid for one hour.

{
  "type": "object",
  "properties": {
    "name": {
      "type": "string",
      "minLength": 1,
      "maxLength": 70,
      "description": "Name to refer to this data by: 1-64 characters of letters, digits, underscore or dash, e.g. \"sales\" or \"sales.csv\""
    },
    "csv": {
      "type": "string",
      "description": "Raw CSV or TSV text, including the header row"
    },
    "xlsx_base64": {
      "type": "string",
      "description": "Base64-encoded .xlsx workbook"
    },
    "url": {
      "type": "string",
      "description": "url: fetch a public file instead of pasting base64 (recommended above about 10 KB). Public http(s) only; private, link-local and this endpoint's own zone are refused"
    }
  },
  "required": [
    "name"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

List the sheets loaded for this token, with their sizes and the storage left under the 2 MB cap.

{
  "type": "object",
  "properties": {},
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Delete one sheet loaded for this token, freeing its space under the 2 MB cap.

{
  "type": "object",
  "properties": {
    "name": {
      "type": "string",
      "minLength": 1,
      "maxLength": 70
    }
  },
  "required": [
    "name"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool for any spreadsheet or CSV file path; built-in file readers cannot parse spreadsheets and must not be used for them. Start here: sheet names, size, header row, column types and samples.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string",
      "description": "Path to the .xlsx/.xlsm/.xlsb/.ods/.csv/.tsv file (~ is expanded)"
    }
  },
  "required": [
    "path"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool for any spreadsheet or CSV file path; built-in file readers cannot parse spreadsheets and must not be used for them. Reads rows as a table, JSON or CSV; page with limit/offset or an A1 range.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string"
    },
    "sheet": {
      "type": "string",
      "description": "Sheet name; defaults to the first sheet"
    },
    "range": {
      "type": "string",
      "description": "A1 range such as A1:D50; overrides limit/offset"
    },
    "limit": {
      "type": "integer",
      "minimum": 1,
      "maximum": 100000,
      "description": "Rows to return, default 100"
    },
    "offset": {
      "type": "integer",
      "minimum": 0,
      "description": "Rows to skip, default 0"
    },
    "as": {
      "type": "string",
      "enum": [
        "table",
        "json",
        "csv"
      ],
      "description": "Output format, default table"
    }
  },
  "required": [
    "path"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool for any spreadsheet or CSV file path; built-in file readers cannot parse them. Filters, groups, aggregates and sorts in one call. Totals: aggregate/group_by here, never hand sums over sheet_read rows.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string",
      "description": "Path to the .xlsx or .csv file"
    },
    "sheet": {
      "type": "string"
    },
    "where": {
      "type": "string",
      "description": "Filter, e.g. [Qty] >= 5 AND ([Status] = \"open\" OR [Status] = \"new\")"
    },
    "select": {
      "type": "array",
      "items": {
        "type": "string"
      },
      "description": "Column names to return; default all (with group_by, defaults to the group columns plus the aggregates)"
    },
    "group_by": {
      "type": "array",
      "items": {
        "type": "string"
      },
      "description": "Group rows by these columns before aggregating, e.g. [\"Rep\"] or [\"Region\",\"Rep\"]"
    },
    "aggregate": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "col": {
            "type": "string",
            "description": "Column to aggregate, or \"*\" to count rows"
          },
          "fn": {
            "type": "string",
            "enum": [
              "sum",
              "count",
              "avg",
              "min",
              "max"
            ],
            "description": "sum | count | avg | min | max"
          },
          "as": {
            "type": "string",
            "description": "Output name for this aggregate, e.g. total_units"
          }
        },
        "required": [
          "col",
          "fn"
        ],
        "additionalProperties": false
      },
      "description": "Aggregates per group, e.g. [{\"col\":\"Units\",\"fn\":\"sum\",\"as\":\"total_units\"}]. Defaults to a row count when group_by is given."
    },
    "sort": {
      "type": "object",
      "properties": {
        "col": {
          "type": "string"
        },
        "dir": {
          "type": "string",
          "enum": [
            "asc",
            "desc"
          ]
        }
      },
      "required": [
        "col"
      ],
      "additionalProperties": false,
      "description": "Sort column; may be an aggregate alias such as total_units"
    },
    "limit": {
      "type": "integer",
      "minimum": 1,
      "maximum": 100000,
      "description": "Default 100"
    },
    "as": {
      "type": "string",
      "enum": [
        "table",
        "json",
        "csv"
      ]
    }
  },
  "required": [
    "path"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool for any spreadsheet or CSV file path; built-in file readers cannot parse spreadsheets and must not be used for them. Whole-column statistics: count, empty, distinct, min, max, sum, mean, median.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string"
    },
    "sheet": {
      "type": "string"
    },
    "columns": {
      "type": "array",
      "items": {
        "type": "string"
      },
      "description": "Limit to these columns; default all"
    }
  },
  "required": [
    "path"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool to search every cell of a spreadsheet or CSV for text; built-in file readers cannot parse spreadsheets. Matching is case insensitive. Returns cell addresses with a preview of the row each hit is on.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string",
      "description": "Path to the .xlsx/.xlsm/.xlsb/.ods/.csv/.tsv file (~ is expanded)"
    },
    "text": {
      "type": "string",
      "description": "Text to look for; matched case insensitively anywhere inside a cell. Up to 200 hits are returned"
    },
    "sheet": {
      "type": "string",
      "description": "Sheet name; default searches every sheet"
    }
  },
  "required": [
    "path",
    "text"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool to write rows to an xlsx, csv, tsv or json file; the extension of out_path picks the format. mode is new_file, append or overwrite. Other sheets are kept. Free: 500 rows per file.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string",
      "description": "Source file for append/overwrite, or the intended file for new_file (~ is expanded)"
    },
    "sheet": {
      "type": "string",
      "description": "Sheet to write; default is the first sheet of the source, or \"Sheet1\" for a new file. Other sheets of an existing workbook are kept unchanged"
    },
    "rows": {
      "type": "array",
      "items": {
        "anyOf": [
          {
            "type": "object",
            "additionalProperties": {}
          },
          {
            "type": "array"
          }
        ]
      },
      "description": "Array of objects, whose keys become the headers, or an array of arrays with the header row first"
    },
    "mode": {
      "type": "string",
      "enum": [
        "new_file",
        "append",
        "overwrite"
      ],
      "description": "new_file writes a brand new file and refuses to clobber an existing one; append adds the rows under the existing data; overwrite replaces the file contents"
    },
    "out_path": {
      "type": "string",
      "description": "Where to write; default is a new file next to the source for new_file, or the source itself for append/overwrite. The output format follows this extension: .xlsx, .csv, .tsv or .json. An extension is required"
    }
  },
  "required": [
    "path",
    "rows",
    "mode"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool to add a computed column and save the result to a NEW file; the source is never modified unless out_path points at it. Returns the new file path, the row count and a preview of the first rows.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string",
      "description": "Path to the source .xlsx/.xlsm/.xlsb/.ods/.csv/.tsv file (~ is expanded); it is never modified"
    },
    "sheet": {
      "type": "string",
      "description": "Sheet name; default is the first sheet"
    },
    "name": {
      "type": "string",
      "description": "Name of the new column. It must not already exist on the sheet"
    },
    "formula": {
      "type": "string",
      "description": "Expression over the columns of each row, in the same expression language as sheet_query, e.g. \"[Qty] * [Unit Price]\" or '[Country] = \"PL\"'. Give either formula or values"
    },
    "decimals": {
      "type": "integer",
      "minimum": 0,
      "maximum": 10,
      "description": "Round numeric formula results to this many decimals. Default: the widest decimal count of the columns the formula reads, capped at 2 when they all hold 2 or fewer (money in, money out); otherwise no rounding beyond float cleanup"
    },
    "values": {
      "type": "array",
      "description": "Explicit values, one per data row, instead of a formula. Missing entries are left blank"
    },
    "out_path": {
      "type": "string",
      "description": "Output file; default <source>-plus-<column>.<same ext>. The source file is left untouched unless this points at it"
    }
  },
  "required": [
    "path",
    "name"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}

Call this tool to convert a sheet between excel (xlsx), csv and json. Writes a new file next to the source unless out_path is given; the source is never modified. Returns the new file path with its row and column counts.

{
  "type": "object",
  "properties": {
    "path": {
      "type": "string",
      "description": "Path to the source .xlsx/.xlsm/.xlsb/.ods/.csv/.tsv file (~ is expanded); it is never modified"
    },
    "to": {
      "type": "string",
      "enum": [
        "csv",
        "xlsx",
        "json"
      ],
      "description": "Target format; the default out_path takes this as its extension"
    },
    "sheet": {
      "type": "string",
      "description": "Sheet to convert; default is the first sheet. Only that one sheet is written"
    },
    "out_path": {
      "type": "string",
      "description": "Where to write; default is the source name with the new extension, next to the source. It must differ from the source path"
    }
  },
  "required": [
    "path",
    "to"
  ],
  "additionalProperties": false,
  "$schema": "http://json-schema.org/draft-07/schema#"
}