DenchClaw + Google Sheets: Export and Sync Data

Google Sheets is where most teams do their reporting. DenchClaw stores your CRM data in a local DuckDB file. Connecting the two gives you the best of both worlds: a fast, local CRM with the collaborative reporting layer your team already knows.

Here's how to export from DenchClaw to Google Sheets, import data back in, build live dashboards, and decide when to use each tool.

The Two Integration Paths#

There are two ways to connect DenchClaw and Google Sheets:

1. CSV Export → Import: Pull data from DuckDB, save as CSV, upload to Sheets. Simple, no ongoing sync.
2. gog skill + Google Sheets API: DenchClaw's gog skill connects directly to Google Workspace. Read and write Sheets data without manual file transfers.

For one-off reports, CSV is faster. For ongoing sync or automation, use the gog skill.

Exporting CRM Data to Google Sheets via the gog Skill#

The gog skill wraps the Google Workspace API and is available as a DenchClaw skill. Here's the full workflow.

Step 1: Set up the gog skill#

# In your DenchClaw workspace
npx denchclaw skill install gog

Authenticate with Google (one-time OAuth flow):

npx denchclaw skill run gog auth

Step 2: Export data with a DuckDB query#

First, build the query you want to export. For example, export all leads from the last 30 days:

SELECT 
  name,
  email,
  company_name,
  status,
  created_at::DATE as date_added,
  lead_source
FROM v_people
WHERE status = 'Lead'
  AND created_at >= NOW() - INTERVAL 30 DAYS
ORDER BY created_at DESC;

Step 3: Push to Google Sheets#

npx denchclaw export sheets \
  --query "SELECT name, email, company_name, status, created_at::DATE as date_added FROM v_people WHERE status = 'Lead'" \
  --sheet-id "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms" \
  --tab "Leads" \
  --mode overwrite

The --mode overwrite flag clears the tab before writing. Use --mode append to add rows without clearing existing data.

Step 4: Verify in Sheets#

Open your Google Sheet. The Leads tab should now have fresh data from DuckDB.

Scheduled Exports with DuckDB#

For automated, recurring exports, set up a cron job that queries DuckDB and pushes to Sheets.

Create an export script#

Save this as scripts/export-to-sheets.sh in your workspace:

#!/bin/bash
# Export weekly pipeline report to Google Sheets
 
SHEET_ID="your-sheet-id-here"
 
# Export pipeline by stage
npx denchclaw export sheets \
  --query "
    SELECT 
      status as stage,
      COUNT(*) as count,
      SUM(value) as total_value,
      AVG(value) as avg_value
    FROM v_deals
    GROUP BY status
    ORDER BY total_value DESC
  " \
  --sheet-id "$SHEET_ID" \
  --tab "Pipeline Summary" \
  --mode overwrite
 
# Export recent activities
npx denchclaw export sheets \
  --query "
    SELECT
      name,
      company_name,
      last_activity,
      status,
      owner
    FROM v_people
    WHERE last_activity >= NOW() - INTERVAL 7 DAYS
    ORDER BY last_activity DESC
  " \
  --sheet-id "$SHEET_ID" \
  --tab "Recent Activity" \
  --mode overwrite
 
echo "Export complete: $(date)"

Schedule with cron#

# Run every Monday at 9 AM
0 9 * * 1 /path/to/workspace/scripts/export-to-sheets.sh >> /tmp/sheets-export.log 2>&1

Importing from Google Sheets Back to DenchClaw#

Sometimes your team updates data in Sheets (a list of new leads, event attendees, a conference contact dump) and you need to get it back into DenchClaw.

Step 1: Export from Sheets as CSV#

In Google Sheets: File → Download → Comma Separated Values (.csv)

Or use the gog skill to pull it programmatically:

npx denchclaw skill run gog sheets read \
  --sheet-id "your-sheet-id" \
  --tab "New Contacts" \
  --output /tmp/new-contacts.csv

Step 2: Import into DenchClaw#

npx denchclaw import csv \
  --file /tmp/new-contacts.csv \
  --object people \
  --field-map name:Name,email:Email,company_name:"Company",phone:Phone \
  --dedupe email

The --dedupe email flag checks if an entry with that email already exists before inserting, preventing duplicates.

Step 3: Verify the import#

-- Check the most recent entries
SELECT name, email, company_name, created_at
FROM v_people
ORDER BY created_at DESC
LIMIT 20;

Building Live Dashboards in Google Sheets#

Google Sheets works well as a reporting layer when you push fresh data from DuckDB on a schedule. Here's how to build a dashboard that auto-refreshes.

The dashboard structure#

Set up your Google Sheet with these tabs:

• Raw Data (auto-populated by DenchClaw exports)
• Dashboard (pulls from Raw Data using Sheets formulas)
• Config (holds your targets and thresholds)

Sample DuckDB queries for dashboard data#

Pipeline by stage:

SELECT status, COUNT(*) as deals, SUM(value::DECIMAL) as total
FROM v_deals
GROUP BY status
ORDER BY total DESC;

Conversion rates:

SELECT 
  COUNT(*) FILTER(WHERE status = 'Lead') as leads,
  COUNT(*) FILTER(WHERE status = 'Qualified') as qualified,
  COUNT(*) FILTER(WHERE status = 'Closed Won') as won,
  ROUND(
    COUNT(*) FILTER(WHERE status = 'Closed Won')::DECIMAL / 
    NULLIF(COUNT(*) FILTER(WHERE status = 'Lead'), 0) * 100, 1
  ) as lead_to_close_pct
FROM v_people;

Monthly revenue (if tracking deal close dates):

SELECT 
  DATE_TRUNC('month', close_date::DATE) as month,
  SUM(value::DECIMAL) as revenue,
  COUNT(*) as deals_closed
FROM v_deals
WHERE status = 'Closed Won'
  AND close_date IS NOT NULL
GROUP BY 1
ORDER BY 1 DESC
LIMIT 12;

Push each query to a different tab in your sheet, then build charts and summaries in the Dashboard tab using standard Sheets formulas (=SUM(A:A), =SPARKLINE(...), etc.).

CSV Export Workflow (No API Key Required)#

If you don't want to set up the gog skill, plain CSV export works for most use cases.

Export from DuckDB directly#

# Export to CSV using DuckDB CLI
duckdb /path/to/workspace.duckdb \
  "COPY (SELECT * FROM v_people WHERE status = 'Lead') TO '/tmp/leads.csv' (HEADER, DELIMITER ',')"

Or via DenchClaw:

npx denchclaw export csv --object people --filter "status = 'Lead'" --output leads.csv

Then upload manually to Google Sheets (File → Import).

When to Use Sheets vs DenchClaw's Built-in Views#

The short version: DenchClaw is your system of record. Sheets is your reporting and sharing layer. Don't use Sheets for data entry — it turns into a mess. Use DenchClaw for that, then export when you need to share.

FAQ#

Does the gog skill require a Google Cloud project? Yes, you need to create a Google Cloud project and enable the Sheets API to get OAuth credentials. The gog skill's README walks through this setup. It's a one-time 10-minute process.

Can I sync data bidirectionally (DenchClaw ↔ Sheets)? Bidirectional sync is technically possible but creates conflict risks. The recommended pattern is DenchClaw as the source of truth, with Sheets as read-only output. If your team needs to update data in Sheets and push it back, establish a clear "import on Mondays" workflow rather than continuous sync.

How large a dataset can I export to Sheets? Google Sheets has a 10 million cell limit. For most CRMs, this is plenty. If you're hitting this, export to separate tabs or use Google BigQuery instead.

Can I trigger a Sheets export from a DenchClaw chat command? Yes. Ask DenchClaw: "Export all leads this month to my pipeline sheet" — it'll run the appropriate DuckDB query and push to Sheets using the gog skill, as long as the skill is installed and authenticated.

Will the sheet data update automatically? Only if you schedule the export script with cron (or via DenchClaw's built-in scheduler). Sheets doesn't pull from DuckDB automatically — you have to push on a schedule.

Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →