是否存在Google Forms脚本，可在表单提交后从电子表格依次提取并发放密钥？

Got it, this is totally achievable with Google Apps Script! Let me walk you through exactly how to set this up so every form submission grabs the next unused key from your spreadsheet, marks it as issued, and delivers it to the respondent.

Step 1: Prep Your Key Spreadsheet #

First, create a dedicated Google Sheet to store your keys:

• Make a new Google Sheet (name it something like "Key Repository")
• In column A, list all your keys (one per row, starting from row 2—row 1 will be your header)
• In cell B1, add the header Issued (this column will track which keys have been handed out, starting empty or with FALSE for unused keys)
• Copy your sheet's ID from the URL (it's the long string between /d/ and /edit—keep this handy for later)

Step 2: Add the Script to Your Google Form #

Now head over to your Google Form and set up the script:

1. Open your form, click Extensions > Apps Script to open the script editor
2. Delete the default myFunction() code and paste the script below
3. Replace YOUR_KEY_SHEET_ID with the sheet ID you copied earlier
4. Adjust the sheet name ("Sheet1") if your keys are on a different tab in the spreadsheet

function onFormSubmit(e) {
  // Replace with your key spreadsheet ID
  const KEY_SHEET_ID = "YOUR_KEY_SHEET_ID";
  const keySheet = SpreadsheetApp.openById(KEY_SHEET_ID).getSheetByName("Sheet1");
  const allData = keySheet.getDataRange().getValues();

  // Find the first unused key
  let availableKey = null;
  let keyRow = -1;

  // Skip header row (start at index 1)
  for (let i = 1; i < allData.length; i++) {
    const currentKey = allData[i][0];
    const isIssued = allData[i][1];

    // Check if the key exists and hasn't been issued yet
    if (currentKey && (!isIssued || isIssued === false)) {
      availableKey = currentKey;
      keyRow = i + 1; // Convert array index to spreadsheet row number (rows start at 1)
      break;
    }
  }

  // Handle case where all keys are used up
  if (!availableKey) {
    sendConfirmation(e, "Sorry, all keys have been issued. Please check back later!");
    return;
  }

  // Mark the key as issued
  keySheet.getRange(keyRow, 2).setValue(true);

  // Option 1: Send the key via email (requires form to collect respondent emails)
  const respondentEmail = e.response.getRespondentEmail();
  if (respondentEmail) {
    MailApp.sendEmail({
      to: respondentEmail,
      subject: "Your Unique Key",
      body: `Hi there!\nYour key is: ${availableKey}\nPlease keep it safe.`
    });
  }

  // Option 2: Show the key on the form's confirmation page
  sendConfirmation(e, `Thanks for submitting! Your unique key is: ${availableKey}`);
}

// Helper function to set the form's confirmation message
function sendConfirmation(e, message) {
  const form = FormApp.getActiveForm();
  form.setConfirmationMessage(message);
}

Step 3: Set Up the Form Submit Trigger #

The script needs to run automatically when someone submits the form:

1. In the Apps Script editor, click the clock icon (Triggers) on the left sidebar
2. Click Add Trigger in the bottom right corner
3. Configure the trigger like this:
   • Choose which function to run: onFormSubmit
   • Choose which deployment to run: Head
   • Select event source: From form
   • Select event type: On form submit
4. Click Save—you'll need to authorize the script to access your form and spreadsheet (follow the prompts, you may need to click "Advanced" to allow access)

Important Notes #

• Make sure your form is set to collect respondent emails (go to Settings > Responses and check "Collect email addresses") if you want to send keys via email
• If you don't need email delivery, you can remove the entire MailApp.sendEmail block
• Test the flow by submitting the form yourself—you should see the confirmation message with a key, and the corresponding row in your spreadsheet will have TRUE in column B
• If all keys are used up, the form will show the "all keys issued" message instead

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