Google Sheets - Prevent users from pasting over cells with data validation

Question

Is it possible to prevent a user from pasting in cells that have drop-down options based on data validation?

While it could be a convenience, when one cell (with data validation) is copied and pasted to another cell (with its own validation) it rewrites the data validation to match range being pasted, if that makes sense.

Alternatively, perhaps there is a script that will accept the info being pasted but rewrite that data validation to its original range??

Answer 1

This may be very tricky to pull off depending on your workbook usage, and is more complex than it sounds. In the old days, GAS had ScriptDB so developers would revert this kind of thing by recreating the "UNDO" functionality. When that was sunset, one recommendation was to have a duplicate sheet, and making sure that it always stays aligned with the user's active sheet - then, when the user messes up your data validations, you can just scan all data validation cells and revert those from the duplicate sheet (https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder). In any case, I concluded that Google Sheets was not made for this specific type of client-facing solutions (this is where Google Forms is used), so if you cannot make it work using "Protected Sheets and Ranges" you will probably end up implementing a hack, as follows.

---

Here was my hack approach; I was unable to prevent this from happening, however, I was able to auto-revert cells to proper format by using onEdit(), and reverting all damaged named cells after each user edit. The idea is to define rules inside names of named ranges.

(1) You can create a named range for every cell that you want to protect. The name of the named range must encapsulate your data validation rules. For example, if you have a dropdown cell at [B28 on a sheet with ID "1380712296"], that feeds from [range A11-A14 (4 items in the dropdown) on sheet with ID "936278947"], you can name the dropdown cell as follows:

z_1380712296_r?936278947?A11.A14_B28

Or, in a generic form:

z_ DROPDOWN-SHEET-ID _ DATA-VALIDATION-TYPE ? DATA-VALIDATION-SOURCE-SHEET-ID ? DATA-VALIDATION-START-CELL . DATA-VALIDATION-END-CELL _ DROPDOWN-CELL-RANGE-IN-A1-FORMAT

(2) Write a trigger to execute on every user edit, as follows:

function onEdit(e) {
  try {
    autocorrectFormat(e.range);
  }
  catch (e) {}
}

function autocorrectFormat(modifiedRange) {
  // Get named ranges on active sheet
  var sheetNamedRanges = SpreadsheetApp.getActiveSheet().getNamedRanges();

  // Fix active cells that intersect
  for (var i in sheetNamedRanges) {
    // we only go through the named ranges that begin with z_ since others may be declared by users
    if (sheetNamedRanges[i].getName().substring(0,2) == "z_") {
      // This rangesIntersect function below is necessary because onEdit's e.range is often wrong when pasting groups of cells
      // so we want to check if the edited range intersects with a named range's range rather than equals exactly
      if (rangesIntersect(sheetNamedRanges[i].getRange(), modifiedRange)) {
        // Here parse the information on the named range's name and fix the potentially damaged cell using the data 
        // validation rules pulled from the named range's name
        var currentCellInformation = [sheetNamedRanges[i].getRange(),sheetNamedRanges[i].getName().split("_")];
        // Parsing
        var part_1 = currentCellInformation[1][2].charAt(0);
        var part_2 = currentCellInformation[1][2].split("?");  
        // Function to rebuild the dropdown cell
        fixRange(...);
      }
    }
  }
  return;
}

// https://stackoverflow.com/a/36365775/7053599
function rangesIntersect(R1, R2) {
  return (R1.getLastRow() >= R2.getRow()) && (R2.getLastRow() >= R1.getRow()) && (R1.getLastColumn() >= R2.getColumn()) && (R2.getLastColumn() >= R1.getColumn());
}

As you noticed, pasting overwrites the former data validation rules. You may also want to assess the effects of Ctrl+X, Drag+Drop, and Ctrl+\ and their effects on named ranges, since, for example, Ctrl+X moves the named range as well, and Ctrl+\ never executes onEdit().

I included the code for you to get familiar with the kinds functions you would be using - you may also be using DataValidationBuilder in your fixRange() function where you rebuild the dropdown cell.

---

In response to "Alternatively, perhaps there is a script that will accept the info being pasted but rewrite that data validation to its original range??":

This is a good idea, it had occurred to me as well, but it is not possible to intercept information being pasted with GAS. The closest we have is onEdit(). This is why I mentioned above that I could not prevent it, but rather attempt to auto-revert damaged cells.