Locoia
  • Overview
  • Account and User Settings
    • User types
    • Adding Users
    • Teams
    • Access Permissions
    • 2 Factor Authentication 2FA
    • Versioning and Snapshots
    • Activity Log
  • Reset your Password
  • Invoices and Payments
  • Automation
    • Flow Builder
      • Flow Building Best Practices
      • Jinja Template Language
        • Jinja: (Custom) variables, wildcards and functions
        • Magic Code Samples
      • Connectors & APIs
        • Titles and References
        • Referencing data of objects, lists, arrays - how to pass data dynamically
        • Accessing Objects with JSONPath
        • Merging nested JSON objects
        • Parsing JSONs from String
        • Response Headers & Status Codes
        • Custom Data Fields
        • Wildcard API calls and actions
        • Response cleaning
      • Text Strings, Date & Time, Numbers and Currencies
        • Text and Strings
        • Dates & Time
        • Numbers (Thousand Separators, Currencies)
      • Email-formatting
      • Code Fields
      • Running single Flow steps
      • Flow run data retention, logging, and error notifications
      • Advanced View
      • Dynamic Title
      • Custom Error Handling
      • Error Handling Flows
      • Automatic Pagination
    • Flow Debugger
      • Automatic Retrying
      • Run Flows again
      • Troubleshooting Flows
    • Community Library
  • Connectors & Helpers
    • Connectors
      • Monday.com
      • ActiveCampaign
      • Aircall
      • Allthings
      • Amplitude
      • Animus
      • Assetti
      • Awork
      • AWS RDS Database - How to connect
      • bubble.io
      • Casavi
      • Chargebee
      • CleverReach
      • comgy
      • commercetools
      • Everreal
      • Exact Online
      • Facebook Marketing
      • Fahrländer Partner
      • FastBill
      • FILESTAGE.io
      • Freshdesk
      • Freshsales
      • Google Ads
      • Google Ads Lead Form
      • Google Analytics
      • Google Chat
      • Google Drive
      • Google Sheets
      • Gmail
      • HubSpot
      • Heyflow
      • iDWELL
      • ImmobilienScout24
      • Instagram Ads
      • Intercom
      • klaviyo
      • Kiwi Opening Doors
      • Klenty
      • Klipfolio
      • Kolibri CRM
      • konfipay
      • KUGU
      • Shopify
      • S3 AWS
      • SQS AWS
      • Lambda AWS
      • Learnster
      • lexoffice
      • LineMetrics
      • Linkedin
      • Locoia
      • Notion
      • MailGun
      • Makula
      • Microsoft Dynamics 365
      • Microsoft OneDrive
      • MixPanel
      • MongoDB
      • Odoo
      • OnFleet
      • OnOffice
      • Oracle NetSuite
      • Outbrain
      • Quickbooks
      • Trello
      • PandaDoc
      • Personio
      • Pinterest Ads
      • Pipedrive
      • Plentific
      • PriceHubble
      • relay
      • REALCUBE
      • Sage ERP
      • Salesforce
      • SAP
      • Scoro
      • Seafile
      • sevDesk
      • SharePoint
      • SharpSpring
      • Slack
      • Snapchat Marketing
      • Snowflake
      • Teamleader Focus
      • Teamwork.com
      • Tableau
      • TikTok
      • TinQwise
      • The Trade Desk
      • Twitter
      • Typeform
      • WordPress
      • Xero
      • Youtube
      • Zendesk
      • Zoho CRM
      • Zoom
    • Helpers
      • Scheduler
      • Webhook
      • Dict Helper
      • Spreadsheet Helper
      • REST Helper
      • Boolean Helper
      • Multi Case Helper
      • Looper
      • FTP Helper
      • CSV Helper
      • XLSX Helper
      • Mail Sender
      • Flow Trigger
      • File Storage Helper
      • Terminate Helper
      • Delay Helper
      • SQL Connector
      • PDF Helper
      • Zip Helper
      • Data Warehouse Helper
      • XML Helper
      • Form Helper
      • Arrow
      • Error Arrow
    • Authentication Types Available
      • Setting up authentication
      • OAuth1
      • OAuth2
      • Refreshable token
      • AWS Signature
      • Basic Auth and Other Simple Authentication Methods
      • How are API versioning and API updates handeled?
      • Custom OAuth2 clients (apps)
    • Building Connectors
      • Base Connector Setup
        • Connector Auth Validation
        • GraphQL APIs
        • Rendering with User Input
      • Building Connector Actions
        • Actions Examples
      • Search Automation
      • Pagination Automation
      • Uploading Files in Actions
      • Working with SOAP APIs
    • Super Actions
    • Webhook Trigger for Connectors
    • Data Mapping and Env Variables
  • Embed - White Label Portal
    • Embed Overview
      • 1. Embed Flow
        • 1.1 Creating Embed Flows
        • 1.2 Updating Embed Flows
        • 1.3 Embed Error Handling
        • 1.4 Setting up Callbacks for Integration activation/deactivation
        • 1.5 Setting up Remote search
        • 1.6 Setting up End User logs
      • 2. Configure Embed
        • 2.1 Embed Integration via SSO
        • 2.2 Proprietary connector setup
        • 2.3 Sharing level
        • 2.4 Consent screen
        • 2.5 Account Secrets
        • 2.7 Further settings
      • 3. Integrate Embed
        • 3.1 iframe vs native embed
        • 3.2 Customizing CSS
        • 3.3 Events emitted from iframe to parent window
      • 4. Embed for End User
        • 4.1 Embed Remote Search
        • 4.2 Embed End User Logs
      • 5. Test Embed Configuration
        • Testing example
      • 6. Embed Integrations and Connector Auths
    • Embed FAQs
  • Data and Dashboards
    • Dashboards & Insights
      • Introduction to Dashboards
      • Introduction to Insights
      • Introduction to Data Sources
      • Dashboard Filters
      • Insight Marketplace - Using Pre-Built Insights
      • Writing SQL Queries
      • Useful SQL Examples
      • Charts
        • Line Chart
        • Bar and Horizontal Bar Chart
        • Stat Card
        • Pie Chart
        • Gauge Chart
        • Donut Chart
        • Stacked Bar, Horizontal Stacked Bar, and Normalized Horizontal Stacked Bar
        • Multiple Line Chart
        • Pivot Table
        • Map Chart
  • Best Practice Guides
    • Integration Best Practices
    • Integration Check List
    • CSV Files in Excel
    • Multi-Tenant Flows
    • On-Premise Integrations
    • Database Connection Setup
    • Data and General Security
    • Using Tags
    • FAQ
  • API
    • Locoia API Authentication - Personal Access Token
    • Create Connector Authentication
  • Contact us
  • Status of Service
  • Data Privacy
  • Imprint
Powered by GitBook
On this page
  • Overview
  • Infer data types
  • Actions
  • 1. Group columns by
  • 2. Insert column
  • 3. Join
  • 4. Query spreadsheet
  • 5. Remove duplicates
  • 6. Remove specific labels
  • 7. Return first n rows
  • 8. Sort by

Was this helpful?

  1. Connectors & Helpers
  2. Helpers

Spreadsheet Helper

Used for spreadsheet and SQL like operations, like grouping or joining data.

PreviousDict HelperNextREST Helper

Last updated 1 year ago

Was this helpful?

Overview

SpreadSheet Helper is a tool designed for performing operations similar to spreadsheets and SQL like operations, such as data grouping and joining. You need to specify the data you want to work with by referencing it.

Infer data types

To keep data type consistent, you can choose whether to enable data type inference or leave it disabled (it's turned off by default).

If this option is turned on, the Helper will try to automatically infer the data type of each column. This might cause unexpected results when working with zip codes and other numbers that can start with 0.

Otherwise, the data types will be left as provided by the source.

Actions

1. Group columns by

Group a spreadsheet or CSV file and apply different aggregates and properties.

The available aggregation functions are:

  • mean

  • sum

  • size

  • count

  • std

  • var

  • sem

  • first

  • last

  • min

  • max

  • median

For example, let's find the lowest price among devices. To do this we need to group columns by Product Result columns must use the following syntax: <column name or index>;<aggregation function><new column name (optional)> In the example Price - column name min - aggregation function lowest_price - new column name (optional)

2. Insert column

3. Join

This comes in handy if you want to map different tables based on common column data.

4. Query spreadsheet

Special cases not covered by SQLite:

5. Remove duplicates

6. Remove specific labels

7. Return first n rows

8. Sort by

Insert a column into a spreadsheet for further details

Join multiple spreadsheets or tables with a common column (VLookup) for further details.

The database is purely in memory and SQLite is used. See SQLite functions .

- can be achieved like here

Remove duplicates from a spreadsheet for further details

Remove a specified row or column for further details

Return the first n rows of a spreadsheet for further details

Sort the spreadsheet by one or more columns - for further details

- Pandas Docs
- Pandas Docs
here
Full outer join
- Pandas Docs
- Pandas Docs
- Pandas Docs
Pandas Docs
Infer data types option in all relevant actions