L2 Hub
Google
gsheets
Copy Map Range

L2 Construct: copy_map_range

sheets google

Description

CopyMapRange copies data from a source Google Sheet to a destination Google Sheet, mapping specified columns and applying filters/orders. It supports:

  1. Copying data between different spreadsheets or sheets within the same spreadsheet
  2. Mapping source columns to destination columns with different names
  3. Applying filters to the source data before copying
  4. Applying orders to the source data before copying
  5. Creating the destination sheet if it doesn't exist
  6. Optionally extending formulas in the destination sheet

This function is useful for data migration, automated reporting, and maintaining synchronized datasets across multiple sheets or spreadsheets.

L2 Data

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

Example Step

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

Input

Example

{
  "src_sheet": {
    "origin": {
      "row": 0,
      "col": 0
    },
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheet_name": "Sheet1",
    "margin": {
      "skip_last_n_rows": 0
    }
  },
  "sheet_operation": {
    "filters": [
      {
        "column": "Department",
        "operator": "EQUALS",
        "value_type": "",
        "value": "Sales"
      }
    ],
    "order_by": [
      {
        "column": "Salary",
        "direction": "DESC"
      }
    ]
  },
  "src_headers": ["Employee ID", "Name", "Department", "Salary", "Start Date"],
  "dest_sheet": {
    "origin": {
      "row": 0,
      "col": 0
    },
    "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheet_name": "Sheet2",
    "margin": {
      "skip_last_n_rows": 0
    }
  },
  "dest_headers": ["Employee ID", "Name", "Department", "Salary", "Start Date"],
  "extend_formulas": true
}

Schema

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://pontus.bpa/pkgs/l2/google/gsheets/copy-map-range-input",
  "$ref": "#/$defs/CopyMapRangeInput",
  "$defs": {
    "CopyMapRangeInput": {
      "properties": {
        "src_sheet": {
          "$ref": "#/$defs/Sheet"
        },
        "sheet_operation": {
          "$ref": "#/$defs/SheetOperation"
        },
        "src_headers": {
          "items": {
            "type": "string"
          },
          "type": "array"
        },
        "dest_sheet": {
          "$ref": "#/$defs/Sheet"
        },
        "dest_headers": {
          "items": {
            "type": "string"
          },
          "type": "array"
        },
        "extend_formulas": {
          "type": "boolean"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": [
        "src_sheet",
        "sheet_operation",
        "src_headers",
        "dest_sheet",
        "dest_headers"
      ]
    },
    "Origin": {
      "properties": {
        "row": {
          "type": "integer"
        },
        "col": {
          "type": "integer"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["row", "col"]
    },
    "Sheet": {
      "properties": {
        "origin": {
          "$ref": "#/$defs/Origin"
        },
        "spreadsheet_id": {
          "type": "string"
        },
        "sheet_name": {
          "type": "string"
        },
        "margin": {
          "skip_last_n_rows": {
            "type": "integer"
          },
          "max_empty_rows": {
            "type": "integer"
          }
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": [
        "origin",
        "spreadsheet_id",
        "sheet_name"
        ]
    },
    "SheetFilter": {
      "properties": {
        "column": {
          "type": "string"
        },
        "operator": {
          "type": "string"
        },
        "value_type": {
          "type": "string"
        },
        "value": true
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["column", "operator", "value_type"]
    },
    "SheetOperation": {
      "properties": {
        "filters": {
          "items": {
            "$ref": "#/$defs/SheetFilter"
          },
          "type": "array"
        },
        "order_by": {
          "items": {
            "$ref": "#/$defs/SheetOrderBy"
          },
          "type": "array"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["filters", "order_by"]
    },
    "SheetOrderBy": {
      "properties": {
        "column": {
          "type": "string"
        },
        "direction": {
          "type": "string"
        }
      },
      "additionalProperties": false,
      "type": "object",
      "required": ["column", "direction"]
    }
  }
}

Output

Example

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

Schema

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