Google Apps Script条件分支问题：else if执行逻辑不符合预期

Hey there, let's break down what's going wrong with your current code and fix it to match your exact requirements.

Your Core Requirements Recap #

First, let's make sure we're aligned on what you need:

• If there are no open-status rows of type A, add a new row with A, open
• If there are multiple open-status rows of type A, set all of them to close
• Ignore all other row types (B, C, empty, etc.) entirely

What's Wrong With the Original Code #

Your current loop processes each row one by one, and the else if triggers every time it hits a non-A row (that's not closed). This means you're adding new rows repeatedly for every non-A entry, instead of checking the entire sheet first to see if there are any open A rows.

Fixed Code #

Here's a refactored version that addresses all your needs:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetName');
  // Get data range starting from row 2 (skip header) with 2 columns (A and B)
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2);
  var data = dataRange.getValues();
  var openARowNumbers = [];

  // First pass: Collect all row numbers where type is A and status is open
  for (var i = 0; i < data.length; i++) {
    var rowType = data[i][0];
    var rowStatus = data[i][1];
    if (rowType === 'A' && rowStatus === 'open') {
      // Add the actual row number (since we started at row 2, it's i + 2)
      openARowNumbers.push(i + 2);
    }
  }

  // Second pass: Act based on our collected data
  if (openARowNumbers.length > 0) {
    // We found open A rows - set all of them to close
    openARowNumbers.forEach(function(rowNum) {
      sheet.getRange(rowNum, 2).setValue('close');
    });
  } else {
    // No open A rows exist - add a new one
    var nextEmptyRow = sheet.getLastRow() + 1;
    sheet.getRange(nextEmptyRow, 1, 1, 2).setValues([['A', 'open']]);
  }
}

How This Works #

1. First Loop: We scan every row once to collect all positions of open A rows. This lets us check the entire sheet's state first, instead of making decisions row-by-row.
2. Decision Logic:
   • If we found any open A rows, we loop through those row numbers and set their status to close
   • If we found zero open A rows, we add a new row with A, open at the bottom of the sheet
3. Ignoring Other Types: Since we only care about rows where type === 'A', all other row types are completely skipped in our logic—no accidental new rows triggered by B/C/empty entries.

This approach is cleaner, more efficient, and perfectly matches your updated requirements.

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