Google Sheets is a popular Smart Connector in NinjaCat, but integration issues can occur due to formatting, permissions, or sync configuration. This guide provides quick fixes for the most common Google Sheets problems.

📘

Getting Started

For step-by-step instructions on setting up your first Google Sheets connection, see Setup Google Sheets.

📘

Understanding Google Sheets Requirements

For requirements, best practices, and platform limits, see Google Sheets Performance & Troubleshooting.

📘

Important: Google Sheets data syncs are full replacements, not incremental updates. Each sync completely refreshes your dataset with the current data from your sheet.

Before You Troubleshoot: Key Google Sheets Concepts

Understanding these fundamentals will help you diagnose issues faster:

Empty Row Behavior:

• NinjaCat reads consecutive rows until the first empty row
• A single empty row at row 50 means only rows 1-49 are imported
• This is the #1 cause of missing data

Consistency Requirement:

• Column headers, order, and labels must stay exactly the same after initial setup
• Any structural changes require using Refresh Columns
• See Requirements & Best Practices for details

Google Platform Limits:

• 20MB file size maximum
• 400,000 total cells maximum
• 256 columns per sheet maximum
• See Platform Limitations for details

Sync Behavior:

• Each sync is a full replacement, not incremental
• All data is refreshed from the Google Sheet
• Previous data is completely replaced

If you've confirmed your sheet meets these requirements and you're still having issues, use the troubleshooting sections below.

Quick Troubleshooting Flowchart

Use this decision tree to identify your issue:

1. Is sync status "Complete" in NinjaCat?

   • NO → Check connection errors (see Error 4 below)
   • YES → Continue to step 2

2. Can you see data in the Google Sheet itself?

   • NO → Problem is with the sheet, not NinjaCat
   • YES → Continue to step 3

3. Are there any empty rows in your data?

   • YES → See Error 1 below — NinjaCat stops reading at the first empty row
   • NO → Continue to step 4

4. Are column headers text-only (no numbers or symbols)?

   • NO → Add text prefix to headers (see Error 2 below)
   • YES → Continue to step 5

5. Do date columns use YYYY-MM-DD format?

   • NO → Reformat dates or use Date Override Format (see Error 1 below)
   • YES → Continue to step 6

6. Has the sheet structure changed since initial mapping?

   • YES → Refresh columns in Data Cloud (see Error 1 below)
   • NO → Contact Support with screenshots

Error 1: "Sync Complete" But No Data in Dataset

Symptoms:

• Dataset sync status shows "Completed"
• Sync History shows "X rows added"
• But Explore view shows zero rows OR missing columns

Common Causes and Fixes:

📘

Date Override Format Alternative: Instead of reformatting dates in your Google Sheet, you can configure NinjaCat to parse dates in different formats. Go to your dataset settings > Advanced tab > Date Override Format field and specify the format (uses Snowflake date format syntax).

Step-by-Step Fix:

1. Open your Google Sheet in a browser
2. Remove all empty rows from your data range (not just at the top)
3. Verify data is visible and headers are in Row 1
4. Go to NinjaCat > Data Cloud > Connectors > Your Google Sheets Connector
5. Select Datasets > Your Dataset
6. Click Refresh Columns to update the structure
7. Click Sync Now to test

📘

Sync Now vs. Manually Sync Data: Be aware of two different sync actions:

• Sync Now — Runs a normal sync to refresh your data
• Manually Sync Data... (in More Actions menu) — Deletes all existing data and does a fresh backfill. Use with caution!

Error 2: Columns Not Appearing in Dataset

Symptoms:

• You have 10 columns in Google Sheets
• Only 7 columns appear in NinjaCat dataset
• Missing columns are often purely numeric (e.g., "2024", "123")

Root Cause:
NinjaCat requires column headers to be text-based. Purely numeric headers (e.g., "2024", "123") are not recognized as valid field names.

How to Fix:

In Google Sheets, add a text prefix to numeric headers.

Before (doesn't work):

Campaign | 2024 | 2025 | 123

After (works):

Campaign | Year_2024 | Year_2025 | ID_123

Other invalid headers to avoid:

• Special characters: # @ $ % & *
• Starting with a number: 1_Campaign, 2024_Budget
• Only symbols: --- or ***

After fixing headers:

1. Save the Google Sheet
2. Go to NinjaCat > Data Cloud > Connectors > Your Connector > Datasets > Your Dataset
3. Click Refresh Columns to pick up the new headers
4. Click Sync Now
5. Columns should now appear

📘

Pro Tip: Use descriptive headers like "Impressions_Jan", "Clicks_Feb", "Cost_2024" instead of just "Jan", "Feb", "2024".

Error 3: Zero Values in Report Despite Data in Sheet

Symptoms:

• Google Sheet has values like "1,234" or "$500"
• NinjaCat dataset shows "0" or blank

Root Cause:
Number formatting in Google Sheets is interpreted as text by NinjaCat if it contains commas, currency symbols, or other non-numeric characters.

How to Fix:

In Google Sheets, remove formatting from numeric columns:

1. Select the column (click column letter)
2. Click Format > Number > Plain Text OR Number
3. Remove currency symbols ($ €), commas (,), and percentage signs (%)

Before (interpreted as text):

Campaign | Cost     | Conversions
Brand    | $1,234   | 5%

After (interpreted as numbers):

Campaign | Cost  | Conversions
Brand    | 1234  | 5

For currency/percentage formatting:

• Store raw numbers in Google Sheets (e.g., 1234, 0.05)
• Apply formatting in NinjaCat Template Builder (widget display settings)

After fixing:

1. Save Google Sheet
2. Go to NinjaCat > Dataset > Click Sync Now
3. Values should now display correctly

📘

For more information on troubleshooting zero values across all data sources, see Troubleshooting Zero Values in Reports and Dashboards.

Error 4: "Connection Error" During Sync

Symptoms:

• Sync status shows "Failed"
• Error message: "Unable to access Google Drive" or "Connection timeout"

Common Causes and Fixes:

Error 5: Data Not Updating After Manual Sheet Edits

Symptoms:

• You edited values in Google Sheet
• NinjaCat dataset still shows old values
• Sync status shows "Completed"

Root Cause:
Dataset not set to auto-sync OR sync happened before you made edits.

How to Fix:

Option 1: Manual Sync (Immediate)

1. Go to Data Cloud > Connectors > Your Connector > Datasets > Your Dataset
2. Click Sync Now button
3. Wait for sync to complete (usually 1-2 minutes)
4. Refresh your report/dashboard

Option 2: Enable Auto-Sync (Ongoing)

1. Go to Data Cloud > Connectors > Your Connector > Datasets > Your Dataset
2. Click Settings tab
3. Scroll to Schedule Sync
4. Select:
   • Once a day — Select the hour for daily refresh
   • More than once a day — Choose interval: Every 15 Minutes, Every 30 Minutes, Every Hour, or Every 2 Hours
5. Click Save

📘

Remember: Each sync is a full replacement of all data in the dataset, not an incremental update. The entire dataset is refreshed with current Google Sheet data.

Error 6: Special Characters Causing Import Errors

Symptoms:

• Sync fails with error message mentioning "encoding" or "invalid character"
• Dataset has garbled text or missing rows

Root Cause:
Google Sheets contains emojis, special symbols, or non-UTF-8 characters that NinjaCat can't parse.

Characters to Avoid:

• Emojis: 😀 🚀 ❤️ etc.
• Smart quotes: " " ' ' (use regular quotes: " ' instead)
• Curly apostrophes: ' (use straight: ')
• En/em dashes: – — (use regular dash: -)
• Non-breaking spaces and other invisible characters

How to Fix:

In Google Sheets, use Find and Replace to remove problematic characters:

1. Press Ctrl+H (Windows) or Cmd+Shift+H (Mac)
2. Search for: emoji or special character
3. Replace with: leave blank or use regular character
4. Click Replace all

Common replacements:

• Smart quotes → Regular quotes
• Emojis → Text description (e.g., "thumbs up" instead of 👍)
• Curly apostrophes → Straight apostrophes

After cleaning:

1. Save Google Sheet
2. Go to NinjaCat > Dataset > Click Sync Now

📘

Pro Tip: Copy-pasting from Word or websites often introduces special characters. Type directly in Google Sheets when possible.

Error 7: Large Dataset Performance Issues

Symptoms:

• Sync takes a very long time
• Reports are slow to load
• Dataset has a very large number of rows

Root Cause:
Google Sheets Smart Connectors work best for small to medium datasets. Performance degradation can occur when approaching Google's platform limits or when datasets become very large.

Check Google's Platform Limits First:

• File size: 20MB maximum
• Total cells: 400,000 cells maximum
• Columns: 256 columns maximum

If you're hitting these limits, you must reduce your data or use an alternative connector.

How to Fix:

Option 1: Filter Data in Google Sheets

Use Google Sheets Filter Views to reduce rows before syncing — for example, only sync last 90 days of data instead of all-time.

Option 2: Split into Multiple Datasets

1. Create separate sheets for different date ranges or categories
2. Example: "Sheet_2024", "Sheet_2025", "Sheet_2026"
3. Create separate NinjaCat datasets for each
4. Combine in reports using multiple data sources

Option 3: Use a Different Smart Connector

For very large datasets, consider these alternatives:

• Email Smart Connector — Files under 20MB, automatic delivery via email
• Amazon S3 — Better for large files
• FTP/URL Connectors — Direct file access
• SQL/BigQuery Connectors — For database-stored data
• Snowflake Share — No practical row limit

See When to Consider Alternatives to Google Sheets for detailed comparison and setup guides for each alternative.

Error 8: Formulas Not Syncing Correctly

Symptoms:

• Google Sheet cell contains a formula like =SUM(A1:A10)
• NinjaCat dataset shows unexpected values or errors

Root Cause:
Formula errors in Google Sheets (like #REF!, #VALUE!, #N/A) will cause data issues. Also, if cells are formatted as "Plain Text" before entering a formula, the formula won't calculate.

How to Fix:

In Google Sheets, fix formula errors:

1. Open the sheet and check for formula errors: #REF!, #VALUE!, #N/A
2. Fix any broken formulas or cell references
3. If cells show formula text instead of values: The cell may be formatted as "Plain Text" — change to "Automatic" or "Number" format, then re-enter the formula
4. Convert formulas to values (if formulas aren't needed in the sheet):
   • Select cells with formulas
   • Right-click > Copy
   • Right-click > Paste special > Values only
   • This replaces formulas with their calculated results

After fixing:

1. Save the Google Sheet
2. Go to NinjaCat > Dataset > Click Sync Now to test

Still Having Issues?

If you've tried the solutions above and still encounter problems, contact NinjaCat Support with:

• Google Sheet URL (make sure support can access it)
• Dataset name in NinjaCat
• Screenshot of sync error (from Sync History tab)
• Screenshot of Google Sheet showing headers and first few rows
• What you've tried from this troubleshooting guide

Expected Response Time: 24-48 hours

Best Practices for Google Sheets Integration

To prevent common issues, follow these guidelines:

Use consistent header naming:

• Always start with a letter
• Use underscores instead of spaces (e.g., "Campaign_Name" not "Campaign Name")
• Avoid special characters and purely numeric headers

Format data before connecting:

• Dates: YYYY-MM-DD format (or use Date Override Format setting)
• Numbers: No commas or currency symbols
• Text: No emojis or special characters

Keep sheet structure stable:

• Don't add/remove columns after initial mapping without using Refresh Columns
• Don't reorder columns
• Keep headers in Row 1
• Ensure there are no empty rows anywhere in your data range

Set appropriate sync schedules:

• Once a day for most use cases
• More than once a day (15min, 30min, 1hr, 2hr intervals) only if data changes frequently
• Manual sync for one-time imports or troubleshooting

Understand sync behavior:

• Each sync is a full replacement, not incremental
• Check Backfill settings (Previous Days to Pull, Earliest Reporting Date) to ensure you're pulling the expected date range
• Be aware of the difference between Sync Now (safe) and Manually Sync Data... (destructive)

Monitor sync status regularly:

• Check Sync History tab weekly
• Set up alerts for failed syncs (if available)
• Use Refresh Columns after any sheet structure changes