> For the complete documentation index, see [llms.txt](https://help.openletterconnect.com/olc-help-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.openletterconnect.com/olc-help-docs/integrations/live-apps/google-sheet.md).

# Google Sheet

**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

{% embed url="<https://www.loom.com/share/7e9b84727ef545e49f657d5fd60f67cb>" %}

#### Step 1: Copy the Sheet Template

Use this pre-configured sheet layout:&#x20;

[**Google Sheets Template – Direct Mail Drip**<br>](https://docs.google.com/spreadsheets/d/1LpnXddEDXnw20vzvedQhrPKdAUfr5gPp5dZ6cP2cSwM/edit?usp=sharing)

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          | <john@example.com> |

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

```javascript
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.