My onEdit() function keeps deleting my data

问题:

I have one Google Sheet with two tabs (1) Admin and (2) User. And I've got this script:

function onEdit() {
       var source = SpreadsheetApp.openById('xxx');
       var sourcesheet = source.getSheetByName('Admin');

       var target = SpreadsheetApp.openById('yyy')
       var targetsheet = target.getSheetByName('User');

       var targetrange = targetsheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
       var rangeValues = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();

       targetrange.setValues(rangeValues);
}

So basically, whatever updates in the Admin will also reflect in the User sheet.

The problem is, when I update something in the User, my script automatically deletes it unless it's also the same as that in the Admin.

I want it to be a one-way connection where when the User sheet updates, nothing happens in the Admin sheet. But when something updates in the Admin, User will also be updated.

Many thanks!

回答1:

The reason that edits to the "User" sheet are "automatically deleted unless also the same as that in the Admin" is because your onEdit function activates after any cell edit by a user - that is what naming it onEdit means: you want that function to use the simple trigger with the "edit" criteria.

Thus, when you edit a cell on the "User" worksheet (or a 3rd or 4th worksheet in the same workbook), your script runs, and overwrites the "User" sheet with the current state of the "Admin" sheet. To prevent this from occurring, you need to add "code guards", in the form of if statements, which will block access to sections of code unless certain criteria of your choosing are met:

if (myVar === "silly string") {
  // only run this code when `myVar` is exactly `"silly string"`
} else if (myVar === "bubble gum") {
  // only run this code when `myVar` is exactly `"bubble gum"`
} else {
  // run this code when `myVar` is anything except `"silly string"` or `"bubble gum"`
}

You can flat-out stop executing the function, by calling return:

function onEdit(e) {
  if (!e)
    return; // Stop executing this function

  // We are guaranteed that `e` is not `false`, `null`, `undefined`, `""`, or `0` at this point.
}

Thus, to achieve your stated one-way edit link, you need to guard the code that writes to the User sheet. You can obtain information about the edit from the passed event object:

function onEdit(eventObject) {
  if (!eventObject) return;
  const edited = eventObject.range;
  const sheet = edited.getSheet();
  if (sheet.getName() === "Admin") {
    HandleEditsToAdmin_(eventObject);
    // other code to be run for edits to the sheet named "Admin"
  }
  // other code to be run for generic edits (or other guards for other specific edit handling, etc.)
}
function HandleEditsToAdmin_(e) {
  // your code here
}

Note that your current code explicitly copies the values of the "Admin" sheet verbatim to the "User" sheet, so even if you add code guards, as soon as "Admin" is edited, any previous direct changes to the "User" sheet will be removed, unless you also update your code per @Ed Nelson's answer.

回答2:

Aside from @tehhowch's great contribution, here's another solution:

function onEdit(e) {
       if(e.source.getActiveSheet().getName() !== 'Admin') return;
       e.source.getSheetByName('User').getRange(e.range.getA1Notation()).setValue(e.value);
}

This script will copy all updates from the Admin to the User but not the other way around, this will also preserve any changes in the User sheet when the Admin has been updated again provided that the cell is null or blank in the Admin.