Protect spreadsheet against duplicate data and sheet addition/deletion

Found my own solution.

Let's say column A is where you want users to enter data. Due to the fact that trolls may copy and paste useless data in column A, you might run into something like this:

Useful Data 1
Useful Data 2
Troll Data 1
Troll Data 1
Troll Data 1
Troll Data 1

So to minimize the amount of duplicate data, we need to clone column A. I'm using column C as a clone of column A. In the first cell (C2) of column C (ignore C1 as this is used as header), enter the following:

=arrayformula( unique( iferror( vlookup(A2:A, A2:A, 1, false) ) ) )

Then, in your Google Sheets, create a Google Script that links to the sheet by going to Tools -> Script Editor.

Add the following:

function removeDuplicates5() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange('C2:C999').getValues()
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
if(row.join().toLowerCase() == newData[j].join().toLowerCase()){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}

sheet.getRange('A2:A999').clearContent();
// sheet.getRange('A:A').clearContent();
sheet.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
}

This assumes you're using column A and column C as explained above. Set the trigger to run onEdit. Basically, Google Script will monitor your Google Sheets and anytime there's an edit, it will detect the change and remove any duplicate data.

Of course, trolls can still spam by entering unique useless data, but at least this cuts it down by a bit.

OK, what about removing profanity word entries made by the trolls? Well, use the following Google Script found here: Preventing certain words from appearing on a Google Sheets

Lastly, you can set Data Validation in the columns (or rows or cells) to also filter any words you don't want the trolls to use. Something like this (assume for column A):

=if(REGEXMATCH(LOWER(A:A),"word1|word2|word3"),False,True)

Hope this helps someone.

• you can lock down the spreadsheet:

  

• you can protect the sheets

  

• if you need users to be able to make input then create a new spreadsheet for them with some instructions how to behave and then filter out gathered data with QUERY or FILTER and import them to your spreadsheet with IMPORTRANGE from where all the email action will happen