Google Sheet Connector Setup

At CorralData, we leverage Google Sheets not as a place for styled dashboards or pre-aggregated reports, but as a lightweight, flexible tool to power ad hoc data sources and database tables for BI. This guide outlines best practices to ensure your synced Google Sheets function smoothly and scalably within our data pipelines.

1. Use a Single Tab for Your Data Source

Why: This keeps your data structure clean and optimized as a single, reliable table. CorralData connects to one tab within a Google Sheet per connection. Avoid using multiple tabs for segmented information or calculations. Create additional Google Sheet connections to sync additional tabs.

2. Table Headings in Row 1

Why: CorralData expects headers in Row 1 to define column names in the resulting table. Ensure your column headings start at row 1 without any title, note, or spacing rows above.

3. No Skipped, Styled, or Merged Rows

Why: Extra formatting or spacing can confuse the sync and schema generation process. Avoid inserting styled rows, merged cells, or leaving gaps. Keep the dataset tight and unstyled.

4. Avoid Aggregation, Subtotal, or Total Rows

Why: These introduce semantic noise and may interfere with analysis or mislead downstream consumers. Maintain raw, atomic data only. Do not include subtotal rows, rollups, or summary calculations. All aggregations & calculations can be handled within CorralData.

5. Always Include a Date Column (Format: YYYY-MM-DD)

Why: A date dimension is essential for time-based filtering in CorralData dashboards. Ensure at least one column contains clean ISO-formatted dates (yyyy-mm-dd). Do not mix text or ambiguous formats within date columns.

6. Appropriately Typed Columns

Why: Ensures proper schema mapping and enables type-specific filters and aggregations. Numbers must be stored as numbers, text as text, and dates as dates in the sheet — not as formatted strings.

7. Use Column-Wide Named Ranges

Why: Allows the sync to auto-expand with new rows of data as the sheet grows. Instead of defining named ranges like A1:A1000, use column-wide references like A:A. This ensures CorralData captures all present and future entries.

8. Use Full State Names for Geographic Fields

Why: Supports compatibility with CorralData’s map visualizations. Use full names like "California" instead of abbreviations like "CA" when referencing U.S. states.

9. Avoid or Clean Up Formula Outputs

Why: Sheets designed for BI ingestion should contain clean, flat data without #N/A, #VALUE!, or formula-based errors. If formulas are used, ensure all outputs are valid. Prefer pasting values using CTRL + SHIFT + V to remove formulas altogether. This prevents sync issues and ensures compatibility with downstream BI tools.

10. No Formatting Characters in Empty Values (ie. N/A or -)

Why: Sheets must handle empty cells / values in a consistent way to ensure that data can be properly aggregated.

Don't use values like N/A of - to denote empty values. Corral will handle all cases of empty or NULL values.

Summary

These practices help ensure your Google Sheet behaves like a well-structured, scalable database table rather than a styled or pre-aggregated report. When done right, this setup enables faster insights, easier debugging, and smoother syncing across our analytics ecosystem.