Google Apps Script：两列数据匹配问题求助（附示例表格与代码）

Got it, let's finish that two-column matching script for your Google Sheets. Based on what you've started, I'll assume you want to match a key column (like asset IDs) between the two sheets, and update the Master List with today's date (plus a status flag) when a match is found. Here's the completed, commented script tailored to your setup:

Completed Auto-Audit Script #

function autoAudit() { 
  var ss = SpreadsheetApp.getActive(); 
  var sA = ss.getSheetByName('Asset Audit Import'); 
  var sM = ss.getSheetByName('Master List'); 

  // Format today's date as mm/dd/yyyy (your existing logic preserved)
  var today = new Date(); 
  var dd = today.getDate(); 
  var mm = today.getMonth() + 1; 
  var yyyy = today.getFullYear(); 
  if(dd < 10) { dd = '0' + dd } 
  if(mm < 10) { mm = '0' + mm } 
  today = mm + '/' + dd + '/' + yyyy; 

  // Get data ranges (avoid blank rows by using getLastRow())
  // Adjust column indexes below to match your actual matching columns!
  var auditMatchColumn = sA.getRange(2, 1, sA.getLastRow() - 1, 1).getValues(); // Column A (rows 2+) in Audit Import
  var masterData = sM.getRange(2, 1, sM.getLastRow() - 1, 3).getValues(); // Columns A-C (rows 2+) in Master List

  // Convert audit values to a Set for fast lookups (way faster than nested loops)
  var auditKeys = new Set(auditMatchColumn.flat());

  // Prepare an array to hold updates for the Master List
  var updateBatch = [];

  // Loop through each row in the Master List
  masterData.forEach(function(row) {
    var masterKey = row[0]; // Assumes match key is in Column A of Master List
    if(auditKeys.has(masterKey)) {
      // If match found: update date (Column C) and mark as "Matched" (Column B)
      updateBatch.push(["Matched", today]);
    } else {
      // If no match: keep existing date and mark as "Not Matched"
      updateBatch.push(["Not Matched", row[2]]);
    }
  });

  // Write all updates back to the Master List in one go (efficient for large datasets)
  if(updateBatch.length > 0) {
    sM.getRange(2, 2, updateBatch.length, 2).setValues(updateBatch);
  }

  // Optional: Show a confirmation popup
  SpreadsheetApp.getUi().alert("Audit matching finished! Updated " + updateBatch.length + " rows.");
}

Customization Tips #

• Adjust Matching Columns: If your match key isn't in Column A for both sheets, tweak the getRange parameters:
  • For example, if your audit key is in Column D: sA.getRange(2, 4, sA.getLastRow() -1, 1) (4 = Column D)
  • If your master key is in Column E: sM.getRange(2, 5, sM.getLastRow() -1, 3) (5 = Column E)
• Modify Update Columns: The script updates Column B (status) and Column C (date). To change this, adjust the updateBatch.push values and the final getRange call (the second number is the starting column: 2 = B, 3 = C, etc.)
• Test First: Run this on a copy of your sheet first to verify it behaves as expected—better safe than sorry with live data!

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