基于Google Spreadsheet验证Google Form随机码输入的可行性及实现方法

Absolutely doable! This is a super common setup for controlling access to Google Forms with unique codes, and you can make it work using Google Apps Script. Let’s walk through exactly how to build this:

Short answer: Yes! This is a standard use case for Google Forms paired with Google Sheets, and you can implement it with a bit of script magic to validate codes in real-time.

1. Prep Your Google Sheet First #

First, organize your sheet to track valid codes and their usage status:

• Column A: Label it Random Codes (this is where you’ll store all your active, unused codes)
• Column B: Label it Used Status (optional but highly recommended—use "Yes" or "No" to mark if a code has been redeemed, so you don’t get duplicate submissions from the same code)

Make sure your sheet has no blank rows between codes, and the header row is in row 1.

2. Add Validation Logic with Google Apps Script #

Next, we’ll write a script that checks if the code entered in the form exists in your sheet and hasn’t been used yet.

1. Open your Google Form, click the three-dot menu in the top right, and select Script editor.
2. Delete the default myFunction() code, and paste this instead:

// Replace these values with your own Sheet ID and sheet name
const SHEET_ID = "YOUR_GOOGLE_SHEET_ID";
const SHEET_NAME = "Sheet1"; // Or whatever your sheet is named

// This function checks if the entered code is valid and unused
function validateCode(inputCode) {
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
  const allData = sheet.getDataRange().getValues();
  
  // Skip the header row (row 1) and loop through all codes
  for (let i = 1; i < allData.length; i++) {
    const storedCode = allData[i][0].trim();
    const isUsed = allData[i][1]?.trim() === "Yes";
    
    // Match the input code to stored codes (case-sensitive by default)
    if (storedCode === inputCode.trim() && !isUsed) {
      // Mark the code as used to prevent reuse
      sheet.getRange(i + 1, 2).setValue("Yes");
      return true; // Code is valid—allow submission
    }
  }
  return false; // Code is invalid or already used—block submission
}

// This function sets up the validation rule on your form question
function setupFormValidation() {
  const form = FormApp.getActiveForm();
  // Replace "Enter your random code" with the exact title of your code input question
  const codeQuestion = form.getItemByTitle("Enter your random code").asTextItem();
  
  // Create a custom validation that uses our validateCode function
  const validation = FormApp.createTextValidation()
    .setHelpText("Oops! That code is invalid or has already been used. Please enter a valid code.")
    .requireTextMatchesPattern(".+") // Ensure the user doesn't submit an empty field
    .setValidationFunction("validateCode")
    .build();
  
  codeQuestion.setValidation(validation);
}

3. Replace YOUR_GOOGLE_SHEET_ID (you can find this in your sheet’s URL, between /d/ and /edit) and SHEET_NAME with your actual sheet details. Also, update the question title in form.getItemByTitle() to match your form’s code input question.
4. Click the run button ▶️ to execute setupFormValidation. You’ll need to authorize the script to access your form and sheet—follow the prompts (you may need to click "Advanced" > "Go to [Script Name]" to proceed).

3. Test the Workflow #

Now it’s time to make sure everything works:

• Try submitting the form with a valid, unused code: it should go through, and you’ll see the Used Status for that code update to "Yes" in your sheet.
• Try submitting with an invalid code or a code that’s already been used: the form should show an error message and block the submission.

Pro Tips #

• Case Insensitivity: If you don’t want validation to be case-sensitive, change the match line to:

  if (storedCode.toLowerCase() === inputCode.trim().toLowerCase() && !isUsed)
  

• Bulk Adding Codes: If you need to add new codes later, just paste them into Column A with "No" in Column B—no need to re-run the script; validation will check the latest data automatically.
• Sheet Permissions: Make sure your sheet is shared with the Google account you used to create the script, so it can access the data.

内容的提问来源于stack exchange，提问作者madscientist