谷歌表格跨表行匹配：翻译校验字体变色功能异常排查

Let's break down what's going wrong with your script and fix it step by step. I see you've confirmed data traversal works, but the matching logic has a few key issues stopping the font color change from behaving as expected.

Key Issues in Your Original Code #

• Global Variable Scope: Variables like lastColumn, lastRow, and searchRange are defined globally. This means they only initialize once when the script loads, not every time you run transcheck—so if your sheet data updates later, the script uses outdated values.
• Incorrect Matching Logic: You're comparing the first character of cells (cell[0] === cellB[0]) instead of the full cell value. Even worse, you're only checking against a single cell in the key sheet, not the entire range of possible correct answers for each column.
• Syntax Error: else if(cell[0] =! cellB[0]) uses an assignment operator (=) instead of a comparison operator (!==), and this condition is redundant anyway—you can just use a simple else clause.

Corrected Script #

var ui = SpreadsheetApp.getUi();

function onOpen(){
  ui.createMenu("Check My Translation.")
   .addItem("Check Set 1", "transcheck")
   .addToUi();
}

function transcheck(){
  // Get active spreadsheet (student template)
  var ssA = SpreadsheetApp.getActiveSpreadsheet();
  var worksheet = ssA.getSheetByName("Worksheet");
  var rangeData = worksheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();
  // Student answers range: rows 2 onwards, columns 2 onwards
  var studentRange = worksheet.getRange(2, 2, lastRow - 1, lastColumn - 1);
  var studentValues = studentRange.getValues(); // Fetch all student values at once (more efficient)

  // Open the key spreadsheet
  var ssB = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1jKuxXo6o5YJjSUrQmaHR0n1ydvw7PgUl29I9mtycF_g/edit#gid=0");
  var keySheet = ssB.getSheetByName("Key");
  var keyRange = keySheet.getDataRange();
  var keyValues = keyRange.getValues(); // Fetch all key values at once

  // Iterate through each student answer cell
  for (var col = 0; col < studentValues[0].length; col++){
    // Get all correct answers for this column (skip header row, trim/normalize)
    var correctAnswers = keyValues.slice(1)
     .map(row => row[col]?.toString().trim().toLowerCase())
     .filter(Boolean); // Remove empty values from the correct list
    
    for (var row = 0; row < studentValues.length; row++){
      var studentAnswer = studentValues[row][col]?.toString().trim().toLowerCase();
      var cell = worksheet.getRange(row + 2, col + 2); // Convert to sheet row/column (offset by 2)

      // Check if student answer matches any correct answer in the column
      if (correctAnswers.includes(studentAnswer)){
        cell.setFontColor("green");
      } else {
        cell.setFontColor("red");
      }
    }
  }
}

What Changed & Why #

1. Moved Variables to Function Scope: All sheet/range variables live inside transcheck now, so they pull fresh data every time the function runs.
2. Bulk Value Retrieval: Using getValues() instead of fetching each cell individually is way more efficient (Google Apps Script has quotas on read/write operations, so this helps avoid hitting limits).
3. Full Column Correct Answer Check: For each column, we extract all valid answers from the key sheet, normalize them (trim whitespace, lowercase), then check if the student's answer is in that list.
4. Case & Whitespace Insensitivity: Normalizing both answers ensures minor typos like extra spaces or capitalization don't count as wrong (remove .toLowerCase() if case matters for your translations).
5. Cleaned Up Syntax: Replaced the broken =! with a simple else clause—if the answer isn't in the correct list, it should be red.

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