Google Apps Script搜索功能异常：请求修正转换Google Sheets QUERY查询逻辑的脚本代码

Let's walk through the issues in your original code and fix them step by step. Your goal is to replicate the QUERY formula's behavior of finding suppliers where the SUPP column contains "IMPEX" and outputting the results starting at D15—here's how to make that work:

Issues in the Original Code #

• Function syntax mistake: function searchName{ is missing parentheses, should be function searchName() {
• Invalid comment: The line var ws = ss.getSheetByName("Sheet1");/ uses a single / instead of // for a comment
• Case-sensitive variable error: Var D15 should be var d15 (JavaScript is case-sensitive, and variable names start with lowercase by convention)
• Incorrect condition check: data[i][1] = "IMPEX" uses assignment (=) instead of comparison, and it only matches exact equality—we need to check if the cell value contains "IMPEX"
• Non-existent method: d15.displayRow(result); isn't a valid Google Apps Script method; we use setValues() to write data to a range
• Poor performance: Writing to the sheet inside the loop is inefficient—we should collect all results first, then write them once

Corrected Code #

function searchName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Sheet1");
  // Get all data from A1 to C360 (includes header row)
  var dataRange = ws.getRange("A1:C360");
  var data = dataRange.getValues();
  
  // Define the range where results will be placed (D15:F, adjust rows as needed)
  var outputRange = ws.getRange("D15:F");
  // Clear previous results first to avoid leftover data
  outputRange.clearContent();
  
  var result = [];
  // Skip the header row (i starts at 1 instead of 0)
  for (var i = 1; i < data.length; i++) {
    var suppValue = data[i][1];
    // Check if the SUPP column value contains "IMPEX" (case-insensitive, remove .toLowerCase() if you want case-sensitive)
    if (suppValue && suppValue.toLowerCase().includes("impex")) {
      result.push(data[i]);
    }
  }
  
  // Only write results if there are any
  if (result.length > 0) {
    // Resize the output range to match the number of result rows
    var targetRange = ws.getRange("D15:F" + (15 + result.length - 1));
    targetRange.setValues(result);
  }
}

Key Improvements Explained #

1. Case-insensitive search: Added .toLowerCase() so it matches "IMPEX", "impex", "Impex", etc.—remove that part if you want exact case matching
2. Clears old results: Makes sure previous searches don't linger in the output area
3. Efficient writing: Collects all matching rows first, then writes them to the sheet in one go (way better for performance than writing inside the loop)
4. Handles empty values: Checks suppValue exists before trying to use .includes() to avoid errors if a cell is blank
5. Resizes output range: Ensures we only write to the exact number of rows needed, no extra empty rows

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