如何在Google Apps Script中用for循环遍历行，结合嵌套IF输出多数据？

Got it, let's fix up your Google Apps Script step by step. You're trying to loop through spreadsheet rows, collect multiple data points, and use nested IF statements to generate output—but you've hit a few common issues: variable name mismatches, missing gasmileage data retrieval, and an unclosed regular expression. Here's how to resolve everything:

Key Issues & Fixes #

• Variable Name Mismatch: You defined compType but referenced carType elsewhere—this causes a ReferenceError because the interpreter can't find the undefined carType variable. We'll standardize the variable name across the script.
• Missing gasmileage Value: Your original code didn't pull this data from the spreadsheet row. We'll add logic to fetch it from the appropriate column.
• Unclosed Regular Expression: Regex literals in JavaScript must be wrapped in / characters. We'll ensure your regex is properly formatted to avoid syntax errors.

Corrected Full Code #

Assuming your spreadsheet has columns set up like:

• Column A: Vehicle Type (compType)
• Column B: Gas Mileage (gasmileage)
• Column C: Additional Data to collect
• Column D: Output results (where we'll write our final text)

function processVehicleData() {
  // Get active sheet and full data range
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const allData = activeSheet.getDataRange().getValues();
  
  // Skip header row (adjust i=1 to i=0 if there's no header)
  for (let i = 1; i < allData.length; i++) {
    const currentRow = allData[i];
    
    // 1. Fix: Fetch and use consistent variable names
    const compType = currentRow[0];
    // 2. Fix: Retrieve gasmileage from the correct column (B = index 1)
    const gasmileage = currentRow[1];
    const additionalData = currentRow[2];
    
    // 3. Fix: Properly close the regular expression
    const validVehicleRegex = /^[A-Z]/; // Checks if type starts with uppercase letter
    
    // Nested IF logic to generate output
    let resultText = "";
    if (validVehicleRegex.test(compType)) {
      if (gasmileage > 30) {
        resultText = `High-efficiency ${compType} | Mileage: ${gasmileage} | Extra: ${additionalData}`;
      } else if (gasmileage >= 20 && gasmileage <= 30) {
        resultText = `Standard-efficiency ${compType} | Mileage: ${gasmileage} | Extra: ${additionalData}`;
      } else {
        resultText = `Low-efficiency ${compType} | Mileage: ${gasmileage} | Extra: ${additionalData}`;
      }
    } else {
      resultText = `Invalid vehicle type: ${compType}`;
    }
    
    // Output to console and write to column D (index 3)
    console.log(resultText);
    activeSheet.getRange(i + 1, 4).setValue(resultText);
  }
}

Quick Notes for Customization #

• Adjust the column indices (currentRow[0], currentRow[1], etc.) to match your actual spreadsheet layout.
• If your sheet doesn't have a header row, change the loop start from i=1 to i=0.
• Modify the regex pattern (/^[A-Z]/) and nested IF thresholds (like gasmileage > 30) to fit your specific data requirements.

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