Google Sheets跨工作表自定义函数对比功能失效求助

Let's break down what's going wrong with your two code snippets and fix them step by step.

Issues with Your First Code (btk3) #

• Early return bug: Your else { return "No"; } triggers immediately when the first name doesn't match. This stops the function from checking all other rows after the first mismatch, which is almost certainly not what you want.
• No fallback for no matches: If the loop finishes without finding any matching name/date pair, the function returns nothing (resulting in #VALUE! in your sheet).

Here's the fixed version of btk3:

function btk3(Date1, Name1, Date2, Name2) {
  // Loop through each row in the Name2/Date2 ranges
  for (var i = 0; i < Name2.length; i++) {
    // Compare name and use timestamps for safe date matching
    if (Name1 === Name2[i][0] && Date1.getTime() === Date2[i][0].getTime()) {
      return "Yes";
    }
  }
  // Only return "No" if no matches are found after checking all rows
  return "No";
}

Quick notes on this fix:

• Changed Name2[0].length to Name2.length: When you pass a column range (like B2:B101) to a custom function, it’s a 2D array where each sub-array is a row — so Name2.length gives the correct number of rows.
• Used getTime() for dates: Google Sheets passes dates as JavaScript Date objects, and direct === checks can fail due to hidden time components. Comparing timestamps avoids this issue.

Issues with Your Second Code (btk) #

• Incorrect range syntax: You used ; instead of : in getRange("B2;B101") — Google Sheets uses colons to define cell ranges.
• Single value instead of array: getValue() only returns the first cell’s value in the range. To get all values, use getValues() which returns a 2D array.
• Broken loop logic: You’re not iterating through the target sheet’s rows; instead, you’re comparing Name1 to the entire range’s value every time, which will never match.

Here's the fixed cross-sheet version (no need to pass Date2/Name2 as arguments since we fetch directly from the target sheet):

function btk(Date1, Name1) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("Buff da Villa");
  
  // Fetch full ranges for names and dates as 2D arrays
  var names = targetSheet.getRange("B2:B101").getValues();
  var dates = targetSheet.getRange("A2:A101").getValues();
  
  // Loop through each row
  for (var i = 0; i < names.length; i++) {
    // Skip empty rows to avoid errors
    if (!names[i][0] || !dates[i][0]) continue;
    
    // Compare name and date timestamp
    if (Name1 === names[i][0] && Date1.getTime() === dates[i][0].getTime()) {
      return "Sim";
    }
  }
  
  // No match found
  return "Não"; // Swap to "No" if you prefer English
}

Quick notes on this fix:

• Added a check to skip empty rows, preventing errors if some cells in your target range are blank.
• Removed the unused activeSheet variable to clean up the code.

How to Use These Functions #

For btk3 (pass ranges as arguments):
In a cell, enter:
=btk3(A2, B2, 'Buff da Villa'!A:A, 'Buff da Villa'!B:B)

For btk (direct cross-sheet fetch):
In a cell, enter:
=btk(A2, B2)

Key Tips for Google Sheets Custom Functions #

• Date comparison: Always use getTime() to compare dates — hidden time components in Date objects can break direct equality checks.
• 2D arrays: Range inputs to custom functions are always 2D arrays, even for single columns/rows. Access values with array[row][column].
• Early returns: Only return early in loops if you’ve confirmed you’ve found the exact match you’re looking for.

内容的提问来源于stack exchange，提问作者Daniel Mejía