0

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?

0
  • 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

  • Lock down spreadsheet won't work. They need to have Edit access. I don't know who "they" are, they are just random people that also happen to backed the same Kickstarter project as me. Not sure if protecting a sheet will help. Let's say you protect Sheet2 and the users enter their data in Source. People who spam the Source sheet could copy and paste the same thing in multiple cells or rows. They could add random things to it. Your filter will have to constantly be updated in order to remove the duplicates and there could be many different variations of a sentence or word that can be duped. – shadowz1337 Feb 18 at 20:39
  • If you could provide me with an example on how to use filter? I've looked it up before but don't really see how this could remove AND prevent dupe data. – shadowz1337 Feb 18 at 20:43
  • you use filter like this: i.stack.imgur.com/fOoA0.png. to prevent duplicates you will need to wrap it into UNIQUE - i.stack.imgur.com/PY9dB.png – user0 Feb 18 at 21:52
  • Thanks. I know how unique and filter works, but I'm looking for a solution that can do in-place edit. For example, this script works quite well in automatically clearing any newly added entries containing profanity words - webapps.stackexchange.com/a/107437/209878 The script runs automatically when there's a new edit. I want something similar, where any duplicate words are automatically cleared. I don't want to create additional columns or sheets. I want users to use existing columns and as soon as a word is duplicated, it automatically gets cleared. – shadowz1337 Feb 19 at 0:05
  • 1
    Yes. I'm found a script that does a "sheet.getRange('A2:A999').clearContent()" and it sort of works. But as soon as I add data validation into the column, it somehow conflicts with the script. – shadowz1337 Feb 19 at 8:06
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.