Create Gmail Reply Drafts from Google Forms Responses

How does Google Forms become a queue for Gmail drafts?

Google Apps Script reads the linked Google Forms response sheet, creates plain-text Gmail drafts, and saves their IDs in tracking columns.

The drafts are new messages with reply-style subjects, not threaded replies: form submissions provide no original Gmail message. Nothing is sent automatically. Drafts belong to the executing account, including the trigger creator for installed triggers.

Rows with a draft ID are skipped. A CREATING marker written before contacting Gmail reduces duplicate risk after interrupted executions.

Which response-sheet columns does the Gmail draft script require?

The Gmail draft script requires exactly one each of Name, Email, Inquiry, Draft ID, and Draft status in row 1.

Use form questions titled Name, Email, and Inquiry; require Email and Inquiry. Add the tracking columns after form-generated columns. Different question titles require matching changes to the script’s required headers and references.

Timestamp	Name	Email	Inquiry	Draft ID	Draft status
7/14/2026 09:00:00	Fictional Mira	mira@example.com	Can you explain your remote onboarding process?
7/14/2026 09:05:00	Fictional Theo	theo@example.com	Can we discuss a team workshop next week?
7/14/2026 09:10:00	Fictional Zed	not-an-email	Please send more information.

These fictional examples use reserved example.com addresses. Columns are located by header, so additional columns are allowed. Email validation checks formatting, not ownership or deliverability.

How do you configure and authorize the Gmail draft creator?

Configure the spreadsheet-bound Apps Script project with the response spreadsheet ID and tab name, then authorize the intended Gmail account.

1. Open the response spreadsheet. From Google Forms, open the linked spreadsheet through Responses; confirm the response tab’s headers.
2. Open Apps Script. Choose Extensions > Apps Script and replace the starter code with the script below.
3. Set Script Properties. In Project Settings, add RESPONSE_SPREADSHEET_ID and RESPONSE_SHEET_NAME. The ID is between /d/ and /edit in the spreadsheet address; the sheet name is the tab name, such as Form Responses 1.
4. Save and run createInquiryDrafts. Authorize spreadsheet and Gmail access with the account intended to own the drafts.
5. Inspect results. Check tracking cells and that account’s Gmail Drafts folder. Existing eligible responses create real drafts.
6. Install the trigger. In Triggers, select createInquiryDrafts, From spreadsheet, and On form submit. Use the same account.

Each execution attempts at most 20 drafts. Manually rerun for backlogs or add a time-driven trigger, which also picks up corrected rows. No API key is required.

What complete Apps Script creates drafts without sending messages?

The following createInquiryDrafts function uses GmailApp.createDraft to create plain-text drafts without any sending call.

function createInquiryDrafts() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const properties = PropertiesService.getScriptProperties();
    const spreadsheetId = properties.getProperty(
      "RESPONSE_SPREADSHEET_ID"
    );
    const sheetName = properties.getProperty("RESPONSE_SHEET_NAME");

    if (!spreadsheetId || !sheetName) {
      throw new Error(
        "Set RESPONSE_SPREADSHEET_ID and RESPONSE_SHEET_NAME " +
        "in Script Properties."
      );
    }

    const sheet = SpreadsheetApp.openById(spreadsheetId)
      .getSheetByName(sheetName);

    if (!sheet) {
      throw new Error("Response sheet not found: " + sheetName);
    }

    const rows = sheet.getDataRange().getDisplayValues();
    const headers = rows[0].map(value => value.trim());
    const required = [
      "Name", "Email", "Inquiry", "Draft ID", "Draft status"
    ];
    const columns = {};

    for (const header of required) {
      const matches = headers.filter(value => value === header);
      if (matches.length !== 1) {
        throw new Error(
          'Expected exactly one header named "' + header + '".'
        );
      }
      columns[header] = headers.indexOf(header);
    }

    const eligibleStatuses = ["", "FIX EMAIL", "FIX INQUIRY"];
    const maxAttempts = 20;
    let attempts = 0;

    for (let index = 1; index < rows.length; index++) {
      if (attempts >= maxAttempts) {
        break;
      }

      const row = rows[index];
      const draftId = row[columns["Draft ID"]].trim();
      const status = row[columns["Draft status"]].trim();

      if (draftId || !eligibleStatuses.includes(status)) {
        continue;
      }

      const name = row[columns["Name"]].trim();
      const email = row[columns["Email"]].trim();
      const inquiry = row[columns["Inquiry"]].trim();

      if (!name && !email && !inquiry) {
        continue;
      }

      const rowNumber = index + 1;
      const idCell = sheet.getRange(
        rowNumber, columns["Draft ID"] + 1
      );
      const statusCell = sheet.getRange(
        rowNumber, columns["Draft status"] + 1
      );

      const emailPattern = /^[^\s@,;<>]+@[^\s@,;<>]+\.[^\s@,;<>]+$/;
      if (!emailPattern.test(email)) {
        statusCell.setValue("FIX EMAIL");
        continue;
      }

      if (!inquiry) {
        statusCell.setValue("FIX INQUIRY");
        continue;
      }

      const greetingName = name.replace(/\s+/g, " ") || "there";
      const subject = "Re: Your inquiry";
      const body = [
        "Hi " + greetingName + ",",
        "",
        "Thanks for your inquiry. You wrote:",
        "",
        inquiry,
        "",
        "[Replace this line with your response before sending.]",
        "",
        "Best,",
        "[Your name]"
      ].join("\n");

      // Persist the claim before asking Gmail to create anything.
      statusCell.setValue("CREATING");
      SpreadsheetApp.flush();
      attempts++;

      try {
        const draft = GmailApp.createDraft(email, subject, body);
        idCell.setValue(draft.getId());
        statusCell.setValue("DRAFTED");
        SpreadsheetApp.flush();
      } catch (error) {
        // Never automatically retry an ambiguous Gmail operation.
        const message = String(error.message || error)
          .replace(/\s+/g, " ")
          .slice(0, 200);
        statusCell.setValue("REVIEW: " + message);
        SpreadsheetApp.flush();
      }
    }
  } finally {
    lock.releaseLock();
  }
}

The lock coordinates executions only within this script project. Do not install another project against the same queue.

What drafts and tracking values should the sample responses produce?

The sample responses should create Gmail drafts for Fictional Mira and Fictional Theo and mark Fictional Zed’s invalid address FIX EMAIL.

Both drafts use Re: Your inquiry. The ID labels below are placeholders for Gmail-generated values.

Name	Draft ID	Draft status
Fictional Mira	Gmail-generated ID for Mira’s draft	DRAFTED
Fictional Theo	Gmail-generated ID for Theo’s draft	DRAFTED
Fictional Zed		FIX EMAIL

Mira’s draft is addressed to mira@example.com with this body:

Hi Fictional Mira,

Thanks for your inquiry. You wrote:

Can you explain your remote onboarding process?

[Replace this line with your response before sending.]

Best,
[Your name]

Theo receives the same template with Theo’s name and inquiry; Zed receives no draft. Before manually sending, replace both bracketed placeholders and verify the recipient.

DRAFTED records creation, not later edits, deletion, or sending. Reruns skip the completed rows and recheck Zed’s address.

Why does createInquiryDrafts use GmailApp.createDraft and tracking columns to queue messages?

createInquiryDrafts uses GmailApp.createDraft to prepare editable messages without sending them, while tracking columns control which responses remain eligible.

First, getScriptLock and waitLock coordinate executions within the project rather than allowing overlapping processing. Script Properties select the spreadsheet and tab explicitly. getDisplayValues reads the sheet as text, then trimmed headers map fields by name instead of fixed column positions.

The loop checks draft IDs and eligible statuses before processing response fields, limiting each execution to 20 creation attempts rather than draining the entire queue. It trims inputs, skips empty responses, and checks email formatting and inquiry content before building a message. The greeting normalizes whitespace and supplies a fallback; join("\n") assembles a plain-text body containing the inquiry and editable placeholders.

Before contacting Gmail, the script writes CREATING and calls flush to persist that claim rather than recording progress only afterward. GmailApp.createDraft then creates a new message with the reply-style subject. Its returned ID and DRAFTED status record completion. The catch block records a shortened REVIEW message instead of automatically retrying. Finally, releaseLock frees the project lock even when processing exits through an error.

How do you fix blocked response rows without duplicating Gmail drafts?

Fix invalid fields directly, but manually reconcile CREATING or REVIEW rows with Gmail before making them eligible again.

Missing or duplicate header

Symptom: Execution reports Expected exactly one header named "Email"., or another required header.

Exact fix: Restore that exact header in row 1 of the configured tab; rename duplicates. Confirm the column contains the intended data, then rerun. Capitalization matters: Email address does not match Email.

FIX EMAIL or FIX INQUIRY

Symptom: A row has no draft ID and identifies an invalid field.

Exact fix: Enter one plain email address, such as zed@example.com, or supply the missing inquiry. Do not include display names or multiple recipients. Leave the status unchanged and rerun manually or through a periodic trigger; editing Sheets does not fire the submission trigger.

CREATING or REVIEW

Symptom: A row without a draft ID remains skipped.

Exact fix: Pause installed triggers and wait for active executions to finish. Inspect the executing account’s drafts for the recipient and matching inquiry:

• One matching draft: Set status to DRAFTED; the ID may remain blank for this exceptional recovery.
• No matching draft: Address the error, clear the status, and rerun.
• Several matches: Resolve duplicates manually before resetting anything.

For authorization errors, manually run as the trigger owner and complete authorization. For quota errors, wait until the account limit permits another attempt. Resume triggers after recovery.

Gmail creation and sheet writes are not one transaction, so ambiguous outcomes require review.

When should you avoid this response-sheet draft workflow?

Avoid this Apps Script workflow when you need shared-inbox assignment, guaranteed exactly-once processing, or replies inside existing Gmail threads.

Requirement	Better approach
Shared assignment and conversation history	Help desk or shared-inbox workflow
Large queues with exactly-once requirements	Queued integration with durable reconciliation
Existing conversation threads	Start from incoming Gmail messages

Each run reads the sheet, and Gmail account limits apply. Draft IDs do not synchronize subsequent Gmail activity.

Do not sort, delete, or rearrange response rows during execution: writes use row positions from the initial read. Use filter views for review and protect tracking columns against accidental edits.