Google Forms动态生成问题后，如何通过代码同步响应至Google Sheet？

Got it, let's adjust your existing script to collect responses into arrays and append them as rows to your Google Sheet. Since you're regularly resetting form questions, the default Forms-Sheets sync won't work reliably—this custom script will handle that perfectly.

Step 1: Modified Script with Sheet Integration #

Here's the updated code that captures each full response as a row and appends it to your target spreadsheet:

function pushResponsesToSheet() {
  // Replace with your Form ID and Sheet ID
  const FORM_ID = '1234567890abcdefghijklmnopqrstuvwxyz';
  const SHEET_ID = 'your-google-sheet-id-here';
  
  // Open the form and target sheet
  const form = FormApp.openById(FORM_ID);
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('Sheet1'); // Replace with your sheet name
  
  // Get all form responses
  const formResponses = form.getResponses();
  
  // Iterate through each full response
  for (let i = 0; i < formResponses.length; i++) {
    const formResponse = formResponses[i];
    const itemResponses = formResponse.getItemResponses();
    const rowData = [];
    
    // Add timestamp (optional but useful)
    rowData.push(formResponse.getTimestamp());
    
    // Collect each question's response into the row array
    for (let j = 0; j < itemResponses.length; j++) {
      const itemResponse = itemResponses[j];
      const responseValue = itemResponse.getResponse();
      
      // Handle multi-select responses (convert array to comma-separated string)
      const formattedResponse = Array.isArray(responseValue) ? responseValue.join(', ') : responseValue;
      rowData.push(formattedResponse);
    }
    
    // Append the row to the sheet
    sheet.appendRow(rowData);
    Logger.log(`Successfully appended response #${i+1} to sheet`);
  }
}

Key Improvements & Explanations: #

• Sheet Integration: We open your target spreadsheet by ID and specify the sheet name (adjust Sheet1 to match your actual sheet).
• Row-wise Data Collection: For each full form response, we create a rowData array to store all answers (plus an optional timestamp) so they're written as a single row.
• Multi-select Handling: If a question allows multiple answers (like checkboxes), getResponse() returns an array—we convert this to a comma-separated string so it fits in one cell.
• Append to Last Row: sheet.appendRow(rowData) automatically adds the new row to the end of your sheet, no need to calculate the last row index manually.

Optional: Avoid Duplicate Entries #

If you run this script multiple times, it will re-write all responses. To fix this, you can track the last processed timestamp in your sheet (e.g., in a hidden column) and only process responses after that timestamp. Here's a quick add-on:

// Add this inside the function before iterating responses
const lastTimestamp = sheet.getRange(sheet.getLastRow(), 1).getValue();
const filteredResponses = formResponses.filter(response => response.getTimestamp() > lastTimestamp);

// Then iterate over filteredResponses instead of formResponses

How to Use: #

1. Replace FORM_ID, SHEET_ID, and sheet name with your actual IDs/names.
2. Save the script in Google Apps Script (open your Form > Extensions > Apps Script).
3. Run the function pushResponsesToSheet—you'll need to authorize permissions the first time.
4. (Optional) Set up a trigger to run this script automatically when a new response is submitted (Edit > Current project's triggers > Add trigger).

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