Automated Lead Generators

What this episode is about

Episode 3 of Zero to AI moves from mindset into a practical business example: the Changeable Contact Engine. It is a simple lead-qualification and booking flow built with a form, a spreadsheet, some scoring logic, automated email responses and Calendly booking.

The goal was not to build a large platform or complicated CRM. It was to solve a real friction point in the enquiry process. When someone reaches out, the first minute matters. If the reply is slow, vague or inconsistent, momentum can disappear before a conversation even starts.

This episode shows how a lightweight, well-designed workflow can turn a contact page from a passive inbox into a reliable engine that captures enquiries, qualifies them and moves the right people toward a conversation.

Why this matters

Quality lead generation is not just about getting more enquiries. It is about getting the right enquiries and responding in a way that feels fast, relevant and human. In the early days, Steve was manually copying form submissions, judging fit and writing replies one at a time.

That created two problems. Hot leads could cool down before a reply went out, and time was being spent on admin instead of meaningful conversations. The contact engine was built to solve that by giving every enquiry a consistent first response while still keeping the tone warm and human.

The system captures enquiries through a form, stores them in a sheet, scores each one by fit, urgency and value, sends the right email and routes qualified people to a booking link. For business implementation support, Changeable’s workflow and automation services are the more complete service pathway, while this episode remains a practical learning example.

How the contact engine works

The flow starts when someone completes a contact form. Their details land in a Google Sheet. A script then cleans the data and scores the lead using three simple factors: fit, urgency and value. Those scores create a band, such as advanced, operational, emerging or early.

Based on that score, the system sends the right kind of reply. Strong-fit leads receive a warm booking invitation. People who are not quite ready receive a helpful nurture email with resources and an open invitation to reply later.

When someone books through Calendly, a webhook logs the booking, avoids duplicates and syncs details back into the main contact sheet. The result is a tidy view of new enquiries, booked calls and follow-up status.

Key ideas from the episode

The system at a glance

The version described in the episode uses Google Forms, Google Sheets, Google Apps Script and Calendly. It keeps the build accessible because each part has a clear job.

Build guide: form and spreadsheet

The build begins with a simple Google Form. The form should collect only the information needed to respond well: full name, email, company, phone, timeline, main challenge, extra notes and consent.

Once the form is linked to a Google Sheet, rename the response tab to contact_raw. Then add two new tabs: contact_clean and calendly_log. The clean tab becomes the single source of truth. The Calendly log records booking events.

contact_clean headers:
Timestamp | Date | Week | FullName | Email | Company | Phone | Timeline | Challenge | ExtraNotes | Consent | FitScore | UrgencyScore | ValueScore | TotalScore | Band | ActionTaken | ProcessedAt | ReplyMins | WithinSLA | Cal_BookedAt | Cal_Start | Cal_URL

calendly_log headers:
Email | InviteeName | Status | BookedAt | StartTime | EndTime | EventType | EventURL | CancellationAt | Notes | Key

Apps Script: webhook and sync code

The next step is to open Apps Script from the spreadsheet and create the main script file. This code receives Calendly webhook events, logs bookings, avoids duplicates and syncs the latest booking back to the contact record.

/************ CONFIG ************/
const LOG_SHEET     = 'calendly_log';
const CONTACT_SHEET = 'contact_clean';

const LOG_HEADERS = [
  'Email','InviteeName','Status','BookedAt','StartTime','EndTime',
  'EventType','EventURL','CancellationAt','Notes','Key'
];
/********************************/

function sh(name){ return SpreadsheetApp.getActive().getSheetByName(name); }

function mapCols(sheet, needed){
  const hdr = sheet.getRange(1,1,1, Math.max(sheet.getLastColumn(), needed.length))
    .getValues()[0].map(h => String(h).trim());

  const out = {};
  needed.forEach(h => {
    const i = hdr.indexOf(h);
    if (i === -1) throw new Error(`Missing header "${h}" on sheet "${sheet.getName()}"`);
    out[h] = i + 1;
  });

  return out;
}

function toIso(x){ return x ? new Date(x).toISOString() : ''; }

function ensureDebug(){
  const ss = SpreadsheetApp.getActive();
  if (!ss.getSheetByName('debug')) {
    ss.insertSheet('debug').getRange(1,1,1,4)
      .setValues([[ 'When','What','Type','Peek' ]]);
  }
}

function dbg(what, type, peek){
  try {
    ensureDebug();
    SpreadsheetApp.getActive().getSheetByName('debug')
      .appendRow([ new Date(), what, type || '', (peek || '').toString().slice(0,500) ]);
  } catch (_) {}
}

function doGet(){
  return ContentService.createTextOutput('OK');
}

function doPost(e){
  try {
    dbg('HIT', e?.postData?.type, e?.postData?.contents);

    const log = sh(LOG_SHEET);
    ensureLogHeaders();
    const c = mapCols(log, LOG_HEADERS);

    let body = {};
    try { body = JSON.parse(e?.postData?.contents || '{}'); } catch(_) {}

    const eventType = body.event || '';
    const status = eventType === 'invitee.canceled' ? 'canceled' : 'booked';

    let email = (body.payload?.invitee?.email || '').trim();
    let name = body.payload?.invitee?.name || '';
    let bookedAt = body.payload?.invitee?.created_at || body.created_at || '';
    let start = body.payload?.event?.start_time || '';
    let end = body.payload?.event?.end_time || '';
    let url = body.payload?.event?.uri || '';

    const inviteeUri = typeof body.payload?.invitee === 'string'
      ? body.payload.invitee : (body.payload?.invitee?.uri || '');

    const eventUri = typeof body.payload?.event === 'string'
      ? body.payload.event : (body.payload?.event?.uri || '');

    try {
      const headers = { Authorization: 'Bearer ' + CALENDLY_PAT };

      const fetchJson = u => {
        if (!u) return null;
        const res = UrlFetchApp.fetch(u, { headers, muteHttpExceptions: true });
        if (res.getResponseCode() < 200 || res.getResponseCode() >= 300) return null;
        const j = JSON.parse(res.getContentText());
        return j?.resource || j;
      };

      const isCalendlyApiUrl = u => /^https:\/\/api\.calendly\.com\//.test(String(u || ''));

      if ((!email || !name || !bookedAt) && isCalendlyApiUrl(inviteeUri)) {
        const r = fetchJson(inviteeUri);
        if (r) {
          email = (r.email || email || '').trim();
          name = r.name || name;
          bookedAt = r.created_at || bookedAt;
        }
      }

      if ((!start || !end || !url) && isCalendlyApiUrl(eventUri)) {
        const r = fetchJson(eventUri);
        if (r) {
          start = r.start_time || start;
          end = r.end_time || end;
          url = r.uri || url || eventUri;
        }
      }
    } catch (err) {
      dbg('ENRICH_ERR', 'doPost', String(err).slice(0,180));
    }

    const uniq = ((url || ((email || '') + '|' + (start || ''))) + '|' + status);

    const lr = Math.max(log.getLastRow() - 1, 0);
    const keys = lr ? log.getRange(2, c.Key, lr, 1).getValues().flat() : [];
    let row = keys.indexOf(uniq);
    row = row >= 0 ? (row + 2) : (log.getLastRow() + 1);

    log.getRange(row, c.Email).setValue(email);
    log.getRange(row, c.InviteeName).setValue(name);
    log.getRange(row, c.Status).setValue(status);
    log.getRange(row, c.BookedAt).setValue(bookedAt);
    log.getRange(row, c.StartTime).setValue(start);
    log.getRange(row, c.EndTime).setValue(end);
    log.getRange(row, c.EventType).setValue(eventType);
    log.getRange(row, c.EventURL).setValue(url || eventUri || '');
    log.getRange(row, c.CancellationAt).setValue(
      status === 'canceled' ? (body.payload?.cancellation?.canceled_at || '') : ''
    );
    log.getRange(row, c.Notes).setValue(toIso(new Date()));
    log.getRange(row, c.Key).setValue(uniq);

    return ContentService.createTextOutput('OK');
  } catch (err) {
    dbg('FATAL', 'doPost', String(err));
    return ContentService.createTextOutput('ERR');
  }
}

function ensureLogHeaders(){
  const sheet = sh(LOG_SHEET);
  const need = LOG_HEADERS;
  const have = sheet.getRange(1,1,1, Math.max(sheet.getLastColumn(), need.length)).getValues()[0];
  const out = [...have];

  need.forEach((h,i) => {
    if (!out[i]) out[i] = h;
  });

  sheet.getRange(1,1,1,out.length).setValues([out]);
}

function syncCalendlyToContacts(){
  const logSheet = sh(LOG_SHEET);
  const conSheet = sh(CONTACT_SHEET);

  const lc = mapCols(logSheet, ['Email','Status','StartTime','EventURL','BookedAt']);
  const cc = mapCols(conSheet, ['Email','Cal_BookedAt','Cal_Start','Cal_URL']);

  const ln = Math.max(logSheet.getLastRow() - 1, 0);
  const cn = Math.max(conSheet.getLastRow() - 1, 0);
  if (!cn) return;

  const rows = ln ? logSheet.getRange(2,1,ln, logSheet.getLastColumn()).getValues() : [];
  const latest = new Map();

  rows.forEach(r => {
    const email = String(r[lc.Email - 1] || '').toLowerCase();
    if (!email) return;
    if (String(r[lc.Status - 1] || '').toLowerCase() !== 'booked') return;

    const start = r[lc.StartTime - 1] || '';
    const ts = start ? Date.parse(start) : 0;
    const prev = latest.get(email);

    if (!prev || ts > prev.ts) {
      latest.set(email, {
        ts,
        start,
        bookedAt: r[lc.BookedAt - 1] || '',
        url: r[lc.EventURL - 1] || ''
      });
    }
  });

  const emails = conSheet.getRange(2, cc.Email, cn, 1).getValues()
    .map(v => String(v[0] || '').toLowerCase());

  emails.forEach((em, i) => {
    const m = latest.get(em);
    if (!m) return;

    const row = i + 2;
    conSheet.getRange(row, cc['Cal_BookedAt']).setValue(m.bookedAt);
    conSheet.getRange(row, cc['Cal_Start']).setValue(m.start);
    conSheet.getRange(row, cc['Cal_URL']).setValue(m.url);
  });
}

function runSyncSafe() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) {
    Logger.log('runSyncSafe: skipped because another sync is running');
    return;
  }

  try {
    syncCalendlyToContacts();
  } finally {
    lock.releaseLock();
  }
}

function installSyncTrigger() {
  ScriptApp.getProjectTriggers().forEach(t => {
    const h = t.getHandlerFunction();
    if (h === 'runSyncSafe' || h === 'syncCalendlyToContacts') ScriptApp.deleteTrigger(t);
  });

  ScriptApp.newTrigger('runSyncSafe').timeBased().everyHours(1).create();
  Logger.log('Installed hourly trigger');
}

Apps Script: Calendly setup

Create a second file called calendly_setup.gs. This connects the Apps Script web app URL to Calendly using a personal access token. After deploying the web app, replace the two placeholder values with your own token and callback URL.

const CALENDLY_PAT = 'PASTE_YOUR_CALENDLY_PAT_HERE';
const CALLBACK_URL = 'PASTE_YOUR_WEB_APP_EXEC_URL_HERE';

function testCalendlyToken() {
  const headers = { Authorization: 'Bearer ' + CALENDLY_PAT };
  const res = UrlFetchApp.fetch('https://api.calendly.com/users/me', {
    headers,
    muteHttpExceptions: true
  });

  Logger.log('HTTP ' + res.getResponseCode());
  Logger.log(res.getContentText());
}

function listCalendlyWebhooks() {
  const headers = { Authorization: 'Bearer ' + CALENDLY_PAT };

  const me = JSON.parse(
    UrlFetchApp.fetch('https://api.calendly.com/users/me', { headers }).getContentText()
  ).resource;

  const orgUrl = 'https://api.calendly.com/webhook_subscriptions?scope=organization&organization='
    + encodeURIComponent(me.current_organization);

  const r = UrlFetchApp.fetch(orgUrl, {
    headers,
    muteHttpExceptions: true
  });

  Logger.log('LIST ' + orgUrl + ' → HTTP ' + r.getResponseCode());
  Logger.log(r.getContentText());
}

function resetCalendlyWebhook() {
  const headers = {
    Authorization: 'Bearer ' + CALENDLY_PAT,
    'Content-Type': 'application/json'
  };

  const me = JSON.parse(
    UrlFetchApp.fetch('https://api.calendly.com/users/me', { headers }).getContentText()
  ).resource;

  const list = UrlFetchApp.fetch(
    'https://api.calendly.com/webhook_subscriptions?scope=organization&organization='
      + encodeURIComponent(me.current_organization),
    { headers, muteHttpExceptions:true }
  );

  const items = (JSON.parse(list.getContentText()).collection || []);
  Logger.log('Found ' + items.length + ' webhooks');

  items.forEach(x => {
    const uuid = (x.uri || '').split('/').pop();
    const del = UrlFetchApp.fetch('https://api.calendly.com/webhook_subscriptions/' + uuid, {
      method:'delete',
      headers,
      muteHttpExceptions:true
    });

    Logger.log('Deleted ' + uuid + ' → ' + del.getResponseCode());
  });

  const payload = {
    url: CALLBACK_URL,
    events: ['invitee.created','invitee.canceled'],
    organization: me.current_organization,
    scope: 'organization'
  };

  const create = UrlFetchApp.fetch('https://api.calendly.com/webhook_subscriptions', {
    method:'post',
    headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions:true
  });

  Logger.log('Create → HTTP ' + create.getResponseCode());
  Logger.log(create.getContentText());
}

Optional: simulate a Calendly booking

Create a third script file called Simulator.gs if you want to test the webhook without waiting for a real booking. Running this should create a debug hit and add a booking row to the Calendly log.

function simulateCalendlyBooking() {
  const payload = {
    event: 'invitee.created',
    payload: {
      invitee: {
        email: 'sim@tester.com',
        name: 'Sim Tester',
        created_at: new Date().toISOString()
      },
      event: {
        uri: '',
        start_time: new Date(Date.now() + 15 * 60 * 1000).toISOString(),
        end_time: new Date(Date.now() + 45 * 60 * 1000).toISOString()
      }
    }
  };

  const e = {
    postData: {
      type: 'application/json',
      contents: JSON.stringify(payload)
    }
  };

  return doPost(e);
}

Deploy and connect the workflow

In Apps Script, deploy the project as a web app. Use Execute as: Me and Who has access: Anyone. Copy the web app URL that ends in /exec, then paste it into the Calendly setup file as the callback URL.

In Calendly, create a personal access token from the API and Webhooks area. Paste that token into the setup file, then run resetCalendlyWebhook. The logs should show a successful webhook creation.

Once the webhook is active, run installSyncTrigger to keep booking data synced back to the contact sheet every hour.

Optional: auto-email on form submit

The optional email script sends a different response depending on whether the lead is qualified. Use this as a starter only. Replace placeholder links, refine the wording and make sure the scoring logic matches your own form and sheet setup.

function onFormSubmit(e){
  try {
    const nv = e.namedValues || {};

    const email = (nv['Email'] || nv['Enter your email'] || [''])[0].trim();
    const name = (nv['Full name'] || nv['Enter your full name'] || [''])[0].trim();

    const fit = Number((nv['Fit Score'] || [''])[0]) || 0;
    const urg = Number((nv['Urgency Score'] || [''])[0]) || 0;
    const val = Number((nv['Value Score'] || [''])[0]) || 0;
    const total = fit + urg + val;

    if (!email) return;

    const qualified = total >= 16;

    if (qualified) {
      MailApp.sendEmail({
        to: email,
        subject: 'Let’s start: book a quick 30-minute call',
        htmlBody:
`Kia ora ${name || ''},

Thanks for reaching out. From what you shared, we may be able to help you move fast.

Please grab a time that suits you here:
Book a 30-minute intro.

If nothing fits, reply with a couple of windows and we’ll make it work.

The team`
      });
    } else {
      MailApp.sendEmail({
        to: email,
        subject: 'Got your message — here’s a next step',
        htmlBody:
`Kia ora ${name || ''},

Thanks for getting in touch.

A couple of resources to get you moving:
• Resource 1
• Resource 2

What is the one outcome that would make this a win for you?

The team`
      });
    }
  } catch(err) {
    Logger.log(err);
  }
}

Privacy, reliability and testing

Because this workflow handles contact information, privacy matters. Keep data inside your controlled workspace, honour update or deletion requests and avoid collecting information you do not need. For a New Zealand context, the Privacy Act 2020 information privacy principles are a useful reference point.

Test the whole flow before relying on it. Submit a form entry using your own email, check the raw and clean tabs, confirm the auto-reply if you enabled it, book a Calendly slot using the same email and check that the booking appears in the log.

If something does not work, check the webhook URL, header spelling, form question labels, triggers and email matches. Most issues in small automations come from tiny naming mismatches.

Where to go next

This page is designed to stand alone as a foundation episode. You can listen, read, reflect and try the build only if it fits your own work. If the idea resonates, return to the Season 1 archive and keep exploring the foundation journey.

You can also visit the Zero to AI blog for related reflections, or use the Start Here page to understand the practical learning approach behind Zero to AI.