Create Gmail Reply Drafts from Google Forms Responses
How can I create Gmail reply drafts from Google Forms responses and record each draft ID in Google Sheets?
Have Apps Script read the linked response sheet, create plain-text Gmail drafts, and record each draft ID beside its response. The drafts are new messages with reply-style subjects, not threaded replies. Tracking columns keep ordinary reruns from processing completed rows again. Review every draft by hand.
Use Apps Script to read unprocessed Google Forms responses in the linked sheet, create plain-text drafts through GmailApp.createDraft, and save each draft ID beside the corresponding response. These are new messages with reply-style subjects, not replies attached to existing conversations. Nothing in the script below calls a sending method.
Which response-sheet columns does the Gmail draft script require?
Row 1 must contain exactly one of each required header: Name, Email, Inquiry, Draft ID, and Draft status.
Give the form questions the titles Name, Email, and Inquiry, and make Email and Inquiry required. Add the tracking columns after the columns generated by the form. If you want different question titles, update both the required headers and the field references in the script to match.
| Timestamp | Name | 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. |
Extra columns are fine. The script finds fields by their trimmed header text, but capitalization must match exactly: Email address is not Email. Reserve the two tracking columns for script state. Put general notes in other columns.
The email check allows one address with a plausible format. It cannot verify who owns that address or whether mail will reach it. For contact cleanup outside this workflow, see normalize CRM email addresses before a CSV import. Cleaning an address does not give you permission to send to it.
How do you configure and authorize the Gmail draft creator?
Set the response spreadsheet ID and tab name, then authorize the script with the account that should own the Gmail drafts.
- Open the linked response spreadsheet. Check the response tab and confirm that every required header is present.
- Open Extensions > Apps Script. Put the function below in place of the starter code.
- Set Script Properties. In Project Settings, add
RESPONSE_SPREADSHEET_IDandRESPONSE_SHEET_NAME. The spreadsheet ID is the part of the spreadsheet address between/d/and/edit. Use the tab name as the sheet name. - Save and run
createInquiryDrafts. Grant spreadsheet and Gmail authorization using the account that should own the drafts. - Inspect the tracking cells and Gmail drafts. The function processes existing eligible rows too, not just future submissions.
- Install the trigger. Using that same account, choose
createInquiryDrafts, From spreadsheet, and On form submit.
GmailApp needs authorization, and it acts in the account that granted it. Installed triggers run as the account that created them. That account owns the drafts, not the person who submitted the form.
I would begin with only the sample responses in a separate response sheet. A manual run creates real drafts; it is not a preview. Review the message template before letting the script process a backlog.
What complete Apps Script creates drafts without sending messages?
The function below uses GmailApp.createDraft to create messages and contains no 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();
}
}
A run makes up to 20 attempts to create drafts. Rows with invalid fields do not count toward that limit: validation happens before the attempt counter increases. To work through a backlog, run the function again manually or add an optional time-driven trigger that continues processing eligible rows.
For the greeting, the script reduces whitespace in the name and uses “there” when the name is blank. The inquiry goes into the body as plain text. The answer is still yours to write before sending.
What drafts and tracking values should the sample responses produce?
If both draft-creation attempts succeed, the sample responses should produce two drafts and one row marked FIX EMAIL.
One of the three response rows has an invalid address, so only two are eligible for draft attempts. Each message has the subject Re: Your inquiry. The ID labels in the table represent generated values; do not copy them into the sheet as literal IDs.
| 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 goes to mira@example.com with the following 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]
The draft for Theo contains Theo’s name and inquiry. Zed gets no draft. Replace the response and signature prompts before sending either message, check the recipient, and decide whether to leave the quoted inquiry in the outgoing message.
The DRAFTED status means the function created a draft, nothing more. It does not track subsequent edits, deletion, or sending. Run the function again without changing the sheet and it skips the two completed rows, rejects the same invalid address, and creates zero additional drafts.
How does the script reduce duplicate drafts on reruns?
The script avoids ordinary duplicates by skipping rows with a draft ID or an ineligible status and using a project lock to keep overlapping executions from processing the queue simultaneously.
LockService.getScriptLock coordinates executions only within this script project. A separate project does not share its lock. Keep a second project away from the same response queue.
The function writes CREATING and flushes the change before it calls Gmail. When creation succeeds, it saves the returned draft ID and sets the status to DRAFTED. If an error occurs inside the creation block, it attempts to save a shortened REVIEW message. Rows marked CREATING or REVIEW are not retried automatically.
Excluding those rows from retries avoids a preventable duplicate. Draft creation and the tracking update are separate operations, so an interruption between them can leave a draft in Gmail without an ID in the sheet. I would rather reconcile that row by hand than create another message without checking.
If you need to send reminders rather than prepare drafts, use the separate guide to send one daily task reminder without repeat emails. Do not mix that guide’s sending rules into this draft queue.
How do you fix blocked response rows without duplicating Gmail drafts?
Fix invalid fields directly in the sheet, but check Gmail drafts before resetting any row marked CREATING or REVIEW.
Missing or duplicate header
Put the exact required header back in row 1 of the configured tab, and rename any duplicates. Before rerunning the function, check the data beneath the header. Renaming a column can satisfy header validation without making its contents the right field data.
FIX EMAIL or FIX INQUIRY
Replace the invalid value with one plain email address, or fill in the missing inquiry. Keep the existing status. Both correction statuses remain eligible when the script next processes rows.
A sheet edit does not fire the form-submission trigger. After correcting the row, run createInquiryDrafts manually or wait for an optional time-driven trigger to run it.
CREATING or REVIEW
Pause installed triggers and wait for active executions to finish. In the account that runs the function, look for drafts with the same recipient and inquiry:
- One matching draft: Change the status to
DRAFTED. For this exceptional recovery, the ID can remain blank. - No matching draft: Fix the error, clear the status, and then run the function again.
- Several matches: Sort out the duplicate drafts manually before resetting any tracking values.
Even with a blank ID, a DRAFTED row is skipped because its status makes it ineligible. Do not clear those tracking cells while cleaning up the sheet. Clearing both cells makes the row eligible for another draft.
If authorization fails, run the function manually as the trigger owner and complete authorization. Reconcile the affected rows before turning the triggers back on.
When should you avoid this response-sheet draft workflow?
Do not use this script when you need shared-inbox assignment, guaranteed exactly-once processing, or replies attached to existing conversations.
| 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 |
The createDraft method receives a recipient, subject, and body. This script gives it no original message to reply to. A subject starting with Re: does not, by itself, connect the draft to a conversation.
I would keep this in a small-business stack only while someone has time to review the drafts and reconcile blocked rows. The tracking sheet helps manage message preparation. It is not proof that customer follow-up is complete.
Keep response rows in their original order while the function runs. It writes to the row positions captured during its initial read, so sorting, deleting, or rearranging rows during execution can put tracking values beside the wrong response. Protect the tracking columns from casual edits and keep operational notes in a separate column.