Protect spreadsheet against duplicate data and sheet addition/deletion

Question

There are some people that love to troll and spam my Google spreadsheet with useless info. Google Sheet lacks granular permission restriction.

I need to prevent duplicate data from being submitted. I can prevent manual duplicate data entry by setting Data Validation with a custom formula:

=countif(A$1:A1,A1)=1

However, this only prevents manual data entry. If users fill the cells down, or if they copy and paste data into multiple cells, Data Validation won't work.

I also need to prevent them from adding new sheets or deleting an entire sheet.

How can I achieve this?

Answer 1

• 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

Answer 2

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.

Answer 3

I found simple solution for this.

=COUNTIF(master!T3:T15,C3)

I used above formula in cell and it returns "0" if no duplicate found.

It returns >0 values i.e 1,2,3,.. if found duplicates.

after this just go into appscript and paste below code at the beginning of your script var dupentry=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("yoursheetname").getRange("A17").getValue(); // this is the cell in which we have entered "=COUNTIF(master!T3:T15,C3)" formula

if(dupentry=='0'){

//write the script here for whatever you want to do if no duplicates found

}

else SpreadsheetApp.getUi().alert("Error duplicate entry found");

return;}