提交时关联表单数据至另一表单的技术问题求助

Hey there, let's work through this problem to get your Google Forms/Sheets setup working smoothly!

The Core Issue #

Right now, you're triggering an update of the second form's grid options every time someone submits the correction form—that's why you're getting duplicate columns. Instead, we only want to add new activities to the correction form's grid when a new activity is submitted via the first admin form.

Step-by-Step Solution #

We'll split this into two parts: updating the correction form's grid only when a new activity is created, and fixing your existing correction form submission script to avoid errors.

1. Add a Trigger for New Activity Submissions

This script will run only when the admin submits a new activity via the first form, adding the new activity to the correction form's grid (and its linked sheet) only if it doesn't already exist.

// Trigger this function when the FIRST form (admin activity submission) is submitted
function onNewActivitySubmitted(e) {
  // 1. Extract the new activity name from the admin form submission
  var newActivityName = "";
  const itemResponses = e.response.getItemResponses();
  
  // Replace "活动名称" with the exact title of the activity name field in your first form
  for (const response of itemResponses) {
    if (response.getItem().getTitle() === "活动名称") {
      newActivityName = response.getResponse();
      break;
    }
  }
  
  if (!newActivityName) return; // Exit if we couldn't find the activity name

  // 2. Access the correction form and its target grid question
  const correctionFormId = "YOUR_CORRECTION_FORM_ID"; // Replace with your second form's ID
  const correctionForm = FormApp.openById(correctionFormId);
  let gridQuestion = null;
  
  // Replace "需要修正的活动" with the exact title of your correction form's grid question
  const formItems = correctionForm.getItems();
  for (const item of formItems) {
    if (item.getTitle() === "需要修正的活动") {
      gridQuestion = item.asGridItem();
      break;
    }
  }
  
  if (!gridQuestion) return; // Exit if we couldn't find the grid question

  // 3. Check if the activity already exists in the grid options
  const existingRows = gridQuestion.getRows();
  const existingActivityNames = existingRows.map(row => row.getTitle());
  
  if (!existingActivityNames.includes(newActivityName)) {
    // Add the new activity to the grid
    gridQuestion.addRow(newActivityName);
    
    // Add a matching column to the correction form's linked sheet
    const correctionSheetId = "YOUR_CORRECTION_SHEET_ID"; // Replace with your second sheet's ID
    const correctionSheet = SpreadsheetApp.openById(correctionSheetId);
    const lastCol = correctionSheet.getLastColumn();
    correctionSheet.getRange(1, lastCol + 1).setValue(newActivityName);
  }
}

To set up the trigger for this function:

• Open the first admin form's script editor
• Go to Edit > Current project's triggers
• Add a new trigger:
  • Choose the function onNewActivitySubmitted
  • Select event type: On form submit
  • Save the trigger

2. Fix Your Correction Form Submission Script

Your existing script has a couple of small errors (like using append on an array, which doesn't exist, and typos with item vs items). Here's the corrected version:

var ss = SpreadsheetApp.openById("1eTE_ZDLJjzLmzwjC-P6LBCFhNd_pgVndfZkUNKLFXkM"); 

function submitFormFunction(e) { 
  // Gather response data from the correction form
  var items = e.response.getItemResponses(); 
  var responses = {}; 
  
  for (var i = 0; i < items.length; i++) { 
    responses[i] = items[i].getResponse(); 
  } 

  // Build the row to write to the sheet
  var responseRow = []; 
  responseRow.push(responses[0]); // Use push() instead of append() for arrays
  
  // Adjust the loop to match your actual response fields
  for (var j = 2; j < items.length - 1; j++) { 
    responseRow.push(responses[j]); 
  } 

  // Write the row to the correct sheet (replace with your sheet name)
  const targetSheet = ss.getSheetByName("Correction Responses");
  targetSheet.appendRow(responseRow); 
}

Key Notes to Avoid Duplicates #

• Disable FormRanger's automatic refresh: Since we're now controlling when options are added via script, FormRanger's auto-refresh will cause conflicts. Turn it off to keep your grid options clean.
• Double-check field titles: Make sure the titles in the script (like "活动名称") match exactly what's in your forms—capitalization and spacing matter!
• Test with a dummy activity: Submit a new activity via the admin form, then check if the correction form's grid has the new option and its linked sheet has a new column (no duplicates!).

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