修复谷歌表格多行动态依赖下拉列表“Loading...”错误及扩展方案

Let's work through your Google Apps Script issues and get those dynamic dropdowns working reliably, plus extend the functionality to your E and F columns.

First: Diagnosing the "Loading..." Issue in Column C #

Your original script had a few key problems that caused the empty dropdown with "Loading...":

1. Incorrect range handling: It pulled the entire column (including empty rows) for validation, which confused Sheets' dropdown renderer.
2. Fragile indexing: The logic to find the matching consignor column was prone to errors if your Consignees sheet structure shifted slightly.
3. No empty value handling: It didn't properly clear the child column when the parent cell was emptied.

Fixed Code for Column C (Consignee) #

Replace your existing onEdit function with this revised version, which addresses all those issues:

function onEdit(e) {
  const tabLists = "Consignees";
  const tabValidation = "Orders";
  const ss = e.source;
  const activeSheet = ss.getActiveSheet();
  const activeCell = e.range;

  // Only trigger for edits to Column B (Consignor) in the Orders sheet, row 2+
  if (activeSheet.getName() !== tabValidation || activeCell.getColumn() !== 2 || activeCell.getRow() <= 1) {
    return;
  }

  const consignorValue = activeCell.getValue();
  const childCell = activeCell.offset(0, 1); // Column C (Consignee)

  // Clear child cell if parent is empty
  if (!consignorValue) {
    childCell.clearContent().clearDataValidations();
    return;
  }

  const datass = ss.getSheetByName(tabLists);
  // Get the row of consignor headers (B1 to last column in Consignees)
  const consignorHeaders = datass.getRange(1, 2, 1, datass.getLastColumn() - 1).getValues()[0];
  const targetColumn = consignorHeaders.indexOf(consignorValue) + 2; // Convert to column number

  // If no matching consignor found, clear child cell
  if (targetColumn === 1) {
    childCell.clearContent().clearDataValidations();
    return;
  }

  // Get only non-empty values from the target consignee column
  const allValues = datass.getRange(2, targetColumn, datass.getLastRow() - 1).getValues();
  const validOptions = allValues.filter(row => row[0] !== "").flat();

  // Create validation rule (allow new entries with the second `true` parameter)
  const validationRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(validOptions, true)
    .setAllowInvalid(false) // Set to `true` if you want to allow free-form input
    .setHelpText("Select a consignee or enter a new one")
    .build();

  childCell.setDataValidation(validationRule);
}

Key Improvements: #

• Uses the e event object for more reliable sheet/range access
• Filters out empty rows from the validation options
• Adds clear handling for empty parent cells
• Uses requireValueInList instead of range-based validation to avoid loading delays
• Explicitly allows new entries (adjust setAllowInvalid if you want to restrict input to existing options)

Expanding to E & F Columns (Destinations) #

To extend this functionality to your E and F columns, we'll generalize the script to handle multiple dependency pairs. First, set up your Destinations sheet to mirror the Consignees structure:

1. Destinations Sheet Setup:
   • A2: =SORT(UNIQUE(Orders!D2:D),1,TRUE) (unique values from your trigger column for E, e.g., Origin)
   • B1: =TRANSPOSE(A2:A) (horizontal list of trigger values)
   • B2: =SORT(UNIQUE(FILTER(Orders!$E$2:$E,Orders!$D$2:$D=B1)),1,TRUE) (matching options for E column)
   • C2: =SORT(UNIQUE(FILTER(Orders!$F$2:$F,Orders!$E$2:$E=B1)),1,TRUE) (matching options for F column, adjust if F depends on E instead)
   • Drag B2/C2 across to cover all needed columns

Extended Script for All Columns #

This version handles three dependency pairs (B→C, D→E, E→F) and is easy to modify for more columns later:

function onEdit(e) {
  const ss = e.source;
  const activeSheet = ss.getActiveSheet();
  const activeCell = e.range;
  const tabValidation = "Orders";

  // Ignore edits outside the Orders sheet
  if (activeSheet.getName() !== tabValidation) {
    return;
  }

  // Define all dependency rules: parent column → {child column, data sheet, header row, start column}
  const dependencies = {
    2: { // B (Consignor) → C (Consignee)
      childColumn: 3,
      dataSheet: "Consignees",
      headerRow: 1,
      startColumn: 2
    },
    4: { // D (Origin) → E (Destination)
      childColumn: 5,
      dataSheet: "Destinations",
      headerRow: 1,
      startColumn: 2
    },
    5: { // E (Destination) → F (Delivery Type)
      childColumn: 6,
      dataSheet: "Destinations",
      headerRow: 1,
      startColumn: 3 // Adjust if your F options start at a different column
    }
  };

  // Check if the edited column is a parent column in our rules
  const currentRule = dependencies[activeCell.getColumn()];
  if (!currentRule) {
    return;
  }

  const { childColumn, dataSheet, headerRow, startColumn } = currentRule;
  const parentValue = activeCell.getValue();
  const childCell = activeSheet.getRange(activeCell.getRow(), childColumn);

  // Clear child cell if parent is empty
  if (!parentValue) {
    childCell.clearContent().clearDataValidations();
    return;
  }

  const dataSheetObj = ss.getSheetByName(dataSheet);
  if (!dataSheetObj) {
    console.error(`Data sheet "${dataSheet}" not found!`);
    return;
  }

  // Get headers from the data sheet to find the matching parent value
  const headers = dataSheetObj.getRange(headerRow, startColumn, 1, dataSheetObj.getLastColumn() - startColumn + 1).getValues()[0];
  const targetColumn = headers.indexOf(parentValue) + startColumn;

  // No match found? Clear child cell
  if (targetColumn < startColumn) {
    childCell.clearContent().clearDataValidations();
    return;
  }

  // Get only non-empty options from the target column
  const allValues = dataSheetObj.getRange(headerRow + 1, targetColumn, dataSheetObj.getLastRow() - headerRow).getValues();
  const validOptions = allValues.filter(row => row[0] !== "").flat();

  // Create validation rule
  const validationRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(validOptions, true)
    .setAllowInvalid(false) // Set to `true` to allow free-form input
    .setHelpText("Select an option or enter a new one")
    .build();

  childCell.setDataValidation(validationRule);
}

How to Adjust This for Your Setup: #

• Modify the dependencies object to match your actual column pairs and sheet names
• Update startColumn values if your data in Destinations starts at a different column
• Toggle setAllowInvalid to true if you want users to be able to enter completely new values (they'll automatically appear in the dropdown after the data sheet formulas refresh)

Final Notes #

• Test the script by editing a parent column (e.g., B, D, or E) and verifying the child column loads the correct dropdown options
• When users enter new values, your Consignees/Destinations sheet formulas will automatically update the unique lists within a few seconds
• If you run into permission issues, make sure you've authorized the script the first time you run it manually

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