Google Sheet

Connect Google Sheets to OLC via Zapier Webhook

Who this is for: Clients who don’t have a CRM, or prefer using Google Sheets to manage leads and send direct mail through Open Letter Connect.

This method works with any mailer type: postcards, letters, snap packs, or real penned.

This guide walks you through:

• Structuring your lead list in Google Sheets

• Installing a Google Apps Script to manage sending logic

• Connecting Zapier to receive those leads

• Sending mail automatically through OLC

Step 1: Copy the Sheet Template

Use this pre-configured sheet layout:

Google Sheets Template – Direct Mail Drip

The sheet includes:

• A tab named Direct Mail Drip (your main lead list)

• A tab named Mail Log (automated log of sent mail)

Required Columns:

Column	Example
First Name	John
Last Name	Smith
Address Line 1	123 Main St
Address Line 2	Apt 4
City	Dallas
State	TX
Zip	75201
Email	[email protected]

Additional fields can be added as needed.

Step 2: Install the Apps Script (Required)

This script powers the automation: it filters new leads, sends data to Zapier via webhook, and logs each send.

1. In your Google Sheet, go to Extensions > Apps Script

2. Delete any sample code and paste the script below

3. Replace ZAP_WEBHOOK_URL with your real Zapier webhook URL

4. Save and close

INPUT_SHEET_NAME = 'Direct Mail Drip';
const LOG_SHEET_NAME = 'Mail Log';
const ZAP_WEBHOOK_URL = 'https://hooks.zapier.com/hooks/catch/XXX/YYY/'; // Replace this

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Mail Log')
    .addItem('Log New Entries', 'logNewEntries')
    .addToUi();
}

function logNewEntries() {
  const ss = SpreadsheetApp.getActive();
  const inS = ss.getSheetByName(INPUT_SHEET_NAME);
  const logS = getOrCreateLogSheet(ss, inS.getRange(1, 1, 1, inS.getLastColumn()).getValues()[0]);

  const inData = inS.getDataRange().getValues();
  const headers = inData[0];
  const loggedCol = ensureHeader(inS, headers, 'Logged');
  const logHeaders = logS.getRange(1, 1, 1, logS.getLastColumn()).getValues()[0];
  const logLoggedCol = ensureHeader(logS, logHeaders, 'Logged');

  const timestamp = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  let count = 0;

  for (let i = 1; i < inData.length; i++) {
    const row = inData[i];
    if (row[0] && !row[loggedCol]) {
      const payload = {};
      headers.forEach((h, j) => { payload[h] = row[j]; });
      UrlFetchApp.fetch(ZAP_WEBHOOK_URL, {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify(payload)
      });

      const logRow = logHeaders.map(h => h === 'Logged' ? timestamp : (headers.indexOf(h) >= 0 ? row[headers.indexOf(h)] : ''));
      logS.appendRow(logRow);
      inS.getRange(i + 1, loggedCol + 1).setValue(timestamp);
      count++;
    }
  }

  SpreadsheetApp.getUi().alert(`${count} new row(s) logged & pushed to Zapier.`);
}

function ensureHeader(sheet, headers, name) {
  let idx = headers.indexOf(name);
  if (idx === -1) {
    idx = headers.length;
    sheet.getRange(1, idx + 1).setValue(name);
    headers.push(name);
  }
  return idx;
}

function getOrCreateLogSheet(ss, headersToUse) {
  let sheet = ss.getSheetByName(LOG_SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(LOG_SHEET_NAME);
    sheet.appendRow(headersToUse.concat(['Logged']));
  }
  return sheet;
}

Step 3: Create the Zap in Zapier

1. Click Create Zap

2. Trigger:

   • App: Webhooks by Zapier

   • Event: Catch Hook

3. Copy your custom webhook URL

4. Paste the webhook into the Apps Script and save

5. Back in Zapier, click Test Trigger and run Mail Log > Log New Entries in your sheet to send test data

6. Action:

   • App: Open Letter Connect

   • Action: Send Postcard, Send Letter, or Send Snap Pack

   • Connect your OLC API key

   • Choose your saved template

   • Map the fields (e.g., To First Name, Address Line 1, etc.)

7. Turn on the Zap

How to Use It

1. Paste or import leads into the Direct Mail Drip tab

2. Open the custom Mail Log > Log New Entries menu

3. Script pushes any unsent leads to Zapier and logs them

4. Zapier sends mail via OLC

No CRM needed. No duplicate mailers. Fully automated.

Troubleshooting

Issue	Resolution
Nothing happens	Use Mail Log > Log New Entries manually
Zap shows no data	Check that the webhook URL was correctly pasted into the script
Zap sends blank fields	Review Zap field mapping step
Orders fail in OLC	Fund your OLC wallet or enable auto-funding
Logs aren’t updating	Check that "Logged" column exists in both tabs

FAQ

Can I send any mailer format? Yes. This setup works with any OLC mailer: postcards, letters, snap packs, and real penned.

Can I filter by tag or status before sending? Yes. Add logic in the Apps Script to filter by any custom field.

Can I schedule the mailing date? Yes. OLC’s Zapier action lets you select “Send ASAP” or a scheduled date.

Can I update contacts later? Yes. Just avoid modifying the “Logged” column, which acts as your tracker.