Google Forms Prefilled Links from Sheets via Apps Script

Google Forms is a go‑to tool for collecting information, but sending a blank form to each respondent can be time‑consuming and prone to errors. By pre‑filling answers directly from a Google Sheet, you can personalize each questionnaire, reduce manual entry, and improve response rates. This article walks you through the entire workflow: preparing the source data, creating dynamic pre‑filled links with Apps Script, and automatically dispatching those links via personalized email. Whether you’re managing event registrations, employee surveys, or client feedback, the step‑by‑step guide below will equip you with a repeatable process that scales with your data and saves you countless hours.

Understanding Pre‑filled Links in Google Forms

Every Google Form generates a unique URL that can contain query parameters representing individual answers. When these parameters are appended to the base form URL, the form loads with those fields already populated. The key points to grasp are:

• Entry IDs – each form field has an internal identifier (e.g., entry.123456789) that you must reference in the URL.
• URL Encoding – special characters (spaces, commas, line breaks) must be encoded to ensure the link works correctly.
• Read‑only vs. Editable – pre‑filled values can still be edited by the respondent unless you lock the field in the form settings.

Once you know the entry IDs, you can programmatically build a link for every row of data.

Preparing Your Data in Google Sheets

The source spreadsheet should contain one row per recipient and a column for each form field you want to pre‑fill. Follow these best practices:

• Use clear header names that match the form fields (e.g., First Name, Email, Preferred Session).
• Keep a dedicated column for the recipient’s email address – this will be used for the personalized message.
• Remove empty rows and ensure there are no hidden characters that could break URL encoding.
• Optionally add a “Status” column to track whether an email has been sent.

With a clean sheet, you can reliably pull values into the script and generate accurate pre‑filled URLs for each participant.

Generating Pre‑filled URLs with Apps Script

Google Apps Script bridges the sheet and the form. The script performs three core actions:

1. Read the sheet – fetches each row as an object keyed by the header names.
2. Construct the URL – concatenates the base form link with the appropriate entry. parameters, applying encodeURIComponent() to every value.
3. Write back – stores the generated link in a new column (e.g., “Prefilled Link”) for reference or debugging.

A minimal example:

function createPrefilledLinks() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(‘Responses’);
  var data = sheet.getDataRange().getValues();
  var headers = data.shift();
  var baseUrl = ‘https://docs.google.com/forms/d/e/FORM_ID/viewform?’;
  data.forEach(function(row, i) {
    var params = [];
    headers.forEach(function(col, j) {
      if (col.startsWith(‘entry.’)) {
        params.push(col + ‘=’ + encodeURIComponent(row[j]));
      }
    });
    var link = baseUrl + params.join(‘&’);
    sheet.getRange(i+2, headers.length+1).setValue(link);
  });
}

Replace FORM_ID with your actual form ID and ensure the header row contains the exact entry. identifiers. After running the script, each row will have a ready‑to‑use pre‑filled link.

Automating Personalized Email Delivery

With links in place, the next step is to email them. Apps Script can also send Gmail messages, allowing you to tailor the subject line and body for each recipient. Key steps include:

• Retrieve the email address and pre‑filled link from the same row.
• Compose a friendly message that references the recipient’s name and explains why the form is being sent.
• Use MailApp.sendEmail() to dispatch the email, optionally adding HTML formatting for a polished look.
• Update the “Status” column to “Sent” and record the timestamp to avoid duplicate sends.

Sample email routine:

function sendPrefilledEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(‘Responses’);
  var rows = sheet.getDataRange().getValues();
  var headers = rows.shift();
  var emailIdx = headers.indexOf(‘Email’);
  var linkIdx = headers.indexOf(‘Prefilled Link’);
  rows.forEach(function(row, i) {
    var email = row[emailIdx];
    var link = row[linkIdx];
    if (email && link) {
      var subject = ‘Your Personalized Survey Invitation’;
      var body = ‘Hi ‘ + row[headers.indexOf(‘First Name’)] + ‘,

‘ +
        ‘Thank you!’;
      MailApp.sendEmail({to: email, subject: subject, htmlBody: body});
      sheet.getRange(i+2, headers.indexOf(‘Status’)+1).setValue(‘Sent’);
    }
  });
}

Running this function sends a customized email to every participant, dramatically cutting down manual effort.

Testing, Troubleshooting, and Best Practices

Before launching at scale, perform a few sanity checks:

• Validate a sample link – open it in an incognito window to ensure all fields populate correctly.
• Check email deliverability – send a test email to yourself and verify formatting and link functionality.
• Monitor quota limits – Gmail imposes daily sending caps; consider using a service account or batching if you exceed them.
• Secure sensitive data – pre‑filled URLs expose information in the URL string; avoid using them for confidential data.

Maintain a versioned backup of both the sheet and the script. If you need to modify form fields, update the entry. headers in the sheet and re‑run the link‑generation function. Regularly audit the “Status” column to spot any rows that failed to send and resend them manually if needed.

By following these steps—understanding how pre‑filled URLs work, preparing clean data, automating link creation, and dispatching personalized emails—you can turn a repetitive manual task into a smooth, scalable workflow. The combination of Google Forms, Sheets, and Apps Script offers a powerful, cost‑free solution for any organization that needs to gather data efficiently while delivering a tailored experience to each respondent.