求助：Google表单联动表格自动化，Index-Match与Array-Formula适配问题

Absolutely, Google Apps Script is a far better fit for your use case here! The combination of INDEX-MATCH and ARRAYFORMULA can get clunky when dealing with dynamically growing form response data—scripts let you handle this cleanly and automatically whenever a new form submission comes in.

Step 1: Understand the Core Workflow #

• When a new form response lands in your "Form Responses 1" sheet, the script triggers automatically.
• It pulls the "SF Name" from the new response row.
• Looks up associated data (like Region, Role, Team) from your "SF Data" sheet.
• Appends this matched data directly to the end of the new response row.

Step 2: Set Up the Script #

1. Open your Google Sheet, go to Extensions > Apps Script to launch the script editor.
2. Replace the default placeholder code with this:

function onFormSubmit(e) {
  // Access your spreadsheet and target sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formResponsesSheet = ss.getSheetByName("Form Responses 1");
  const sfDataSheet = ss.getSheetByName("SF Data");
  
  // Grab the latest form submission row and its SF Name
  const newRowNum = formResponsesSheet.getLastRow();
  const submittedSFName = formResponsesSheet.getRange(newRowNum, 2).getValue(); // Column B = SF Name
  
  // Load all data from the SF Data sheet for lookup
  const sfDataRange = sfDataSheet.getDataRange().getValues();
  const sfDataHeaders = sfDataRange[0];
  
  // Find column positions for the data we need to pull (adjust names if your sheet uses different labels)
  const regionColIndex = sfDataHeaders.indexOf("Region") + 1;
  const roleColIndex = sfDataHeaders.indexOf("Role") + 1;
  const teamColIndex = sfDataHeaders.indexOf("Team") + 1;
  
  // Initialize variables to store matched data
  let matchedRegion = "";
  let matchedRole = "";
  let matchedTeam = "";
  
  // Loop through SF Data to find the matching row
  for (let i = 1; i < sfDataRange.length; i++) {
    if (sfDataRange[i][1] === submittedSFName) { // Column B in SF Data = SF Name
      matchedRegion = sfDataRange[i][regionColIndex - 1];
      matchedRole = sfDataRange[i][roleColIndex - 1];
      matchedTeam = sfDataRange[i][teamColIndex - 1];
      break; // Stop searching once we find a match
    }
  }
  
  // Write the matched data to the form response row (columns D, E, F here—adjust numbers as needed)
  formResponsesSheet.getRange(newRowNum, 4).setValue(matchedRegion);
  formResponsesSheet.getRange(newRowNum, 5).setValue(matchedRole);
  formResponsesSheet.getRange(newRowNum, 6).setValue(matchedTeam);
}

Step 3: Configure the Automatic Trigger #

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. Set up the trigger with these settings:
   • Choose function: onFormSubmit
   • Choose deployment: Head
   • Event source: From spreadsheet
   • Event type: On form submit
4. Click Save and authorize the script when prompted (you’ll need to allow access to your spreadsheet).

Step 4: Tweak for Your Exact Sheet Setup #

• Double-check column positions: If your "SF Name" is in a different column in the form responses sheet, update the number in formResponsesSheet.getRange(newRowNum, 2) (columns are numbered starting at 1).
• Adjust target columns: The script writes to columns D, E, F—change the numbers in getRange(newRowNum, 4) etc. to match where you want the lookup data to appear.
• Verify header names: Make sure the labels like "Region" and "Role" in the script match exactly what’s in your "SF Data" sheet.

Why This Beats Formulas #

• Hands-off automation: Runs instantly when a form is submitted, no need to drag formulas or fix ARRAYFORMULA glitches.
• Flexibility: You can easily add more lookup fields or adjust logic later without rewriting complex formulas.
• Cleaner data: Avoids formula errors in empty rows or when the form adds new columns dynamically.

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