Send pre-filled personalized form links

Northwind’s quarterly check-in form asks every client for their name, email and a few short answers. The name and email are already on file, which means every client opens the form, retypes the two fields they shouldn’t have to, and then gets to the real questions in a slightly worse mood.

Pre-filled links fix this in one pass. The script reads the client roster, asks the form to build a unique URL per client with the name and email already populated, emails each one their personal link, and records the link in the sheet so it can be reused. Clients open the form, see their details in place, and answer the one or two questions that actually need answering.

What you’ll need

• A Clients sheet with columns name, email, and prefilledFormUrl. Leave prefilledFormUrl empty for clients you haven’t sent yet — the script skips any row that already has one.
• A Google Form with two text questions titled Your name and Your email. Add more pre-fillable questions by extending the nameItem/emailItem pattern below.
• The form ID and the clients spreadsheet ID, pasted into the constants at the top.

The script

// IDs for the form being pre-filled and the clients sheet. Both come
// from the URL of the file in Drive.
const FORM_ID = '1abcFormId';
const CLIENTS_SHEET_ID = '1abcClientsId';

// Titles of the form questions the script will pre-fill. Edit these
// to match your form exactly — including punctuation and case.
const NAME_QUESTION = 'Your name';
const EMAIL_QUESTION = 'Your email';

/**
 * Walks the client roster and emails each one a form link with their
 * name and email already filled in. Skips rows that already have a
 * URL, so it is safe to re-run.
 */
function sendPrefilled() {
  const form = FormApp.openById(FORM_ID);

  // 1. Find the two questions we want to pre-fill. Throw early if
  //    either is missing — silently sending blank pre-fills is worse
  //    than a clear failure.
  const items = form.getItems();
  const nameItem = items.find((i) => i.getTitle() === NAME_QUESTION);
  const emailItem = items.find((i) => i.getTitle() === EMAIL_QUESTION);
  if (!nameItem || !emailItem) {
    Logger.log('Could not find both form questions: ' + NAME_QUESTION + ', ' + EMAIL_QUESTION);
    return;
  }

  const sheet = SpreadsheetApp.openById(CLIENTS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;

  // 2. Header->column index. Re-orderable columns, no string-magic.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  if (col.name === undefined || col.email === undefined || col.prefilledFormUrl === undefined) {
    Logger.log('Clients sheet must have name, email and prefilledFormUrl columns.');
    return;
  }

  let sent = 0;
  rows.forEach((r, i) => {
    // 3. Skip clients we have already sent — re-running the job won't
    //    spam anyone. Clear the cell to resend.
    if (r[col.prefilledFormUrl]) return;
    if (!r[col.email]) return;

    // 4. Build a "response" without submitting it. toPrefilledUrl
    //    serialises the answers into the form's prefill query string.
    const response = form.createResponse();
    response.withItemResponse(nameItem.asTextItem().createResponse(r[col.name]));
    response.withItemResponse(emailItem.asTextItem().createResponse(r[col.email]));
    const url = response.toPrefilledUrl();

    // 5. Record the URL in-memory so a one-time write at the end
    //    persists everything.
    values[i + 1][col.prefilledFormUrl] = url;

    // 6. Send the email. Plain body keeps it from landing in spam.
    GmailApp.sendEmail(
      r[col.email],
      'Quick form — pre-filled for you',
      'Hi ' + r[col.name] + ',\n\n' +
      'Your quarterly check-in form is ready. We have already filled in ' +
      'your name and email — just answer the remaining questions:\n\n' +
      url + '\n\nThanks,\nNorthwind'
    );
    sent++;
  });

  // 7. Write the sheet back once at the end — cheaper than a write
  //    per row and keeps us under the per-execution quota.
  sheet.getDataRange().setValues(values);
  Logger.log('Sent ' + sent + ' pre-filled links.');
}

How it works

1. The script opens the form and locates the two questions to pre-fill by title. A missing question logs and exits — better than emailing dozens of blank pre-fills.
2. It reads the clients sheet in one call and builds a header->index map so the column order can be rearranged in the sheet without breaking the script.
3. For each client row, it skips any that already have a prefilledFormUrl (so the script is idempotent) or no email address (nothing to send to).
4. form.createResponse() builds an unsaved response object; withItemResponse attaches the two answers, and toPrefilledUrl() serialises them into the form’s prefill query string. No row is added to the response sheet — it’s URL generation only.
5. The URL is recorded back into the in-memory values array. A single setValues call at the end persists every change.
6. The email body is plain text and addresses the client by name, which keeps it out of “looks like marketing” spam filters.

Example run

The Clients sheet on Monday:

name	email	prefilledFormUrl
Acme Joinery	[email protected]	(empty)
Bramble & Co	[email protected]	(empty)
Coastal Logistics	[email protected]	https://…&entry.1=Coastal…

The script processes Acme and Bramble (Coastal is skipped — already sent), generates two prefill URLs, writes them into the sheet, and emails each client. The next run sends nothing new until someone clears one of those cells or a new row is added.

A pre-filled URL ends up looking like:

https://docs.google.com/forms/d/e/.../viewform?usp=pp_url&entry.123=Acme%20Joinery&entry.456=hello%40acme.test

The client clicks it, sees their name and email already in the form, and gets straight to the new questions.

Run it

This is an on-demand job — usually run once at the start of each quarterly cycle, not on a schedule:

1. In the Apps Script editor, select sendPrefilled and click Run.
2. Approve the authorisation prompt the first time — it needs Forms, Sheets and Gmail scopes.
3. Check the execution log to confirm the send count matches the number of empty prefilledFormUrl rows. Send yourself a row first as a smoke test.

To send to a new batch later, add the new rows to the sheet (leaving prefilledFormUrl empty) and run again. To resend to a specific client, clear their prefilledFormUrl cell first.

Watch out for

• Question titles must match exactly. Rename Your name to Full name on the form and the script logs an error rather than emailing blank pre-fills.
• This only pre-fills text questions. Multiple-choice, checkbox, scale and date items have their own create-response shapes — extend the withItemResponse pattern as needed.
• Pre-filled URLs are not secrets, but they are personal. Anyone with the link can submit as that client. Don’t post them publicly, and don’t include sensitive defaults like billing IDs.
• GmailApp.sendEmail counts against the daily quota (100 for free, 1500 for Workspace). For a roster bigger than that, throttle the loop with a per-day cap and pick up where you left off next run.
• The “skip if already sent” rule is the URL being non-empty. If you ever edit the form’s questions, clear the column to force fresh URLs — otherwise old links pre-fill into the wrong fields.