Google Sheets [🔧Fixed] Prevent users from pasting over cells with data validation (Dropdowns)

Hi, this is Max from CoolTables.

I have already written about fixing #REF error in this post:
Google Sheets [🔧Fixed] #REF Error. Array result was not expanded because it would overwrite data…

Now let’s talk about the problem, stated on StackOverflow:
Prevent users from pasting over cells with data validation

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

The solution

Well, this is not easy, truth. Undo operations with ScriptDB service no longer functions since Dec 18, 2014. Let’s then get the power of duck-typing! The idea is to check columns: if the range above or below has data validation, the current cell also must.

We’ll need a setup here (pity, but we have to):

const keepDropDownConfig = {
  sheets: {
   "Sheet1": 2,
   "Sheet2": 4,
   "Sheet4": 3
   // add more in fomat: "Sheet": <first row with data>
  }
}

Copy Sample Sheet:

https://docs.google.com/spreadsheets/d/1aQ35EslZ2OKXDdKOsNK7YpPF2hLG8a8drIwqabnvBUM/copy

To install this to your existing spreadsheet, please use this code:

const keepDropDownConfig = {
  sheets: {
   "Sheet1": 2, // CHANGE
   "Sheet2": 4, // CHANGE
   "Sheet4": 3  // CHANGE
   // add more in fomat: "Sheet": <first row with data>
  }
}

/**
 * onEdit
 * 
 * Trigger OnEditEvent for Google Sheets
 * https://developers.google.com/apps-script/guides/triggers/OnEditEvents
 * 
 * @typedef {Object} OnEditEvent
 * @property {SpreadsheetApp.Range} range
 * @property {SpreadsheetApp.Spreadsheet} source
 * @property {*} oldValue
 */

/**
 * @param {OnEditEvent} e
 */
function onEdit(e) {

  const sheet = e.range.getSheet();
  const config = keepDropDownConfig;

  const rowFrom = config.sheets[sheet.getName()];
  if (!rowFrom) {
    console.log('do not check this sheet');
    return;
  }

  let numRow = e.range.getRow();
  if (numRow < rowFrom) {
    console.log('Do not check this row');
    return;
  }

  var rowCheck = numRow + 1;
  if (rowCheck > sheet.getLastRow()) {
    rowCheck = numRow - 1;
  }
  if (rowCheck < rowFrom) {
    console.log('nothing to compare with');
    return;
  }
  var numRows = e.range.getHeight();
  var numColumns = e.range.getWidth();

  let fixTasks = [], fixTask;
  const rowOffset = rowCheck - numRow + numRows - 1;
  for (var i = 0; i < numColumns; i++) {
    fixTask = getFixValidationTask_(e, rowOffset, numRows, i);
    if (fixTask) {
      fixTasks.push(fixTask);
    }
    fixTask = null;
  }

  if (!fixTasks.length) {
    console.log('Nothing to fix, clear');
    return
  }

  Browser.msgBox(
    '⚠️Warning', 
    'You tried to overwrite data validation.\\n' +
    'The script will fix this.' +
    '\\n\\nPlease restore original values', 
    Browser.Buttons.OK);

  fixTasks.forEach(fixBrokenDataValidation_);

}

/**
 * @typedef {Object} DVFixTask
 * @property {DataValidation} validation
 * @property {SpreadsheetApp.Range} range
 * @property {*} replaceValue
 */

/**
 * @param {DVFixTask} task
 */
function fixBrokenDataValidation_(task) {
  task.range.setDataValidation(task.validation);
  task.range.setValue(task.replaceValue);
}


/**
 * @param {OnEditEvent} e
 * @param {Number} numColumn
 * 
 * @returns {DVFixTask}
 */
function getFixValidationTask_(e, rowOffset, numRows, columnOffset) {
  const rangeIn = e.range;
  var range = rangeIn.offset(0,columnOffset,numRows,1);
  let validation = rangeIn.offset(0,columnOffset,1,1).getDataValidation();
  var cellCheck = rangeIn.offset(rowOffset, columnOffset, 1, 1);
  var validationCheck = cellCheck.getDataValidation();
  if (!validationCheck) {
    console.log('no validation in check range');
    return null;
  }

  if (validationsAreEqual_(validation, validationCheck)) {
    console.log('all right, validations are same');
    return null;
  }

  let replaceValue = e.oldValue;

  if(replaceValue === undefined) {
    replaceValue = '';
  }

  /** @type DVFixTask */
  const result = {
    range,
    replaceValue,
    validation: validationCheck
  }
  return result;
}

/**
 * @apram {DataValidation} validation1
 * @apram {DataValidation} validation2
 * 
 * @returns {Boolean}
 */
function validationsAreEqual_(validation1, validation2) {
  if (!validation1) {
    return false;
  }
  if (!validation2) {
    return false;
  }
  if (validation1.getCriteriaType() !== validation2.getCriteriaType()) {
    return false;
  }
  return true;
}

Change settings to yours and you are ready to go.

There are different scenarios. You may change the script to show notification, and not restore validation. If a user clicks [Ctrl + Z], values and validations will be restored.