# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.openletterconnect.com/olc-help-docs/integrations/live-apps/google-sheet.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.