Google Sheets CRM: 4 Ways I've Actually Done It (with Apps Script Code)

Every SMB I've worked with treats a Google Sheet as their first real database — leads, deals, inventory, campaign tracking. Then they buy a CRM and ask "how do we get the sheet into HubSpot/Pipedrive/Zoho?" There are four ways I've actually shipped this, and which one is right depends on volume, two-way needs, and how much code you want to maintain.

Here's the decision tree I use:

Need two-way sync?
├── No  → Is your CRM's native Sheets connector free on your tier?
│        ├── Yes → Use that (15-min setup)
│        └── No  → Make.com Free tier (~300 rows/month free)
└── Yes → How much volume?
         ├── < 1k rows/day → Google Apps Script (free, requires code)
         ├── 1k-10k/day    → Make.com Core ($10.59/mo) or n8n self-hosted
         └── > 10k/day     → Custom API integration (eng. effort)

Method 1: Native CRM marketplace integration

HubSpot ships a Google Sheets workflow action. Pipedrive has Marketplace connectors (Coupler.io, Surveyform). Zoho uses Zoho Flow. Salesforce has AppExchange options.

What you get: 15-minute setup, the CRM handles auth, field mapping uses the CRM's property picker.

What you don't get: two-way sync on most free tiers (HubSpot wants Operations Hub Starter at $20/mo). Conditional filtering is rare. Errors are silent — failed rows go to a log nobody reads.

Use this only when you need one-way "new row → new contact" and your CRM tier includes the integration. Otherwise jump to Method 2.

Method 2: Automation platforms (Zapier / Make / n8n)

The path of least resistance for two-way sync with logic. In n8n, a Sheet-to-HubSpot workflow is roughly this shape:

{
  "nodes": [
    {
      "name": "Google Sheets Trigger",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "parameters": {
        "event": "rowAdded",
        "documentId": "your-sheet-id",
        "sheetName": "Leads",
        "pollTimes": { "item": [{ "mode": "everyMinute" }] }
      }
    },
    {
      "name": "Filter",
      "type": "n8n-nodes-base.if",
      "parameters": {
        "conditions": {
          "string": [{ "value1": "={{ $json.email }}", "operation": "isNotEmpty" }]
        }
      }
    },
    {
      "name": "HubSpot",
      "type": "n8n-nodes-base.hubspot",
      "parameters": {
        "resource": "contact",
        "operation": "upsert",
        "email": "={{ $json.email }}",
        "additionalFields": {
          "firstName": "={{ $json.firstname }}",
          "lifecyclestage": "lead"
        }
      }
    }
  ]
}

15-minute build. The trigger node polls every minute. The IF filters out empty rows. The HubSpot node uses upsert on email, which kills the duplicate-creation problem on retry. Make.com and Zapier work the same way with different UIs.

Costs in 2026: Make Free covers ~300 rows/month, Make Core is $10.59/mo for 10k ops. n8n self-hosted is free on a $6 VPS. Zapier Free is useless for this (100 tasks, no filters, no multi-step).

Method 3: Google Apps Script (the free code path)

When you have someone on the team who writes JavaScript, this is the cheapest production option. Code lives inside the Sheet, runs on Google's infra, no external dependencies.

Here's a complete Apps Script that pushes new/edited rows to HubSpot with upsert by email — drop it into your Sheet's Apps Script editor, set the HUBSPOT_TOKEN script property, and add a Synced At column:

// Open the Sheet → Extensions → Apps Script → paste below.
// Project Settings → Script Properties → add HUBSPOT_TOKEN (a Private App token).

const HUBSPOT_BASE = 'https://api.hubapi.com';

function getToken() {
  return PropertiesService.getScriptProperties()
    .getProperty('HUBSPOT_TOKEN');
}

// Trigger this from a time-based trigger or onEdit.
function syncRowsToHubspot() {
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Leads');

  // Assumes columns: A=email, B=firstname, C=lastname,
  // D=company, E=lifecyclestage, F=synced_at
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  const data = sheet.getRange(2, 1, lastRow - 1, 6).getValues();

  data.forEach((row, i) => {
    const [email, firstname, lastname, company, stage, syncedAt] = row;
    if (!email || syncedAt) return; // skip empty / already-synced

    const result = upsertContact({
      email, firstname, lastname, company,
      lifecyclestage: stage || 'lead'
    });

    if (result.ok) {
      // Mark as synced (column F, row i+2)
      sheet.getRange(i + 2, 6).setValue(new Date().toISOString());
    } else {
      Logger.log(`Row ${i + 2} failed: ${result.error}`);
    }
  });
}

function upsertContact(props) {
  const url = `${HUBSPOT_BASE}/crm/v3/objects/contacts/` +
              `${encodeURIComponent(props.email)}?idProperty=email`;

  // Try PATCH first (update if exists)
  const patchOptions = {
    method: 'patch',
    contentType: 'application/json',
    headers: { Authorization: `Bearer ${getToken()}` },
    payload: JSON.stringify({ properties: props }),
    muteHttpExceptions: true
  };

  let res = UrlFetchApp.fetch(url, patchOptions);
  if (res.getResponseCode() === 200) return { ok: true };

  // 404 → contact doesn't exist, POST to create
  if (res.getResponseCode() === 404) {
    const postOptions = {
      ...patchOptions,
      method: 'post',
      payload: JSON.stringify({ properties: props })
    };
    res = UrlFetchApp.fetch(
      `${HUBSPOT_BASE}/crm/v3/objects/contacts`,
      postOptions
    );
    if (res.getResponseCode() === 201) return { ok: true };
  }

  return {
    ok: false,
    error: `${res.getResponseCode()}: ${res.getContentText()}`
  };
}

Wire it to a trigger. In the Apps Script editor: clock icon → Add Trigger → choose syncRowsToHubspot → time-based → every 5 minutes. Now your sheet syncs automatically.

Known limits: 6-min execution cap per run, ~90 minutes total runtime/day on free Google accounts, 20,000 UrlFetch calls/day. For 1,000 rows/day this is fine. For 5,000+ rows, batch by synced_at window and process in chunks.

Method 4: Custom API integration

When volume is real (10k+ rows/day) or you need conflict resolution on two-way sync, you build a small backend. A minimal HubSpot → Sheets webhook receiver in Node.js with signature verification:

import express from 'express';
import crypto from 'crypto';
import { google } from 'googleapis';

const app = express();

// Raw body needed for signature verification
app.post('/hubspot-webhook',
  express.raw({ type: 'application/json' }),
  async (req, res) => {
    const sig = req.headers['x-hubspot-signature-v3'];
    const ts = req.headers['x-hubspot-request-timestamp'];
    const body = req.body.toString('utf8');

    const computed = crypto
      .createHmac('sha256', process.env.HUBSPOT_CLIENT_SECRET)
      .update(`POST${req.originalUrl}${body}${ts}`)
      .digest('base64');

    if (sig !== computed) return res.status(401).end();

    const events = JSON.parse(body);
    for (const ev of events) {
      if (ev.subscriptionType === 'contact.propertyChange') {
        await pushUpdateToSheet(ev.objectId, ev.propertyName, ev.propertyValue);
      }
    }
    res.status(200).end();
  }
);

app.listen(3000);

The other half — pushUpdateToSheet — uses the Sheets API to find the row by HubSpot contact ID and update the column matching the property name. With 80–100 lines total, you've got two-way sync that handles thousands of records/day.

Trade-offs: 1–3 weeks of initial build, OAuth refresh logic, observability, the bus factor problem. Don't go here until automation platforms have failed you.

Gotchas every method shares

Duplicates. Re-running creates duplicate contacts unless you upsert by email or an external ID. The Apps Script above does the PATCH-then-POST dance; n8n's HubSpot node has an "upsert" mode; Make has a "search then create or update" pattern. Use one.

Types. Sheets stores everything as a string. CRMs have types — number, date, boolean, dropdown enum. 5/4/2026 is May 4 in US locale, April 5 elsewhere. A blank cell is empty string "", not null. Normalize before the API call.

Rate limits. HubSpot allows 100 req/10s on standard plans. Pipedrive's are tighter (10 req/2s in some endpoints). Add Utilities.sleep(100) between Apps Script calls when batching, or use the platform's built-in throttling.

OAuth expiry. Tokens die. Native integrations refresh silently. Apps Script using a Private App token in HubSpot doesn't expire (yay). Custom Node.js code with OAuth needs refresh logic — and the first sign it's broken is the sync just stops working with no alert. Wire failure notifications to Slack or email.

Polling latency. Apps Script time-trigger minimum is every minute. Automation platforms poll every 1–15 minutes depending on tier. Webhooks are the only sub-second option, and Google Sheets doesn't emit them natively (onEdit runs server-side but isn't a webhook). For "true real-time," you need the CRM's webhook in the other direction, plus a way to push edits back.

TL;DR

One-way, low volume, native integration free? Use it.
Two-way, low-medium volume, no code? Make.com or n8n.
Have a JS-comfortable team and want $0 ongoing? Apps Script (copy the snippet above).
10k+ rows/day or complex conflict resolution? Custom backend.

Most SMBs land at Make.com or Apps Script. Custom API is reserved for when you've genuinely outgrown both.

Originally published on trackstack.tech with a fuller decision framework and FAQ.

推荐订阅源

DEV Community