求助：Google Apps Script跨表ID匹配复制日期脚本故障排查

Let's break down what's wrong with your script and fix it step by step — the unresponsiveness and incorrect matching are coming from a few key issues:

Key Issues in Your Original Script #

• Broken loop condition: Your loop uses i > sheetA.getLastRow() which means it never runs (since i starts at 0, which is always less than the last row number). It should be i < sheetA.getLastRow().
• Backwards array indexing: dataA[2][i] and dataB[1][i] are reversed. The getValues() method returns a 2D array where the first index is the row, the second is the column. So Sheet1's ID column (B) is dataA[i][1] (columns are 0-indexed), not dataA[2][i].
• Slow row-by-row service calls: Using getRange().getValue() and setValue() inside a loop makes hundreds of calls to the Spreadsheet service, which is slow and causes unresponsiveness. We need to use array operations instead.
• Incorrect matching logic: You assumed rows with the same ID are in the same position in both sheets, but you mentioned Sheet2's row order isn't fixed. We need to check all rows in Sheet2 to find matching IDs.

Fixed Script #

function CopyDate() {
  const ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID'); // Replace with your actual spreadsheet ID
  const sheetA = ss.getSheetByName('Sheet1');
  const sheetB = ss.getSheetByName('Sheet2');

  // Get only rows with data (not entire columns) to save memory
  const dataA = sheetA.getRange(1, 1, sheetA.getLastRow(), 2).getValues();
  const dataB = sheetB.getRange(1, 1, sheetB.getLastRow(), 16).getValues();

  // Create a fast lookup map: ID -> Date
  const idToDate = new Map();
  dataA.forEach(row => {
    const id = row[1]; // Sheet1's B column (ID)
    const date = row[0]; // Sheet1's A column (Date)
    if (id) { // Skip rows with empty IDs
      idToDate.set(id, date);
    }
  });

  // Update Sheet2's P column with matching dates
  dataB.forEach(row => {
    const id = row[0]; // Sheet2's A column (ID) — adjust if your ID is in a different column!
    if (idToDate.has(id)) {
      row[15] = idToDate.get(id); // P column is index 15 (0-indexed)
    }
  });

  // Write all updates back to Sheet2 in one batch call
  sheetB.getRange(1, 1, dataB.length, 16).setValues(dataB);
}

What We Fixed #

• Fast lookup with Map: We first build a map of IDs to dates from Sheet1, so we don't have to loop through Sheet1 every time we check a row in Sheet2 — this drastically speeds up the script.
• Batch operations: We read all data at once with getValues() and write it back in one go with setValues(), which minimizes expensive calls to the Spreadsheet service (the main cause of unresponsiveness).
• Correct indexing: We use 0-indexed columns (A=0, B=1, P=15) which matches how getValues() returns data.
• Handles non-fixed row order: The script checks every row in Sheet2 against the ID map, so it doesn't matter if rows are out of order.
• Skips empty IDs: We added a check to ignore rows with empty IDs, preventing unnecessary entries in the map.

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