I have three source spreadsheets and I managed to consolidate data into a master sheet, but my aim is to see to see to it, whenever data is update or new row is added in any of the source spreadsheets, the master sheet must get updated as well. The current script pulls up all the data into the main sheet along with updated data or new rows added in the source sheet. Also, the trigger won’t run. I have set up onEdit function, Event – From spreadsheet

I am trying to see that any updates in the source sheets such as edit to the exiting data or deleting data or a new row is added gets appended to the main sheet. The result pulls up complete data from all the three source sheets and the new row item

// Global variable to store the last processed row for each source sheet
var lastProcessedRows = {};

// Function to consolidate data from source sheets to the main sheet
function consolidateData() {
  // Define the sources with URLs, sheet names, and ranges
  var sources = [
    { url: "https://docs.google.com/spreadsheets/d/1vi-vOGAwYwKLfBziz0LaJeL6jvDD4pifB_cmalRYSC4/edit#gid=1541407214", sheetName: "DataSheet", range: "A:J" },
    { url: "https://docs.google.com/spreadsheets/d/1RF2zGom8Gc5OGvlnm9mrIVMPQRkI58_Tj_VDAaw3bN8/edit#gid=1541407214", sheetName: "Info", range: "A:J" },
    { url: "https://docs.google.com/spreadsheets/d/17HUUp0bZGKsZa11xe-Mnapd3xtJqaCSnSUTuda09VKk/edit#gid=1541407214", sheetName: "Records", range: "A:J" }
  ];

  // Define the name of the main sheet
  var mainSheetName = "MainSheet";
  
  // Get the main spreadsheet and main sheet
  var mainSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = mainSpreadsheet.getSheetByName(mainSheetName);
  
  // Loop through each source spreadsheet
  sources.forEach(function(source) {
    // Open the source spreadsheet
    var sourceSpreadsheet = SpreadsheetApp.openByUrl(source.url);
    
    // Get the source sheet and range
    var sourceSheet = sourceSpreadsheet.getSheetByName(source.sheetName);
    var dataRange = sourceSheet.getRange(source.range);
    
    // Get the values from the source range
    var values = dataRange.getValues();
    
    // Get the last processed row for this source sheet
    var lastProcessedRow = lastProcessedRows[source.url] || 0;
    
    // Check if there are new rows to append
    if (values.length > lastProcessedRow) {
      var newData = values.slice(lastProcessedRow); // Get new rows
      mainSheet.getRange(mainSheet.getLastRow() + 1, 1, newData.length, newData[0].length).setValues(newData); // Append to main sheet
      lastProcessedRows[source.url] = values.length; // Update last processed row
    }
    
    // Update main sheet based on changes in source sheet
    updateMainSheet(mainSheet, dataRange, mainSheet.getLastRow(), source.url);
  });
}

// Function to update the main sheet based on changes in source sheets
function updateMainSheet(mainSheet, sourceRange, mainSheetLastRow, sourceUrl) {
  var mainValues = mainSheet.getDataRange().getValues();
  var sourceValues = sourceRange.getValues();
  
  // Loop through each row in the source sheet
  sourceValues.forEach(function(sourceRow, rowIndex) {
    var found = false;
    
    // Check if the row exists in the main sheet
    mainValues.forEach(function(mainRow, mainIndex) {
      if (JSON.stringify(sourceRow) === JSON.stringify(mainRow)) {
        found = true;
        return;
      }
    });
    
    // If row not found in main sheet, append it
    if (!found) {
      mainSheet.getRange(mainSheetLastRow + rowIndex + 1, 1, 1, sourceRow.length).setValues([sourceRow]);
    }
  });
}

// Function to check if an edited range is within any of the source ranges
function isInSourceRanges(range, sourceRanges) {
  for (var i = 0; i < sourceRanges.length; i++) {
    var parts = sourceRanges[i].split("!");
    var sheetName = parts[0];
    var rangeString = parts[1];
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(rangeString);
    
    if (range.intersects(sourceRange)) {
      return true;
    }
  }
  
  return false;
}

// Trigger function to run when a change is made in any of the source sheets
function onEdit(e) {
  var editedRange = e.range;
  var sourceRanges = [
    "DataSheet!A:J",
    "Info!A:J",
    "Records!A:J"
  ];
  
  if (isInSourceRanges(editedRange, sourceRanges)) {
    consolidateData();
  }
}