如何批量提取Google Sheets数据验证‘列表项’的准则内容？

Manual copy-pasting hundreds of data validation list items is such a tedious grind—let’s automate this with Google Apps Script. This solution will scan your target cells, pull out the list items from their data validation rules, and write them directly to a specified location (we’ll use the cell to the right by default, but you can tweak this easily).

Step 1: Open the Script Editor #

• In your Google Sheet, click Extensions > Apps Script to launch the script editor.
• Delete any default code that’s already in the editor.

Step 2: Paste the Custom Script #

Replace the empty editor with this code:

function extractDataValidationLists() {
  // Grab your active spreadsheet and the sheet you're working on
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  
  // Define the range of cells with data validation (adjust this to match your sheet)
  // Example: If your Conditions are in column D and Operations in E, use "D2:E" to skip headers
  const targetRange = sheet.getRange("D2:E");
  const dataValidations = targetRange.getDataValidations();
  
  // Loop through every cell in your target range
  for (let row = 0; row < dataValidations.length; row++) {
    for (let col = 0; col < dataValidations[row].length; col++) {
      const dvRule = dataValidations[row][col];
      // Only process cells with LIST-type data validation
      if (dvRule && dvRule.getType() === SpreadsheetApp.DataValidationCriteria.LIST) {
        let listItems = dvRule.getCriteriaValues()[0];
        // If the list references another range, convert it to a comma-separated string
        if (listItems instanceof Range) {
          listItems = listItems.getValues().flat().join(", ");
        }
        // Write the extracted list to the cell immediately to the right (adjust offset as needed)
        sheet.getRange(targetRange.getRow() + row, targetRange.getColumn() + col + 1).setValue(listItems);
      }
    }
  }
  
  SpreadsheetApp.getUi().alert("Extraction done! Check the cells next to your target range for the list items.");
}

Step 3: Customize the Script #

• Adjust the target range: Change "D2:E" to the actual range where your data validation cells live (e.g., "C2:C" if all rules are in column C starting at row 2).
• Change output location: If you don’t want the list items in the cell to the right, modify the getRange line. For example, to overwrite the original cell content, remove the +1 from the column parameter.

Step 4: Run the Script #

• Click the run button (▶️) in the script editor.
• You’ll be asked to authorize the script (it needs access to your sheet to work). Follow the prompts—you may need to click "Advanced" and "Go to [Script Name]" to trust it (it’s safe, you wrote it!).
• Once authorized, the script will run, and you’ll get an alert when it’s finished.

Quick Notes #

• This script only handles LIST-type data validation rules (dropdown lists). Cells with other validation types (like number ranges) will be skipped automatically.
• If your list items are pulled from another range in the sheet, the script will convert that range into a clean comma-separated string.
• For large datasets (hundreds of cells), the script might take a few seconds to run—no need to refresh the sheet mid-process.

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