如何获取Google Sheets中IMPORTRANGE更新时间并脚本实现增量去重？

Hey there! Let's break down how to solve both your questions—tracking when IMPORTRANGE updates, and getting only new unique links without bloating your spreadsheet with daily backups.

1. How to Get the Exact Update Time for IMPORTRANGE Data #

IMPORTRANGE doesn’t have a built-in way to log its update times, but we can use Google Apps Script to detect when the imported data changes and record that timestamp. Here’s how:

Core Idea #

We’ll calculate a unique "hash" of the imported data, store it, and compare it each time the script runs. If the hash changes, that means IMPORTRANGE pulled new data—we’ll log the current time as the update time.

Add This Hash-Check Function #

Add this helper function to your script to generate a unique fingerprint of your imported data:

function getImportDataHash() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const importSheet = ss.getSheetByName("Sheet1");
  // Adjust the range to match where your IMPORTRANGE data lives (e.g., A2:Z if you have multiple columns)
  const dataRange = importSheet.getRange("A2:A"); 
  const data = dataRange.getValues();
  // Create a unique hash of the data
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, JSON.stringify(data))
    .map(byte => byte.toString(16).padStart(2, '0'))
    .join('');
}

Track Updates with Timestamp #

We’ll store the last hash and update time in a dedicated "Metadata" sheet to avoid cluttering your main data:

function logImportUpdateTime() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const metadataSheet = ss.getSheetByName("Metadata") || ss.insertSheet("Metadata");
  
  const currentHash = getImportDataHash();
  const lastHash = metadataSheet.getRange("A1").getValue();
  const lastUpdateTime = metadataSheet.getRange("B1").getValue();

  // If the hash changed, log the new update time
  if (currentHash !== lastHash) {
    const newUpdateTime = new Date();
    metadataSheet.getRange("B1").setValue(newUpdateTime);
    metadataSheet.getRange("A1").setValue(currentHash);
    Logger.log(`IMPORTRANGE updated at: ${newUpdateTime}`);
  }
}

2. Deduplicate New Entries Without a Massive Backup Library #

Instead of saving daily snapshots, we’ll only keep a single list of all unique links, and each time IMPORTRANGE updates, we’ll extract only the links that aren’t already in our unique list. Here’s a complete script to handle this:

Step 1: Set Up Your Sheets #

• Keep your IMPORTRANGE data in Sheet1 (adjust the name if needed).
• Create a History sheet to store all unique links and their first-seen timestamps.
• Optional: Create a NewEntries sheet to see only the latest unique links added each update.

Step 2: Full Deduplication & Update Tracking Script #

function processNewImportedData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const importSheet = ss.getSheetByName("Sheet1");
  const historySheet = ss.getSheetByName("History") || ss.insertSheet("History");
  const newEntriesSheet = ss.getSheetByName("NewEntries") || ss.insertSheet("NewEntries");

  // 1. Get and clean the imported link data (assuming links are in column A, starting at A2)
  const rawImportLinks = importSheet.getRange("A2:A").getValues().flat();
  const uniqueImportLinks = [...new Set(rawImportLinks.filter(link => link !== ""))];

  // 2. Get existing unique links from History
  const existingLinks = historySheet.getRange("A2:A").getValues().flat().filter(link => link !== "");

  // 3. Find links that are new (not in History)
  const newUniqueLinks = uniqueImportLinks.filter(link => !existingLinks.includes(link));

  // 4. Update History and log timestamps if there are new links
  if (newUniqueLinks.length > 0) {
    const currentTime = new Date();
    const nextHistoryRow = historySheet.getLastRow() + 1;

    // Add new links to History with timestamp
    historySheet.getRange(nextHistoryRow, 1, newUniqueLinks.length, 1).setValues(newUniqueLinks.map(link => [link]));
    historySheet.getRange(nextHistoryRow, 2, newUniqueLinks.length, 1).setValue(currentTime);

    // Optional: Add new links to NewEntries sheet for quick access
    const nextNewRow = newEntriesSheet.getLastRow() + 1;
    newEntriesSheet.getRange(nextNewRow, 1, newUniqueLinks.length, 1).setValues(newUniqueLinks.map(link => [link]));
    newEntriesSheet.getRange(nextNewRow, 2, newUniqueLinks.length, 1).setValue(currentTime);

    // Update the last update time in Metadata (from earlier)
    logImportUpdateTime();
  }
}

Step 3: Set Up Triggers to Auto-Run the Script #

IMPORTRANGE updates don’t always trigger the standard onEdit event, so use one of these triggers:

1. On Change Trigger:
   • Open the script editor → Click "Edit" → "Current project’s triggers"
   • Add a new trigger: Choose processNewImportedData, event source = "From spreadsheet", event type = "Change"
   • This will run the script whenever any change happens in the spreadsheet (including IMPORTRANGE updates)
2. Time-Driven Trigger:
   • If you want to check for updates on a schedule (e.g., every hour), set a time-driven trigger instead. This is reliable if IMPORTRANGE updates at predictable intervals.

Key Benefits of This Approach #

• No more bloated backup libraries: The History sheet only stores each unique link once.
• Clear timestamp tracking: You’ll know exactly when each link was first added, and when IMPORTRANGE last updated.
• Low maintenance: The script runs automatically, so you don’t have to manually deduplicate.

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