谷歌表格脚本优化：匹配ID并按需更新日期

Great call switching to a Map-based approach for bulk matching—way more efficient than looping through each row pair! Let's fix those two issues in your script so it only updates dates when needed, and leaves unmatched rows untouched.

Updated Efficient Script #

function updateDatesEfficiently() { 
  const ss = SpreadsheetApp.getActiveSpreadsheet(); 
  const sht1 = ss.getSheetByName("Sht1"); 
  const sht2 = ss.getSheetByName("Sht2"); 

  // Build a Map of ID to Date from Sht2 (adjust column indices if your layout differs!)
  const idDateMap = sht2.getDataRange().getValues().reduce((map, row) => {
    const id = row[1]; // Assuming Sht2's ID is in column B (index 1)
    const date = row[3]; // Assuming Sht2's date is in column D (index 3)
    if (id && date) { // Skip rows with empty ID or date to avoid invalid entries
      map.set(id, date);
    }
    return map;
  }, new Map()); 

  const sht1Range = sht1.getDataRange(); 
  const sht1Values = sht1Range.getValues(); 

  // Iterate through Sht1 rows: only update if ID matches AND date has changed
  sht1Values.forEach(row => {
    const rowId = row[0]; // Assuming Sht1's ID is in column A (index 0)
    const currentDate = row[2]; // Assuming Sht1's date is in column C (index 2)
    
    // Only proceed if the ID exists in our Sht2 map
    if (idDateMap.has(rowId)) {
      const newDate = idDateMap.get(rowId);
      
      // Compare dates accurately to avoid unnecessary overwrites
      if (currentDate instanceof Date && newDate instanceof Date) {
        // Use toISOString to bypass timezone/format quirks and compare exact timestamps
        if (currentDate.toISOString() !== newDate.toISOString()) {
          row[2] = newDate;
        }
      } else {
        // Fallback for cases where dates might be stored as strings
        if (String(currentDate) !== String(newDate)) {
          row[2] = newDate;
        }
      }
    }
    // No action if ID isn't matched—original date stays intact
  }); 

  // Write all updates back to Sht1 in a single batch operation
  sht1Range.setValues(sht1Values); 
}

Key Fixes & Improvements #

• Preserves unmatched dates: We only modify rows where the ID exists in Sht2. If no match is found, the original date in Sht1 remains unchanged instead of being cleared.
• Avoids redundant updates: We add explicit date comparison logic—only overwriting the date in Sht1 if it differs from the corresponding date in Sht2. Using toISOString() ensures we compare exact timestamps, avoiding issues with timezone differences or display formats.
• Robustness: Added checks to skip invalid rows in Sht2 (empty ID/date) and included a fallback comparison for string-formatted dates, just in case your sheet uses non-Date object values.

Note: Double-check the column indices (like row[1] for Sht2's ID) to match your actual sheet layout—adjust these numbers if your ID/date columns are in different positions.

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