Provider Hub
Google
gsheets
Normalize Formulas

L2 Construct: normalize_formulas

sheets google

Description

NormalizeFormulas adjusts the length of formulas in the sheet to match that of the existing data of the sheet.

The function requires:

  • A valid spreadsheet ID
  • A name for the sheet where formulas will be normalized
  • An optional origin point for the sheet (defaults to top-left if not provided)

It returns the spreadsheet ID, the name of the modified sheet, and the total length of the new formulas.

L2 Data

  1. Provider: google
  2. Module: gsheets
  3. Action: normalize_formulas

Example Step

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

Input

Example

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheet_name": "Calculations",
  "sheet_origin": {
    "row": 0,
    "col": 0
  }
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/normalize-formulas-input",
  "$ref": "#/$defs/NormalizeFormulasInput",
  "$defs": {
    "Coordinate": {
      "properties": {
        "row": {
          "type": "integer"
        },
        "col": {
          "type": "integer"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["row", "col"]
    },
    "NormalizeFormulasInput": {
      "properties": {
        "spreadsheet_id": {
          "type": "string"
        },
        "sheet_name": {
          "type": "string"
        },
        "sheet_origin": {
          "$ref": "#/$defs/Coordinate"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["spreadsheet_id", "sheet_name"]
    }
  }
}

Output

Example

{
  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheet_name": "Calculations",
  "new_formulas_length": 100,
  "error": {
    "is_error": false
  }
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/normalize-formulas-output",
  "$ref": "#/$defs/NormalizeFormulasOutput",
  "$defs": {
    "HandlerError": {
      "properties": {
        "is_error": {
          "type": "boolean"
        },
        "errors": {
          "items": {
            "type": "string"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["is_error"]
    },
    "NormalizeFormulasOutput": {
      "properties": {
        "spreadsheet_id": {
          "type": "string"
        },
        "sheet_name": {
          "type": "string"
        },
        "new_formulas_length": {
          "type": "integer"
        },
        "error": {
          "$ref": "#/$defs/HandlerError"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": [
        "spreadsheet_id",
        "sheet_name",
        "new_formulas_length",
        "error"
      ]
    }
  }
}
Get Spreadsheet DataSet Cell