如何在Google Apps Script中用循环替代VBA中的多个GoTo语句并实现循环复用

Great question! VBA's GoTo lets you jump to labels anywhere in your code, but Google Apps Script (built on JavaScript) doesn’t support that kind of arbitrary cross-block jumping. Instead, we can replace those jump-based flows with reusable helper functions and structured loops/conditionals—this makes your code cleaner, easier to debug, and way more maintainable.

Step 1: Break Down the VBA Logic #

First, let’s map out what your extended VBA code does:

1. date_deb: Increment the row index (lig) until it finds a non-empty cell in column cc that doesn’t match the previous row
2. Delete rows 2 through lig-1
3. chg_sem: Reset the row index, grab the current date, and calculate its week number (using Monday as the first day, following the "first four days" rule)
4. date_eq: Skip consecutive identical non-empty dates, then keep skipping if the next date falls in the same week as the initial one

Step 2: Replace GoTo with Reusable Functions & Structured Code #

The key to reusing logic (like your chg_sem flow) is to extract repeated or callable logic into standalone functions. Here’s a polished, optimized version of your Google Apps Script code:

function convertVbaGoToLogic() {
  const ss = SpreadsheetApp.getActive().getSheetByName("GoTo");
  const targetCol = 9; // Column I (9th column)
  let values = ss.getDataRange().getDisplayValues();
  let currentRow = 2; // Starting row (1-indexed for sheet operations)

  // 1. Replace `date_deb` GoTo: Skip consecutive identical non-empty values
  currentRow = skipConsecutiveIdentical(values, currentRow, targetCol);

  // 2. Delete rows 2 to currentRow-1 (adjust for 1-indexed sheet rows)
  if (currentRow > 2) {
    ss.deleteRows(2, currentRow - 2);
    // Refresh values after deletion to avoid stale data
    values = ss.getDataRange().getDisplayValues();
    currentRow = 2; // Reset row index post-deletion
  }

  // 3. Replace `chg_sem` GoTo: Get week number for the starting date
  const startDate = new Date(values[currentRow - 1][targetCol - 1]); // Convert to 0-indexed array
  const targetWeek = getWeekNumber(startDate);

  // 4. Replace `date_eq` GoTo: Skip consecutive dates + same-week dates
  currentRow = skipConsecutiveOrSameWeek(values, currentRow, targetCol, targetWeek);

  const finalRow = currentRow - 1;
  console.log(`Final target row: ${finalRow}`);
  // Use finalRow in your后续 logic as needed
}

// Helper function: Reusable logic to skip consecutive identical non-empty values
function skipConsecutiveIdentical(values, currentRow, colIndex) {
  const col = colIndex - 1; // Convert to 0-indexed array
  while (currentRow < values.length) {
    const currentVal = values[currentRow][col];
    const prevVal = values[currentRow - 1][col];
    if (currentVal !== "" && currentVal === prevVal) {
      currentRow++;
    } else {
      break;
    }
  }
  return currentRow;
}

// Helper function: Skip consecutive values AND dates in the same target week
function skipConsecutiveOrSameWeek(values, currentRow, colIndex, targetWeek) {
  const col = colIndex - 1;
  while (currentRow < values.length) {
    const currentVal = values[currentRow][col];
    const prevVal = values[currentRow - 1][col];
    
    // First check: skip consecutive identical non-empty values
    if (currentVal !== "" && currentVal === prevVal) {
      currentRow++;
      continue;
    }
    
    // Second check: skip if current date is in the target week
    if (currentVal !== "") {
      const currentWeek = getWeekNumber(new Date(currentVal));
      if (currentWeek === targetWeek) {
        currentRow++;
        continue;
      }
    }
    
    // Exit loop if neither condition is met
    break;
  }
  return currentRow;
}

// Helper function: Calculate week number (matches VBA's Format(day, "ww", vbMonday, vbFirstFourDays))
function getWeekNumber(date) {
  const startOfYear = new Date(date.getFullYear(), 0, 1);
  // Adjust start date to follow "first four days" rule (week 1 starts if Jan 1 is Mon-Thu)
  const adjustedStart = startOfYear.getDay() <= 4 ? startOfYear : new Date(startOfYear.getFullYear(), 0, 1 + (7 - startOfYear.getDay()));
  const daysSinceAdjustedStart = Math.floor((date - adjustedStart) / (1000 * 60 * 60 * 24));
  return Math.floor(daysSinceAdjustedStart / 7) + 1;
}

Step 3: Solving Reusability #

If you need to reuse logic like chg_sem (or any other segment) elsewhere in your script:

• Extract it into a standalone function (like getWeekNumber or skipConsecutiveIdentical above)
• Pass only necessary parameters (e.g., values, currentRow, colIndex) instead of hardcoding spreadsheet references inside the function
• Return updated state (like the new currentRow value) so the calling code can pick up where it left off

This approach is way better than labeled loops because it:

• Avoids messy "spaghetti code" from arbitrary jumps
• Makes logic testable in isolation
• Lets you call the same logic from multiple places in your script

Optimizing Your Initial date_deb Function #

Your original date_deb function had redundant code (re-fetching the spreadsheet every time). Here’s a cleaned-up, reusable version that focuses only on the core logic:

function skipConsecutiveIdentical(values, currentRow, colIndex) {
  const col = colIndex - 1;
  while (currentRow < values.length) {
    const currentVal = values[currentRow][col];
    const prevVal = values[currentRow - 1][col];
    if (currentVal && currentVal === prevVal) {
      currentRow++;
    } else {
      break;
    }
  }
  return currentRow;
}

You can call this function anywhere by passing your data array, current row number, and column index—no need to reinitialize spreadsheet connections every time.

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