Automate Follow-Ups with BigQuery

Build a safe BigQuery-to-Gmail workflow with logs and duplicate protection.

Introduction

30 Second Summary

A timely follow-up feels personal. The same message sent twice feels careless.

In this project, you will build a guarded follow-up workflow with Google Apps Script that turns Google BigQuery results into personalized Gmail messages. A contact log in Google Sheets records every attempt to protect scheduled runs from contacting the same action twice.

What You'll Build

Picture opening Gmail to find one personalized follow-up for a recipient whose Contact Log row confirms exactly one delivery attempt.

By the end of this project, you'll have:

  • A recipient preview that displays normalized action_key, email, and first_name fields before any message is created.
  • A personalized Gmail flow that creates a safe draft from one recipient record. You can then prove the live delivery path with a controlled self-send.
  • A duplicate-safe daily automation that blocks repeated action keys. Its Contact Log shows PROCESSING, DRAFTED, SENT, or ERROR for every attempt.
  • Secret Mission: A production recovery drill that pauses the schedule for inspection. You will diagnose the run history before restoring exactly one daily trigger.

Are there any prerequisites?

You need an existing GoogleSQL recipient query with permission to run its BigQuery job. You also need access to the tables referenced by that query.

Your Workspace account needs permission to use Gmail, Google Sheets, and Google Apps Script. The editor runs in your browser, so no local installation is required.

Before We Start

A useful follow-up begins with a precise trigger. This checkpoint defines who qualifies for contact plus what the message should achieve.

Set Up the Safe Automation Workspace

The user action is defined. The workflow still needs a visible audit surface before it receives access to your data or mailbox.

A dedicated Google Sheets file gives every future attempt a place to be inspected. Its bound Google Apps Script project keeps the workflow beside that audit log.

The bound script will later read from BigQuery. It will also create Gmail messages.

In this step, get ready to:
  • Create the spreadsheet audit workspace and its bound Apps Script project.
  • Enable the BigQuery advanced service for the script.
  • Configure the complete workflow for a safe test run and initialize the Contact Log.
Create the audit workspace

A bound Apps Script project belongs to one spreadsheet. This gives the automation a clear home for its code and delivery history.

  • Create a blank Google Sheet named BigQuery-to-Gmail Follow-Up Automator.
  • Copy the spreadsheet ID from the browser address bar into your spreadsheet ID.
  • Click Extensions in the spreadsheet menu.
  • Select Apps Script.
  • Replace the default project title with BigQuery-to-Gmail Follow-Up Automator.
  • Select Project Settings in the left sidebar.
  • Choose your intended daily-run time zone in the Time zone section.

You should now see the named Apps Script project. Its time zone should match when you want the daily email run to occur.

Enable BigQuery access

BigQuery is an Apps Script advanced service. Enabling it gives your script access to the BigQuery query methods used in Code.gs.

  • Return to the Apps Script editor from earlier.
  • Click Editor in the left sidebar.
  • Click Add a service beside Services.
  • Select BigQuery API.
  • Click Add.

The companion API setup depends on whether the script uses the default Apps Script Cloud project or a standard Google Cloud project.

Default Cloud project

Adding the advanced service automatically enables its companion API for the default Apps Script Cloud project.

  • Confirm that BigQuery API now appears under Services in the editor.

Standard Cloud project

A standard Google Cloud project requires the companion API to be enabled separately.

  • Open the Google Cloud console API Library for the standard project linked to your script.
  • Select the Google Cloud project that runs your existing query.
  • Search for BigQuery.
  • Select BigQuery API from the results.
  • Click Enable.
  • Return to the Apps Script editor from earlier.

You should now see BigQuery API listed under Services. This confirms that the script can reach the BigQuery API after authorization.

BigQuery Service Missing?

Confirm that you clicked Add after selecting BigQuery API. If access is restricted, ask your Workspace or Google Cloud administrator to enable the service for your account.

Still blocked? Help me diagnose why the BigQuery advanced service is unavailable in my Apps Script project.

Configure the safe test run

The first configuration keeps every message inside your own inbox. Draft mode lets the workflow prove access without sending an email.

  • Copy the project ID used to run your existing BigQuery query into your Google Cloud project ID.
  • Copy the processing location used by that query into your BigQuery location.
  • Record your work email address for the test recipient and recipient override.
  • Return to the Apps Script editor from earlier.
  • Select Code.gs in the editor sidebar.
  • Replace the starter content with the complete file in the double-check tab below.

✔️ Awesome, I've got everything!

Your complete Code.gs file is ready for the first safe run.

ⓧ I'd like to double check the full code

var CONFIG = {
  projectId: '[[PROJECT_ID="your Google Cloud project ID"]]',
  bigQueryLocation: '[[BIGQUERY_LOCATION="your BigQuery location"]]',
  spreadsheetId: '[[SPREADSHEET_ID="your spreadsheet ID"]]',
  logSheetName: 'Contact Log',

  useTestQuery: true,
  testQuery:
      "SELECT 'test-action-001' AS action_key, " +
      "'[[YOUR_WORK_EMAIL="your work email address"]]' AS email, " +
      "'Test User' AS first_name",
  liveQuery: 'paste-your-existing-google-sql-query-here',

  deliveryMode: 'draft',
  recipientOverride: '[[YOUR_WORK_EMAIL="your work email address"]]',
  liveSendingApproved: false,
  maxEmailsPerRun: 20,
  sendHour: 9,

  subjectTemplate: 'Thanks for completing the action, {{first_name}}',
  bodyTemplate: [
    'Hi {{first_name}},',
    '',
    'Thanks for completing the action. I wanted to follow up with you personally.',
    '',
    'Best,',
    'Your Name'
  ].join('\n')
};

var LOG_HEADERS = [
  'action_key',
  'source_email',
  'delivered_to',
  'status',
  'processed_at',
  'delivery_id',
  'error'
];

function setupAutomation() {
  ScriptApp.requireAllScopes(ScriptApp.AuthMode.FULL);
  var sheet = getLogSheet_();
  console.log('Contact log ready: ' + sheet.getParent().getUrl());
}

function previewRecipients() {
  var recipients = queryRecipients_();
  console.log('Recipient count: ' + recipients.length);
  console.log(JSON.stringify(recipients.slice(0, 5), null, 2));
}

function runNaiveDraftTest() {
  var recipients = queryRecipients_();
  if (recipients.length === 0) {
    console.log('No recipients returned.');
    return;
  }

  var recipient = recipients[0];
  var deliveredTo = CONFIG.recipientOverride || recipient.email;
  var subject = renderTemplate_(CONFIG.subjectTemplate, recipient);
  var body = renderTemplate_(CONFIG.bodyTemplate, recipient);
  var draft = GmailApp.createDraft(deliveredTo, subject, body);

  console.log('Created naive draft: ' + draft.getId());
}

function runAutomation() {
  if (
    CONFIG.deliveryMode === 'send' &&
    !CONFIG.liveSendingApproved &&
    CONFIG.recipientOverride === ''
  ) {
    throw new Error(
      'Live sending is blocked. Use a recipient override for testing or approve live sending.'
    );
  }

  var lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) {
    throw new Error('Another automation run is already in progress.');
  }

  try {
    var sheet = getLogSheet_();
    var blockedKeys = getBlockedKeys_(sheet);
    var recipients = queryRecipients_();
    var attempts = 0;

    for (var i = 0; i < recipients.length; i++) {
      if (attempts >= CONFIG.maxEmailsPerRun) {
        break;
      }

      var recipient = recipients[i];
      var blockKey = 'key:' + String(recipient.action_key);
      if (blockedKeys[blockKey]) {
        continue;
      }

      blockedKeys[blockKey] = true;
      processRecipient_(sheet, recipient);
      attempts++;
    }

    if (attempts === 0) {
      console.log('No new recipients to process.');
    } else {
      console.log('Processed recipient attempts: ' + attempts);
    }
  } finally {
    SpreadsheetApp.flush();
    lock.releaseLock();
  }
}

function createDailyTrigger() {
  ScriptApp.requireAllScopes(ScriptApp.AuthMode.FULL);

  if (CONFIG.useTestQuery) {
    throw new Error('Switch off the test query before creating the live trigger.');
  }
  if (CONFIG.deliveryMode !== 'send') {
    throw new Error('Set deliveryMode to send before creating the live trigger.');
  }
  if (CONFIG.recipientOverride !== '') {
    throw new Error('Clear recipientOverride before creating the live trigger.');
  }
  if (!CONFIG.liveSendingApproved) {
    throw new Error('Set liveSendingApproved to true after the controlled test.');
  }

  deleteAutomationTriggers();
  ScriptApp.newTrigger('runAutomation')
    .timeBased()
    .atHour(CONFIG.sendHour)
    .everyDays(1)
    .create();

  console.log(
    'Daily trigger created for the ' +
      CONFIG.sendHour +
      ':00 hour in the project time zone.'
  );
}

function deleteAutomationTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  console.log('Deleted project triggers: ' + triggers.length);
}

function queryRecipients_() {
  var query = getRecipientQuery_();
  var request = {
    query: query,
    useLegacySql: false,
    location: CONFIG.bigQueryLocation
  };

  var queryResults = BigQuery.Jobs.query(request, CONFIG.projectId);
  var jobId = queryResults.jobReference.jobId;
  var optionalArgs = {location: CONFIG.bigQueryLocation};
  var sleepTimeMs = 500;

  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs = Math.min(sleepTimeMs * 2, 5000);
    queryResults = BigQuery.Jobs.getQueryResults(
      CONFIG.projectId,
      jobId,
      optionalArgs
    );
  }

  var headers = [];
  for (var h = 0; h < queryResults.schema.fields.length; h++) {
    headers.push(queryResults.schema.fields[h].name);
  }

  var rows = queryResults.rows || [];
  while (queryResults.pageToken) {
    optionalArgs.pageToken = queryResults.pageToken;
    queryResults = BigQuery.Jobs.getQueryResults(
      CONFIG.projectId,
      jobId,
      optionalArgs
    );
    rows = rows.concat(queryResults.rows || []);
  }

  var recipients = [];
  for (var i = 0; i < rows.length; i++) {
    var record = {};
    var cells = rows[i].f;

    for (var j = 0; j < headers.length; j++) {
      record[headers[j]] = cells[j] ? cells[j].v : null;
    }

    if (!record.action_key || !record.email) {
      throw new Error(
        'Every query row must contain action_key and email. first_name is optional.'
      );
    }

    record.first_name = record.first_name || 'there';
    recipients.push(record);
  }

  return recipients;
}

function getRecipientQuery_() {
  if (CONFIG.useTestQuery) {
    return CONFIG.testQuery;
  }

  if (CONFIG.liveQuery === 'paste-your-existing-google-sql-query-here') {
    throw new Error('Replace the liveQuery placeholder before using live mode.');
  }

  return CONFIG.liveQuery;
}

function getLogSheet_() {
  var spreadsheet = SpreadsheetApp.openById(CONFIG.spreadsheetId);
  var sheet = spreadsheet.getSheetByName(CONFIG.logSheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(CONFIG.logSheetName);
  }

  if (sheet.getLastRow() === 0) {
    sheet.appendRow(LOG_HEADERS);
    sheet.setFrozenRows(1);
    sheet.autoResizeColumns(1, LOG_HEADERS.length);
  }

  return sheet;
}

function getBlockedKeys_(sheet) {
  var blocked = {};
  var lastRow = sheet.getLastRow();

  if (lastRow <= 1) {
    return blocked;
  }

  var rows = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
  for (var i = 0; i < rows.length; i++) {
    var actionKey = String(rows[i][0]);
    var status = String(rows[i][3]);

    if (
      status === 'PROCESSING' ||
      status === 'DRAFTED' ||
      status === 'SENT'
    ) {
      blocked['key:' + actionKey] = true;
    }
  }

  return blocked;
}

function processRecipient_(sheet, recipient) {
  var deliveredTo = CONFIG.recipientOverride || recipient.email;
  var logRow = beginLog_(sheet, recipient, deliveredTo);
  var subject = renderTemplate_(CONFIG.subjectTemplate, recipient);
  var body = renderTemplate_(CONFIG.bodyTemplate, recipient);

  try {
    if (CONFIG.deliveryMode === 'draft') {
      var draft = GmailApp.createDraft(deliveredTo, subject, body);
      updateLog_(sheet, logRow, 'DRAFTED', draft.getId(), '');
      return 'DRAFTED';
    }

    if (CONFIG.deliveryMode === 'send') {
      GmailApp.sendEmail(deliveredTo, subject, body);
      updateLog_(sheet, logRow, 'SENT', '', '');
      return 'SENT';
    }

    throw new Error('deliveryMode must be draft or send.');
  } catch (error) {
    updateLog_(sheet, logRow, 'ERROR', '', getErrorMessage_(error));
    console.log(
      'Delivery failed for action_key ' +
        recipient.action_key +
        ': ' +
        getErrorMessage_(error)
    );
    return 'ERROR';
  }
}

function beginLog_(sheet, recipient, deliveredTo) {
  sheet.appendRow([
    String(recipient.action_key),
    String(recipient.email),
    String(deliveredTo),
    'PROCESSING',
    new Date(),
    '',
    ''
  ]);
  SpreadsheetApp.flush();
  return sheet.getLastRow();
}

function updateLog_(sheet, rowNumber, status, deliveryId, errorMessage) {
  sheet.getRange(rowNumber, 4, 1, 4).setValues([[
    status,
    new Date(),
    deliveryId,
    errorMessage
  ]]);
}

function renderTemplate_(template, recipient) {
  return template
    .replace(/\{\{first_name\}\}/g, String(recipient.first_name))
    .replace(/\{\{email\}\}/g, String(recipient.email));
}

function getErrorMessage_(error) {
  if (error && error.message) {
    return String(error.message);
  }
  return String(error);
}

What Does This File Prepare?

  • The CONFIG object stores the project connection details and temporary safety controls.
  • The LOG_HEADERS array defines the seven fields used to audit each delivery attempt.
  • The workflow functions cover query retrieval, result normalization, message delivery, duplicate protection, locking, logging, and trigger management.
  • The current test settings route work to your own address in draft mode.
  • Save Code.gs.
  • Select setupAutomation from the function menu in the Apps Script editor.

The permission screen can feel broad because this project eventually works across your spreadsheet, mailbox, and query data. This first run asks you to approve those connections before any workflow function can use them.

Before you run it, do you expect the audit tab to exist already or be created by this first execution?

  • Start setupAutomation with the editor's run control.
  • Grant every permission requested by Apps Script.
  • Return to the Apps Script editor after authorization.
  • Inspect the execution log for the setup result.

You should see Contact log ready: followed by the spreadsheet URL in the execution log.

  • Switch back to the Google Sheet from earlier.
  • Select the new Contact Log tab.
  • Confirm that the first row is frozen.
  • Confirm that the row contains all seven audit headers.

You should see action_key, source_email, delivered_to, status, processed_at, delivery_id, and error across the frozen header row.

That is the workspace secured: the script is authorized in safe test mode and the audit log is ready to record future attempts.

Setup Run Did Not Finish?

Confirm that spreadsheetId contains the ID from your current spreadsheet. Confirm that BigQuery API still appears under Services.

If authorization was blocked, your Workspace administrator may need to allow Apps Script for your account.

Need another set of eyes? Help me diagnose why setupAutomation did not create my Contact Log.

Your audit workspace is ready. Next, you will run the safe test query and inspect its normalized recipient record.

Prove the BigQuery Connection

Your Google Sheets file now has an empty Contact Log ready for future delivery attempts.

Its bound Google Apps Script project already has BigQuery access. Before email enters the workflow, the script must prove that it can read one predictable recipient record.

The query helper then normalizes that row into fields the message template can use.

In this step, get ready to:
  • Confirm the safe test query uses only your own Google Workspace email address.
  • Prove previewRecipients returns one normalized test record.
  • Stage your live GoogleSQL query with the required recipient aliases.
Confirm the safe test query

Test mode isolates the connection check from your real recipient query. The selected row points to your address, which keeps this stage observable without contacting anyone else.

  • Switch back to Code.gs in the Apps Script editor.
  • Locate CONFIG near the top of the file.
  • Confirm useTestQuery is set to true.
  • Confirm the email literal inside testQuery is your own Workspace address.
  • Confirm recipientOverride contains the same address.
  • Confirm deliveryMode is set to draft.
  • Confirm liveSendingApproved is set to false.

These settings route the preview to one safe record. The Contact Log stays empty because previewRecipients() only logs query results.

Run and inspect the preview

The previewRecipients() function calls the query helper. It prints a normalized sample as JSON.

Before you run the preview, do you expect the raw BigQuery row structure or the normalized field names?

  • Select previewRecipients from the editor's function list.
  • Run previewRecipients from the editor toolbar.
  • Wait for the execution to finish.
  • Expand the execution log panel.
  • Inspect the recipient count.
  • Inspect the first normalized row.

You'll see Recipient count: 1 in the execution log.

The JSON output includes action_key, email, and first_name. The email value matches your own test address.

You have the connection working: Apps Script can now turn a BigQuery row into the exact fields the template needs.

No normalized row in the log?

  • Confirm CONFIG.projectId identifies the Cloud project that runs the query.
  • Confirm CONFIG.bigQueryLocation matches the location of the queried data.
  • Confirm the BigQuery advanced service remains listed beside Services.

Help me diagnose my BigQuery preview.

How the preview gets normalized

  • The queryRecipients_() helper selects testQuery because useTestQuery is true.
  • The BigQuery.Jobs.query method starts the query job for the configured project.
  • The BigQuery.Jobs.getQueryResults method retrieves results when the job needs polling or another page.
  • The normalization loop pairs each schema name with its matching cell value.
Stage the live query

A predictable test row confirms the connection. Your production data now needs to follow the same query contract.

The live query must expose a stable action_key for the same completed action. It must also expose email plus first_name.

  • Switch back to Code.gs in the Apps Script editor.
  • Locate liveQuery inside CONFIG.
  • Replace its current string with your existing GoogleSQL query adapted to the required aliases.
  • Save Code.gs.
  • Confirm useTestQuery still reads true.

Before you run the preview again, do you expect the staged live query or the safe test row?

  • Run previewRecipients from the editor toolbar.
  • Inspect the execution log after it finishes.

You'll see the same one-row test preview with your own email address.

The unchanged preview confirms safe test mode remains active while your live query waits inside CONFIG.

Preview changed after staging the query?

  • Confirm liveQuery remains a valid JavaScript string if the script reports a syntax problem.
  • Set useTestQuery back to true if real recipient rows appear.

Help me fix my staged live query.

  • Switch back to the Google Sheet from earlier.
  • Confirm Contact Log still contains only the frozen header row.

No delivery rows appear because this step only previews query results.

Your BigQuery connection now returns a predictable, template-ready record. Next, you'll turn that safe test row into a personalized Gmail draft.

Create a Personalized Draft

Your BigQuery preview now returns a predictable recipient record. It includes action_key, email, and first_name.

This step turns that record into a safe Gmail draft. You will inspect the personalized message before testing how the same naive path behaves on a repeat run.

In this step, get ready to:
  • Personalize the draft template with your message.
  • Create one Gmail draft from the test recipient.
  • Repeat the naive draft run to observe how it handles the same action.
Personalize the draft template

Your Google Apps Script project already contains a subject template plus a body template. The {{first_name}} token connects each message to the normalized recipient record.

  • Return to the Google Apps Script project from earlier.
  • Select Editor in the left navigation.
  • Select Code.gs in the file list.
  • Find subjectTemplate inside CONFIG.
  • Replace the subject wording with a message that contains {{first_name}}.
  • Find bodyTemplate inside CONFIG.
  • Rewrite the body with your follow-up message while keeping {{first_name}} in the greeting.
  • Wait until the editor confirms that your changes are saved.

Your templates now contain your own wording. Both still have a dynamic place for the recipient's first name.

How does the personalization work?

The subjectTemplate value controls the draft subject. The bodyTemplate value controls the plain-text message.

The renderTemplate_() function replaces each {{first_name}} token with the normalized first_name value. One template can therefore adapt to every recipient row.

Create the first test draft

Draft delivery lets you inspect the finished message without sending it. Your existing test settings also keep the recipient pointed at your own address.

Before you run the function, which first name do you expect to see in the draft? Hold that prediction in mind.

  • Select runNaiveDraftTest from the function selector.
  • Click Run in the editor toolbar.

The execution log prints Created naive draft: followed by a draft ID.

  • Open Gmail in another browser tab.
  • Select Drafts in the left sidebar.
  • Open the newest draft.
  • Confirm that the draft is addressed to your own test address.
  • Confirm that the subject includes the test recipient's first name.
  • Confirm that the greeting includes the same first name.
  • Read the body to check your personalized wording.

That first draft is your safe proof that a BigQuery row can become a personalized message without reaching a real recipient.

Draft missing from Gmail?

  • Confirm that CONFIG.useTestQuery remains true.
  • Confirm that CONFIG.recipientOverride still contains your own address.

Help me diagnose why runNaiveDraftTest did not create my Gmail draft.

Rerun the naive draft test

The real test is repeat behavior. The same test row is still available to the function.

Before you repeat the run, do you think runNaiveDraftTest() can recognize the action_key it handled moments ago? Hold that prediction in mind.

  • Switch back to the Apps Script editor from earlier.
  • Select runNaiveDraftTest from the function selector.
  • Click Run in the editor toolbar.

The execution log prints Created naive draft: again with a draft ID.

  • Return to Gmail.
  • Select Drafts in the left sidebar.
  • Refresh the draft list.

You will see two drafts with the same test recipient. Both drafts have the same personalized subject and message body.

This duplicate is intentional

The runNaiveDraftTest() function sends the first normalized row directly to GmailApp.createDraft. The function bypasses the Contact Log.

Each run therefore treats the same stable action_key as fresh work. The duplicate is the visible gap this test was designed to expose.

Only see one draft?

  • Refresh the Gmail draft list after the second execution finishes.
  • Confirm that the execution log printed Created naive draft: twice.
  • Confirm that both runs used runNaiveDraftTest.

Help me find why the second naive Gmail draft was not created.

Idempotency gives a workflow a durable memory of completed work. The naive path has no log entry to consult.

  • Switch back to your contact log in Google Sheets.
  • Select the Contact Log tab.

You will see the seven headers from setup. Both naive runs leave the rows below them empty.

You caught the duplicate risk while every message still points to your own inbox. Next, you will use the Contact Log to reserve each action_key before delivery.

Add Duplicate Protection and an Audit Log

Your duplicate test in Gmail exposed the exact risk in the naive workflow. The same BigQuery action can create another message every time the automation runs.

Idempotency gives repeated runs a memory of completed work. The Contact Log in Google Sheets becomes the memory that makes this possible.

A Google Apps Script script lock also prevents overlapping runs from entering the delivery path together.

In this step, get ready to:
  • Remove the two naive drafts before testing the protected workflow.
  • Run the duplicate-safe automation to create one logged draft.
  • Repeat the run to prove that the existing action key blocks another draft.
Clear the naive drafts and trace the safety flow

The duplicate-safe test needs a clean starting point. Removing the two naive drafts makes the next result easy to identify.

  • Return to Gmail Drafts from the previous step.
  • Select the two identical test drafts.
  • Delete the selected drafts.
  • Confirm the Drafts list no longer shows either naive test message.

Good. The naive drafts are gone. You now have a clean baseline for the protected run.

  • Switch back to the Google Apps Script editor from earlier.
  • Confirm CONFIG.maxEmailsPerRun still uses your small test value.
  • Review the duplicate-safety helpers in Code.gs using the map below.

How does the safety chain work?

  • getBlockedKeys_() reads existing statuses from the Contact Log.
  • PROCESSING, DRAFTED, and SENT block the same action key on later runs.
  • runAutomation() also reserves each accepted key in memory during the current run.
  • beginLog_() records PROCESSING before Gmail receives the delivery request.
  • processRecipient_() uses updateLog_() to record DRAFTED, SENT, or ERROR after the attempt.
  • ERROR remains eligible for a later retry because it is absent from the blocked status list.
  • LockService.getScriptLock() allows only one run to control the delivery section at a time.
Create one logged draft

Audit logging ties each delivery attempt to its source action. The first safe run should produce one draft plus one traceable row.

  • Run runAutomation from the editor's function selector.

The execution log prints Processed recipient attempts: 1 when the test recipient completes the protected path.

  • Return to the Google Sheet from earlier.
  • Select the Contact Log tab.
  • Inspect the first data row beneath the frozen headers.

What should the audit row show?

  • action_key identifies the completed action.
  • source_email preserves the address returned by the query.
  • delivered_to shows your test override address.
  • status reads DRAFTED after the draft succeeds.
  • processed_at records when the status was updated.
  • delivery_id contains the Gmail draft ID.
  • error remains blank after a successful attempt.
  • Return to Gmail Drafts from earlier.
  • Open the newly created draft.

You should see one draft addressed to your test override. Its subject and greeting should contain the personalized name from your query result.

That is the safety layer working. One BigQuery action now produces one message plus one visible audit record.

No DRAFTED row or draft?

  • Check that CONFIG.useTestQuery remains true.
  • Confirm CONFIG.deliveryMode remains 'draft'.
  • Verify CONFIG.recipientOverride still contains your own address.

Help me diagnose why runAutomation() did not create a Gmail draft or a DRAFTED Contact Log row.

Prove that the second run is blocked

The saved DRAFTED status gives the workflow evidence that this action has already been handled. A second sequential run tests that memory directly.

Before the second run, choose your prediction: another draft or a blocked recipient.

  • Run runAutomation again from the editor's function selector.

The execution log prints No new recipients to process. The existing action_key has been filtered out before delivery.

  • Return to the Contact Log tab.
  • Count the data rows beneath the header.
  • Return to Gmail Drafts from earlier.
  • Count the duplicate-safe test drafts.

You should still see one Contact Log row plus one duplicate-safe draft. The second run creates no additional message or audit row.

Why does the lock still matter?

The Contact Log blocks work recorded by an earlier run. LockService.getScriptLock() protects the same check when two executions start close together.

SpreadsheetApp.flush() applies pending log changes before lock.releaseLock() lets another execution continue. This keeps the shared audit state current at the handoff.

Did another draft appear?

  • Confirm you selected runAutomation instead of runNaiveDraftTest.
  • Check that the existing Contact Log row still shows DRAFTED.
  • Verify the test query still returns the same action_key.

Help me find why the duplicate-safe workflow created another draft.

Your workflow now remembers completed actions and records every delivery attempt. Next, you will test one controlled live send before scheduling the daily automation.

Test a Live Send and Schedule the Workflow

Your Contact Log in Google Sheets proves that one action key creates one logged draft. The live delivery path remains untested.

A controlled self-send keeps the next test inside your own Gmail inbox. It proves that Google Apps Script can deliver the message before unattended execution begins.

In this step, get ready to:
  • Send one controlled message to your own Gmail inbox.
  • Preview the real BigQuery recipients under final safety settings.
  • Schedule runAutomation with one daily Google Apps Script trigger.
Send one controlled message

The existing test action key is blocked by its DRAFTED row. A fresh key lets the same test record enter delivery once.

This live step stays contained because CONFIG.recipientOverride still points to your own address. CONFIG.liveSendingApproved can remain false for this controlled test.

  • In Code.gs, replace test-action-001 in CONFIG.testQuery with test-action-002.
  • Set CONFIG.deliveryMode to 'send'.

Before you run the workflow, do you expect the new action key to be processed or blocked?

  • In the Apps Script editor, select runAutomation from the function selector.
  • Use the editor run control to start the selected function.

The execution log prints Processed recipient attempts: 1. This confirms that the fresh action key reached the delivery path.

  • Switch back to your Gmail inbox.
  • Open the newest message sent to your address.
  • Return to the Contact Log tab in your Google Sheet.
  • Inspect the newest row for the controlled self-send.

The message contains your personalized subject plus body. The newest Contact Log row shows SENT with a blank delivery ID plus a blank error field.

Self-Send Missing?

  • Check that test-action-002 differs from the action key in the existing DRAFTED row.
  • Confirm that CONFIG.recipientOverride still contains your own address.
  • Confirm that CONFIG.deliveryMode contains 'send'.

Help me debug a missing controlled self-send from runAutomation.

Restore approved production settings

The self-send proves that live delivery works. Production mode now needs your real query plus an empty recipient override.

Your GoogleSQL aliases form the contract between the query result plus the email workflow. Each result needs action_key, email, plus first_name.

  • In Code.gs, inspect the query assigned to CONFIG.liveQuery.
  • Confirm that the selected fields use the aliases action_key, email, plus first_name.

Those aliases let queryRecipients_ normalize every result into the fields used by your message template.

  • Set CONFIG.useTestQuery to false.
  • Clear CONFIG.recipientOverride so its value is ''.

Before you preview the real query, how different do you expect its recipient count to be from the single test row?

  • Select previewRecipients from the Apps Script function selector.
  • Use the editor run control to preview the real recipients.

The execution log prints Recipient count: followed by the real count. It also prints a normalized JSON sample without sending any email.

What Does the Preview Prove?

The preview runs your real BigQuery query through queryRecipients_. It does not call the Gmail delivery methods.

The query follows the Google Cloud project's normal BigQuery billing. Under on-demand pricing, the first 1 TiB processed each month is free.

  • Set CONFIG.liveSendingApproved to true.
  • Set CONFIG.maxEmailsPerRun to 20.

Before you run the final preview, do you expect the recipient count to stay within the cap of 20?

  • Run the selected previewRecipients function again.

The execution log prints the final real recipient count plus a normalized sample. No message is sent during this check.

Real Query Preview Failing?

  • Check that CONFIG.liveQuery contains your complete GoogleSQL query.
  • Confirm that every row includes stable action_key plus email aliases.
  • Confirm that the query location plus project ID still match your BigQuery setup.

Help me debug my real recipient preview.

Create the daily trigger

A daily time-driven trigger turns the validated workflow into unattended automation. It runs under the account that creates it.

This is the highest-stakes switch in the project, so createDailyTrigger checks every production guard before creating anything.

  • In the Apps Script editor, select createDailyTrigger from the function selector.

Before you run it, do you expect the production safety checks to accept the current configuration?

  • Use the editor run control to create the daily trigger.

The execution log confirms that a daily trigger was created for the hour stored in CONFIG.sendHour.

  • Select Triggers in the Apps Script editor's left navigation.
  • Confirm that the panel lists exactly one time-driven trigger for runAutomation.

You will see one daily trigger tied to runAutomation. This confirms that the schedule is active in the configured project time zone.

How Does the Daily Schedule Work?

The createDailyTrigger function removes existing project triggers before creating the new daily trigger. This keeps the workflow at exactly one scheduled run.

With CONFIG.sendHour set to 9, Apps Script runs at a stable time between 9 AM plus 10 AM. The account that creates the trigger owns each scheduled execution.

Trigger Not Created?

  • Confirm that CONFIG.useTestQuery is false.
  • Confirm that CONFIG.deliveryMode is 'send'.
  • Confirm that CONFIG.recipientOverride is ''.
  • Confirm that CONFIG.liveSendingApproved is true.

Help me resolve a failed daily trigger setup.

That is the full handoff complete: your controlled self-send is logged as SENT. Exactly one daily trigger now runs the duplicate-safe workflow.

Secret mission

Run a Production Recovery Drill

Rehearse the controls you need when a scheduled email workflow requires attention. Pause the trigger, inspect its evidence, complete a safe manual test, restore production settings, and document the recovery procedure.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep the automation scheduled, pause its daily trigger, or remove it entirely. Scheduled BigQuery runs can incur query charges.

Cost warning

Google Apps Script does not require an additional purchase. On-demand BigQuery queries are billed by bytes processed.

The first 1 TiB of query data processed each month is free. Pause or Delete is the safer state when you no longer plan to monitor daily runs.

Resources you used:

  • Your Google Sheets audit file with the Contact Log tab and prior test history.
  • Your bound Google Apps Script project containing Code.gs with the approved production configuration.
  • Exactly one daily time-driven trigger for runAutomation.
  • Any remaining Gmail test draft from duplicate-safe testing.
  • Your controlled personalized self-send.
  • Your team-accessible recovery procedure.

Keep everything running

No action needed. Choose this if the daily workflow should continue processing approved recipients.

  • Review the Contact Log after scheduled runs.
  • Monitor the processed bytes from the live BigQuery query.
  • Keep the Google Sheet restricted to people who need operational access.
  • Keep the recovery procedure available to the team.

Pause - I'll come back to this later

Pausing stops scheduled execution while preserving the Sheet, code, history, and recovery procedure.

  • In the Google Sheet, select Extensions.
  • Select Apps Script to return to the bound project.
  • Run deleteAutomationTriggers.
  • Check the Triggers panel to confirm no project trigger remains.
  • Use the recovery procedure when you are ready to resume.
  • Run createDailyTrigger after restoring the approved production configuration.

Delete - I don't want to use this again

This cleanup is permanent. The trigger goes first, which prevents another scheduled run while you remove the remaining resources.

  • In the Google Sheet, select Extensions.
  • Select Apps Script to return to the bound project.
  • Run deleteAutomationTriggers.
  • Check the Triggers panel to confirm no project trigger remains.
  • Delete the remaining test draft from Gmail if it is still present.
  • Delete the controlled self-send from Gmail if you no longer need it.
  • In Google Drive, move the Google Sheet used as the workflow audit log to trash.
  • Permanently delete the Google Sheet and its bound Apps Script project from Google Drive trash.
  • Remove the recovery procedure from its team-accessible location.

Nice Work!

Nice Work!

You did it! Your BigQuery-to-Gmail follow-up system now turns query results into personalized messages on a daily schedule.

What you learned:

  • Connected Google Apps Script to BigQuery. Normalized each result to the required fields: action_key, email, first_name.
  • Turned query rows into personalized Gmail drafts. Exposed the duplicate-send risk through a deliberate second run. Proved the live delivery path with one controlled self-send.
  • Protected every action_key with idempotency backed by the Contact Log. Recorded every attempt as PROCESSING, DRAFTED, SENT, or ERROR. Applied a conservative per-run cap. Prevented overlapping runs with a script lock. Scheduled exactly one daily trigger.
  • Secret Mission: Completed a production recovery drill. Paused every scheduled run. Diagnosed the Contact Log plus Apps Script Executions. Safely validated the workflow by hand. Restored the approved production configuration. Recreated exactly one daily trigger. Documented the recovery procedure for your team.

Ready to quiz yourself?