L2 Construct: get_spreadsheet

sheets google

Description

GetSpreadsheet retrieves data from a Google Sheets spreadsheet using the provided spreadsheet ID. It returns all the data from the spreadsheet as a collection of tables, along with the corresponding sheet names. This function is useful for fetching the entire contents of a spreadsheet, including data from multiple sheets, in a structured format.

The function returns:

The spreadsheet ID
A list of sheet names within the spreadsheet
A list of tables, where each table corresponds to a sheet in the spreadsheet and contains the sheet's data

This comprehensive retrieval allows for easy access and manipulation of all data within the specified Google Sheets spreadsheet.

L2 Data

Provider: google
Module: gsheets
Action: get_spreadsheet

Example Step

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

Input

Example

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/get-spreadsheet-input",
  "$ref": "#/$defs/GetSpreadsheetInput",
  "$defs": {
    "GetSpreadsheetInput": {
      "properties": {
        "spreadsheet_id": {
          "type": "string"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["spreadsheet_id"]
    }
  }
}

Output

Example

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheet_names": ["Employee Data", "Financial Summary"],
  "tables": [
    {
      "name": "Employee Data",
      "description": "Employee data for the company",
      "headers": ["Employee ID", "Name", "Department", "Salary", "Start Date"],
      "rows": [
        ["EMP001", "John Smith", "Marketing", 65000, "2022-03-15"],
        ["EMP002", "Sarah Johnson", "Engineering", 80000, "2021-09-01"],
        ["EMP003", "Michael Brown", "Sales", 72000, "2023-01-10"]
      ]
    },
    {
      "name": "Financial Summary",
      "description": "Financial summary for the company",
      "headers": ["Quarter", "Revenue", "Expenses", "Profit", "Growth Rate"],
      "rows": [
        ["Q1 2023", 500000, 350000, 150000, 0.05],
        ["Q2 2023", 550000, 380000, 170000, 0.07],
        ["Q3 2023", 600000, 400000, 200000, 0.09]
      ]
    }
  ],
  "error": {
    "is_error": false
  }
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/get-spreadsheet-output",
  "$ref": "#/$defs/GetSpreadsheetOutput",
  "$defs": {
    "GetSpreadsheetOutput": {
      "properties": {
        "spreadsheet_id": {
          "type": "string"
        },
        "sheet_names": {
          "items": {
            "type": "string"
          },
          "type": "array"
        },
        "tables": {
          "items": {
            "$ref": "#/$defs/Table"
          },
          "type": "array"
        },
        "error": {
          "$ref": "#/$defs/HandlerError"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["spreadsheet_id", "sheet_names", "tables", "error"]
    },
    "HandlerError": {
      "properties": {
        "is_error": {
          "type": "boolean"
        },
        "errors": {
          "items": {
            "type": "string"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["is_error"]
    },
    "Table": {
      "properties": {
        "name": {
          "type": "string"
        },
        "description": {
          "type": "string"
        },
        "headers": {
          "items": {
            "type": "string"
          },
          "type": "array"
        },
        "rows": {
          "items": {
            "items": true,
            "type": "array"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["headers", "rows"]
    }
  }
}

Get Sheet Get Spreadsheet Data