L2 Construct: create_pivot_table

sheets google

Description

Create a pivot table in a Google Sheet.

Example:

Create a pivot table from a source sheet to a destination sheet.
The source sheet is the one that contains the data to be pivoted.
The destination sheet is the one that will contain the pivot table.

L2 Data

Provider: google
Module: gsheets
Action: create_pivot_table

Example Step

{
  "name": "insert-your-step-name",
  "type": "l2",
  "l2_data": {
    "provider": "google",
    "module": "gsheets",
    "action": "create_pivot_table",
    "metadata": {}
  }
}

Input

Example

{
  "source_sheet": {
    "origin": {
      "row": 0,
      "col": 0
    },
    "bounds": {
      "row": 0,
      "col": 0
    },
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheet_name": "SourceSheet",
    "margin": {}
  },
  "dest_sheet": {
    "origin": {
      "row": 0,
      "col": 0
    },
    "bounds": {
      "row": 0,
      "col": 0
    },
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheet_name": "PivotTable",
    "margin": {}
  },
  "rows": [
    {
      "field": "Category",
      "sort_order": "ASCENDING",
      "show_totals": true
    }
  ],
  "columns": [
    {
      "field": "Category",
      "sort_order": "ASCENDING",
      "show_totals": true
    }
  ],
  "values": [
    {
      "field": "Amount",
      "agg_function": "SUM",
      "display_name": "Total Amount"
    },
    {
      "field": "Transactions",
      "agg_function": "COUNT",
      "display_name": "Number of Transactions"
    }
  ],
  "filters": [
    {
      "field": "Region",
      "operator": "NOT_EMPTY"
    }
  ]
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/create-pivot-table-input",
  "$ref": "#/$defs/CreatePivotTableInput",
  "$defs": {
    "Coordinate": {
      "properties": {
        "row": {
          "type": "integer"
        },
        "col": {
          "type": "integer"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["row", "col"]
    },
    "CreatePivotTableInput": {
      "properties": {
        "source_sheet": {
          "$ref": "#/$defs/Sheet"
        },
        "dest_sheet": {
          "$ref": "#/$defs/Sheet"
        },
        "rows": {
          "items": {
            "$ref": "#/$defs/PivotGroup"
          },
          "type": "array"
        },
        "columns": {
          "items": {
            "$ref": "#/$defs/PivotGroup"
          },
          "type": "array"
        },
        "values": {
          "items": {
            "$ref": "#/$defs/PivotValueField"
          },
          "type": "array"
        },
        "filters": {
          "items": {
            "$ref": "#/$defs/PivotFilterCriteria"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": [
        "source_sheet",
        "dest_sheet",
        "rows",
        "columns",
        "values",
        "filters"
      ]
    },
    "PivotFilterCriteria": {
      "properties": {
        "field": {
          "type": "string"
        },
        "operator": {
          "type": "string"
        },
        "value": {
          "type": "string"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["field", "operator"]
    },
    "PivotGroup": {
      "properties": {
        "field": {
          "type": "string"
        },
        "source_column_offset": {
          "type": "integer"
        },
        "sort_order": {
          "type": "string"
        },
        "show_totals": {
          "type": "boolean"
        },
        "repeat_label": {
          "type": "boolean"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["field"]
    },
    "PivotValueField": {
      "properties": {
        "field": {
          "type": "string"
        },
        "agg_function": {
          "type": "string"
        },
        "display_name": {
          "type": "string"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["field", "agg_function"]
    },
    "Sheet": {
      "properties": {
        "origin": {
          "$ref": "#/$defs/Coordinate"
        },
        "bounds": {
          "$ref": "#/$defs/Coordinate"
        },
        "spreadsheet_id": {
          "type": "string"
        },
        "sheet_name": {
          "type": "string"
        },
        "margin": {
          "$ref": "#/$defs/SheetMargin"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["origin", "spreadsheet_id", "sheet_name"]
    },
    "SheetMargin": {
      "properties": {
        "skip_last_n_rows": {
          "type": "integer"
        },
        "max_empty_rows": {
          "type": "integer"
        }
      },
      "additionalProperties": false,
      "type": "object"
    }
  }
}

Output

Example

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheet_name": "PivotTable",
  "spreadsheet_url": "",
  "error": {
    "is_error": false
  }
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/create-pivot-table-output",
  "$ref": "#/$defs/CreatePivotTableOutput",
  "$defs": {
    "CreatePivotTableOutput": {
      "properties": {
        "spreadsheet_id": {
          "type": "string"
        },
        "sheet_name": {
          "type": "string"
        },
        "spreadsheet_url": {
          "type": "string"
        },
        "error": {
          "$ref": "#/$defs/HandlerError"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["spreadsheet_id", "sheet_name", "spreadsheet_url"]
    },
    "HandlerError": {
      "properties": {
        "is_error": {
          "type": "boolean"
        },
        "errors": {
          "items": {
            "type": "string"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["is_error"]
    }
  }
}

Create Charts in Sheets Create Spreadsheet