1

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?

1
  • Hi, welcome to WebApps. First things first. Change onEdit() to `onOpen()": assuming that you want the menu at all, you'll want to create it when you open the spreadsheet not when you edit a field.
    – Tedinoz
    Aug 30 at 22:46

1 Answer 1

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.