L2 Construct: create_spreadsheet_with_charts

sheets google

Description

CreateSpreadsheetWithSheetsAndCharts creates a new Google Sheets spreadsheet with multiple sheets and charts. It initializes the spreadsheet with the specified name, creates individual sheets based on the provided data, populates each sheet with the corresponding tabular data, creates charts, and returns the ID, name, URL of the newly created spreadsheet, and the IDs of the created charts.

L2 Data

Provider: google
Module: gsheets
Action: create_spreadsheet_with_charts

Example Step

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

Input

Example

{
  "spreadsheet_name": "Q2 2023 Financial Report",
  "sheets": {
    "Revenue": {
      "headers": ["Month", "Product A", "Product B", "Total"],
      "rows": [
        ["April", 10000, 15000, 25000],
        ["May", 12000, 18000, 30000],
        ["June", 15000, 20000, 35000]
      ]
    }
  },
  "charts": {
    "Revenue Chart": {
      "type": "LINE",
      "sheet_name": "Revenue",
      "data_range": {
        "start_row": 0,
        "end_row": 3,
        "start_col": 0,
        "end_col": 3
      },
      "series_range": {
        "start_row": 5,
        "end_row": 10,
        "start_col": 0,
        "end_col": 3
      },
      "title": "Q2 2023 Revenue"
    }
  }
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/create-spreadsheet-with-sheets-and-charts-input",
  "$ref": "#/$defs/CreateSpreadsheetWithSheetsAndChartsInput",
  "$defs": {
    "Chart": {
      "properties": {
        "type": {
          "type": "string"
        },
        "sheet_name": {
          "type": "string"
        },
        "data_range": {
          "$ref": "#/$defs/Range"
        },
        "series_range": {
          "$ref": "#/$defs/Range"
        },
        "title": {
          "type": "string"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["type", "sheet_name", "data_range", "series_range", "title"]
    },
    "CreateSpreadsheetWithSheetsAndChartsInput": {
      "properties": {
        "spreadsheet_name": {
          "type": "string"
        },
        "sheets": {
          "additionalProperties": {
            "$ref": "#/$defs/Table"
          },
          "type": "object"
        },
        "charts": {
          "additionalProperties": {
            "$ref": "#/$defs/Chart"
          },
          "type": "object"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["spreadsheet_name", "sheets", "charts"]
    },
    "Range": {
      "properties": {
        "start_row": {
          "type": "integer"
        },
        "end_row": {
          "type": "integer"
        },
        "start_col": {
          "type": "integer"
        },
        "end_col": {
          "type": "integer"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["start_row", "end_row", "start_col", "end_col"]
    },
    "Table": {
      "properties": {
        "headers": {
          "items": {
            "type": "string"
          },
          "type": "array"
        },
        "rows": {
          "items": {
            "items": true,
            "type": "array"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["headers", "rows"]
    }
  }
}

Output

Example

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "spreadsheet_name": "Q2 2023 Financial Report",
  "spreadsheet_url": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "chart_ids": {
    "Revenue Chart": 1234567890
  }
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/create-spreadsheet-with-sheets-and-charts-output",
  "$ref": "#/$defs/CreateSpreadsheetWithSheetsAndChartsOutput",
  "$defs": {
    "CreateSpreadsheetWithSheetsAndChartsOutput": {
      "properties": {
        "spreadsheet_id": {
          "type": "string"
        },
        "spreadsheet_name": {
          "type": "string"
        },
        "spreadsheet_url": {
          "type": "string"
        },
        "chart_ids": {
          "additionalProperties": {
            "type": "integer"
          },
          "type": "object"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": [
        "spreadsheet_id",
        "spreadsheet_name",
        "spreadsheet_url",
        "chart_ids"
      ]
    }
  }
}

Create Spreadsheet Get Sheet