使用Google Apps Script在Google表格中添加下拉列表报错：TypeError: ss.addListItem is not a function 的解决求助

Let's break down why your original code isn't working and walk through the correct way to add a dropdown to your sheet.

Key Issues in Your Original Code #

• Wrong Method: addListItem() is a function for Google Forms, not Google Sheets. In Sheets, we use DataValidation to create dropdown menus.
• Custom Function Limitation: Cell-based custom functions (like =dataDrpdwon()) can only return values—they can't modify the spreadsheet's structure (like adding data validation). That's why you're hitting an error when trying to run it directly in a cell.

Correct Approach: Use a Script to Add Data Validation #

Instead of a cell function, create a script you can run manually (or attach to a button) to add the dropdown to your target cell. Here's a working example:

Working Code #

function addCatDogDropdown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); // Gets the currently open sheet
  var targetCell = sheet.getRange("A1"); // Update this to your desired cell
  
  // Define your dropdown options
  var dropdownChoices = ["Cats", "Dogs"];
  
  // Build the data validation rule
  var validationRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(dropdownChoices)
    .setAllowInvalid(false) // Block entries not in the list
    .setHelpText("Select Cats or Dogs") // Optional: Add hover help text
    .build();
  
  // Apply the rule to the target cell
  targetCell.setDataValidation(validationRule);
  
  // Optional: Add a prompt in the cell
  targetCell.setValue("Do you prefer cats or dogs?");
}

How to Use This Script #

1. Open your Google Sheet.
2. Go to Extensions > Apps Script to open the script editor.
3. Replace any existing code with the code above.
4. Save the script (click the floppy disk icon) and name it something like "DropdownTool".
5. Run the function: Select addCatDogDropdown from the function dropdown in the editor, then click the play button. You'll need to authorize the script the first time it runs.

Customizing the Script #

• Change the target cell: Modify sheet.getRange("A1") to point to your desired cell (e.g., "B3", "C5").
• Add more options: Update the dropdownChoices array (e.g., ["Cats", "Dogs", "Both"]).
• Allow custom entries: Remove .setAllowInvalid(false) if you want users to input values not in the dropdown list.

Alternative: Add a Button to Run the Script #

For easy access, attach the script to a button in your sheet:

1. Click Insert > Drawing and create a button (e.g., a rectangle with text like "Add Dropdown").
2. Save the drawing, then click on it and select the three dots > Assign script.
3. Enter addCatDogDropdown (the name of your function) and click OK. Now clicking the button will add the dropdown instantly.

Content of the question comes from Stack Exchange, asked by john