Sync Leads to Google Sheets

This guide shows how to send TailorTalk webhook data to Google Sheets without any third-party extension.

You will set up:

• A Google Apps Script Web App endpoint
• TailorTalk webhook URL with trigger
• Automatic upsert using lead_contact as the primary key

Prerequisites

• A Google Sheet with a tab named Sheet1 (or update the script tab name)
• Access to your TailorTalk Agent settings
• Webhook trigger enabled in TailorTalk (for example: every_message)

Step 1: Create Your Google Sheet

1. Open Google Sheets and create a new file.
2. Keep the first tab name as Sheet1 (recommended for this script).
3. Leave row 1 empty. The script will auto-create headers.

Step 2: Open Apps Script

1. In your sheet, go to Extensions -> Apps Script.
2. Replace the default script with the code below.

function doPost(e) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const payload = JSON.parse(e.postData.contents || '{}');

  // Use fields inside "data" key (fallback to full payload)
  const rawData = (payload && typeof payload.data === 'object' && payload.data !== null)
    ? payload.data
    : payload;

  // Flatten nested objects: lead_attributes.email_id -> lead_attributes_email_id
  const data = flattenObject(rawData);

  const keyField = 'lead_contact';
  const keyValue = String(data[keyField] || '').trim();

  // Existing headers
  let headers = sh.getLastColumn() > 0
    ? sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0].filter(Boolean)
    : [];

  // Ensure standard columns
  ['updated_at', keyField].forEach((h) => {
    if (!headers.includes(h)) headers.push(h);
  });

  // Add new incoming fields as new columns
  Object.keys(data).forEach((k) => {
    if (!headers.includes(k)) headers.push(k);
  });

  // Write headers
  sh.getRange(1, 1, 1, headers.length).setValues([headers]);

  const colMap = Object.fromEntries(headers.map((h, i) => [h, i]));
  const keyCol = colMap[keyField] + 1;
  const lastRow = sh.getLastRow();

  let targetRow = -1;
  if (keyValue && lastRow > 1) {
    const existingKeys = sh.getRange(2, keyCol, lastRow - 1, 1).getValues().flat();
    const idx = existingKeys.findIndex(v => String(v || '').trim() === keyValue);
    if (idx !== -1) targetRow = idx + 2;
  }

  if (targetRow !== -1) {
    // Update existing row
    const row = sh.getRange(targetRow, 1, 1, headers.length).getValues()[0];
    Object.entries(data).forEach(([k, v]) => {
      row[colMap[k]] = v ?? '';
    });
    row[colMap['updated_at']] = new Date();
    sh.getRange(targetRow, 1, 1, headers.length).setValues([row]);
  } else {
    // Insert new row
    const row = headers.map(() => '');
    Object.entries(data).forEach(([k, v]) => {
      row[colMap[k]] = v ?? '';
    });
    row[colMap['updated_at']] = new Date();
    sh.appendRow(row);
  }

  return ContentService
    .createTextOutput(JSON.stringify({ ok: true }))
    .setMimeType(ContentService.MimeType.JSON);
}

function flattenObject(obj, prefix = '', out = {}) {
  Object.entries(obj || {}).forEach(([k, v]) => {
    const key = prefix ? `${prefix}_${k}` : k;
    if (v && typeof v === 'object' && !Array.isArray(v)) {
      flattenObject(v, key, out);
    } else if (Array.isArray(v)) {
      out[key] = v.join(','); // keep arrays sheet-friendly
    } else {
      out[key] = v;
    }
  });
  return out;
}

Step 3: Deploy Apps Script as a Web App

1. Click Deploy -> New deployment.
2. Select type: Web app.
3. Set:
   • Execute as: Me
   • Who has access: Anyone
4. Click Deploy.
5. Copy the Web App URL ending with /exec.

Use URL format like: https://script.google.com/macros/s/AKfy.../exec

Avoid domain-scoped /a/macros/... URLs because they often return 401 Unauthorized.

Step 4: Configure Webhook in TailorTalk

1. Go to Agent Setup in TailorTalk.
2. Click the relevant Agent Card.
3. Click the Webhook option.
4. Paste the Apps Script Web App URL.
5. Select required trigger(s), for example every_message.
6. Click Save.

Step 5: Test End-to-End

1. Trigger a message in TailorTalk (or send a Postman request).
2. Confirm a new row appears in Google Sheet.
3. Send another webhook with the same lead_contact.
4. Confirm the existing row gets updated (not duplicated).

Expected Behavior

• New keys in webhook payload create new sheet columns automatically.
• Nested fields inside data are flattened with _ separator.
• Duplicate events for same lead_contact update the same row.
• updated_at is refreshed on each update.

Troubleshooting

• 401 Unauthorized:
  • Redeploy Web App with Who has access = Anyone
  • Use /macros/s/.../exec URL instead of /a/macros/...
• Rows not updating:
  • Confirm lead_contact exists in webhook data
  • Confirm sheet tab name matches the script (Sheet1)
• Data appears as one JSON blob:
  • Ensure you are using this script (not a JSON.stringify(data) append-only version)