Syncing multiple Google Sheets worksheets into one large Google Sheet document

Question

I have several different Google Sheets workbooks with multiple pages that I'm trying to have sync into a master file whenever they are edited. I cannot have all of the people on the separate documents editing the primary document. In using the Script Editor, I have this:

   function onEdit() {
    SpreadsheetApp.getUi().createMenu("Admin Settings")
     .addItem("Import Data", "importData")
   }
  
    var sourceSpreadsheetID = "TO UPDATE";
    var sourceWorksheetName = "TO UPDATE";
    var targetSpreadsheetID = "TO UPDATE";
    var targetWorksheetName = "TO UPDATE";

   function importData() {
    var thisSpreadsheet = SpreadsheetApp.openById('1yoF-IQo_XKD1X6N_z_54X8N4Akm4dgyUTJ9Av6wQ0Ps');
    var thisWorksheet = thisSpreadsheet.getSheetByName("Barnhardt");
    var thisData = thisWorksheet.getDataRange();
    var toSpreadsheet = SpreadsheetApp.openById('1ktwfwRN3cRCvpwkiFbZMEud-9ez8ZVGciu5ejrNiGAU');
    var toWorksheet = toSpreadsheet.getSheetByName("Barnhardt");
    var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns()) toRange.setValues(thisData.getValues()); 
   }

but I can't figure out why it won't work. The script editor says that it was successful. I also tried to add a trigger so that every time the document is edited, it will update the master document, but I'm not sure how to add that to the script. Help?

Answer 1

Your onEdit trigger doesn't actually execute the function, it merely creates a menu option. So, first things first, assuming that you want the menu at all, change onEdit() to onOpen(). This will create the menu so that you can execute it manually whenever you want.

Second, manually create an onEdit() trigger for importData(). This will execute the function whenever there is a change in the data. You could create the trigger programmatically, but this probably isn't the time or the place to explain that in detail.

FYI, if you have a function called onEdit, then the trigger will be automatically recognised by virtue of using that function name. Same applies for onOpen(). What you do NOT want is to end up with two functions called onEdit() - that's a recipe for a problem.

Lastly, if the script is bound to "thisspreadsheet", then you don't need to open it by ID, you could just use var thisSpreadsheet=SpreadsheetApp.getActiveSpreadsheet().