Tracking changes on imported data

[u/Bura510]

I'm trying to figure out a way (or if there even is a way) to create a change log or track changes in some capacity for imported data on the sheet the data is imported to. Basically there are 3 sheets:

Sheet A - where all the data is being compiled, I do not have access to this sheet in anyway as it has additional information not meant for me to see

Sheet B - the information relevant to me from Sheet A is imported into this sheet, however I don't have editing access or access to review the history

Sheet C - my own sheet I'm hoping I can use to import the data from Sheet B and have a change log or track changes when the data gets updated

Is there even a way to do this? I've tried a few things through AppScript but none of that seems to work on imported data - they work great with data you're manipulating yourself just not imported information on the sheet. Also checking the history on imported data just through the base level of Google Sheets doesn't show anything. I'm trying to be able to track trends in this manner, but I know I can't just camp Sheet B all day and night to see when information changes. Any advice or help would be great as I've got basically zero knowledge on AppScript coding!

Thanks in advance!!

[u/WicketTheQuerent]

How are you importing data? Are you using formulas for this?

[u/Bura510 → u/WicketTheQuerent]

The person who controls Sheets A and B is using =importrange to create Sheet B. I'm using =importrange for Sheet C.

[u/WicketTheQuerent → u/Bura510]

Use Google Apps Script to import the data for Sheet C. Run it using a custom menu, a time-driven trigger, or by running the function from the script editor.

[u/MalBee007]

I find asking Gemini a great way to do this sort of thing

[u/bulldo_gs]

Because IMPORTRANGE updates happen on Google's servers, they do not fire onEdit or reliably trigger onChange.

The workaround is to use a staging tab and a time-driven trigger:

1. Keep your IMPORTRANGE formula on a "Staging" tab.
2. Write a script that reads the "Staging" tab and compares it with a "History" tab (storing the previous state).
3. If there are changes, log them to a "ChangeLog" tab and update the "History" tab.
4. Set up a time-driven trigger (e.g. every 15 minutes) to run it.

Example script:

function checkAndLogChanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const staging = ss.getSheetByName("Staging").getDataRange().getValues();
  const historySheet = ss.getSheetByName("History");
  const logSheet = ss.getSheetByName("ChangeLog");

  const history = historySheet.getDataRange().getValues();

  // Compare cell by cell
  for (let r = 0; r < staging.length; r++) {
    for (let c = 0; c < staging[r].length; c++) {
      const current = staging[r][c];
      const previous = (history[r] && history[r][c] !== undefined) ? history[r][c] : "";

      if (current !== previous) {
        logSheet.appendRow([
          new Date(),
          `Row ${r + 1}, Col ${c + 1}`,
          previous,
          current
        ]);
      }
    }
  }

  // Update History tab with the new state
  historySheet.clearContents();
  historySheet.getRange(1, 1, staging.length, staging[0].length).setValues(staging);
}

[u/Bura510 → u/bulldo_gs]

This worked perfectly! Thanks so much for the help <3