How do i write a code for google sheets that would limit my characters to 11 while at the same time prevent duplication as each new data is entered?

Question

The idea here is that each cell in column A, has to be 11 digits, not more, not less and there cannot be any duplicate at any time data is being entered such that a duplicate entry is rejected. I tried to use data validation, but I noticed I could only apply only one data validation rule. I thought of using google app script but I'm still new to programming. Yimage of the google sheetour assistance is highly appreciated.

function myDatavalidation() {
  var Len=('B1:B')=11
  var range = SpreadsheetApp.getActive().getRange('B1:B')
  var validation = SpreadsheetApp.newDataValidation().requireFormulaSatisfied(Len);
  range.setDataValidation(validation)
}

Answer 1

I believe your goal is as follows.

• When a value is put to the column "B", you want to check whether the inputted value is the length of 11 and duplicated in the column "B". When the inputted value is not the length of 11 or duplicated in the column "B", you don't want to input it.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you run this script, please edit the column "B" of the "Sheet1" (in this sample). By this, the script is run by a simple trigger.

function onEdit(e) {
  const sheetNames = ["Sheet1"]; // Please set the sheet names you want to run the script.
  const range = e.range;
  const sheet = range.getSheet();
  if (!sheetNames.includes(sheet.getSheetName()) || range.columnStart != 2) return;
  const value = range.getDisplayValue();
  const values = sheet.getRange("B1:B" + sheet.getLastRow()).getDisplayValues().map(([b]) => b);
  values.splice(range.rowStart - 1, 1);
  if (value.length != 11 || values.includes(value)) {
    range.clearContent();
  }
}

Note:

• This script is run by the simple trigger of OnEdit. So when you directly run this script, an error like TypeError: Cannot read property 'range' of undefined occurs. Please be careful about this.

Reference:

• Simple Triggers

Answer 2

I found this article that has the Apps script code on how to eliminate duplicates in a range

/**
* remove duplicate rows from Google Sheets data range
*/
function removeDupRows() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  
  // change the row number of your header row
  var startRow = 7;
  
  // get the data
  var range = sheet.getRange(startRow,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  
  // remove duplicates with helper function
  var dedupRange = arrayUnique(range);
  Logger.log(dedupRange);
  
  // check if duplicate sheet exists already, if not create new one
  if (ss.getSheetByName('Sheet1 Duplicates Removed')) { 
    
    // case when dedup sheet already exists
    var dedupSheet = ss.getSheetByName('Sheet1 Duplicates Removed');
    var lastRow = Math.max(dedupSheet.getLastRow(),1);
    var lastColumn = Math.max(dedupSheet.getLastColumn(),1);
    
    // clear out any previous de-duplicate data
    dedupSheet.getRange(1,1,dedupSheet.getLastRow(),dedupSheet.getLastColumn()).clear();
    
    // replace with new de-duplicated data
    dedupSheet.getRange(1,1,dedupRange.length,sheet.getLastColumn()).setValues(dedupRange);
  }
  else {
    
    // case when there is no dedup sheet
    var dedupSheet = ss.insertSheet('Sheet1 Duplicates Removed',0);
    dedupSheet.getRange(1,1,dedupRange.length,dedupRange[0].length).setValues(dedupRange);
  }
  
  // make the de-duplicate sheet the active one
  dedupSheet.activate();
  
}

/** 
* helper function returns unique array
*/
function arrayUnique(arr) {
  
  var tmp = [];
  
  // filter out duplicates
  return arr.filter(function(item, index){
    
    // convert row arrays to strings for comparison
    var stringItem = item.toString(); 
    
    // push string items into temporary arrays
    tmp.push(stringItem);
    
    // only return the first occurrence of the strings
    return tmp.indexOf(stringItem) >= index;
    
  });
}

And this function can help you limit the number of characters in the active cell:

function onEdit(e) {
  var limit = 5;
  if(e.value.length > limit) {
    e.range.setValue(e.value.substring(0, limit) + "...");
    e.range.setNote("Remaining text was: \n....." + e.value.substring(limit));
  }
}