谷歌表格脚本需求：两列值匹配时在指定单元格插入对应值

Fixing Your Google Sheets Script for Multiple Condition-Based Value Insertions #

Hey there! Let's work through getting your script to handle all your requirements properly. I've looked over your current code and spotted a few areas that are limiting its functionality—let's break it down and fix it.

Issues with the Original Script #

• Unnecessary nested loop: You're looping through columns when you only need to check column A (fruit) and column B (status) per row.
• Inefficient range handling: Using getRange('a2:a') pulls every cell in the column, including empty rows, which slows down the script and can cause unexpected behavior.
• Only handles one condition: Your code only accounts for Apple + Ripe—it doesn't include logic for your second requirement (whatever that specific condition is).
• Slow cell-by-cell updates: Setting values one cell at a time with setValue() is inefficient; batch updates are better for performance.

Revised Script with Full Functionality #

Here's an updated version that addresses these issues and makes it easy to add more conditions for your second (or future) requirements:

function updateCostBasedOnFruitAndStatus() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  
  // Get all data in the used range (avoids empty rows)
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  
  // Create an array to store updated cost values
  const updatedCosts = [];
  
  // Skip header row (assuming row 1 is headers)
  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const fruit = row[0]; // Column A
    const status = row[1]; // Column B
    let cost = row[2]; // Column C (keep existing value if no match)
    
    // Add your condition pairs here
    if (fruit === 'Apple' && status === 'Ripe') {
      cost = 5.5;
    } 
    // Add your second requirement condition here
    else if (fruit === 'Banana' && status === 'Unripe') {
      cost = 2.2;
    }
    // Add more conditions as needed
    // else if (...) { ... }
    
    updatedCosts.push([cost]); // Wrap in array to match column range format
  }
  
  // Write all updated costs back to Column C starting at row 2
  if (updatedCosts.length > 0) {
    sheet.getRange(2, 3, updatedCosts.length, 1).setValues(updatedCosts);
  }
}

Key Improvements Explained #

• Batch data handling: We pull all the sheet's data at once, process it in memory, then write the updates back in one go—this is way faster than updating cells individually.
• Clear condition structure: The if/else if blocks make it simple to add as many condition-value pairs as you need for your requirements. Just copy the else if line and adjust the fruit, status, and cost values.
• Respects existing values: If a row doesn't match any of your conditions, it keeps the original value in Column C instead of overwriting it.
• Avoids empty rows: Using getDataRange() only pulls rows that have data, so we don't waste time processing empty cells.

How to Add Your Second Requirement #

Just add another else if block with your specific fruit and status values, and set the corresponding cost. For example, if your second requirement is "when fruit is Banana and status is Unripe, set cost to 2.2", that's already included in the script—you just need to adjust it to match your actual needs.

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